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

Question

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();
  }```

What's the point of the if block at then end? Are you just trying to get the current date? Why not use javascript's new Date()? — Dmitry Kostyuk
The last if statement is something I took from another project as a stamp to say whhen the code was executed. I'm still new to app script but i'll give new Date a try. — Matt Olliff
This isn't so much apps-script as just plain javascript. You might want to look into this documentation and maybe a couple of tutorials on YouTube — Dmitry Kostyuk

Dmitry Kostyuk Dmitry Kostyuk · Accepted Answer · 2021-07-25T12:56:35

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.

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

1 Answers