Create Your Own Personal Finance Tracker App with Google Sheets

ChartsJs.html

<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>

Code.gs

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();
}

CSS.html

<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>

Form.html

<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>

JavaScript.html

<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>

Sidebar.html

<!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>
    
  <?!= include('JavaScript'); ?> <!-- See JavaScript.html file -->
  </body>
</html>

WebApp.html

<!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>