0

I have a script that deletes rows if the certain cells in a google sheet are left blank. I am working with about 6000+ rows. The script works fine, but crashes after 5 minutes. Google's server only allows a run time of 5 minutes.

I want to dodge this run time cap. How can I create a loop that will run the script every 4 minutes?

Here is my code:

function readRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  var rowsDeleted = 0;
  for (var i = 0; i <= numRows - 1; i++) {
    var row = values[i];
    if ((row[2] == 0 || row[2] == '') && (row[3] == 0 || row[3] == '')) {
      sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
      rowsDeleted++;
    } else if ((row[3] == 0 || row[3] == '') && (row[4] == 0 || row[4] == '')) {
      sheet.deleteRow((parseInt(i)+1) - rowsDeleted);
      rowsDeleted++;
    }
  }
 };
Rubén
  • 34,714
  • 9
  • 70
  • 166
Andrew Ramon Medina
  • 109
  • 1
  • 5
  • 12
  • 1
    I was originally going to semi-jokingly suggest a huge setTimeout or setInterval, but I'm not sure if that's your problem. You're saying your JS can only run for 5 minutes at a time, afterwards google shuts it down **and you need to refresh**? Is the script running on a google script runner, or right in your chrome env (console)? – Sze-Hung Daniel Tsui May 29 '15 at 21:55
  • Yes, exactly. I would need to refresh some how. Google has a run time cap at 5 minutes. I have to manually refresh the script. I would liekto set it up to automatically run the script again. – Andrew Ramon Medina May 29 '15 at 21:57
  • Hmm. Tricky. And interesting! I've never done this before, but would this work? (1) figure out how to build a chrome extension that periodically refreshes the page (the 2nd answer seems cleanest: chrome.tabs.reload). (2) Run your script at the desired time! Lemme know, I can turn it into an answer. – Sze-Hung Daniel Tsui May 29 '15 at 22:02
  • Similar question: [Google app script timeout ~ 5 minutes?](http://stackoverflow.com/questions/14450819/google-app-script-timeout-5-minutes) The answer by KyleMit addresses spreadsheet timeouts. – Yogi May 29 '15 at 23:04

3 Answers3

0

I guess you are looking for something like this: 

setInterval(function, delay)

http://www.w3schools.com/jsref/met_win_setinterval.asp

Tr1gZer0
  • 1,482
  • 11
  • 18
0

The easiest thing would be to break it up into smaller bits.

One idea that you could riff on: You could run one function to only process, say, 1000 rows, something safe (although Google will sometimes timeout anyway).

Then, using the user property service, store a variable called startingRow, and set it to (1000 - rowsDeleted). 

var userProperties = PropertiesService.getUserProperties();
var startingRow = userProperties.getProperty("startingRow");

//run loop from startingRow to 1000
//then set new start row for next time function is run

startingRow = 1000 - rowsDeleted;
userProperties.setProperty("startingRow", startingRow);

Start the second iteration of your function at that row and run a 1000 more, etc, until you reach the end of the data, wherein you would reset the startingRow to 0.

I wouldn't set the triggers at 4 minutes apart, make it at least 10, the timeout is actually set at 6 minutes.

Dan Oswalt
  • 2,201
  • 2
  • 14
  • 24
0

The Google Spreadsheets allows you to specify activators. For example, you can say that a function is run every minute, every 5 minutes, etc.

So the easiest way would be to use a control cell and check/update that control cell when the function starts. If the function is interrupted, when it is fired again, you can detect an "in progress" value and resume.

m4ktub
  • 3,061
  • 1
  • 13
  • 17