'Exception: Too many simultaneous invocations: Spreadsheets' - Is it because I am opening spreadsheets in a loop?

Question

I have been getting the error:

Exception: Too many simultaneous invocations: Spreadsheets

I have looked on the quota page and it doesn't say anything about opening spreadsheets. I am assuming a simultaneous invocation is where something happens at the same time as something else, but I would assume my script, which is only run from a single trigger, would work through each process one by one and, therefore, should not have anything happening simultaneously. Am I completely mistaken? I looked at Does google app script simultaneous executions quota apply for the script in question or for each users? but I still don't know what it is about.

I have tried running the script manually and it seems to work through each record absolutely fine and then it just hangs at the end, after reaching the last row (which was processed correctly).

My code is below:

function importDataFromSAN() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  const data = sheet.getDataRange().getValues();
  data.shift();

  const formSheet = SpreadsheetApp.openById('MyID');
  const formData = formSheet.getDataRange().getValues();
  formData.shift();

  data.forEach(row => {

    const fileURL = row[41];
    const studentNumber = row[10];

    if (fileURL === '') {

      console.log('No form');

    }
    else {
      const file = SpreadsheetApp.openByUrl(fileURL);

      const medConditions = file.getRange('B49');
      const currentSituation = file.getRange('B54');

      const matchNumber = formData.filter(a => a[4] === studentNumber);

      matchNumber.forEach(a => {

        const healthNeeds = a[10];
        const learningDifficulty = a[18];
        const riskAssessment = a[27] + ' ' + a[29] + ' ' + a[31] + ' ' + a[33] + ' ' + a[35] + ' ' + a[37] + ' ' + a[39] + ' ' + a[41] + ' ' + a[43] + ' ' + a[45] + ' ' + a[47] + ' ' + a[49];
        const outsideAgencies = a[73] + ' ' + a[75] + ' ' + a[77] + ' ' + a[79] + ' ' + a[81];

        medConditions.setValue('Health Needs - ' + healthNeeds + '\nLearning Difficulties - ' + learningDifficulty);
        currentSituation.setValue('Risk Assessment Details - ' + riskAssessment + '\nOutside Agencies Details - ' + outsideAgencies);
      });
    }
  });
}

I tired running the script manually. I was expecting it to fail after hitting a certain number but it made it through all rows and then got hung up at the very end.

EDIT: After a long wait, it completed on the manual run. I do run a different script from a different project at the same time as this one, could that cause the issue, or is the limit per project and other projects have no impact?

Answer 1

I found a way to stop the error from happening. I made a new column in my spreadsheet to check if the data had been transferred or not and spliced a 'Yes' if it had. Then I had to run it once manually, which it luckily didn't fail on.

Now, a new if/else checks if the new column has a 'Yes' in it and will skip it if it does.

I then decided to add another column to check if the data coming in was different to the data in the spreadsheet, so I spliced the data to this new column of the data I was copying as a string and checked it against a string of the new data.

This means that all of the checking is done in the server before it opens any spreadsheets and the majority of the time it doesn't open any spreadsheets at all, as none of the data has changed.

This now runs really quickly and no longer errors.

Thanks for all the help.