CODEWITHSUNDEEP
google-apps-script
Matt Olliff
Matt Olliff

Reputation: 3

Why is my Script taking so long to execute and how can I fix or get around it?

My code is hitting the maximum exceeded time for google app scripts and I'm not sure why. Previously this code would run in maybe a minute or two, and now with no changes it only just gets over half way through the data I want. Is there a way to optimise this so it takes a shorter amount of time or a way I can save the team and I variables to run the script a second time from where it finished?

  {
    for(var team in response.returnData.equipos) 
    {
      if(response.returnData.equipos[team].members.length > 0) 
      {
        var i = 0;
        while(i < response.returnData.equipos[team].members.length)
        {
          sheetName.appendRow([response.returnData.equipos[team].name, response.returnData.equipos[team].members[i].userId]);
          i++;
        }
      }
    }
  }
  if(team.length > 0) 
  {
    sheetName.getRange('D2').setValue('=NOW()');
    sheetName.getRange('D1').setValue(sheetName.getRange('D2').getValue());
    sheetName.getRange('D2').clear();
  }```

Upvotes: 0

Views: 2071

Answers (1)

Dmitry Kostyuk
Dmitry Kostyuk

Reputation: 1459

The reason your script is slow is that on every loop you are doing an appendRow(), which is an API call, which is slow. You need to refer to the best practices document for guidance.

Now to your problem.

Judging from your code, your source data looks like something like this:

const response = {
  returnData: {
    equipos: [
      { name: 'team 1', members: [{ userId: 1 }, { userId: 2 }] },
      { name: 'team 2', members: [{ userId: 3 }, { userId: 4 }] },
      { name: 'team 3', members: [{ userId: 5 }, { userId: 6 }] },
    ],
  },
};

And the outcome you are looking for to append to our sheet looks like this:

[ [ 'team 1', 1 ], 
  [ 'team 1', 2 ], 
  [ 'team 2', 3 ], 
  [ 'team 2', 4 ], 
  [ 'team 3', 5 ], 
  [ 'team 3', 6 ] ] 

In order to avoid doing an appendRow() on every loop, you need to build up a 2D array that looks like the output above and then add it all with one API call.

To build the output in question, you need to run this code:

const output = response.returnData.equipos.reduce(
  (acc, team) => [
    ...acc,
    ...team.members.map(member => [team.name, member.userId]),
  ],
  []
);

Now that you have that data in the correct format, append this to your sheet like so (this is supposing that sheetName from your code is an actual spreadsheet object and not just its name as the variable name suggests):

const lRow = sheetName.getLastRow();
sheetName
  .getRange(lRow + 1, 1, output.length, output[0].length)
  .setValues(output);

Let me know if this helps.

Upvotes: 2

Related Questions