Funzioni personalizzate in Fogli Google

Fogli Google offre centinaia di funzioni integrate come AVERAGE, SUM e VLOOKUP. Quando non sono sufficienti per le tue esigenze, puoi utilizzare Google Apps Script per scrivere funzioni personalizzate, ad esempio convertire i metri in miglia o recuperare i contenuti dal vivo da Internet, e poi utilizzarli in Fogli Google proprio come una funzione incorporata.

Come iniziare

Le funzioni personalizzate vengono create utilizzando JavaScript standard. Se non conosci JavaScript, Codecademy offre un ottimo corso per principianti. Nota: questo corso non è stato sviluppato da Google e non è associato a Google.

Di seguito è riportata una semplice funzione personalizzata, denominata DOUBLE, che moltiplica un valore di input per 2:

/**
 * Multiplies an input value by 2.
 * @param {number} input The number to double.
 * @return The input multiplied by 2.
 * @customfunction
*/
function DOUBLE(input) {
  return input * 2;
}

Se non sai come scrivere il codice JavaScript e non hai tempo di imparare, controlla nello store dei componenti aggiuntivi per verificare se qualcun altro ha già creato la funzione personalizzata di cui hai bisogno.

Creazione di una funzione personalizzata

Per scrivere una funzione personalizzata:

1. Crea o apri un foglio di lavoro in Fogli Google.
2. Seleziona la voce di menu Estensioni > Apps Script.
3. Elimina il codice nell'editor di script. Per la funzione DOUBLE sopra riportata, copia e incolla il codice nell'editor di script.
4. Fai clic su Salva in alto.

Ora puoi utilizzare la funzione personalizzata.

Recupero di una funzione personalizzata da Google Workspace Marketplace

Google Workspace Marketplace offre diverse funzioni personalizzate come componenti aggiuntivi per Fogli Google. Per utilizzare o esplorare questi componenti aggiuntivi:

1. Crea o apri un foglio di lavoro in Fogli Google.
2. In alto, fai clic su Componenti aggiuntivi > Installa componenti aggiuntivi.
3. Quando si apre la Google Workspace Marketplace, fai clic sulla casella di ricerca nell'angolo in alto a destra.
4. Digita "funzione personalizzata" e premi Invio.
5. Se trovi un componente aggiuntivo per la funzione personalizzata che ti interessa, fai clic su Installa per installarla.
6. La finestra di dialogo potrebbe indicare che il componente aggiuntivo richiede l'autorizzazione. In questo caso, leggi attentamente la notifica, quindi fai clic su Consenti.
7. Il componente aggiuntivo diventa disponibile nel foglio di lavoro. Per utilizzare il componente aggiuntivo in un foglio di lavoro diverso, apri l'altro e fai clic su Componenti aggiuntivi > Gestisci componenti aggiuntivi. Trova il componente aggiuntivo che vuoi utilizzare e fai clic su Opzioni > Utilizza in questo documento.

Utilizzo di una funzione personalizzata

Dopo aver scritto una funzione personalizzata o averne installata una daGoogle Workspace Marketplace, potrai utilizzarla con facilità come:

1. Fai clic sulla cella in cui vuoi utilizzare la funzione.
2. Digita un segno di uguale (=) seguito dal nome della funzione e da eventuali valori di input (ad esempio =DOUBLE(A1)) e premi Invio.
3. La cella mostrerà temporaneamente Loading..., quindi restituirà il risultato.

Linee guida per le funzioni personalizzate

Prima di creare una funzione personalizzata, devi seguire alcune linee guida.

Denominazione

Oltre alle convenzioni standard per la denominazione delle funzioni JavaScript, tieni presente quanto segue:

• Il nome di una funzione personalizzata deve essere diverso dai nomi delle funzioni integrate, ad esempio SUM().
• Il nome di una funzione personalizzata non può terminare con un trattino basso (_), che indica una funzione privata in Apps Script.
• Il nome di una funzione personalizzata deve essere dichiarato con la sintassi function myFunction(), non var myFunction = new Function().
• L'uso delle maiuscole non è importante, anche se i nomi delle funzioni dei fogli di lavoro sono tradizionalmente maiuscole.

Argomenti

Come una funzione incorporata, una funzione personalizzata può assumere argomenti come valori di input:

• Se chiami la funzione con un riferimento a una singola cella come argomento (come =DOUBLE(A1)), l'argomento sarà il valore della cella.

• Se chiami la tua funzione con un riferimento a un intervallo di celle come argomento (ad esempio =DOUBLE(A1:B10)), l'argomento sarà una matrice bidimensionale dei valori delle celle. Ad esempio, nello screenshot di seguito, gli argomenti in =DOUBLE(A1:B2) vengono interpretati da Apps Script come double([[1,3],[2,4]]). Tieni presente che il codice campione per DOUBLE da sopra deve essere modificato per accettare una matrice come input.

  
  

• Gli argomenti delle funzioni personalizzate devono essere deterministici. Ciò significa che le funzioni integrate nei fogli di lavoro che restituiscono un risultato diverso ogni volta che vengono calcolate, come NOW() o RAND(), non sono consentite come argomenti per una funzione personalizzata. Se una funzione personalizzata cerca di restituire un valore in base a una di queste funzioni volatili integrate, mostrerà Loading... all'infinito.

Valori restituiti

Ogni funzione personalizzata deve restituire un valore da visualizzare, in modo che:

• Se una funzione personalizzata restituisce un valore, viene visualizzato nella cella da cui è stata chiamata la funzione.
• Se una funzione personalizzata restituisce una matrice bidimensionale dei valori, i valori occupano le celle adiacenti, purché tali celle siano vuote. In questo modo, la matrice sovrascriverà i contenuti della cella esistente, la funzione personalizzata genererà un errore. Per un esempio, consulta la sezione sull'ottimizzazione delle funzioni personalizzate.
• Una funzione personalizzata non può influire su celle diverse da quelle a cui restituisce un valore. In altre parole, una funzione personalizzata non può modificare celle arbitrarie, solo le celle da cui viene chiamata e le celle adiacenti. Per modificare celle arbitrarie, utilizza un menu personalizzato per eseguire una funzione.
• Una chiamata funzione personalizzata deve tornare entro 30 secondi. In caso contrario, viene visualizzato un errore Internal error executing the custom function. nella cella

Tipi di dati

Fogli Google archivia i dati in formati diversi a seconda della natura dei dati. Quando questi valori vengono utilizzati nelle funzioni personalizzate, Apps Script li considera il tipo di dati appropriato in JavaScript. Di seguito sono riportate le aree di confusione più comuni:

• Le date e gli orari in Fogli diventano oggetti Data in Apps Script. Se il foglio di lavoro e lo script utilizzano fusi orari diversi (un problema raro), la funzione personalizzata dovrà compensare.
• Anche i valori della durata in Fogli diventano oggetti Date, ma utilizzarli può essere complicato.
• I valori percentuali in Fogli diventano numeri decimali in Apps Script. Ad esempio, una cella con valore 10% diventa 0.1 in Apps Script.

Completamento automatico

Fogli Google supporta il completamento automatico per le funzioni personalizzate, proprio come per le funzioni integrate. Mentre digiti il nome di una funzione in una cella, viene visualizzato un elenco di funzioni integrate e personalizzate corrispondenti a ciò che inserisci.

Le funzioni personalizzate verranno visualizzate in questo elenco se il relativo script include un tag JsDoc @customfunction, come nell'esempio DOUBLE() riportato di seguito.

