Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Some Google Apps Script for logging finances in a Google Spreadsheet.
/**
* Retrieves all the rows in the active spreadsheet that contain data and logs the
* values for each row.
* For more information on using the Spreadsheet API, see
* https://developers.google.com/apps-script/service_spreadsheet
*/
function readRows() {
var sheet = SpreadsheetApp.getActiveSheet();
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();
for (var i = 0; i <= numRows - 1; i++) {
var row = values[i];
Logger.log(row);
}
};
function logNetWorth() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var overview = sheet.getSheetByName("Financial Overview");
var history = sheet.getSheetByName("History");
var netWorthRange = overview.getRange("B13");
var col = 3;
var row = 1;
do{
row++;
}while(!history.getRange(row,col).isBlank())
var nwHist = history.getRange(row,col+1);
var nwHistDate = history.getRange(row,col);
nwHist.setValue(netWorthRange.getValue());
nwHistDate.setValue(new Date());
};
function logAssetsValue() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var overview = sheet.getSheetByName("Financial Overview");
var history = sheet.getSheetByName("History");
var assetValueRange = overview.getRange("B10");
var col = 5;
var row = 1;
do{
row++;
}while(!history.getRange(row,col).isBlank())
var nwAsstVal = history.getRange(row,col+1);
var nwAsstDate = history.getRange(row,col);
nwAsstVal.setValue(assetValueRange.getValue());
nwAsstDate.setValue(new Date());
};
function logLiabilitiesValue() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var overview = sheet.getSheetByName("Financial Overview");
var history = sheet.getSheetByName("History");
var liabilitiesRange = overview.getRange("B11");
var col = 7;
var row = 1;
do{
row++;
}while(!history.getRange(row,col).isBlank())
var nwLiabVal = history.getRange(row,col+1);
var nwLiabDate = history.getRange(row,col);
nwLiabVal.setValue(liabilitiesRange.getValue());
nwLiabDate.setValue(new Date());
};
function logCashFlowValue() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var overview = sheet.getSheetByName("Financial Overview");
var history = sheet.getSheetByName("History");
var cashFlowRange = overview.getRange("B7");
var col = 1;
var row = 1;
do{
row++;
}while(!history.getRange(row,col).isBlank())
var nwCF = history.getRange(row,col+1);
var nwCFDate = history.getRange(row,col);
nwCF.setValue(cashFlowRange.getValue());
nwCFDate.setValue(new Date());
};
function logCashOnHandValue() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var bankAccount = sheet.getSheetByName("Bank Accounts");
var history = sheet.getSheetByName("History");
var cohRange = bankAccount.getRange("I1");
var col = 9;
var row = 1;
do{
row++;
}while(!history.getRange(row,col).isBlank())
var cohVal = history.getRange(row,col+1);
var cohDate = history.getRange(row,col);
cohVal.setValue(cohRange.getValue());
cohDate.setValue(new Date());
};
function logAllValues() {
logNetWorth();
logAssetsValue();
logLiabilitiesValue();
logCashFlowValue();
logCashOnHandValue();
}
/**
* Adds a custom menu to the active spreadsheet, containing a single menu item
* for invoking the readRows() function specified above.
* The onOpen() function, when defined, is automatically invoked whenever the
* spreadsheet is opened.
* For more information on using the Spreadsheet API, see
* https://developers.google.com/apps-script/service_spreadsheet
*/
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name : "Read Data",
functionName : "readRows"
},
{
name : "Log Net Worth",
functionName: "logNetWorth"
}
,
{
name : "Log Assets Value",
functionName: "logAssetsValue"
}
,
{
name : "Log Liabilities Value",
functionName: "logLiabilitiesValue"
}
,
{
name : "Log Cash Flow Value",
functionName: "logCashFlowValue"
}
,
{
name : "Log Cash on Hand Value",
functionName: "logCashOnHandValue"
}
,
{
name : "Log All Values",
functionName: "logAllValues"
}];
sheet.addMenu("Script Center Menu", entries);
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.