Created
June 29, 2023 15:10
-
-
Save bpwebs/9b04e08c49c7548a12ef1d087380e8a8 to your computer and use it in GitHub Desktop.
Create Your Own Personal Finance Tracker App with Google Sheets
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<script src="https://www.gstatic.com/charts/loader.js"></script> | |
<script> | |
// Load the Visualization API and the corechart package. | |
google.charts.load('current', {'packages':['corechart']}); | |
// Set a callback to run when the Google Visualization API is loaded. | |
google.charts.setOnLoadCallback(drawCharts); | |
function drawCharts() { | |
google.script.run.withSuccessHandler(drawChartIncomeVsExpenditure).getCurrentMonthIncomeAndExpenditure(); | |
google.script.run.withSuccessHandler(drawChartExpenditureByCategory).getCurrentMonthExpenditureByCategory(); | |
google.script.run.withSuccessHandler(drawChartIncomeByCategory).getCurrentMonthIncomeByCategory(); | |
} | |
function drawChartIncomeVsExpenditure(data){ | |
try{ | |
var chartData = google.visualization.arrayToDataTable(data); | |
var options = { | |
title: "Income Vs Expenditure for the Month", | |
is3D: true, | |
backgroundColor: 'black', | |
colors: ['green', 'red'], | |
titleTextStyle: { | |
color: 'white', | |
fontSize: 18, | |
bold: true, | |
textAlign: 'center' | |
}, | |
legend: { | |
textStyle: { | |
color: 'white' | |
}, | |
position: 'bottom' | |
} | |
} | |
var chart = new google.visualization.PieChart(document.getElementById('pie_chart_this_month_income_expenditure')); | |
chart.draw(chartData, options); | |
}catch (error){ | |
console.error("An error occurred while displaying the chart:", error); | |
} | |
} | |
function drawChartExpenditureByCategory(data){ | |
var chartData = google.visualization.arrayToDataTable(data); | |
var options = { | |
title: "Expenditure By Category for the Current Month", | |
is3D: true, | |
backgroundColor: 'black', | |
titleTextStyle: { | |
color: 'white', | |
fontSize: 18, | |
bold: true, | |
textAlign: 'center' | |
}, | |
legend: { | |
textStyle: { | |
color: 'white' | |
}, | |
position: 'bottom' | |
} | |
} | |
var chart = new google.visualization.PieChart(document.getElementById('pie_chart_this_month_expenditure')); | |
chart.draw(chartData, options); | |
} | |
function drawChartIncomeByCategory(data){ | |
var chartData = google.visualization.arrayToDataTable(data); | |
var options = { | |
title: "Income By Category for the Current Month", | |
is3D: true, | |
backgroundColor: 'black', | |
titleTextStyle: { | |
color: 'white', | |
fontSize: 18, | |
bold: true, | |
textAlign: 'center' | |
}, | |
legend: { | |
textStyle: { | |
color: 'white' | |
}, | |
position: 'bottom' | |
} | |
} | |
var chart = new google.visualization.PieChart(document.getElementById('pie_chart_this_month_income')); | |
chart.draw(chartData, options); | |
} | |
</script> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
const SETTINGS_SHEET = "Settings"; | |
const ENTRY_CAT_RANGE = "A2:B"; | |
const PAYMENT_METHOD_RANGE = "D2:D"; | |
const DATA_SHEET = "Data"; | |
const INCOME_DATA_SHEET = "Income"; | |
const INCOME_RANGE = "A1:H" | |
const EXPENDITURE_DATA_SHEET = "Expenditure"; | |
const EXPENDITURE_RANGE = "A1:H"; | |
/** | |
* CREATE CUSTOM MENU IN GOOGLE SHEETS MENU BAR | |
*/ | |
function onOpen() { | |
let ui = SpreadsheetApp.getUi(); | |
ui.createMenu("My Menu") | |
.addItem("Sidebar Form","showFormInSidebar") | |
.addToUi(); | |
} | |
/** | |
* CREATE HTML WEB APP | |
*/ | |
function doGet(){ | |
let template = HtmlService.createTemplateFromFile('WebApp'); | |
let html = template.evaluate().setTitle('My Budget'); | |
html.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL); | |
html.addMetaTag('viewport', 'width=device-width, initial-scale=1'); | |
return html; | |
} | |
/** | |
* OPEN THE FORM IN SIDEBAR | |
*/ | |
function showFormInSidebar() { | |
let form = HtmlService.createTemplateFromFile('Sidebar').evaluate().setTitle('My Budget'); | |
SpreadsheetApp.getUi().showSidebar(form); | |
} | |
/** | |
* PROCESS THE formObject RETURNED FORM SIDEBAR FORM | |
*/ | |
function processForm(formObject){ | |
let sheet = SpreadsheetApp.getActive().getSheetByName(DATA_SHEET); | |
if(formObject.entryType=="income"){ | |
sheet.appendRow([ | |
new Date().toLocaleString(), | |
formObject.entryType, | |
formObject.entryCategory, | |
formObject.paymentMethod, | |
formObject.transactionDate, | |
formObject.description, | |
Number (formObject.amount), | |
, | |
formObject.remarks | |
//Add your new field names here | |
]); | |
}else if(formObject.entryType=="expenditure"){ | |
sheet.appendRow([ | |
new Date().toLocaleString(), | |
formObject.entryType, | |
formObject.entryCategory, | |
formObject.paymentMethod, | |
formObject.transactionDate, | |
formObject.description, | |
, | |
Number (formObject.amount), | |
formObject.remarks | |
//Add your new field names here | |
]); | |
} | |
} | |
/** | |
* GET INPUT CATEGORIES FOR THE "Entry Category" DROPDOWN | |
*/ | |
function getInputCategories(){ | |
const entryCategories = readDataFromSheets(SETTINGS_SHEET,ENTRY_CAT_RANGE); | |
const filteredEntryCategories = filterData(entryCategories); | |
return filteredEntryCategories; | |
} | |
/** | |
* GET PAYMENT METHODS FOR THE "Payment Method" DROPDOWN LIST | |
*/ | |
function getPaymentMethods(){ | |
const entryCategories = readDataFromSheets(SETTINGS_SHEET,PAYMENT_METHOD_RANGE); | |
const filteredPaymentMethods = filterData(entryCategories); | |
return filteredPaymentMethods; | |
} | |
/** | |
* GET THE SUM OF INCOME AND EXPENDITURE FOR THE CURRENT MONTH TO DRAW A PIE CHART | |
*/ | |
function getCurrentMonthIncomeAndExpenditure(){ | |
const currentMonthIncome = getDataForCurrentMonth(INCOME_DATA_SHEET,INCOME_RANGE); | |
const currentMonthExpenditure = getDataForCurrentMonth(EXPENDITURE_DATA_SHEET,EXPENDITURE_RANGE); | |
currentMonthIncome.shift(); | |
currentMonthExpenditure.shift(); | |
let groupedIncomeAndExpenditure = groupData([...currentMonthIncome,...currentMonthExpenditure],1,6); | |
groupedIncomeAndExpenditure.unshift(['Entry Type','Amount']); | |
return groupedIncomeAndExpenditure; | |
} | |
function getCurrentMonthExpenditureByCategory(){ | |
const currentMonthExpenditure = getDataForCurrentMonth(EXPENDITURE_DATA_SHEET,EXPENDITURE_RANGE); | |
currentMonthExpenditure.shift(); | |
groupedExpenditure = groupData(currentMonthExpenditure,2,6); | |
groupedExpenditure.unshift(['Category','Amount']); | |
Logger.log(groupedExpenditure); | |
return groupedExpenditure; | |
} | |
function getCurrentMonthIncomeByCategory(){ | |
const currentMonthIncome = getDataForCurrentMonth(INCOME_DATA_SHEET,INCOME_RANGE); | |
currentMonthIncome.shift(); | |
groupedIncome = groupData(currentMonthIncome,2,6); | |
groupedIncome.unshift(['Category','Amount']); | |
Logger.log(groupedIncome); | |
return groupedIncome; | |
} | |
function groupData(data,keyCol,valCol){ | |
var groupedData = data.reduce(function(acc, row) { | |
var key = row[keyCol]; | |
var value = row[valCol]; | |
if (!acc[key]) { | |
acc[key] = 0; | |
} | |
acc[key] += value; | |
return acc; | |
}, {}); | |
var result = Object.keys(groupedData).map(function(key) { | |
return [key, groupedData[key]]; | |
}); | |
return result; | |
} | |
/** | |
* READ THE DATA FROM GOOGLE SHEETS | |
*/ | |
function readDataFromSheets(sheetsName,dataRange){ | |
const ss = SpreadsheetApp.getActive().getSheetByName(sheetsName); | |
const range = ss.getRange(dataRange); | |
const values = range.getValues(); | |
return values; | |
} | |
/** | |
* REMOVE EMPTY ROWS iN THE DATA RANGE. | |
* ALSO CONVERT DATE TO STRING, THE CODE WILL NOT WORK OTHERWISE. | |
*/ | |
function filterData(data) { | |
const filteredData = data.filter(row => row.some(cell => cell !== "" || cell instanceof Date)); | |
const formattedData = filteredData.map(row => row.map(cell => { | |
if (cell instanceof Date) { | |
return cell.toLocaleDateString(); | |
} | |
return cell; | |
})); | |
return formattedData; | |
} | |
/** | |
* GET THE DATA FOR THE CURRENT MONTH OF THE CURRENT YEAR | |
*/ | |
function getDataForCurrentMonth(sheetName, dataRange) { | |
let data = readDataFromSheets(sheetName,dataRange); | |
let headerRow = data[0]; | |
let currentDate = new Date(); | |
let currentMonth = currentDate.getMonth() + 1; // Adding 1 to get the current month (January is 0) | |
let currentYear = currentDate.getFullYear(); // Get the current year | |
let filteredData = data.filter(function(row) { | |
let date = row[0]; // Assuming the date column is the first column, modify accordingly if different | |
if (date instanceof Date) { | |
let month = date.getMonth() + 1; // Adding 1 to get the month (January is 0) | |
let year = date.getFullYear(); // Get the year of the date | |
return month === currentMonth && year === currentYear; | |
} | |
return false; | |
}); | |
filteredData.unshift(headerRow); | |
return filteredData; | |
} | |
/** | |
* INCLUDE HTML PARTS, EG. JAVASCRIPT, CSS, OTHER HTML FILES | |
*/ | |
function include(filename) { | |
return HtmlService.createHtmlOutputFromFile(filename).getContent(); | |
} | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha3/dist/css/bootstrap.min.css" rel="stylesheet" | |
integrity="sha384-KK94CHFLLe+nY2dmCWGMq91rCGa5gtU4mk92HdvYe+M/SXH301p5ILy+dN9+nJOZ" crossorigin="anonymous"> | |
<style> | |
//Your CSS goes here | |
</style> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<br> | |
<form class="form" id="budgetDetails" onsubmit="handleFormSubmit(this)"> | |
<div class="form-group"> | |
<label for="entryType"class="form-label">Entry Type</label><br> | |
<input type="radio" name="entryType" id="income" value="income"> | |
<label for="income" class="btn btn-sm btn-success">Income</label> | |
<input type="radio" name="entryType" id="expenditure" value="expenditure"> | |
<label for="expenditure" class="btn btn-sm btn-danger">Expenditure</label> | |
</div> | |
<div class="form-group"> | |
<label for="entryCategory" class="form-label">Entry Category</label> | |
<select class="form-select form-select-sm" id="entryCategory" name="entryCategory" required> | |
<option>--Select Entry Category--</option> | |
</select> | |
</div> | |
<div class="form-group"> | |
<label for="paymentMethod" class="form-label">Payment Method</label> | |
<select class="form-select form-select-sm" id="paymentMethod" name="paymentMethod" required> | |
<option>--Select Payment Method--</option> | |
</select> | |
</div> | |
<div class="form-group"> | |
<label for="transactionDate">Transaction Date</label> | |
<input id="transactionDate" class="form-control" type="date" name="transactionDate"/> | |
</div> | |
<div class="form-group"> | |
<label for="description">Description</label> | |
<input type="text" name="description" id="description" class="form-control"> | |
</div> | |
<div class="form-group"> | |
<label for="amount">Amount</label> | |
<input type="number" step="0.01" name="amount" id="amount" class="form-control" required> | |
</div> | |
<div class="form-group mb-3"> | |
<label for="remarks">Remarks</label> | |
<input type="text" name="remarks" id="remarks" class="form-control"> | |
</div> | |
<button type="submit" class="btn btn-primary">Submit</button> | |
</form> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<script> | |
window.addEventListener("load", functionInit, true); | |
//Initialize functions onload | |
function functionInit(){ | |
var inputCategoryList = ""; | |
preventFormSubmit(); | |
getAllInputCategories(); | |
getPaymentMethods(); | |
}; | |
// Prevent forms default behaviour (Prevent reloading the page) | |
function preventFormSubmit() { | |
var forms = document.querySelectorAll('form'); | |
for (var i = 0; i < forms.length; i++) { | |
forms[i].addEventListener('submit', function(event) { | |
event.preventDefault(); | |
}); | |
} | |
} | |
/** | |
* The handleFormSubmit() function passes the formObject to the processForm | |
* function in the Code.gs file | |
*/ | |
function handleFormSubmit(formObject) { | |
google.script.run.processForm(formObject); | |
document.getElementById("budgetDetails").reset(); | |
} | |
/** | |
* The getAllInputCategories() function call the getInputCategories() function | |
* in the Code.gs file during the page load and retrieve all the input categories | |
* defined in the "Settings" tab of the Google Sheet. | |
*/ | |
function getAllInputCategories(){ | |
google.script.run.withSuccessHandler(createEntryCategoryLists).getInputCategories(); | |
} | |
/** | |
* The function createEntryCategoryLists() separates the income and expenditure categories | |
* from the list returned by the getInputCategories() *function in the Code.gs file. | |
* It then creates two lists, named Income and Expenditure containing the respective categories. | |
*/ | |
function createEntryCategoryLists(inputCategories){ | |
const incomeArray = []; | |
const expenditureArray = []; | |
inputCategories.forEach(function (entry) { | |
const category = entry[0]; | |
const value = entry[1]; | |
if (category === "Income") { | |
incomeArray.push(value); | |
} else if (category === "Expenditure") { | |
expenditureArray.push(value); | |
} | |
}); | |
inputCategoryList = { | |
income: incomeArray, | |
expenditure: expenditureArray | |
}; | |
} | |
/** | |
* The following adds event listener to the income and expenditure radio buttons. | |
* The options list for the entryCategory dropdown list is assigned based on the | |
* selection of the radio button. | |
*/ | |
var radioButtons = document.getElementsByName('entryType'); | |
radioButtons.forEach(function (radioButton) { | |
radioButton.addEventListener('click', function () { | |
var selectedOption = this.value; | |
if(selectedOption=="income"){ | |
populateDDL(inputCategoryList.income,"entryCategory"); | |
}else if(selectedOption=="expenditure"){ | |
populateDDL(inputCategoryList.expenditure,"entryCategory"); | |
} | |
}); | |
}); | |
/** | |
* The following getPaymentMethods() function calls the getPaymentMethods() in the Code.gs file. | |
* Then the return values pass to the populatePaymentMethodsDDL() function. | |
*/ | |
function getPaymentMethods(){ | |
google.script.run.withSuccessHandler(populatePaymentMethodsDDL).getPaymentMethods(); | |
} | |
/** | |
* The populatePaymentMethodsDDL() function receives the payment method list from the | |
* getPaymentMethods() function above and populate the payment methods dropdown list with it. | |
*/ | |
function populatePaymentMethodsDDL(paymentMethods){ | |
populateDDL(paymentMethods,"paymentMethod"); | |
} | |
/** | |
* The following function populates any dropdown list of the given ID from the | |
* list of values it receives. | |
*/ | |
function populateDDL(values,id) { | |
var list = document.getElementById(id); | |
list.innerHTML = ''; // Clear the existing content of the dropdown | |
for (var i = 0; i < values.length; i++) { | |
var option = document.createElement("option"); | |
option.value = values[i]; | |
option.text = values[i]; | |
list.appendChild(option); | |
} | |
} | |
</script> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<!doctype html> | |
<html lang="en"> | |
<head> | |
<!-- Required meta tags --> | |
<meta charset="utf-8"> | |
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no"> | |
<?!= include('CSS'); ?> <!-- See CSS.html file --> | |
</head> | |
<body class="bg-dark text-light"> | |
<div class="container"> | |
<?!= include('Form'); ?> <!-- See Form.html file --> | |
<div id="pie_chart_this_month_income_expenditure" class="row mt-3"></div> | |
<div id="pie_chart_this_month_expenditure" class="row mt-3"></div> | |
<div id="pie_chart_this_month_income" class="row mt-3"></div> | |
</div> | |
<?!= include('JavaScript'); ?> <!-- See JavaScript.html file --> | |
</body> | |
<?!= include('ChartsJs'); ?> | |
</html> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment