Google script timeout-CodePudding

I've been using a macro in an Excel for some years and wanted to translate it in a google script to collaborate in Drive.

I'm using a two sheets setup (one named "BILAN" which is the overview and one named INPUT for entering data. The script works just fine while there is not too much inputs, but I'm expecting to reach near a thousand inputs by the end of the file's use.

Basically, the script is a double loop to summarize the inputs in the BILAN sheet. Thanks in advance for your help !!

Here's the code I'm using :

function getTransportDates() {
  var ss = SpreadsheetApp.getActive();

 var strDatesTransport = '';
 const intNbClients = ss.getSheetByName('BILAN').getDataRange().getLastRow();
 const intNbInputs = ss.getSheetByName('INPUT').getDataRange().getLastRow();

 for (let i = 4; i <= intNbClients; i  ) {    // loop through the addresses in BILAN
  if (ss.getSheetByName('BILAN').getRange(i, 9).getValue() >0) {
   for (let j = 4; j <= intNbInputs; j  ) {  // loop through the adresses in INPUT
    if (ss.getSheetByName('INPUT').getRange(j, 2).getValue() == ss.getSheetByName('BILAN').getRange(i, 1).getValue()) {
     strDatesTransport = strDatesTransport   ' // '   ss.getSheetByName('INPUT').getRange(j, 1).getValue(); //.toISOString().split('T')[0];
    }
   }
  }
  ss.getSheetByName('BILAN').getRange(i, 10).setValue(strDatesTransport);
  strDatesTransport = '';
 }
};

CodePudding user response：

Reduce the number of calls to get info from Google Sheets

Whenever the interpreter comes to something like this:

ss.getSheetByName('INPUT')

... it has to go to the internet to see if there is (currently) a sheet of that name, and then has to find the relevant cell within that sheet.

The easiest way to reduce the number of calls is to read each of the sheets ("BILAN" and "INPUT") into a local Javascript variable.

In fact, it looks to me like you are extracting extremely specific sets of cells from each of the spreadsheets. Could you get each set of cells into an array, and then process the arrays?

CodePudding user response：

Try it this way:

function getTransportDates() {
  const ss = SpreadsheetApp.getActive();
  var sdt = '';
  const csh = ss.getSheetByName('BILAN');
  const cvs = csh.getRange(4, 1, csh.getLastRow() - 3, csh.getLastColumn()).getValues();
  const ish = ss.getSheetByName('INPUT');
  const ivs = ish.getRange(4, 1, ish.getLastRow() - 3, ish.getLastColumn()).getValues();
  cvs.forEach((cr,i) => {
    if((cr[8] > 0)) {
      ivs.forEach((ir,j)=>{
        if(ir[1] == cr[0]) {
          sdt  = ir[0];
        }
      });
    }
    ss.getSheetByName('BILAN').getRange(i   4, 10).setValue(sdt);
    sdt = '';
  });
}

Don't know where this goes: //.toISOString().split('T')[0];