Fetching XML by Google App Script ends on Exceeded maximum execution time

Question

I am trying to fetch XML by Google App Script and then passing these values to Google Sheets. Everything works fine for a few iterations. If I try more, I will exceed the maximum time of execution.

The code below results in an error message "Exceeded maximum execution time". If I lower a number of iteration (3), It will work. But I need to iterate through approximately 20K rows.

function myFunction(){
  var url = '<<file name>>';
  var bggXml = UrlFetchApp.fetch(url).getContentText();

  var document = XmlService.parse(bggXml);
  var root = document.getRootElement();
  var now = new Date();



  for(var i = 0; i <= 20; i++){


  var shopitem = root.getChildren('SHOPITEM')[i];

  var code = shopitem.getChild('CODE').getText();
  var stock100 = shopitem.getChild('STOCK').getChild('WAREHOUSES').getChildren('WAREHOUSE')[0].getChild('VALUE').getText();
  var stock801 = shopitem.getChild('STOCK').getChild('WAREHOUSES').getChildren('WAREHOUSE')[1].getChild('VALUE').getText();




  SpreadsheetApp.getActiveSheet().getRange(1+i,1).setValue(code);
  SpreadsheetApp.getActiveSheet().getRange(1+i,2).setValue(stock100);
  SpreadsheetApp.getActiveSheet().getRange(1+i,3).setValue(stock801);


 } 


}

Feed structure

<SHOP>
    <SHOPITEM>
        <CODE>#SKU#</CODE>
        <STOCK>
            <WAREHOUSES>
                <WAREHOUSE>
                    <NAME>Sklad 100</NAME>
                    <VALUE>"stock value"</VALUE>
                </WAREHOUSE>
                <WAREHOUSE>
                    <NAME>Sklad 801</NAME>
                    <VALUE>"stock value"</VALUE>
                </WAREHOUSE>
            </WAREHOUSES>
        </STOCK>
    </SHOPITEM>
</SHOP>

Any suggestion, what might be wrong?

Thank you very much

score 0 · Answer 1 · answered Mar 18 '20 at 12:09

As the error you are getting says, you are reaching the script execution time limit, which can be 6 or 30 minutes depending on the type of your account, as specified here.

To avoid this, you can do two things (not mutually exclusive):

Improve code-efficiency:

Increase the efficiency of your code. You could, for example, replace the different setValue with one single setValues. You would have to replace this:

SpreadsheetApp.getActiveSheet().getRange(1+i,1).setValue(code);
SpreadsheetApp.getActiveSheet().getRange(1+i,2).setValue(stock100);
SpreadsheetApp.getActiveSheet().getRange(1+i,3).setValue(stock801);

With this:

SpreadsheetApp.getActiveSheet().getRange(1+i, 1, 1, 3).setValues([[code, stock100, stock801]]);

Split script into multiple executions:

You can also split your loop into different executions by setting a time-based trigger that will fire each successive execution after the previous one has finished. You would have to do the following:

Find out how many iterations you can make before reaching the time limit (in the sample below, that is set to 3). Each execution will have to make this amount of iterations before finishing.
Create the following time-based trigger at the end of your function: after(durationMilliseconds). Thanks to this, you can run whatever function you specify after the amount of milliseconds you indicate. After each execution, a trigger will be created to fire the next one.
Because you want to split the loop, you have to store the loop counter (i) somewhere (you could use PropertiesService at the end of each execution, or write it in the spreadsheet) and retrieve it at the beginning of the next, so that each in successive execution, the script knows where to resume the loop. See, for example, this answer if you don't know how to store and retrieve script properties.

Sample code (check inline comments):

function myFunction(){
  var i_old = // Retrieve i stored in previous execution (from PropertiesService? Spreadsheet?) (should be 0 if first execution)
  var n_int = 3 // Number of iterations that can be done in a single execution without reaching time limit (change accordingly)
  var total = 20 // Total number of iterations (change accordingly)
  var url = '<<file name>>';
  var bggXml = UrlFetchApp.fetch(url).getContentText();
  var document = XmlService.parse(bggXml);
  var root = document.getRootElement();
  var now = new Date();
  // Loop starts at previous i store until it reaches the specified number of iterations for one execution or reaches the total number:
  for(var i = i_old; i <= i_old + n_int && i <= total; i++) { 
    var shopitem = root.getChildren('SHOPITEM')[i];
    var code = shopitem.getChild('CODE').getText();
    var stock100 = shopitem.getChild('STOCK').getChild('WAREHOUSES').getChildren('WAREHOUSE')[0].getChild('VALUE').getText();
    var stock801 = shopitem.getChild('STOCK').getChild('WAREHOUSES').getChildren('WAREHOUSE')[1].getChild('VALUE').getText();
    SpreadsheetApp.getActiveSheet().getRange(1 + i, 1, 1, 3).setValues([[code, stock100, stock801]]);
  }
  // Store i somewhere (PropertiesService? Spreadsheet?)
  if (i <= total) { // Create trigger if i hasn't reach the total number of iteration
    ScriptApp.newTrigger("myFunction")
    .timeBased()
    .after(1000 * 60) // This fires the function 1 minute after the current execution ends. Change this time according to your preferences
    .create();  
  }
}

Fetching XML by Google App Script ends on Exceeded maximum execution time

1 Answers1

Improve code-efficiency:

Split script into multiple executions:

Sample code (check inline comments):

Reference:

Linked