An easy way to deal with Google Apps Script’s 6-minute limit

Google Apps ScriptGoogle DriveGoogle Sheets
Dec 14 /
inclu-cat /
53

About the 6-minute limit

Google Apps Script is handy, and it will help you a lot in your work. However, as you use it, you may hit a big wall. That is the six-minute limit on execution time. As the official documentation states, the maximum allowed time per execution of Google Apps Script is 6 minutes. ( As far as I can tell from the official documentation, there is no difference between paid users and free users. )

If the script execution time reaches 6 minutes, the script will stop suddenly, and an error message “Exceeded maximum execution time” will be displayed.

You will see an error message like this. 😥

The processing speed of Apps Script is not that fast, so if you are loading data from Google Sheets or Google Drive, you will reach the time limit in no time. Then, what should we do? I will tell you how to solve this.

How to solve this?

This problem can be solved with Script properties and Time-driven triggers. You can use these mechanisms to rerun the Script as many times as you need.

Class PropertiesService allows you to store simple data in key-value pairs scoped to one script. So you can store the number of times your function has processed as the script’s property.

Time-driven triggers let scripts execute at a particular time. So you can set a trigger to run the function next time before the 6-minute limit is reached.

The mechanisms for overcoming the six-minute limit

However, when it comes to actually do it, you’ll find that it’s not easy😓. You can find a lot of solutions that use this mechanism on the Internet. But you’ll soon understand that you have to do many things to use it; you will have to remove triggers and properties that are no longer needed, and you should determine how to pass arguments, and there are many other things.

So I created a Javascript class named LongRun to efficiently deal with the 6-minute time limit. It was created as an extension of my open-source project GAS-Terminal.

The LongRun class

This class hides operations such as PropertiesService and Time-based Triggers behind it, making it easy to perform long-running scripts. Here’s an example code. (NOTE: This sample is designed to be run in the GAS-Terminal)

function LongRunTest(
    // there must be no arguments, because the parameters must be retrieved from LongRun class.
    /* times: number, funcExecutionSeconds: number, maxExecutionSeconds: number, triggerDelayMinutes: number */
  ) {
  let longRun = LongRun.instance;

  // funcName must equal this function's name.
  const funcName = 'LongRunTest';

  // you can get the parameters from LongRun class.
  // the order of the array is the same as the order of the command definition.
  const params = longRun.getParameters(funcName);
  const times = parseInt(params[0]);
  const funcExecutionSeconds = parseInt(params[1]);
  const maxExecutionSeconds = parseInt(params[2]);
  const triggerDelayMinutes = parseInt(params[3]);

  // you can set the long-running configurations. of course you can use the default values.
  longRun.setMaxExecutionSeconds(maxExecutionSeconds); // default is 240 seconds
  longRun.setTriggerDelayMinutes(triggerDelayMinutes); // default is 1 minute

  // you should get the index to resume(zero for the first time)
  let startIndex = longRun.startOrResume(funcName);
  if( startIndex === 0 ){
    LogUtils.i('--- LongRunTest command started. ---');
  }

  try {
    // Execute the iterative process.
    for (let i = startIndex; i < times; i++) {
      LogUtils.i('Processing: ' + i);

      // Each time before executing a process, you need to check if it should be stopped or not.
      if (longRun.checkShouldSuspend(funcName, i)) {
        // if checkShouldSuspend() returns true, the next trigger has been set
        // and you should get out of the loop.
        LogUtils.i('*** The process has been suspended. ***');
        break;
      }

      // *** code your main process here! ***
      Utilities.sleep(funcExecutionSeconds * 1000); // demonstrate the process

      LogUtils.i('Processing Done!: ' + i);
    }
  }
  catch (e) {
    LogUtils.ex(e);
  }
  finally {
    // you must always call end() to reset the long-running variables if there is no next trigger.
    const finished = longRun.end(funcName);
    if( finished ){
      LogUtils.i('--- LongRunTest command finished. ---');
    }
  }
}

You can get the source code of the LongRun class from my GitHub repository. Please, check it! Of course, you can use the class solely, but it is more effective when used in conjunction with the GAS-Terminal framework.

For those who want to use the LongRun class without GAS-Terminal (ADDED 10/24/2021)

The LongRun class was created to enhance the GAS-Terminal, but it can be used without GAS-Terminal without any problem; you can take the LongRun.ts file only and put it into your project and use it.

I have also created a sample project that shows how to use this class independently. If you are interested, please see this repository.

