Income And ExpenseTracker

 Here is a script to track income and expenses in a Google Sheet. It automatically timestamps each entry and calculates the balance based on the values entered in the income and expense columns.Double tap the image to see clearly

 

Instructions

Open your Google Sheet and create a sheet named Sheet1. Go to Extensions > Apps Script and paste the a script given below. Save the script and close the Apps Script editor. Run the 'setInitialValues' function to set up the initial values in your sheet. Now, whenever you enter data in columns B, C, or D, the script will automatically add a timestamp in column A and update the balance in column E.

Copy the AppScript code below:

function setInitialValues() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1'); sheet.getRange('A1').setValue('AUTO TIME STAMP'); sheet.getRange('B1').setValue('PARTICULARS'); sheet.getRange('C1').setValue('INCOME'); sheet.getRange('D1').setValue('EXPENSE'); sheet.getRange('E1').setValue('BALANCE'); // Simulate an edit event to trigger onEdit function var fakeEvent = { source: SpreadsheetApp.getActiveSpreadsheet(), range: sheet.getRange('C1') }; onEdit(fakeEvent); } function onEdit(e) { var sheet = e.source.getSheetByName('Sheet1'); // Get the sheet by name var editedRange = e.range; // Check if edited column is B (column index 2), C (column index 3), or D (column index 4) // and row is not the header row if ((editedRange.getColumn() >= 2 && editedRange.getColumn() <= 4) && editedRange.getRow() > 1) { var row = editedRange.getRow(); var existingTimestamp = sheet.getRange(row, 1).getValue(); // Get the existing timestamp value in column A // Check if the edited cell is in column B (index 2), C (index 3), or D (index 4) if (editedRange.getColumn() == 2 || editedRange.getColumn() == 3 || editedRange.getColumn() == 4) { if (!existingTimestamp) { // If there is no existing timestamp, add a new one var date = new Date(); // Get current date and time sheet.getRange(row, 1).setValue(date); // Set value in column A of the same row } // If there is an existing timestamp, do nothing and keep it unchanged } } // Update the formula in column E when columns B, C, or D are edited if (editedRange.getColumn() >= 2 && editedRange.getColumn() <= 4) { var lastRow = sheet.getLastRow(); for (var i = 3; i <= lastRow; i++) { var colCValue = sheet.getRange(i, 3).getValue(); // Get value in column C var colDValue = sheet.getRange(i, 4).getValue(); // Get value in column D if (colCValue !== "" || colDValue !== "") { // Check if column C or D is not blank sheet.getRange(i, 5).setFormula('=E' + (i - 1) + '+C' + i + '-D' + i); } else { sheet.getRange(i, 5).clearContent(); // Clear the content if C and D are blank } } } }

---