Funzioni personalizzate in Fogli Google

Fogli Google offre centinaia di funzioni integrate come AVERAGE, SUM e VLOOKUP. Se queste non sono sufficienti per le tue esigenze, puoi utilizzare Apps Script per scrivere funzioni personalizzate e poi utilizzarle in Fogli come se fossero funzioni integrate.

Per esempi di funzioni personalizzate, consulta i seguenti tutorial:

• Calcolare il prezzo di vendita degli articoli scontati (guida rapida)
• Calcolare uno sconto per prezzi a livelli
• Calcolare la distanza in auto e convertire i metri in miglia
• Riassumere i dati di più fogli
• Verificare l'accuratezza delle affermazioni con un agente AI ADK e il modello Gemini

Per iniziare

Le funzioni personalizzate vengono create utilizzando JavaScript standard. Se non hai mai utilizzato JavaScript, Codecademy offre un corso per principianti. Questo corso non è stato sviluppato da Google e non è associato a Google.

Ecco una 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 in JavaScript e non hai tempo per imparare, controlla il Google Workspace Marketplace per vedere se qualcun altro ha già creato la funzione personalizzata di cui hai bisogno.

Creare una funzione personalizzata

Per scrivere una funzione personalizzata:

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

Ora puoi utilizzare la funzione personalizzata.

Ottenere una funzione personalizzata dal Google Workspace Marketplace

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

1. Crea o apri un foglio di lavoro in Fogli.
2. In alto, fai clic su Componenti aggiuntivi > Installa componenti aggiuntivi.
3. Una volta aperto il 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 di funzioni personalizzate che ti interessa, fai clic su Installa per installarlo.
6. Una finestra di dialogo potrebbe indicare che il componente aggiuntivo richiede l'autorizzazione. In questo caso, leggi attentamente l'avviso, quindi fai clic su Consenti.
7. Il componente aggiuntivo diventa disponibile nel foglio di lavoro. Per utilizzare il componente aggiuntivo in un altro foglio di lavoro, apri l'altro foglio di lavoro e, in alto, fai clic su Componenti aggiuntivi > Gestisci componenti aggiuntivi. Trova il componente aggiuntivo che vuoi utilizzare e fai clic su Opzioni more_vert > Utilizza in questo documento.

Utilizzare una funzione personalizzata

Una volta scritta una funzione personalizzata o installata una dal Google Workspace Marketplace, viene utilizzata come una funzione integrata:

1. Fai clic sulla cella in cui vuoi utilizzare la funzione.
2. Digita un segno di uguale (=) seguito dal nome della funzione e da qualsiasi valore di input — ad esempio, =DOUBLE(A1) — e premi Invio.
3. Nella cella viene visualizzato temporaneamente Loading..., quindi viene restituito il risultato.

Linee guida per le funzioni personalizzate

Prima di scrivere la tua funzione personalizzata, devi conoscere alcune linee guida.

Denominazione delle funzioni

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 come 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().
• La distinzione tra maiuscole e minuscole non è importante, anche se i nomi delle funzioni dei fogli di lavoro sono tradizionalmente in maiuscolo.

Argomenti

Come una funzione integrata, una funzione personalizzata può accettare argomenti come valori di input:

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

• Se chiami la funzione con un riferimento a un intervallo di celle come argomento (ad esempio =DOUBLE(A1:B10)), l'argomento è un array bidimensionale dei valori delle celle. Ad esempio, nello screenshot seguente, 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 descritto in precedenza dovrebbe essere modificato per accettare un array come input.

  
  

• Gli argomenti delle funzioni personalizzate devono essere deterministici. Ciò significa che le funzioni dei fogli di lavoro integrate che restituiscono un risultato diverso ogni volta che vengono calcolate, come NOW() o RAND(), non sono consentite come argomenti di una funzione personalizzata. Se una funzione personalizzata tenta di restituire un valore basato su una di queste funzioni integrate volatili, viene visualizzato Loading... a tempo indeterminato.

