Google app script for Auto Budget Android app

Auto Budget Script

code.gs

// Google App Script for Auto Budget app (https://play.google.com/store/apps/details?id=me.moallemi.autobudget)

var scriptVersionCode = 401
var scriptVersionName = "4.0.1"

function onOpen() {
  SpreadsheetApp.getUi()
  .createMenu('Auto Budget')
  .addItem('Setup', 'setup')
  .addItem('Sort by Date', 'sort')
  .addSeparator()
  .addItem('Check for update...', 'checkForUpdate')
  .addItem('About', 'showAbout')
  .addToUi();
}

function setup() {
  createTagColumns();  
  setupTrigger();
}

function createTagColumns() {
  if(getColumnIndexByName("برچسب سوم") == -1) {
    var listSheet = SpreadsheetApp.getActive().getSheetByName('List')
    listSheet.insertColumnAfter(5)
    listSheet.getRange('F1').setValue('برچسب سوم');
  }
  if(getColumnIndexByName("برچسب دوم") == -1) {
    var listSheet = SpreadsheetApp.getActive().getSheetByName('List')
    listSheet.insertColumnAfter(5)
    listSheet.getRange('F1').setValue('برچسب دوم');
  }
  if(getColumnIndexByName("برچسب اول") == -1) {
    var listSheet = SpreadsheetApp.getActive().getSheetByName('List')
    listSheet.insertColumnAfter(5)
    listSheet.getRange('F1').setValue('برچسب اول');
  }
}

function setupTrigger() {
  var allTriggers = ScriptApp.getUserTriggers(SpreadsheetApp.getActive());
  for (var i = 0; i < allTriggers.length; i++) {
    if (allTriggers[i].getHandlerFunction() == 'autobudgetTrigger') {
      ScriptApp.deleteTrigger(allTriggers[i]);
      break;
    }
  }
  
  ScriptApp.newTrigger('autobudgetTrigger')
  .forSpreadsheet(SpreadsheetApp.getActive())
  .onChange()
  .create();
  
  allTriggers = ScriptApp.getUserTriggers(SpreadsheetApp.getActive());
  if (allTriggers.length == 1) {
    
    setMetaDataRow('script_installed', "true");
    setMetaDataRow('script_version_code', scriptVersionCode);
    setMetaDataRow('script_version_name', scriptVersionName);
    
    var ui = SpreadsheetApp.getUi();
      var result = ui.alert(
        'Auto Budget',
        'Activated successfully',
        ui.ButtonSet.OK);
  }
}

function autobudgetTrigger() {
  setLastRow();
  sort();
}

function showAbout() {
  var ui = SpreadsheetApp.getUi();
  
  var result = ui.alert(
     'Auto Budget',
     'Version '+ scriptVersionName +
    '\n\n' + 
    'https://play.google.com/store/apps/details?id=me.moallemi.autobudget',
      ui.ButtonSet.OK);
}

function checkForUpdate() {
  var ui = SpreadsheetApp.getUi();
  
  var result = ui.alert(
     'Auto Budget',
    'Update scriot from: ' +
    '\n' + 
    'https://gist.github.com/moallemi/50ccd221a7ac1c94250736f8de9604b4',
      ui.ButtonSet.OK);
}

function getColumnIndexByName(name) {
  var sheet = SpreadsheetApp.getActive().getSheetByName('List')
  var range = sheet.getRange(1, 1, 1, sheet.getMaxColumns());
  var values = range.getValues();
  
  for (var row in values) {
    for (var col in values[row]) {
      if (values[row][col] == name) {
        return (col + 1);
      }
    }
  }
  return -1;
}

function setLastRow() {
  var listSheet = SpreadsheetApp.getActive().getSheetByName('List')
  
  var lastRow = listSheet.getLastRow()
  setMetaDataRow('last_row', lastRow);
  
  calculateThisMonthExpense()
  calculatePreviousMonthExpense()
}

function sort() {  
  var sortFirst = 5 // column date
  var sortFirstAsc = true
  var headerRows = 1
  
  var sheet = SpreadsheetApp.getActive().getSheetByName('List')
  var range = sheet.getRange(headerRows+1, 1, sheet.getMaxRows()-headerRows, sheet.getLastColumn())
  range.sort([{column: sortFirst, ascending: sortFirstAsc}])
}

function calculateThisMonthExpense() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('List')
  var data = sheet.getDataRange().getValues().reverse();
  
  var lastDateParts = data[0][4].split("-")
  var lastYear = lastDateParts[0]
  var lastMonth = lastDateParts[1]
  
  var totalExpense = 0
  var thisMonthStartIndex = 0
  
  for(var i = 0; i < data.length; i++){
    var date = data[i][4].split("-")
    var year = date[0]
    var month = date[1]
    var day = date[2]
    
    if(year == lastYear &&  month == lastMonth) {
      totalExpense += data[i][3]
      thisMonthStartIndex = data.length - i
    }
  }
  
  setMetaDataRow('this_month', totalExpense);
  setMetaDataRow('this_month_start_row', thisMonthStartIndex);
}

function calculatePreviousMonthExpense() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('List')
  var data = sheet.getDataRange().getValues().reverse();
  
  var lastDateParts = data[0][4].split("-")
  var lastYear = lastDateParts[0]
  var lastMonth = lastDateParts[1]
  
  var previousMonthString = ""
  var previousMonth = parseInt(lastMonth, 10) -1
  if (previousMonth == 0) {
    previousMonthString = "12"
    lastYear = (parseInt(lastYear, 10) -1) + ""
  } else if (previousMonth > 9) {
    previousMonthString = previousMonth
  }  else {
    previousMonthString = "0" + previousMonth
  }
  
  var totalExpense = 0
  var previousMonthStartIndex = 0
  
  for(var i = 0; i < data.length; i++){
    var date = data[i][4].split("-")
    var year = date[0]
    var month = date[1]
    var day = date[2]
    
    if(year == lastYear &&  month == previousMonthString) {
      totalExpense += data[i][3]
      previousMonthStartIndex = data.length - i
    }
  }
  
  setMetaDataRow('previous_month', totalExpense);
  setMetaDataRow('previous_month_start_row', previousMonthStartIndex);
}

function getMetaDataRowByKey(key){
  var sheet = SpreadsheetApp.getActive().getSheetByName('.MetaData')
  var data = sheet.getDataRange().getValues();
  var index = -1;
  for(var i = 0; i < data.length; i++) {
    if (data[i][0] == key){
      index = i + 1;
      break;
    }
  }
  if (index == -1) {
    sheet.appendRow([key, ''])
    index = getMetaDataRowByKey(key)
  }
  return index;
}

function setMetaDataRow(key, value) {
  var sheet = SpreadsheetApp.getActive().getSheetByName('.MetaData')
  var rowIndex = getMetaDataRowByKey(key)
  sheet.getRange("B" + rowIndex).setValue(value)
}

function findAndReplace() {
  var find = "";
  var replace = "";
  var sheet = SpreadsheetApp.getActive().getSheetByName('List')
  var data = sheet.getDataRange().getValues();
  for(var i = 0; i < data.length; i++) {
      if (data[i][2] == find) {
        data[i][5] = find;
        data[i][2] = replace;
        var index = i + 1
        sheet.getRange("C" + index).setValue(replace);
        sheet.getRange("F" + index).setValue(find);
      }
  }
}