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