Funzioni personalizzate in Fogli Google

Fogli Google offre centinaia di funzioni integrate come AVERAGE, SUM e VLOOKUP Quando non sono per soddisfare le tue esigenze, puoi usare Google Apps Script per scrivere funzioni personalizzate ad esempio per convertire i metri in miglia o recuperare contenuti in diretta da internet, per poi usarli. in Fogli Google, proprio come una funzione integrata.

Per iniziare

Le funzioni personalizzate vengono create usando JavaScript standard. Se non hai mai utilizzato JavaScript, Codecademy offre una ottimo corso per principianti. Nota: questo corso non è stato sviluppato da e non è associato a Google.

Ecco una funzione personalizzata semplice, denominata DOUBLE, che moltiplica una 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 scrivere JavaScript e non hai tempo per imparare, dai un'occhiata allo store dei componenti aggiuntivi per vedere se qualcun altro ha già creato la funzione personalizzata di cui hai bisogno.

crea 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 eventuale codice nell'editor di script. Per la funzione DOUBLE riportata sopra, è sufficiente copia e incolla il codice nell'editor di script.
  4. In alto, fai clic su Salva .

Ora puoi utilizzare la funzione personalizzata.

Ottieni una funzione personalizzata dall' Google Workspace Marketplace

Google Workspace Marketplace offre diverse opzioni personalizzate funziona come componenti aggiuntivi di 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 > Scarica componenti aggiuntivi.
  3. Una volta che lo strumento 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 con 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. Se sì, Leggi attentamente l'avviso, poi fai clic su Consenti.
  7. Il componente aggiuntivo diventa disponibile nel foglio di lavoro. Per utilizzare il componente aggiuntivo in una foglio di lavoro diverso, apri l'altro foglio di lavoro e fai clic su in alto, componenti aggiuntivi > Gestisci i componenti aggiuntivi. Trova il componente aggiuntivo che vuoi utilizzare e fai clic su Opzioni > Utilizza in questa documento di identità.

Utilizzo di una funzione personalizzata

Dopo aver scritto una funzione personalizzata o installata una funzione dal Google Workspace Marketplace, è facile da usare come 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. La cella mostrerà temporaneamente Loading..., poi restituirà il risultato.

Linee guida per le funzioni personalizzate

Prima di scrivere una funzione personalizzata, è necessario conoscere alcune linee guida.

Denominazione

Oltre alle convenzioni standard per la denominazione delle funzioni JavaScript, a conoscenza di 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().
  • L'uso delle lettere maiuscole non è importante, anche se i nomi delle funzioni dei fogli di lavoro sono tradizionalmente maiuscole.

Argomenti

Analogamente a una funzione integrata, una funzione personalizzata può assumere argomenti come valori di input:

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

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


  • Gli argomenti delle funzioni personalizzate devono essere deterministico. Questo sono le funzioni integrate dei fogli di lavoro che restituiscono ogni volta un risultato diverso calcoli, come NOW() o RAND(), non sono consentiti come argomenti a una funzione personalizzata. Se una funzione personalizzata cerca di restituire un valore in base a una di queste funzioni volatili integrate, mostrerà Loading... a tempo indeterminato.

Valori restituiti

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

  • 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 di valori bidimensionale, i valori l'overflow nelle celle adiacenti purché queste celle siano vuote. Se questo perché l'array sovrascriva i contenuti esistenti delle celle, la funzione personalizzata genera invece un errore. Per un esempio, consulta la sezione ottimizzare le funzioni personalizzate.
  • Una funzione personalizzata non può influire su celle diverse da quelle in cui restituisce un valore. In altre parole, una funzione personalizzata non può modificare celle arbitrarie, solo dalle celle da cui viene chiamato e dalle celle adiacenti. Per modificare celle arbitrarie, usa invece un menu personalizzato per eseguire una funzione.
  • Una chiamata di funzione personalizzata deve essere restituita entro 30 secondi. In caso contrario, cella visualizza #ERROR! e la nota della cella è Exceeded maximum execution time (line 0).

Tipi di dati