/**
 * Multiplies the input value by 2.
 *
 * @param {number} input The value to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return input * 2;
}

Avanzati

Utilizzo dei servizi Google Apps Script

Le funzioni personalizzate possono chiamare determinati servizi Google Apps Script per eseguire attività più complesse. Ad esempio, una funzione personalizzata può chiamare il servizio Lingua per tradurre una frase inglese in spagnolo.

A differenza della maggior parte degli altri tipi di Apps Script, le funzioni personalizzate non richiedono mai agli utenti di autorizzare l'accesso ai dati personali. Di conseguenza, possono chiamare solo i servizi che non hanno accesso ai dati personali, in particolare:

Servizi supportati	Note
Cache	Funziona, ma non particolarmente utile nelle funzioni personalizzate
HTML	Possono generare codice HTML, ma non possono visualizzarlo (raramente utile).
JDBC
Language
Blocca	Funziona, ma non particolarmente utile nelle funzioni personalizzate
Maps	Può calcolare le indicazioni stradali, ma non visualizzare le mappe
Proprietà	getUserProperties() ottiene solo le proprietà del proprietario del foglio di lavoro. Gli editor dei fogli di lavoro non possono impostare le proprietà utente in una funzione personalizzata.
Foglio di lavoro	Sola lettura (può utilizzare la maggior parte dei metodi get*(), ma non set*()). Impossibile aprire altri fogli di lavoro (SpreadsheetApp.openById() o SpreadsheetApp.openByUrl()).
Recupero URL
Utilità
XML

Se la funzione personalizzata genera il messaggio di errore You do not have permission to call X service., il servizio richiede l'autorizzazione dell'utente e non potrà quindi essere utilizzata in una funzione personalizzata.

Per utilizzare un servizio diverso da quelli elencati sopra, crea un menu personalizzato che esegua una funzione di Apps Script invece di scrivere una funzione personalizzata. Una funzione attivata da un menu chiederà l'autorizzazione all'utente, se necessario, e di conseguenza potrà utilizzare tutti i servizi Apps Script.

Condivisione

Le funzioni personalizzate iniziano con associazione al foglio di lavoro in cui sono state create. Ciò significa che una funzione personalizzata scritta in un foglio di lavoro non può essere utilizzata in altri fogli di lavoro, a meno che non utilizzi uno dei seguenti metodi:

• Fai clic su Estensioni > Apps Script per aprire l'editor di script, quindi copia il testo dello script dal foglio di lavoro originale e incollalo nell'editor di script di un altro foglio di lavoro.
• Crea una copia del foglio di lavoro che contiene la funzione personalizzata facendo clic su File > Crea una copia. Quando viene copiato un foglio di lavoro, vengono copiati anche tutti gli script associati. Chiunque abbia accesso al foglio di lavoro può copiare lo script. I collaboratori che hanno solo accesso in visualizzazione non possono aprire l'editor di script nel foglio di lavoro originale. Tuttavia, quando creano una copia, ne diventano i proprietari e possono visualizzare lo script.
• Pubblica lo script come componente aggiuntivo Editor di Fogli Google.

Tutti gli script associati ai container hanno gli stessi elenchi di accesso dei container. Questo significa che chiunque abbia l'autorizzazione per la modifica del foglio di lavoro può modificare qualsiasi codice Apps Script allegato.

Ottimizzazione

Ogni volta che in un foglio di lavoro viene utilizzata una funzione personalizzata, Fogli Google effettua una chiamata separata al server Apps Script. Se il foglio di lavoro contiene decine (o centinaia) o migliaia di chiamate di funzioni personalizzate, la procedura può essere abbastanza lenta.

Di conseguenza, se prevedi di utilizzare una funzione personalizzata più volte in un intervallo di dati di grandi dimensioni, valuta la possibilità di modificare la funzione in modo che accetti un intervallo come input sotto forma di una matrice bidimensionale, quindi restituisca una matrice bidimensionale che può fuoriuscire nelle celle appropriate.

Ad esempio, la funzione DOUBLE() mostrata sopra può essere riscritta per accettare una singola cella o un intervallo di celle come segue:

/**
 * Multiplies the input value by 2.
 *
 * @param {number|Array<Array<number>>} input The value or range of cells
 *     to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return Array.isArray(input) ?
      input.map(row => row.map(cell => cell * 2)) :
      input * 2;
}

L'approccio precedente utilizza il metodo map dell'oggetto Array di JavaScript per richiamare ricorsivamente DOUBLE su ogni valore nella matrice bidimensionale di celle. Restituisce una matrice bidimensionale che contiene i risultati. In questo modo, puoi chiamare DOUBLE una sola volta, ma fare in modo che venga calcolato per un numero elevato di celle contemporaneamente, come mostrato nello screenshot di seguito. Puoi eseguire la stessa operazione con istruzioni if nidificate anziché la chiamata map.

Analogamente, la funzione personalizzata di seguito recupera in modo efficiente i contenuti dal vivo da Internet e utilizza un array bidimensionale per visualizzare due colonne di risultati con una sola chiamata di funzione. Se ogni cella richiedesse una propria chiamata funzione, l'operazione richiederebbe molto più tempo, poiché il server Apps Script dovrebbe scaricare e analizzare il feed XML ogni volta.

/**
 * Show the title and date for the first page of posts on the
 * Developer blog.
 *
 * @return Two columns of data representing posts on the
 *     Developer blog.
 * @customfunction
 */
function getBlogPosts() {
  var array = [];
  var url = 'https://gsuite-developers.googleblog.com/atom.xml';
  var xml = UrlFetchApp.fetch(url).getContentText();
  var document = XmlService.parse(xml);
  var root = document.getRootElement();
  var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
  var entries = document.getRootElement().getChildren('entry', atom);
  for (var i = 0; i < entries.length; i++) {
    var title = entries[i].getChild('title', atom).getText();
    var date = entries[i].getChild('published', atom).getValue();
    array.push([title, date]);
  }
  return array;
}

Queste tecniche possono essere applicate a quasi tutte le funzioni personalizzate utilizzate ripetutamente durante un foglio di lavoro, anche se i dettagli dell'implementazione possono variare a seconda del comportamento della funzione.