A gist for Financial Goal Tracker Google Appscript

completed_code.gs

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

starter_code.gs

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