• Per attivare il ricalcolo, devi passare una cella o un intervallo di celle a cui viene fatto riferimento direttamente come argomento alla funzione personalizzata. In caso contrario, la funzione personalizzata non viene ricalcolata finché non modifichi la funzione o non modifichi il valore di una cella a cui viene fatto riferimento. Se utilizzi il metodo getValue nelle funzioni personalizzate, tieni presente che l'intervallo a cui viene fatto riferimento non viene passato direttamente come argomento alla funzione personalizzata.

Valori restituiti

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

• Se una funzione personalizzata restituisce un valore, questo viene visualizzato nella cella da cui è stata chiamata la funzione.
• Se una funzione personalizzata restituisce un array bidimensionale di valori, i valori vengono inseriti nelle celle adiacenti purché queste siano vuote. Se l'array sovrascrive il contenuto delle celle esistenti, la funzione personalizzata genera un errore. Per un esempio, consulta la sezione su l'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, ma 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 di funzione personalizzata deve essere restituita entro 30 secondi. In caso contrario, nella cella viene visualizzato #ERROR! e la nota della cella è Exceeded maximum execution time (line 0).

Tipi di dati

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

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

Completamento automatico

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

Le funzioni personalizzate vengono visualizzate in questo elenco se lo script include un JSDoc @customfunction tag, come nell'DOUBLE() esempio.

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

Avanzate

Questa sezione tratta argomenti avanzati relativi alle funzioni personalizzate.

Utilizzare i servizi di Google Apps Script

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

A differenza della maggior parte degli altri tipi di script Apps Script, le funzioni personalizzate non chiedono 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 i seguenti:

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 pertanto non può essere utilizzato in una funzione personalizzata.

Per utilizzare un servizio diverso da quelli nell'elenco precedente, crea un menu personalizzato che esegue una funzione Apps Script anziché scrivere una funzione personalizzata. Una funzione attivata da un menu chiede all'utente l'autorizzazione, se necessario, e di conseguenza può utilizzare tutti i servizi Apps Script.

Condividere funzioni personalizzate

Le funzioni personalizzate iniziano associate 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 gli script allegati. 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, diventano proprietari della copia e possono vedere lo script.
• Pubblica lo script come componente aggiuntivo dell'editor di Fogli.

Tutti gli script associati a un container condividono gli stessi elenchi di accesso dei relativi container. Ciò significa che chiunque abbia l'autorizzazione per modificare il foglio di lavoro può anche modificare qualsiasi codice Apps Script allegato. Per ulteriori informazioni, consulta Accesso agli script associati.

Ottimizzazione

Ogni volta che una funzione personalizzata viene utilizzata in un foglio di lavoro, Fogli effettua una chiamata separata al server Apps Script. Se il foglio di lavoro contiene decine (o centinaia o migliaia) di chiamate di funzioni personalizzate, questo processo può essere lento. Alcuni progetti con molte funzioni personalizzate o funzioni personalizzate complesse potrebbero subire un ritardo temporaneo nelle esecuzioni.

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 array bidimensionale, quindi restituisca un array bidimensionale che possa essere inserito nelle celle appropriate.

Ad esempio, la funzione DOUBLE() mostrata in precedenza 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;
}

Questo approccio utilizza il metodo map dell'oggetto Array di JavaScript sull'array bidimensionale di celle per ottenere ogni riga, quindi per ogni riga utilizza map di nuovo per raddoppiare il valore di ogni cella. Restituisce un array bidimensionale contenente i risultati. In questo modo, puoi chiamare DOUBLE una sola volta, ma calcolare contemporaneamente un numero elevato di celle, come mostrato nello screenshot seguente. Puoi ottenere lo stesso risultato con le istruzioni if nidificate anziché con la chiamata map.

Allo stesso modo, la seguente funzione personalizzata recupera in modo efficiente i contenuti live da internet e utilizza un array bidimensionale per visualizzare due colonne di risultati con una sola chiamata di funzione. Se ogni cella richiedesse la propria chiamata di 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 in un foglio di lavoro, anche se i dettagli di implementazione variano a seconda del comportamento della funzione.