For those who want to use the LongRun class more easily. (ADDED 12/14/2021)

I think the LongRun class has made it much easier to control tedious long-time processing, but it still remains complicated. If it’s a long process that doesn’t require such detailed control, wouldn’t it be easier to just pass the function to the LongRun class?

So I prepared a global function, executeLongRun() in LongRun.ts, which works by just passing the name of the function to process each one and the number of times to process it. (Thanks to Fabrice for the suggestion!)
This function can optionally be passed parameters, a function to initialize, and a function to be executed on exit.

Here is a sample:

import {executeLongRun, LongRun} from "../LongRun";

function LongRunTest() {
  const params = [];
  params.push("param1");
  params.push("param2");
  const loopCount = 100;

  // execute the long-run task
  executeLongRun("main", loopCount, params, "initializer", "finalizer" );
}

// This function will be executed on first or restart. (optional)
function initializer(startIndex: number, params: string[]){
  // Do some initial processing.
}

// This function will be executed on each time.
function main(index: number, params: string[]){
  // Do some main processing.
}

// This function will be called on interruption or when all processing is complete. (optional)
function finalizer(isFinished: boolean, params: string[]){
  // Do some final processing.
}

For a working sample, please see Test2.ts in the repository.

Note that it is not possible to use executeLongRun() to execute different long-time processes simultaneously.

Conclusion

The 6-minute limit problem is painful but can be solved by using the mechanism provided. And there are also some useful classes, such as the LongRun class. Let’s make use of Google Apps Script for long-time processing!👍

Tags:

6-minute limitGASGAS-TerminalGoogle Apps ScriptGoogle Sheetslong-running

