Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
A gist for Financial Goal Tracker Google Appscript
var name = 'FinancesTracker'
var folderName = 'Goals'
const sheetNames = ["Finances", "Target"]
function doPost(request){
var action = request.parameter.action;
if(action == 'postTarget'){
return postTarget(request);
}else if(action == 'postEntry'){
return postEntry(request);
}else if(action == 'deleteEntry'){
return deleteEntry(request);
}
}
function doGet(request){
var action = request.parameter.action;
if(action == 'getTarget'){
return getTarget();
}else if(action == 'getEntries'){
return getEntries();
}
}
function postEntry(request){
var sheet = getEntrySheet();
try{
var entryPayload = JSON.parse(request.postData.contents);
sheet.appendRow([Utilities.getUuid(), entryPayload.source, entryPayload.amount, entryPayload.type, new Date(entryPayload.date)]);
}catch(exc){
var result = {"success": false, "message": exc};
return ContentService
.createTextOutput(JSON.stringify(result))
.setMimeType(ContentService.MimeType.JSON);
}
return getEntries();
}
function deleteEntry(request){
try{
var sheet = getEntrySheet();
var values = sheet.getDataRange().getValues();
/// Row index starts at 1
for(var i = 1; i <= values.length; i++){
if(values[i][0] == request.parameter.id){
sheet.deleteRow(i)
}
}
}catch(exc){
var result = {"success": false, "message": exc};
return ContentService
.createTextOutput(JSON.stringify(result))
.setMimeType(ContentService.MimeType.JSON);
}
return getEntries();
}
function getEntries(success, message){
var sheet = getEntrySheet();
sheet.getDataRange().sort({column: 5, ascending: false});
var values = sheet.getDataRange().getValues();
var entries = []
var totalCredit = 0;
var totalDebit = 0;
var barChartData = {};
// Set index 1 as the minimum to avoid fetching the headers
for (var i = values.length - 1; i >= 1; i--) {
var row = values[i];
var entry = {};
entry['ID'] = row[0];
entry['source'] = row[1];
entry['amount'] = row[2];
entry['type'] = row[3];
entry['date'] = Utilities.formatDate(new Date(row[4]), Session.getScriptTimeZone(),"dd MMM yyyy");
var monthYear = Utilities.formatDate(new Date(row[4]), Session.getScriptTimeZone(),"MMM yyyy");
var dateChartData = barChartData[monthYear];
if(dateChartData == null){
dateChartData = {
"credit" : 0,
"debit": 0,
}
}
var type = entry['type'].toString().toLowerCase();
if(type == "debit"){
// Sum grand total debit
totalDebit += entry['amount'];
// Sum total debit for this month
dateChartData['debit'] = dateChartData['debit'] + entry['amount'];
}else if(type == "credit"){
// Sum grand total credit
totalCredit += entry['amount'];
// Sum total debit for this month
dateChartData['credit'] = dateChartData['credit'] + entry['amount'];
}
barChartData[monthYear] = dateChartData;
// Push each row object in data
entries.push(entry);
}
if(success == null){
success = true;
}
if(message == null){
message = "Sucess";
}
var targetCell = getTargetCell();
var target = targetCell.getValue();
var completionPercentage = 0;
if(targetCell != null){
completionPercentage = (totalCredit-totalDebit)*100/target;
}
var data = {
"success": success,
"message" : message,
"data" : {
"total_credit" : totalCredit,
"total_debit" : totalDebit,
"completion_percentage" : completionPercentage,
"entries": entries,
'target' : target,
"bar_chart_data": barChartData,
},
}
Logger.log("getEntries() data response: %s", JSON.stringify(data));
return ContentService
.createTextOutput(JSON.stringify(data))
.setMimeType(ContentService.MimeType.JSON);
}
function getEntrySheet(){
var sheet = getSpreadsheet().getSheetByName(sheetNames[0]);
if(sheet == null){
sheet = getSpreadsheet().insertSheet(sheetNames[0]);
var range = sheet.getRange("A1:E1");
range.setValues([["ID", "Source", "Amount", "Type", "Date"]]);
formatHeaderRange(range);
}
return sheet;
}
function postTarget(request){
var sheet = getSpreadsheet().getSheetByName(sheetNames[1]);
var result = {"success": true};
try{
var target = JSON.parse(request.postData.contents);
try{
sheet = getSpreadsheet().insertSheet(sheetNames[1]);
}catch(e){
}
var range = sheet.getRange("A1:B1");
range.setValues([["Target" ,"Currency"]]);
formatHeaderRange(range)
sheet.getRange("A2:B2")
.setValues([[target.target, target.currency]]);
result = {
"success" : true,
"target": sheet.getRange(sheetNames[1] + "!A2"),
}
}catch(exc){
// If error occurs, throw exception
result = {"success": false, "message": exc};
return ContentService
.createTextOutput(JSON.stringify(data))
.setMimeType(ContentService.MimeType.JSON);
}
Logger.log("postTarget() result data %s", result)
// Return result
return getTarget();
}
function getTarget(){
var sheet = getSpreadsheet().getSheetByName(sheetNames[1]);
var data = {};
try{
if(sheet == null){
data = {
"success" : false,
"message" : "No target found"
}
}else{
data = {
"success" : true,
"data": getTargetCell().getValue(),
}
}
}catch(exc){
data = {
"success" : false,
"message" : exc
}
}
Logger.log("getTarget() data response %s", data)
return ContentService
.createTextOutput(JSON.stringify(data))
.setMimeType(ContentService.MimeType.JSON);
}
function getTargetCell(){
var sheet = getSpreadsheet().getSheetByName(sheetNames[1]);
return sheet.getRange(sheetNames[1] + "!A2");
}
function getSpreadsheet(){
var topFolder = DriveApp.getFoldersByName(folderName);
var file = null;
if(topFolder.hasNext()){
file = topFolder.next().getFilesByName(name);
}
if(file != null && file.hasNext()){
return SpreadsheetApp.open(file.next());
}else{
// Create a folder and get it's reference
var folder = DriveApp.createFolder(folderName);
// Create a spreadsheet and save it's reference in a variable
var newSpreadsheetFile = SpreadsheetApp.create(name)
// Get the new spreadsheet file
var file = DriveApp.getFileById(newSpreadsheetFile.getId());
// Add the file to the folder just created
folder.addFile(file);
// Delete the spreadsheet file from google drive root
DriveApp.getFolderById("root").removeFile(file);
// Open spreadsheet file
return SpreadsheetApp.open(file);
}
}
function formatHeaderRange(range){
range.setBackgroundRGB(12,128,144);
var headerStyle = SpreadsheetApp
.newTextStyle()
.setFontSize(12)
.setBold(true)
.setForegroundColor("#ffffff")
.build();
range.setTextStyle(headerStyle);
}
var name = 'FinancesTracker'
var folderName = 'Goals'
const sheetNames = ["Finances", "Target"]
function doPost(request){
var action = request.parameter.action;
if(action == 'postTarget'){
return postTarget(request);
}else if(action == 'postEntry'){
return postEntry(request);
}else if(action == 'deleteEntry'){
return deleteEntry(request);
}
}
function doGet(request){
var action = request.parameter.action;
if(action == 'getTarget'){
return getTarget();
}else if(action == 'getEntries'){
return getEntries();
}
}
function postEntry(request){
//TODO: Implement saving an entry
}
function deleteEntry(request){
//TODO: Implement deleting an entry
}
function getEntries(success, message){
//TODO: Implement getting entries
}
function getEntrySheet(){
//TODO: Implement getting entry sheet
}
function postTarget(request){
// Retrieve a Sheet by name, in this case "Target"
var sheet = getSpreadsheet().getSheetByName(sheetNames[1]);
// Define a map response
var result = {"success": true};
try{
// Get the target amount from the request payload
var target = JSON.parse(request.postData.contents);
try{
// Try inserting the Target sheet name, if doesn't exist.
// If it does exist, an exception is thrown
sheet = getSpreadsheet().insertSheet(sheetNames[1]);
}catch(e){
}
// The first row of column A and B is gotten here,
var range = sheet.getRange("A1:B1");
// Set the header value
range.setValues([["Target" ,"Currency"]]);
// We can also use App script to format a cell or cells
// this helper method we created does that
formatHeaderRange(range)
// Set the target actual values in second row of column A and B
sheet.getRange("A2:B2")
.setValues([[target.target, target.currency]]);
}catch(exc){
// If an error occurs, while trying the above, return an error response
result = {"success": false, "message": exc};
return ContentService
.createTextOutput(JSON.stringify(data))
.setMimeType(ContentService.MimeType.JSON);
}
// I use this to log the result in the console
Logger.log("postTarget() result data %s", result)
// Return target if insertion was successful
return getTarget();
}
function getTarget(){
// Get Target sheet
var sheet = getSpreadsheet().getSheetByName(sheetNames[1]);
var data = {};
try{
// If the sheet is null, set the result data to error
if(sheet == null){
data = {
"success" : false,
"message" : "No target found"
}
}else{
// Set the result data to success, and get the target cell value
data = {
"success" : true,
"data": getTargetCell().getValue(),
}
}
}catch(exc){
data = {
"success" : false,
"message" : exc
}
}
Logger.log("getTarget() data response %s", data)
// return the Json response
return ContentService
.createTextOutput(JSON.stringify(data))
.setMimeType(ContentService.MimeType.JSON);
}
function getTargetCell(){
var sheet = getSpreadsheet().getSheetByName(sheetNames[1]);
// Get the value of second row of column A of the "Target" sheet
return sheet.getRange(sheetNames[1] + "!A2");
}
function getSpreadsheet(){
//TODO: Implementing getting a spreadsheet instance
}
function formatHeaderRange(range){
range.setBackgroundRGB(12,128,144);
var headerStyle = SpreadsheetApp
.newTextStyle()
.setFontSize(12)
.setBold(true)
.setForegroundColor("#ffffff")
.build();
range.setTextStyle(headerStyle);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment