Premetto subito di aver provato ad intavolare la stessa discussione su "masterdrive.it/linguaggi-strumenti-26/efficentamento-macro-per-google-apps-script-102538", e spero di non cadere nel crossposting. Li la discussione è ferma e volevo provare a confrontarmi anche con altri. Riporto di seguito la discussione:
Chiedo aiuto per un fastidio (perché non è un vero e proprio problema) che mi affligge.
Sto cercando di aggiornare dinamicamente gli elenchi per le validazioni dei dati nelle celle in Google Fogli e purtroppo, pur ottenendo il risultato sperato, mi ritrovo ad affrontare un problema di tempi di esecuzione che poco mi garba.
Premettendo che il codice in questione è questo:
/** @OnlyCurrentDoc */
function AggiornamentoSelezioni() {
//Costruisco la stringa per la validazione
var args = new Array();
var shSituazione = SpreadsheetApp.getActive().getSheetByName('Situazione');
var valoriLuoghi = shSituazione.getRange(2,1,1000 ,3).getValues();
for(i=0;i<1000;i++){
//Ciclo le righe del range: se il valore della seconda colonna è strettamente maggiore della terza, aggiungo all'array la prima colonna.
if(valoriLuoghi[i][0].toString() != ''){
if(valoriLuoghi[i][1]>valoriLuoghi[i][2]){
args.push(valoriLuoghi[i][0].toString());
}
}
else {
//Se trovo una cella vuota, esco dal ciclo per non scorrere tutte e 1000 le righe
break;
}
}
//Imposto la validazione
var shScelte = SpreadsheetApp.getActive().getSheetByName('Scelte');
var celle = shScelte.getRange(2,2,100,1);
var valoriCelle = celle.getValues();
var regole = celle.getDataValidations();
var fine = false;
var pushed = false;
//Qui ciclo le varie DataValidations. Ho seguito quanto riportato dalla <a href="https://developers.google.com/apps-script/reference/spreadsheet/data-validation#copy" target="_blank">guida ufficiale</a>
for(var j = 0; j < regole.length; j++){
for(var k = 0; k < regole[j].length; k++){
var regola = regole[j][k];
if(regola != null){
if(valoriCelle[j][0] != ''){
args.push(valoriCelle[j][0].toString());
pushed = true;
}
regole[j][k] = regola.copy().withCriteria(regola.getCriteriaType(), [args, true]).build();
if(pushed){
args.pop();
pushed = false;
}
} else {
fine = true;
break;
}
}
if(fine) break;
}
celle.setDataValidations(regole);
Browser.msgBox('File pronto per la modifica!');
};
La logica è piuttosto semplice:
- Verifico che nel primo foglio vi siano ancora posti disponibili. Se si, aumento i valori disponibili per l'array;
- Imposto l'array precedentemente calcolato come lista di valori per la tendina della validazione dati. se è già presente un valore nella cella, aggiungo anche quello all'array e poi lo tolgo subito dopo.
Ho già sfruttato i suggerimenti che da Google per le
Best Practices ed effettivamente la miglioria si è vista parecchio. Inizialmente avevo affrontato il problema "alla VBA di Excel", ma qui leggere le singole celle una per volta è un bagno di sangue in termini prestazionali.
Mi resta il dubbio che la mia "autodidatticità" mi porti a scrivere del codice inefficiente e quindi chiedo se qualcuno, a tempo perso, abbia voglia di spulciare lo script per vedere se ho commesso oscenità che mi percuotano questa zappa sui piedi. Altrimenti è il server di Google che fa cilecca, ma in quel caso mi metto l'anima in pace.
Grazie in anticipo!