53 Responses

  1. Hi! I’m currently doing a project which involves massive data communication between the google sheets and scripts, thus breaching the 6-minute rule. I’m a beginner in app script. My question is: how do I import longrun class from your GAS-terminal? I’m seeing the .ts are interconnected to each other. Which files only pertain to LongRun?

    Reply

    • Thanks! You are right.
      Sorry, there is less information about that, because I mainly think of it as a part of GAS-Terminal.
      In LongRun.ts, if you look at the import statement at the top of the file, you’ll see that it only uses StringUtils.ts.
      (I updated the LongRun.ts now because it had another unnecessary import statement(LogUtils.ts), sorry.😓)
      So please take the two .ts files, and place them together where you want.😊

      Reply

  2. Hi, and thank you! I’d like to use the LongRun class in my script, but I can’t get it working… if I try to run the LongRunTest function, parameters are not retrieved, even when I try to run it from apps script ide in a copy of GAS-Terminal sheet. am I missing anything?

    Reply

  5. Hello, I like your GAS terminal and the LongRun class. Is it possible to change the minutes in “How many minutes later the next trigger will be activated” into seconds? How can I do it? The waiting time of one minute is too long for me. Sorry for the question, I’m not an advanced programmer. Thank you.

    Reply

    • Thank you for using GAS-Terminal! I definitely agree with your thoughts. But unfortunately, Google doesn’t allow to use time-driven triggers in seconds… For example, If you use the trigger function manually, it will look like this (minutes is the smallest unit):https://imgur.com/gthwgKu
      If the situation changes, I would like to be able to specify the parameters in seconds 👍

      Reply

  6. Thanks a lot for this class, brilliant! Do you think feasible to hide the trigger function into the LongRun class (or in the same file I put in a library) and just pass to the LongRun class the “code your main process here!” ? (and optionally a function to call in the finally block when all iterations are done … Anyways thanks for this!

    Reply

    • Thank you for your comment!
      In my experience, I had to put the trigger function outside because of the following cases.
      A) The processing loop may not always be simple.
      B) There are times when you want to perform special processing, such as when suspending.

      But after listening to your suggestion, I feel that it is indeed better to hide everything in the LongRun class for the simple case on the contrary. I’ll try to make a version with the enhancements you suggested soon. Thanks!

      Reply

      • Hello & thanks for your reply. I rewrote a bit your class but did not manage to get all inside. In my view, we would have to set a generic trigger (the one contained in the LongRun class) but this trigger should call itself the user function to execute in the loop. But I did not manage how to pass this function (outside of the LongRun class) to the generic trigger. The only way is to use the script properties, but we can only store strings.

        Reply

  7. Hi, I’m fairly new to scripts. I’m timing out on consolidation script that pulls information from hundreds of sheets within one google sheet file.
    I’m having trouble knowing how to integrate LongRun into my script to keep everything going. Any help would be greatly appreciated.

    Reply

  8. Hello Inclu Cat, thanks a lot for the code you shared related to our previous discussion (hide most of the Long Run mechanics). I put my code (largely using yours) on Github. It works well …. BUT when I put the class (and generic trigger function ) in a Google library, my user functions (intializer, mainProcess and finalizer) are not recognized in the trigger… Any idea ?

    Reply

  9. Nice idea! I have had problems with a script that now takes too long and it errors with a time out. I will discuss the solution you propose with my team. Thanks!!!

    Reply

  10. Hello Inclu Cat,

    I am trying to perform batch operations on the google sheet script and tried to cut the API calls to sheet the most I could. But the script has to make request to external source (Zillow API) with URLFetch which is time consuming. Also, because I can not make more than one request per second, I had to wait for about 700 milliseconds before I make another request to the API.

    The script behaves exactly it should be now I encounter exceeded maximum execution time (of 6 minutes). Thank you for sharing your work, most people are using it but I have problem implementing it in my use case. Can you please suggest how can I implement it in this script? Any suggestions would be really appreciated.

    If you create a copy of the sheet along with the script, you will have all the details required at your copy. Just for reference, the script data from first four columns to make first API call, based on which it makes another call and populates the rest of columns for that row. For 18 rows it takes around 31 seconds to complete the execution but when the dataset increases, it just throws the exceeded maximum execution time exception.

    Sheet with Script: https://docs.google.com/spreadsheets/d/1cT2mKgZywgwzV-So5nr8QEWPlo5CL9VIEsGWgmXwjLQ/edit?usp=sharing

    Reply

    • Hi Aamir,
      Thanks for your comment!
      I found your script very interesting and modified it so that it can run for a long time.
      I tried to achieve it in the simplest way, but if the time for initialization and termination process is a problem, please try defining an “initializer” or “finalizer”.
      It is implemented as a combinedTest function in the script of this sheet.
      https://docs.google.com/spreadsheets/d/10sC_jqk1yqlzrmzlEyrueDIsl4JYEtccNCGxLB1fDf8/edit?usp=sharing

      Reply

      • Thank you so much for the help. I was taking the same approach but was a bit impatient to wait and test the script with more data. When testing my approach in the editor window, after 5 minutes it prints “*** The process has been suspended. ***” to the console and then stops execution. Which actually stops the execution of the current trigger and registers a new trigger.

        Now after testing your script with about 1300 rows of data, the console did stop the execution but after a few minutes, it started updating the data from the row where it left in the previous trigger. This is what I wanted to do and it works like a charm.

        Wish you more power and success. 😊

        Reply

  11. Hello there! I have a situation with the 6-minute limit and I’m not sure if this solution can help me:
    1. I have a folder with 30000 pdf files from which I have to get their names and URL. I implemented a function that works properly for getting the name and URL but it takes much longer than 6-minutes so it runs into this problem.
    2. So, I decided to make 30 folders with 1000 pdf each and the code works but I have to be running it every time manually.

    I’m not so pro to figure out if this code can help me to process the folder with 30000 pdf but my idea is to process chunks of 1000 pdf and then stop the process in some way and restarted and process the next 1000 pdf and so on until it completes the 30000 pdf.

    Is this possible? How do you recommend me do it? Thanks!

    Reply

  12. Hello, I tried to implement the template but if I click on Execute nothing happens (after selecting my function name and setting the parameters 5,30,200,1). If I run the Script it only works up to a 6-minute limit 😦 My code is as follow:

    var folders = [‘35001a36000′,’36001a37000’]

    function getNameUrl() {
    var folderlisting = ‘CONSOLIDADOGLOBAL35001a37000’
    var ss = SpreadsheetApp.create(folderlisting);
    var sheet = ss.getActiveSheet();
    sheet.appendRow( [‘name’, ‘link’] );
    folders.forEach(item => {
    var items = DriveApp.getFoldersByName(item)
    var folder = items.next()
    var contents = folder.getFiles()
    // console.log(contents)

    var file
    var name
    var link
    var row

    while(contents.hasNext()) {
    file = contents.next();
    name = file.getName();
    link = file.getUrl();
    sheet.appendRow( [name, link] );
    }
    })
    };

    I replaced the HelloWorld function with this code. Please guide me about the correct use of the class. Thanks

    Reply

      • Many thanks for your help!!!! What are the implications of the last comment? Because with my previous code, each folder of 1000 pdf takes around 4 minutes to process. But splitting the folders with 1000 pdf each one is time-consuming and my idea is to be able to process one folder with 15000 or more pdf in it which, as you may guess, will take longer than 6 minutes to process. I’ve tested the LongRun function with the folder with 15000 pdf but the minute-6 is still finishing the process. What can I do in this case? Thanks for all your help!

        Reply

      • Also, I tried running the code with 4 folders with 1000 pdf each one and started at 2:29:54am, then at 2:34:41am I got “The process has been suspended” and one second after the execution was completed. When I open the file it looks like is still filling it with the name and URL even when the code mentioned the execution was completed. This exact behavior happened in both tests I ran with 3 folders and 4 folders respectively. Why is happening this? Is correct to finish the execution in 1 second right after mentioning the process has been suspended? I’d really appreciate all your support! Thanks!!!!

        Reply

  13. Thanks for your reply. 😊
    “The process has been suspended” means that the process will resume in a few moments. That is correct behavior.
    “Execution completed” message is just announcing that the first process is finished.
    You can see all execution logs in Apps Script Editor’s “Executions” pane and can see the next trigger in the “Triggers” pane.

    And I’ve made another version of the script you asked about above. Código2.gs shows how to process many pdf files which are all in one folder. Please see the file I already shared with you. Thanks!

    Reply

  14. Hello there, could you try running it with the whole folder of 15000 pdf? The value to be putted on the pdfCount should be the total pdf quantity? In my case 15000?
    Also, something happened to the first version. Yesterday I was able to run it using an array of folders and I was able to get 3000 pdf in one solely file but today, as I mentioned in one of the last messages, when I run it, for instance, with 3 pdf in the array it takes only 3 pdf names and URL instead of processing 3 complete folders.
    This is the code of Código.gs:
    var folders = [‘35001a36000′,’36001a37000′,’37001a38000′,’38001a39000′,’39001a40000′,’40001a41000′,’41001a42000’]
    function myFunction123() {
    var folderlisting = ‘CONSOLIDADOGLOBAL35001a42000final’
    var ss = SpreadsheetApp.create(folderlisting);
    var sheet = ss.getActiveSheet();
    sheet.appendRow( [‘name’, ‘link’] );
    var params = [ss.getId()];
    executeLongRun(“myFunction123Main”, folders.length, params);
    // folders.forEach(item => {
    // var items = DriveApp.getFoldersByName(item)
    // var folder = items.next()
    // var contents = folder.getFiles()
    // // console.log(contents)
    // var file
    // var name
    // var link
    // var row
    // while(contents.hasNext()) {
    // file = contents.next();
    // name = file.getName();
    // link = file.getUrl();
    // sheet.appendRow( [name, link] );
    // }
    // });
    }
    function myFunction123Main(index, params) {
    var ss = SpreadsheetApp.openById(params[0]);
    var sheet = ss.getActiveSheet();
    let item = folders[index];
    var items = DriveApp.getFoldersByName(item)
    var folder = items.next()
    var contents = folder.getFiles()
    // console.log(contents)
    var file
    var name
    var link
    var row
    while(contents.hasNext()) {
    file = contents.next();
    name = file.getName();
    link = file.getUrl();
    sheet.appendRow( [name, link] );
    }
    }
    At the end of the day, I’m trying to get this file: https://docs.google.com/spreadsheets/d/1qj3IKexgeKiYcpWCP5QQMsq6jBwjA2l7W3f7SMX-Eos/edit?usp=sharing
    Today I had to work with the basic function I made again and change manually the name of the folder with 1000pdf in it but I’m still studying your functions to find the cause of the issue (of course, I don’t have a clue lol, I hope someday became such as developer like you).
    BTW the file you shared with me in the previous comment is on reading mode. Could you authorize me access?
    Is possible we can have a short meeting pleaseee?

    Reply

  15. If you run it with pdfCount=15000, it will be successful.
    However, Google Drive has multiple limits other than 6minutes-limit, and processing such a large amount of files may hit the limit, I don’t know.

    You can copy the result file I created for 400 pdfs by clicking on this link.
    https://docs.google.com/spreadsheets/d/1_eNn884Wrv1viSjrXjQbCcyTYpw87iy5vIQ-Kogm1DA/copy

    By the way, I still don’t speak English, so the meeting will confuse you even more, lol.
    Good effort👍

    Reply

  17. Hello Inclu,
    So grateful I found your Longrun. Was able to incorporate it into a VBA to Apps Script conversion that runs about 30 minutes in desktop Excel. In testing/tweaking it I often want to kill the app and restart from 0. It doesn’t seem to shut down everything if I just click the Execute button again, as it sometimes picks up in Index where it left off. Not sure how best to cancel execution. Can you please advise?
    Thanks

    Reply

    • Hi, Ed! Thanks for using LongRun.!
      LongRun uses the Property Service and maybe there are some old data in it.
      To reset this, I think you can at first call LongRun.instance.reset(“function name here”).
      But sorry to be honest, I’ve never tried this. Can you please try it?

      Reply

  18. Hi again,
    Thanks for the prior suggestion. Doesn’t seem to be working
    .
    To recap, I’ve adapted the GAS-Terminal by modifying the LongTermTest code to substitute my main program for the sleep function. It runs as I had hoped for, iterating 50 times to create 3 sheets with 1000×30 cells of data and takes about 40 minutes of total execution time to complete. However, many times I wish to interrupt the processing to start completely over – eg. reset the loop index to 0 and flush anything else in process, and not wait for the full 50 cycles.
    I’ve tried clicking Execute button again, but that does’t seem work – either it doesn’t stop or it jumps to some new index or the LogUtils displays the final ‘finished’ message and then keeps going. Whatever.
    So I created a macro that I can initiate to kill it while it’s looping, and put this kind of code in the macro, using LongRun and LongRunTest as the funcName
    function executionReset() {
    var isReset = LongRun.instance.reset(LongRunTest);
    LogUtils.i(‘reset =’ + isReset);
    return isReset;
    }
    Not sure if this is what you were alluding to previously, but this one doesn’t work and just shows reset = undefined.
    Thanks for any help you can provide.

    Reply

    • Sorry, I’m late.
      Unfortunately, you can’t stop the process immediately, you can only stop the process after it suspends( i.e. when the log shows *** The process has been suspended. ***).
      When it suspends, the process registers the trigger so that it can be resumed the next time.
      LongRun.instance.reset() deletes the trigger.

      So after “** The process has been suspended. ***” message shows, can you please try to call the executionReset() method?
      And I noticed that you don’t pass the function name to LongRun.instance.reset() as String.
      correct -> LongRun.instance.reset(“LongRunTest”);

      And this reset method never returns any value, so isReset is undefined.
      Thanks.

      Reply

      • Thanks so much for the help above!

        I can now get the execution to quit by selection of my (now corrected) macro from the Extensions menu of the google sheet on my PC.

        My full objective is to be able to Go and Reset the execution from my mobile iphone. so I’ve adapted the 5th parameter box to make a data-validated drop down box with ‘Go’ and ‘Reset’ choices to kick off the execution and stop it, respectively, then inserting the into the onEdit event some code to test for Go and Reset and try to call the
        new TerminalController().onExecuteButtonClick();
        and my executeReset() custom function
        in the gas-TerminalController file.

        Also hard code “yes” so to eliminate the popup prompts asking for LongRun and Ignore Exisiting.

        Doesn’t see to work for either Go or Reset
        Do you think this is doable?

        Best regards,
        Ed

        Reply

        • Hi, did your code run?
          If you insert ‘console.log()’ code in the onEdit() and then change the drop-down box from your iPhone, is the log output?
          Is it possible to check how far it has been executed by inserting log() codes in various places?
          Is there an error log?

          Thanks!

          Reply

  19. Hi Inclu-Cat! I am using the script published by Wriju over at https://dev.to/wrijugh/recursive-file-list-from-google-drive-1mdk to perform a recursive directory listing of a huge Google Drive archive. I am not the author of this script and I have no experience with scripting unfortunately. Of course, I run into the six-minute limit. How could I combine your workaround with this script to continue to allow me to do my recursive directory list?
    Thank you so much!

    Reply

    • Hi monchavo, sorry I’m so late.
      I saw your code, but I think it’s difficult to apply the LongRun class to your code.
      Because your code uses recursive call while the LongRun class can only be applied to standard loops.
      Thanks.

      Reply

  20. Hi！Thank you for your excellent work! I’m working with tabular data, and during a long run, I need to keep track of which row I’m currently running on. How can I keep track of this variable?
    Thank you so much!

    Reply

  21. Hi Inclu Cat! I just wanted to say that your work was very helpful, I was able to implement it successfully, and I really appreciate it!

    Thank you very much, you’re awesome!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *