I'm facing an issue with the Google Apps Script I use for automating some product feed operations (parsing an XML, adding supplemental data, converting to different formats: TSV, CSV, XML), etc.

When I run the script directly it works 100% of the time, I didn't experience an error in over 6 months of using it. However, when setting up an automated time trigger on a daily basis between 1am-5am CEE Time, the % of failure due to exceeding time is almost 50% (I know the interval is 1 hour, but this happens with multiple scripts scheduled in this interval).

I looked over the script and can not really find any solution so far, didn't find any other similar issues also.

Good to Mention:

• the project is linked to a Google Cloud Project, so the execution time is 30 mins.
• the XML source is a spreadsheet with roughly 16K lines & columns till AA (27 columns)
• the source spreadsheet content comes from an IMPORTRANGE (the architecture is one huge feed with all the columns needed and many mini-feeds with IMPORTRANGES of the meaningful columns & custom column names). From this point if needed, I run a script for XML export as Google Drive does not support direct XML conversion. This XML convert script fails.
• the script does not throw an error, only times out after 1800 seconds.

I considered not using the XML Service and using some string concatenation to build the XML string which should speed up things, but I have this issue with a script that also parses the initial XML and there using regex for parsing could get quite complex, I'd rather understand what I'm doing wrong here from an efficiency standpoint.

I have some experience with coding, but not a lot with modern JS, and also not a lot with Google Apps Script, so along with that any tips/meaningful articles are appreciated.

Convert to XML Script (one of the problematic scripts)

`

function main(){
  const ID = "";
  var data = SpreadsheetApp.openById(ID).getDataRange().getValues();
  var headers = [];
  var root = XmlService.createElement('shop');
    for (var j = 0; j < data.length; j  ){
      if(j == 0){
        headers = data[j];
        continue;
      }
      if(data[j][0] != ""){
        var row = XmlService.createElement('shopitem')
        for(var k = 0; k < data[0].length; k  ){
          if(headers[k].startsWith("P_")){
            var vbl = XmlService.createElement("PARAM");
                vbl.addContent(XmlService.createElement("PARAM_NAME").setText(headers[k].replace("P_", "")));
                vbl.addContent(XmlService.createElement("PARAM_VAL").setText(data[j][k]));
          }else{
            var col = headers[k];
            var type = data[j][k].constructor.name;
            if(data[j][k].constructor.name == 'Date'){        
              var vbl = XmlService.createElement(col).setText(data[j][k].toISOString().slice(0, 10));
            }else{
              var vbl = XmlService.createElement(col).setText(data[j][k])
            }
          }
          
          row.addContent(vbl)
        }
        
        root.addContent(row) 
      
      }
    }  
   
   var document = XmlService.createDocument(root);
   var xml = XmlService.getPrettyFormat().format(document);
  
  
  overwriteFile(new Utilities.newBlob(xml), "file-id")
}

function overwriteFile(blobOfNewContent,currentFileID) {
  var currentFile;
  
  currentFile = DriveApp.getFileById(currentFileID);    
  
  if (currentFile) {//If there is a truthy value for the current file
    Drive.Files.update({
      title: currentFile.getName(), mimeType: currentFile.getMimeType()
    }, currentFile.getId(), blobOfNewContent);
  }
}

`

Questions:

1. Why the console run works everytime, but time-trigger does not?
2. What can be done to optimize that?

Thank you all!

I tried to rather create the file in Drive as a new file instead of replacing its content, but that didn't result in an improvement in the error rate, so I assume the issue is with the calls to the XMLService API, but I'm not sure if "bypassing it/not using it" is the solution.

I've research bulk XML parsing to avoid using api calls inside of for loops, but couldn't find anything.

My next option is to construct/parse the XML as a string with regex/concatenation rules, but that feels like a not-so-professional alternative, and I'm not sure if it will solve the issue as I didn't manage to diagnose the reason.

CodePudding user response：

If the amount of data processed is the same both times, I can't think of a reason (nor find a documented reason) for the runtime execution to be longer when running via time-driven trigger.

However, here are some points that might help optimize the runtime performance:

• If the data fetched from the source Spreadsheet is large, you might want to split the range values and process each "ranged area" at a time.
• Although Sheets and Apps Scripts can handle a considerable amount of data, those are not solutions built nor recommended for it. You may want to consider using a proper database or other Google solution (like BigQuery or Firebase's Firestore)
• Create another timed trigger programatically to finish the execution after a certain amount of time.
• Alternatively, you can report this behavior to Google using this template.