mbwasi.com

If you are an Android developer, you might be familiar with the excellent ACRA library which allows you to receive your Apps crash reports into your Google Docs account as a spreadsheet.

The only problem is Google Docs Spreadsheets fail catastrophically once they get to a certain size, you cant open them therefore you cant reduce their size, you cant download or export them. And if you choose to delete them then you will lose the connection between the App and the spreadsheet thus loosing any future crash reports (the spreadsheet and the app are linked by a form key imbedded in the App and unique on Google Docs).

After some Googling and a bit of script programming I have managed to find a fix that will allow you to reduce a spreadsheet without needing to open it!

This is mainly a modification of the Script by user Ronen I found on Accra-Discuss Google Group which works only from within the Spreadsheet which is impossible once you have already run into problems.

DATA LOSS:!! THIS SCRIPT WILL DELETE ROWS FROM THE SPREADSHEET TO HELP GET IT TO A WORKING SIZE.

How to fix:

1. Go into any working Spreadsheet or create a new one.
2. Go to Tools –> Script Editor…
3. Select Blank Project
4. A new empty Script is created, delete everything, paste in the code bellow. Get the code here: http://pastebin.com/vVasrXy1 , take the RAW one, copying the code bellow leads to errors because of html formatting!! 
5. Change the SPREADSHEET_KEY value to the value for the spreadsheet you are trying to fix, this can be obtained from the browser URL when trying to open a spreadsheet, or if you share a spreadsheet via a link this key is included in the link.
6. SHEET_NAME is the name of the actual sheet within the spreadsheet, this is “Sheet1” unless you changed it.
7. rowsToKeep is the number of Rows that will be left in the spreadsheet after the cleanup is done, in this case 1000.
8. Save the Script by hitting the save button, Script will be saved to this Spreadsheet, not the broken one.
9. Click the Play button to Run the Script, it can take a while.\
10. Go and open the previously broken Spreadsheet, it has been fixed!!

function acraCleanup() {
var SPREADSHEET_KEY = “AABVGGHJGFGDFG4GFD65GHDF56HGFHG53″;

// Replace with your spreadsheet Key,

//you can find this by trying to share the Spread sheet

// and getting the key between the “=” and the “&”
var SHEET_NAME = “Sheet1″; //Sheet1 unless you changed sheet name
var rowsToKeep = 1000; //Will keep bottom 1000 Rows
var sheet  = SpreadsheetApp.openById(SPREADSHEET_KEY).getSheetByName(SHEET_NAME);
var rows = sheet.getLastRow();
var numToDelete = rows – rowsToKeep  -1;
if (numToDelete > 0) sheet.deleteRows(2, numToDelete);
}