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!

Trench

Custom functions have a time limit of 30s. Consider creating a menu or button to execute script. – Sheffy

[oups, overlapped with TheMaster] Most likely you started hitting a quota - check if your custom functions return under 30 sec (+UrlFetchApp.fetch timeouts in 30s) via "View -> Executions" menu item. Once you do, if everything seems ok or if you don't know how to debug - update the question and one of us surely will try to help (also, you can ping me to check up on the question) – Combat

To be honest, I am a newbie and I found it very difficult to turn a custom function into a script for scheduling. Could anyone please help me on this? I hope that my request is not too demanding :( – Finley

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

Venue

I am not sure I understand bullet point 3, does the previous bullet point not cover it? – Khorma

@Khorma They're related, but I added both points in order to clarify the situation. – Venue

Hi, I am here. The code has worked perfectly for the last several months and I love it! But the problem came last week since it is the first time my data make the code run for more than 6 mins, which is the limit for a script. What could I do now? I am thinking of separating the task into smaller tasks but it seems not possible for me. Could you please help? – Finley

@NguyễnĐứcLong Sorry for the late response, I just noticed your comment. If you're still having this issue, I'd suggest you to post a new question, including the code you are working on and explaining why splitting into smaller tasks doesn't seem to be possible. Cheers! – Venue