Search code examples
google-apps-scriptgoogle-sheetsgoogle-apps

Is there a way where I can minimize execution time of a google apps script through triggers?

So, let's think of it like this: I have 20 units of data where the processing of each unit takes 1 minute to be processed (total 20 minutes of processing), and the maximum execution time for an apps script function is (360 seconds), the thing is I dont want to divide them to like 4 parallel executions manually so I thought of this:

  1. make a driver function to get the 20 units at once 2) check for current triggers.

  2. if < 20 triggers are there, divide the units by the available triggers to create some sort of concurrency (then the whole execution will finish in 1 minute if n of triggers atm is 0.

  3. if >=20 (max limit) sleep for a minute then check for available triggers again

  4. run a function to delete each trigger after it finishes execution.

so is this achievable or is there a better way to workaround the max execution time and the concurrency problems in apps script?

Solution

  • I believe your goal is as follows.

    • You have 20 units.
    • At each unit, you want to convert the data.
      • You have already created the script for converting the data.
    • You want to achieve this using Google Apps Script.
    • You want to reduce the cost of this process.

    Issue and workaround:

    Unfortunately, in the current stage, there are no methods for directly running the Google Apps Script with the asynchronous process. So, in order to achieve your goal, it is required to think of a workaround. In this answer, I would like to propose a workaround. The flow of this workaround is as follows.

    1. Web Apps is deployed.
      • Your current script for converting the data is used with Web Apps.
    2. In order to achieve the asynchronous process, a Google Apps Script library is used. Ref
      • This library achieves the asynchronous process using the fetchAll method of Class UrlFetchApp. Ref

    By this, the script can be run with the asynchronous process.

    Usage:

    1. Install a library.

    Please install the library to your current Google Apps Script project. You can see the method for installing it at here.

    2. Prepare sample script.

    // This is your current script.
function myFunction(e) {

  // do something.
  //return result; // Please return the values you want to return.

  return e; // This is a sample return value for testing this script.
}

function doPost(e) {
  return RunAll.RunFunctionsByDoPost(this, e);
}

// Please run this function.
function main() {
  var url = "https://script.google.com/macros/s/###/dev"; // Here, please set the URL of Web Apps.
  var token = ScriptApp.getOAuthToken();
  var resource = [
    {
      functionName: "myFunction",
      arguments: "sample parameter 1", // Please set the value you want to give.
      webAppsURL: url,
      accessToken: token,
    },
    {
      functionName: "myFunction",
      arguments: "sample parameter 2", // Please set the value you want to give.
      webAppsURL: url,
      accessToken: token,
    },
    {
      functionName: "myFunction",
      arguments: "sample parameter 3", // Please set the value you want to give.
      webAppsURL: url,
      accessToken: token,
    },
  ];
  var res = RunAll.DoWebApps(resource);
  res.forEach(function (r) {
    Logger.log(r.getContentText());
  });
}
    • This is a sample script. Of cource, you can create the value of resouece using a loop.

    3. Deploy Web Apps

    1. On the script editor, Open a dialog box by "Publish" -> "Deploy as web app".
    2. Select "User accessing the web app" or "Me" for "Execute the app as:".
    3. Select "Only myself" for "Who has access to the app:".
      • By this setting, Web Apps can be accessed by only you who are the owner. If "Anyone, even anonymous" is set, all people who know the URL of Web Apps can be accessed. So please be careful.
    4. Click "Deploy" button as new "Project version".
    5. Automatically open a dialog box of "Authorization required".
      1. Click "Review Permissions".
      2. Select own account.
      3. Click "Advanced" at "This app isn't verified".
      4. Click "Go to ### project name ###(unsafe)"
      5. Click "Allow" button.
    6. Copy the URL of "latest code" of "Test web app for your latest code.".
      • It's like https://script.google.com/macros/s/###/dev.
    7. Click "OK".
    • By this, even when the script in the project deployed Web Apps was modified, the latest script can be automatically used.
    • In this setting, the deployed Web Apps in the project is accessed from the script in the same project.

    4. Testing:

    When above script is run, when your setting is correct, the following result is obtained.

    {"FunctionName":"myFunction","Arguments":"sample parameter 1","Result":"sample parameter 1"}

{"FunctionName":"myFunction","Arguments":"sample parameter 2","Result":"sample parameter 2"}

{"FunctionName":"myFunction","Arguments":"sample parameter 3","Result":"sample parameter 3"}

    Note:

    • When I tested this script, the maximum number of workers is 29. So I thought that this workaround might be able to use to your situation. But I'm not sure about your actual script. So when this number cannot be used, please modify it.

    References: