Google apps script 6 minute execution time limit

Question

I am copying data from google sheets into a mysql table via a stored procedure. This sheet has some 3000 odd records. After copying some 2000 odd records I hit the 6 minute execution time limit. Is there a work around to this 6 minute execution time limit.

function myfunction() { 
  var colA;
  var colB; 
  var colC; 
  var colD;
  var colE;  

  var mysqldb = Jdbc.getConnection("jdbc:mysql;dbipaddress","user","pa$$word");
  var sql = mysqldb.createStatement();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet1'); 
  var data = sheet.getDataRange().getValues();  

  for (var i = 1; i < data.length; i++) {
  colA = data[i][0];
  colB = data[i][1]; 
  colC = data[i][2]; 
  colD = data[i][3]; 
  colE = data[i][4];  

  var query = "{call [dbo].[sp_copygsheets](?,?,?,?,?)}";
  sql = mysqldb.prepareCall(query);
  sql.setString(1, colA);
  sql.setString(2, colB); 
  sql.setString(3, colC); 
  sql.setString(4, colD);
  sql.setString(5, colE); 
  sql.executeUpdate();
  }

  sql.close();
  mysqldb.close();
}

Possible duplicate of [Exceeded maximum execution time in Google Apps Script](http://stackoverflow.com/questions/7854573/exceeded-maximum-execution-time-in-google-apps-script) — Rubén, Dec 24 '16 at 22:46
From Google Apps Script JDBC page: `Batching is vital for bulk operations`. Look at the batch example on this page: https://developers.google.com/apps-script/guides/jdbc — Joshua Dawson, Dec 27 '16 at 16:29

Paul · Answer 1 · 2016-12-24T09:51:49.093

Without access to a SQL db to test, and with little experience of working with JDBC in Google AppScripts, I'm going to have to make some presumptions

I will presume the biggest portion of the execution time in the for loop

for (var i = 1; i < data.length; i++) {
...
}

If so, have you tried limiting the loop to something that finishes in the 6 minute time limit and saving last value of i as a user property? Then you can run the script multiple times until the the script is marked as completed (as, say, another user property).

E.g. Something like:

var startVal = PropertiesService.getUserProperties().getProperty("lastLoop");
    for (var i = startVal; i < numLoops; i++) {
    ...
    }
var newProp = {myStartVal: startVal+numLoops};
PropertiesService.getUserProperties().setProperties(newProp);

Then set the script on a time trigger and have it run until numLoops equals data.length, which can be tested prior to the execution of the for loop.

Not a full answer, but I hope it puts you in the right direction.

I would like to add that I feel that this wasn't really worthy of an answer but more appropriate as a short comment, but with a reputation of less than 50 I can't add comments, so apologies for the partial answer. — Paul, Dec 24 '16 at 09:48

Google apps script 6 minute execution time limit

1 Answers1