Script to help automate analysis of bank balances.

ImportFNB.gs

/* 
Author: Emerick Bosch

This script imports new transaction history values.
Input is a .csv file - downloaded from FNB, Account, Transaction History. 
The input .csv is uploaded to Google Drive and converted to a Google Sheet. 
The input file name, as well as the google serial numbers of the input file and its containing folder are needed and entered below.
*/


//    ON OPEN

// add menu item to spreadsheet
function onOpen() 
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var fnbMenuEntries = [{name: "Load data from FNB file", functionName: "importFromGoogleSpreadsheet"}];
  ss.addMenu("FNB", fnbMenuEntries);
}



//    FUNCTIONS

function importFromGoogleSpreadsheet() 
{
  // names and google serial numbers
  var fileName = '62206800767';
  var folderName = '0B4GZge00aT8VeDIzQzQ2YWt0OGM';
  var sheetName = '0AoGZge00aT8VdEt3SW1ZUG5WXzVzQTJYamU0c0xBNkE';
  
  var mySheet = SpreadsheetApp.openById(sheetName).getSheets()[0];
  var fileFNB = DocsList.getFolderById(folderName).find(fileName);
  
  if (fileFNB.length === 0)
    Browser.msgBox("No files containing the name \"" + fileName + "\" were found in the drive.");
  
  /* 
  cycle through files found
  open file with exact match to name 
  get ranges of the transactions in import file
  if there are transactions newer than those in this sheet, import them
  */
  else { 
    for(var i = 0; i < fileFNB.length; i++) {
      file = fileFNB[i];
      
      if (file.getName() === fileName) {
        var importSheet = SpreadsheetApp.open(fileFNB[i]).getSheets()[0];
        var lastDate = mySheet.getRange('A10').getValue();
        var newRange = getLatestTransactionRange(importSheet, lastDate);
        
        Browser.msgBox("New entries: " + newRange);
        
        if (newRange > 0) {
          mySheet.insertRowsAfter(9, newRange);
          mySheet.getRange(10, 1, newRange, 4).setValues(importSheet.getRange(8, 1, newRange, 4).getValues());
        }
      }
    }
  } 
}

function getLatestTransactionRange(sheet, dateLast)
{
  var firstRow = 8; // transaction data starts here
  var lastDate = new Date(dateLast);
  var latestRow = -1;
  
  // cycle through rows until latest date.
  for (var i = firstRow; i <= sheet.getLastRow(); i++) {
    var currDate = new Date(sheet.getRange("A" + i).getValue());
    
    if (currDate.valueOf() > lastDate.valueOf())
      latestRow = i;
  }
  
  // return range
  if (latestRow > 0) { return latestRow - firstRow + 1; }
  else { return 0; }
}