Fogli Google archivia i dati in diversi formati a seconda la natura dei dati. Quando questi valori vengono utilizzati nelle funzioni personalizzate, Lo script li considera come tipo di dati appropriato in JavaScript. Ecco le aree di confusione più comuni:

  • Le ore e le date in Fogli diventano Oggetti data in Apps Script. Se il foglio di lavoro e utilizzano fusi orari diversi (un problema raro), la funzione personalizzata devono compensare.
  • Anche i valori della durata in Fogli diventano Date oggetti, ma lavorare con loro può essere complicato.
  • I valori percentuali in Fogli diventano numeri decimali in Apps Script. Per 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, come per funzioni integrate. Man mano che digita il nome di una funzione in una cella, vedrai un elenco di corrispondenti a quanto inserito.

Le funzioni personalizzate verranno visualizzate in questo elenco se il relativo script include un oggetto 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;
}

Avanzate

Utilizzo dei servizi Google Apps Script

Le funzioni personalizzate possono chiamare servizi Google Apps Script per svolgere attività più complesse attività di machine learning. Ad esempio, una funzione personalizzata può richiamare Servizio Lingua per tradurre un testo in inglese a frase in spagnolo.

A differenza della maggior parte degli altri tipi di script di Google Apps, 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 a dati personali, in particolare:

Servizi supportati Note
Cache Funziona, ma non particolarmente utile nelle funzioni personalizzate
HTML Può generare codice HTML, ma non può visualizzarlo (raramente utile)
JDBC
Lingua
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à dell'oggetto proprietario del foglio di lavoro. Gli editor dei fogli di lavoro non possono impostare le proprietà utente in un funzione personalizzata.
Foglio di lavoro Sola lettura (è possibile 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 quindi non può essere utilizzata in una funzione personalizzata.

Per utilizzare un servizio diverso da quelli elencati sopra, crea un'istanza menu personalizzato che esegue una funzione Apps Script anziché scrivere una funzione personalizzata. Una funzione che viene attivata da un menu chiederà all'utente l'autorizzazione, se necessario, e potrà quindi utilizzare Apps Script.

Condivisione

Le funzioni personalizzate iniziano sono associate al foglio di lavoro in cui sono stati creati. Ciò significa che una funzione personalizzata scritta in un foglio di lavoro non può essere utilizzato in altri fogli a meno che non utilizzi uno dei seguenti metodi:

  • Fai clic su Estensioni > Apps Script per apri l'editor di script e 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 un foglio di lavoro viene copiato, tutti gli script allegati vengono copiate. 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 fanno una copia, diventano proprietario della copia e può visualizzare lo script).
  • Pubblica lo script come componente aggiuntivo dell'editor di Fogli Google.
di Gemini Advanced.

Ottimizzazione

Ogni volta che una funzione personalizzata viene utilizzata in un foglio di lavoro, Fogli Google crea una una chiamata separata al server Apps Script. Se il foglio di lavoro contiene decine (o centinaia o migliaia!) di chiamate di funzione personalizzate, questo processo può lento.

Di conseguenza, se prevedi di utilizzare una funzione personalizzata più volte su un un intervallo di dati, valuta la possibilità di modificare la funzione in modo che accetti un intervallo l'input sotto forma di array bidimensionale, poi restituisce una un array di dati che può fuoriuscire nelle celle appropriate.

Ad esempio, la funzione DOUBLE() mostrata sopra può essere riscritta per accettare una singola cella o 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 illustrato sopra utilizza il parametro map dell'oggetto Array di JavaScript in modo ricorsivo richiama DOUBLE su ogni valore nell'array bidimensionale di celle. Restituisce un valore un array bidimensionale che contiene i risultati. In questo modo, puoi chiamare DOUBLE solo una volta, ma fare in modo che venga calcolato per un numero elevato di celle contemporaneamente, come mostrato nello screenshot seguente. (Potresti ottenere lo stesso risultato con l'elemento if nidificato) anziché la chiamata map.)

Analogamente, la funzione personalizzata riportata di seguito recupera in modo efficiente i contenuti live Internet e utilizza un array bidimensionale per visualizzare due colonne di risultati con con una singola chiamata di funzione. Se ogni cella richiede una propria chiamata di funzione, il richiederebbe molto più tempo, dato che il server di Apps Script scaricare e analizzare ogni volta il feed XML.

/**
 * 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 dell'implementazione variano a seconda del comportamento della funzione.