ITHelpGuy
ITHelpGuy

Reputation: 1037

Google apps script 360 seconds exceeded time limit work around

Coding for import of google sheets data into a MySQL table and trying to work around the 360 seconds execution limit. I am using batch execution and even this is not helping import sheets with larger data. Is there a way in google apps script to break away either at 350 seconds or number of records whichever is first and have the next execution pick it up from where it left off.

var address = 'database_IP_address';
var rootPwd = 'root_password';
var user = 'user_name';
var userPwd = 'user_password';
var db = 'database_name';

var root = 'root';
var instanceUrl = 'jdbc:mysql://' + address;
var dbUrl = instanceUrl + '/' + db;

function googleSheetsToMySQL() {   

  var RecId;
  var Code;
  var ProductDescription;
  var Price;

  var dbconnection = Jdbc.getConnection(dbUrl, root, rootPwd);
  var statement = dbconnection.createStatement();
  var googlesheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('product'); 
  var data = googlesheet.getDataRange().getValues();  

dbconnection.setAutoCommit(false)

for (var i = 1; i < data.length; i++) {
RecId = data[i][0];
Code = data[i][1];
ProductDescription = data[i][2];
Price = data[i][3];

var sql = "{call [dbo].[sp_googlesheetstotable](?,?,?,?)}";
statement = dbconnection.prepareCall(sql);
statement.setString(1, RecId);
statement.setString(2, Code);
statement.setString(3, ProductDescription);
statement.setString(4, Price);
statement.addBatch()
statement.executeBatch()
}

dbconnection.commit()

Upvotes: 1

Views: 797

Answers (2)

Vytautas
Vytautas

Reputation: 2286

Yes, that is easily doable so long as the issue is caused by a looped process. A bit trickier if there is no loop and impossible if it's a single operation. Lucky for us, yours seems to be the first kind.

In essence we first of all need to know when the script starts, so we do a

var startTime = new Date();

to get the starting time. Next we need to check what time it is after each iteration, so inside of your for loop, we would add (personally I would declare the variable first and not have the var here, but that is just my stylistic preference)

var execTime = new Date();
execTime = execTime - startTime

which tells us how long the scrip has been executing for. If you want to get fancy, you can check how long your loop takes on average and check if there is enough time left, but generally just giving it some time to perform the cutoff is enough. Then you need to do a few things. First of all, you wish to store the data you have by using

PropertiesService.getScriptProperties().setProperty(key, value)

where key is the name of the property, and value is the object you wish to store. If it's an array, make sure you stringify it with JSON. You also need to create a trigger to fire off from where you left off. Read more on that here. You can of course create a trigger first and then store the properties, there is no problem.

Here is an example of a funcion that does this segment in my script:

function autoTrigger(passProperties, sysKeys) {  
  var sysProperties = new systemProperties();

  if (typeof sysKeys === 'undefined' || sysKeys === null) {
    sysKeys = new systemKeys();
  }

  var triggerID = ScriptApp.newTrigger('stateRebuild')
                           .timeBased()
                           .after(60000)
                           .create()
                           .getUniqueId();

  Logger.log('~~~ RESTART TRIGGER CREATED ~~~');

//-------------------------------------------------------------------------------------------------
// In order to properly retrieve the time later, it is stored in milliseconds
  passProperties.timeframe.start = passProperties.timeframe.start.getTime();
  passProperties.timeframe.end = passProperties.timeframe.end.getTime();
//-------------------------------------------------------------------------------------------------

//-------------------------------------------------------------------------------------------------
// Properties are stored in User Properties using JSON
  PropertiesService.getUserProperties()
                   .setProperty(sysKeys.startup.rebuildCache, JSON.stringify(passProperties));
//-------------------------------------------------------------------------------------------------

  Logger.log('~~~ CURRENT PROPERTIES STORED ~~~');
  return triggerID;
}

Now you have to stop your script. You would do this with throw. Here is how I did it:

if (typeof execProperties.nextPageId !== 'undefined' && execProperties.nextPageId) {
  if (isTimeUp(startTime, Math.max(averageExec, execTime)) === true) {
    autoTrigger(execProperties);
    throw new Error('Script reseting');
  }
}

where I leave 1 minute just in case.

function isTimeUp(start, need) {  
  var cutoff = 500000 // in miliseconds (5 minutes)
  var now = new Date();
  return cutoff - (now.getTime() - start.getTime()) < need; 
}

Note that the script does not call the same function in my case as there is a different startup procedure. What you would need to do is adjust your script to fetch the values you stored in the properties. You can read on properties here further. So if your script can find properties, what you would want to do is delete the trigger, retrieve the variables and clear all those properties and then skip all the way to the loop. This can be easily done with a couple of if statements.

Hope this helps.

Upvotes: 1

Bradly Hale
Bradly Hale

Reputation: 63

What I do in this case is create an empty sheet that will allow me to store the starting ID of the database, and then I set a trigger on the script to run every 10 minutes. To do that, just click the little clock icon to the left of the play button in the GAS editor.

Let's say for example that I have a database with 1000 records, and I want to lift 100 records each time the trigger runs the script. I'd create a sheet -- let's say "Sheet1" - and put 0 in cell A1 initially.

In GAS, I'd have the script get the value of A1 each time it runs:

var ss = SpreadsheetApp.openById("XXXXX");
var sheet = ss.getSheetByName("Sheet1");
var a1 = sheet.getRange("A1").getValue();
var a1 = parseInt(a1);
var last_row = a1+100;

Then your SQL statement should be something like the following query:

"SELECT * FROM `table` WHERE `id` >= '"+a1+"' AND `id` < '"+last_row+"'"

Once the query finishes and outputs its results to the spreadsheet, you'd then update A1 with last_row:

sheet.getRange("A1").setValue(last_row);

When the script runs again (via the trigger), it will take the new value, 100, add 100 (making it 200), query the table for the results greater than or equal to 100 and less than 200, and display them accordingly.

Upvotes: 1

Related Questions