Sunday, January 6, 2013

Advanced ACRA 3: Auto delete old records

One issue with using Google Spreadsheet for ACRA reports is the size limitation of Google Spreadsheet. Specifically, the maximum size of Google Spreadsheet is 400K cells (which translates to 11K reports as of ACRA 4.2.3). More over, if the Google Spreadsheet become too large it simply won't open (there are multiple post of people saying they just can't open large ACRA Spreadsheets).

To avoid this limitation - automatically delete old ACRA reports:
1. Add the following Google Apps Script to your ACRA Google Spreadsheet ("Tools" -> "Script Editor" -> "Blank Project").
2. Schedule the script to run once a day using Time-Driven trigger (in the Script Editor, "Resources"-> "Current script's triggers").

I've extended the script to:
*. remove old versions entries so I could focus on the latest version only, just adjust the version number parameter.
*. Remove invalid entries (I think that was solved in later version of ACRA).



function deleteObsoleteRows() {  
  // Application specific
  //  // https://developers.google.com/apps-script/class_spreadsheetapp#openById
  var SPREAD_SHEET_KEY = "ENTER YOUR KEY HERE";
  //  // Delete any recoprd below this version 
  var APP_VERSION_CODE_CURRENT = 0;

  // ACRA 4.2.3 version specific - report cell locations
  var APP_VERSION_CODE = 2;

  // Maximum records per spreadsheet - if there are more - delete the oldest records
  // Google Spreadhseet over too many records is not useable
  var MAX_RECORDS = 10000;
    
  // For details see 
  // https://developers.google.com/apps-script/class_spreadsheetapp#openById
  var sheet = SpreadsheetApp.openById(SPREAD_SHEET_KEY);
  //var sheet = SpreadsheetApp.getActiveSheet();
  
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  // Delete invalid records
  for (var i = numRows - 1; i >= 1; i--) {
    var row = values[i];
    
    // Logger.log(row);
    
    // Delete empty APP_VERSION_CODE
    if (row[APP_VERSION_CODE] == "") 
      sheet.deleteRow(i+1);
    // Delete old version records
    else if (row[APP_VERSION_CODE] < APP_VERSION_CODE_CURRENT) 
      sheet.deleteRow(i+1);
  }
  
  // Delete the oldest records if there are too many records
  for (var i = numRows - MAX_RECORDS; i > 0; i--) {
    sheet.deleteRow(2);
  }
};