Script for Google Docs to automatically delete older rows (to avoid reaching Google Docs size limit)

[Ronen]

Hi,

As google spreadsheets are limited to 400K cells, it's a good idea to
have a script that cleans it automatically. If you reach the limits
your sheet may not open anymore!
This script can be triggered periodically automatically.
Simply go to Tools->Script Editor and paste the following script. Then
go to Resources->Current script's triggers and set your time-driven
trigger.
From what I've read, you won't be able to keep more than ~11K rows
without reaching the limits.
BE CAREFUL AND CHANGE THE SCRIPT FOR YOUR NEEDS AS IT WILL DELETE ROWS
FROM YOUR SPREADSHEET!

function acraCleanup() {
var rowsToKeep = 5000; //CHANGE TO YOUR DESIRED NUMBER OF ROWS TO
KEEP.
var rows = SpreadsheetApp.getActiveSheet().getLastRow();
var numToDelete = rows - rowsToKeep -1;
SpreadsheetApp.getActiveSheet().deleteRows(2, numToDelete);
}

[Tomcat]

WOW!

It has been so painful for me to delete rows manually from time to time. I added your script to 3 acra-spreadshetts.

You are my lifesaver. Thanks.

2012年4月9日月曜日 22時20分20秒 UTC+9 Ronen:

[Zorilla]

Thank you very much for the script! That's absolutely fantastic.

Unfortunately I have two spreadsheets I can't open because there seems to be too many rows already.
I guess there is no way to clean the content.
What I have seen in the Android Developer Console is the following:

java.lang.StackOverflowError
at org.acra.ErrorReporter.a(SourceFile:1277)
at org.acra.ACRA.initAcra(SourceFile:263)
at org.acra.ACRA.init(SourceFile:238)

May this be related to the full spreadsheet?

What happens if I delete the acra spreadsheet completely?

[martin manony]

Hello,

this script worked fine on the first run. When the spreadsheed was cleaned up and I started the sript a second time I got an error at deleteRows.

The reason was that numToDelete was zero. I added a check if numToDelete is greater 0 before deleteRows is called. 

if (numToDelete > 0) SpreadsheetApp.getActiveSheet().deleteRows(2, numToDelete); 

regards Martin

[Roman Mbwasi]

Thank you!

With a little modification of your scripts I have managed to fix an ACRA Spreadsheet that wouldn't open at all because it got too big! Here is my script, I created it in another spreadsheet and used the details of the broken one,  full details on my blog.  http://mbwasi.com/blog/

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 = "Sheet 1"; //Sheet 1 unless you changed the 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); 
}

[Carlos Lopez]

Hi Ronen,
I am wondering if you can help me modiy I script I came across a while ago that uses the onEdit trigger.  The script basically looks at a cell value and moves the data to another worksheet on the same spreadsheet.  This would be so helpful for my googlespreadsheet that stores data entered on a form.  What I am looking for is to move data to another sheet based on updates made through the form.  I have a workflow that requires approval.  When my row is updated through the form with an Approved status, I'd like for the row to 1st be moved to my Archive Sheet and then delete the row.  

Here is the script I found, that works well if I manually update the contents on column 9 (Column 9 has the Approval Decision).  Column 41 has the name of the sheet to where to copy the row to.  In my case, I named it Archive.  Again, the script works fine if I manually change the status, but it does not work when the cell is updated through the form on submit.

/**

 * Moves row of data to another spreadsheet based on criteria in column 9 to sheet with same name as the value in column 42.  We have a formula that evalues whether a form submission has been approved. If it is in status of blank or Denied, the cell in Column41 will be blank.  If the status of the form submission is approved, the formula is set to update the cell with "Archive".  This Archive is the name of the worksheet where the row will be moved to.  The source row will then be deleted, thus cleaning up the active FormMule sheet removing the "already processed" data.

*/

function onEdit(e) {

  // see Sheet event objects docs

  // https://developers.google.com/apps-script/guides/triggers/events#google_sheets_events

  var ss = e.source;

  var s = ss.getActiveSheet();

  var r = e.range;

  

  // to let you modify where the action and move columns are in the form responses sheet

  var actionCol = 9; // This is the Action you want or the cell that will trigger the move of row to new spreadsheet.  When this cell is updated/changed to approve, this will be the trigger to move the data to new sheet.

  var nameCol = 41; // This is the column that you want to check (i.e.: We want to move the rows where in Column X? or where the Approval Decision = Approved.  For our process, this means it has been approved and we can archive

  // Get the row and column of the active cell.

  var rowIndex = r.getRowIndex();

  var colIndex = r.getColumnIndex();

  

  // Get the number of columns in the active sheet.

  // -1 to drop our action/status column

  var colNumber = s.getLastColumn()-1;

  

  // if our action/status col is changed to Approved do stuff

  if (e.value == "Approved" && colIndex == actionCol) {

    // get our target sheet name - in this example we are using the priority column

    var targetSheet = s.getRange(rowIndex, nameCol).getValue();

    // if the sheet exists do more stuff

    if (ss.getSheetByName(targetSheet)) { 

      // set our target sheet and target range

      var targetSheet = ss.getSheetByName(targetSheet);

      var targetRange = targetSheet.getRange(targetSheet.getLastRow()+1, 1, 1, colNumber);

      // get our source range/row

      var sourceRange = s.getRange(rowIndex, 1, 1, colNumber);

      // new sheets says: 'Cannot cut from form data. Use copy instead.' 

      sourceRange.copyTo(targetRange);

      // ..but we can still delete the row after

      s.deleteRow(rowIndex);

      // or you might want to keep but note move e.g. r.setValue("moved");

    }

  }

}

[William Ferguson]

Carlos,

Google has long since (more than 3 years) asked everyone not to use GoogleDocs to capture crash data.

There are many free/cheap crash servers out there to point you apps at.

Strongly recommend you make use of one of them rather than Docs.

William

[acol...@emblematic.co.uk]

Hi Ronen,

I was wondering if you could help me. I'm newish to google sheets and have been searching for a way to auto archive/move rows over 500 on to another sheet/tab. I'm sure it can be done just not sure how. I have got the following code in place but this means you have to type yes in each line for it to archive it and would like it just to automatically move once we hit a particular amount of rows.

Current Code:

function myFunction2() {
  // moves a row from a sheet to another when a magic value is entered in a column
  // adjust the following variables to fit your needs
  // see https://productforums.google.com/d/topic/docs/ehoCZjFPBao/discussion
  var sheetNameToWatch = "Sheet1";
  var columnNumberToWatch = 8; // column A = 1, B = 2, etc.
  var valueToWatch = "YES";
  var sheetNameToMoveTheRowTo = "Archive-2019";
 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveCell();
 
  if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) {
    var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);
    var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);
    sheet.deleteRow(range.getRow());