Exceeded maximum execution time (line 0)

Question

I have a .tsv file from a tool and I have to import it the Google Sheet (nearly) real-time for reports. This is my code for importing:

function importBigTSV(url) {return Utilities.parseCsv(UrlFetchApp.fetch(url).getContentText(),'\t');}

It worked till some days ago when Error messages keep saying "Exceeded maximum execution time (line 0)."

Could anyone help? Thank you a lot!

Answer 1

Issue:

As @TheMaster said, custom functions have a hard limit of 30 seconds, which your function is most probably reaching. Regular Apps Script executions have a much more generous time limit (6 or 30 minutes, depending on your account), so you should modify your function accordingly.

Differences between functions:

In order to transform your function, you have to take into account these basic differences:

• You cannot pass parameters to a function called by a Menu or a button. Because of this, you have to find another way to specify the URL to fetch.
• Values returned by a regular function don't get automatically written to the sheet. You have to use a writing method (like setValues, or appendRow) to do that.
• A non-custom function is not called in any particular cell, so you have to specify where do you want to write the values to.

Since, from what I understand, you are always fetching the same URL, you can specify that URL just by hardcoding it into your function.

Solution:

The function below, for example, will write the parsed output to the range that is currently selected (at the moment of triggering the function). You could as well provide a default range to write the output to, using getRange:

function importBigTSV() {
  var url = "{url-to-fetch}";
  var range = SpreadsheetApp.getActiveRange();
  try {
    var output = Utilities.parseCsv(UrlFetchApp.fetch(url).getContentText(),'\t');
    var outputRange = range.offset(0, 0, output.length, output[0].length);
    outputRange.setValues(output);
  } catch(err) {
    console.log(err);
  }
}

If the URL can change, I'd suggest you to have a list of URLs to fetch, and, before triggering the function, select the desired URL, and use getActiveRange in order to get this URL.

Attaching function to Menu:

In any case, once you have written your function, you have to attach this function somehow, so that it can be trigged from the sheet itself. You can either create a custom menu, or insert and image or drawing, and attach the script to it. The referenced links provide clear and concise steps to achieve this.

Reference:

• Custom Functions > Return values
• Custom Menus in G Suite