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 = '';
}
};
Whenever the interpreter comes to something like this:
ss.getSheetByName('INPUT')
... it has to go to the Google Sheet to see if there is (currently) a sheet of that name, and then has to find the relevant cell within that sheet. Even though the script is running on a Google server, accessing a spreadsheet still takes more time than accessing a variable within the local Javascript environment.
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?
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];