Nozioni di base di Apps Script con Fogli Google n. 3: utilizzo dei dati

1. Introduzione

Ti diamo il benvenuto nella terza parte della playlist del codelab su Nozioni di base di Apps Script con Fogli Google.

Completando questo codelab, potrai scoprire come utilizzare la manipolazione dei dati, i menu personalizzati e il recupero dei dati delle API pubbliche in Apps Script per migliorare la tua esperienza in Fogli. Continuerai a lavorare con i corsi SpreadsheetApp, Spreadsheet, Sheet e Range introdotti nei codelab precedenti, in questa playlist.

Obiettivi didattici

• Come importare dati da un foglio di lavoro personale o condiviso in Drive.
• Come creare un menu personalizzato con la funzione onOpen().
• Come analizzare e manipolare i valori dei dati stringa nelle celle di Fogli Google.
• Come estrarre e manipolare i dati degli oggetti JSON da un'origine API pubblica.

Prima di iniziare

Questo è il terzo codelab nella playlist Concetti fondamentali di Apps Script with Fogli Google. Prima di iniziare questo codelab, assicurati di completare i codelab precedenti:

1. Macro e funzioni personalizzate
2. Fogli di lavoro, Fogli e intervalli

Che cosa ti serve

• Comprensione degli argomenti di base di Apps Script esplorati nei precedenti codelab di questa playlist.
• Familiarità con l'editor di Apps Script
• Familiarità con Fogli Google
• Possibilità di leggere la notazione A1 di Fogli
• Familiarità con JavaScript e la relativa classe String

2. Configura

Gli esercizi in questo codelab richiedono l'utilizzo di un foglio di lavoro. Segui questi passaggi per creare un foglio di lavoro da utilizzare in questi esercizi:

1. Crea un foglio di lavoro in Google Drive. Puoi farlo dall'interfaccia di Drive selezionando Nuovo > Fogli Google. Viene creato e aperto il nuovo foglio di lavoro. Il file viene salvato nella cartella di Drive.
2. Fai clic sul titolo del foglio di lavoro e modificalo da "Foglio di lavoro senza titolo" a "Manipolazione dei dati e menu personalizzati". Il foglio dovrebbe essere simile a questo:

3. Per aprire l'editor di script, fai clic su Estensioni> Apps Script.
4. Fai clic sul titolo del progetto Apps Script e modificalo da "Progetto senza titolo" a "Manipolazione dei dati e menu personalizzati". Fai clic su Rinomina per salvare la modifica del titolo.

Con un foglio di lavoro e un progetto vuoti, puoi iniziare il lab. Passa alla sezione successiva per iniziare a conoscere i menu personalizzati.

Nota: se utilizzi un account gmail.com, potrebbe essere visualizzata la finestra di dialogo "Non è verificato" quando usi per la prima volta lo script. Google lo utilizza per avvisare gli utenti che potrebbero utilizzare il codice di autori sconosciuti o non attendibili. Se vedi questa finestra di dialogo, puoi procedere poiché sei l'autore dello script. Segui le istruzioni sullo schermo per continuare ad autorizzare lo script:

1. Nella finestra di dialogo dell'app non verificata, fai clic su Avanzate.
2. Fai clic su Vai a Manipolazione dei dati e Menu personalizzati (non sicuro).
3. Fai clic su Consenti.

Durante questo codelab, potresti ricevere diverse richieste di autorizzazione. Puoi trovare ulteriori informazioni su questa procedura in Autorizzazione per i servizi Google.

3. Panoramica: importare i dati con una voce di menu personalizzata

Apps Script ti consente di definire menu personalizzati che possono essere visualizzati in Fogli Google. Puoi anche utilizzare menu personalizzati in Documenti, Presentazioni Google e Moduli Google. Quando definisci una voce di menu personalizzata, crei un'etichetta di testo e la colleghi a una funzione Apps Script nel tuo progetto di script. Puoi quindi aggiungere il menu all'interfaccia utente in modo che venga visualizzato in Fogli Google:

Quando un utente fa clic su una voce di menu personalizzata, viene eseguita la funzione Apps Script associata. Si tratta di un modo rapido per eseguire le funzioni di Apps Script senza dover aprire l'editor di script. Inoltre, consente ad altri utenti del foglio di lavoro di eseguire il tuo codice senza che tu debba sapere nulla su come funziona o come funziona Apps Script. Per loro è solo un altro piatto del menu.

Le voci di menu personalizzate sono definite nella funzione di attivazione semplice di onOpen(), di cui scoprirai la prossima sezione.

4. La funzione onOpen()

Gli attivatori di Apps Script offrono un modo per eseguire codice di Apps Script specifico in risposta a determinate condizioni o eventi. Quando crei un attivatore, devi definire l'evento che ne attiva l'attivazione e fornire una funzione Apps Script eseguita per l'evento.

onOpen() è un esempio di semplice attivatore. Sono facili da configurare: non devi fare altro che scrivere una funzione Apps Script denominata onOpen() e Apps Script la esegue ogni volta che il foglio di lavoro associato viene aperto o ricaricato:

/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
 /* ... */ 
}

Concetto chiave: gli attivatori semplici sono facili da configurare e usare, ma hanno diverse restrizioni. A causa di queste limitazioni, onOpen() viene utilizzato solo per creare voci del menu.

Gli attivatori sono un argomento complesso e, pertanto, non rientrano nell'ambito di questo codelab. Per il momento, puoi considerare onOpen() come un gestore di eventi che aggiunge voci di menu ogni volta che carichi un foglio di lavoro.

Implementazione

Creiamo un menu personalizzato.

1. Sostituisci il codice nel progetto dello script con il seguente:

/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Book-list')
    .addItem('Load Book-list', 'loadBookList')
    .addToUi();
}

2. Salva il progetto di script.

Revisione del codice

Esaminiamo questo codice per capire come funziona. In onOpen(), la prima riga utilizza il metodo getUi() per acquisire un oggetto Ui che rappresenta l'interfaccia utente del foglio di lavoro attivo a cui è associato questo script.

Le tre righe successive creano il menu (Book-list), aggiungi una voce di menu (Load Book-list) e aggiungi il menu all'interfaccia del foglio di lavoro. Questa operazione viene eseguita rispettivamente con i metodi createMenu(caption), addItem(caption, functionName) e addToUi().

Il metodo addItem(caption, functionName) crea un collegamento tra l'etichetta della voce di menu e la funzione Apps Script che viene eseguita quando viene selezionata la voce di menu. In questo caso, se selezioni la voce di menu Load Book-list, Fogli tenta di eseguire la funzione loadBookList() (che non esiste ancora).

Risultati

Esegui questa funzione ora per vedere che funziona:

1. In Fogli Google, ricarica il foglio di lavoro. Nota: in genere questa operazione chiude la scheda con l'editor di script.
2. Riapri l'editor di script selezionando Strumenti > Editor di script.

Dopo che il foglio di lavoro si è ricaricato, il nuovo menu Book-list dovrebbe essere visualizzato nella barra dei menu:

Se fai clic su Lista dei libri, puoi visualizzare il menu risultante:

La sezione successiva crea il codice per la funzione loadBookList() e illustra un modo per interagire con i dati in Apps Script come la lettura di altri fogli di lavoro.

5. Importare i dati di fogli di lavoro

Ora che hai creato un menu personalizzato, puoi creare funzioni che possono essere eseguite facendo clic sulla voce di menu.

Al momento, il menu personalizzato Book-list contiene una voce di menu: Load Book-list. La funzione chiamata quando selezioni la voce di menu Load Book-list, loadBookList(), non esiste nello script, pertanto selezionando Elenco di libri > Carica elenco di libri viene visualizzato un errore:

Puoi correggere questo errore implementando la funzione loadBookList().

Implementazione

Vuoi che la nuova voce di menu inserisca il foglio di lavoro con i dati necessari per l'utilizzo, quindi implementerai loadBookList() per leggere i dati dei libri da un altro foglio di lavoro e copiarli in questo:

1. Aggiungi il seguente codice allo script in onOpen():

/** 
 * Creates a template book list based on the
 * provided 'codelab-book-list' sheet.
 */
function loadBookList(){
  // Gets the active sheet.
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Gets a different spreadsheet from Drive using
  // the spreadsheet's ID. 
  var bookSS = SpreadsheetApp.openById(
    "1c0GvbVUDeBmhTpq_A3vJh2xsebtLuwGwpBYqcOBqGvo" 
  );

  // Gets the sheet, data range, and values of the
  // spreadsheet stored in bookSS.
  var bookSheet = bookSS.getSheetByName("codelab-book-list");
  var bookRange = bookSheet.getDataRange();
  var bookListValues = bookRange.getValues();

  // Add those values to the active sheet in the current
  // spreadsheet. This overwrites any values already there.
  sheet.getRange(1, 1, bookRange.getHeight(), bookRange.getWidth()) 
    .setValues(bookListValues);
  
  // Rename the destination sheet and resize the data
  // columns for easier reading.
  sheet.setName("Book-list");
  sheet.autoResizeColumns(1, 3);
}

2. Salva il progetto di script.

Revisione del codice

Come funziona questa funzione? La funzione loadBookList() utilizza metodi principalmente delle classi Spreadsheet, Sheet e Range introdotte nei codelab precedenti. Considerando questi concetti, puoi suddividere il codice loadBookList() nelle seguenti quattro sezioni:

1: identifica il foglio di destinazione

La prima riga utilizza SpreadsheetApp.getActiveSheet() per ottenere un riferimento all'oggetto del foglio corrente e lo archivia nella variabile sheet. Questo è il foglio in cui verranno copiati i dati.

2: identifica i dati di origine

Le prossime righe definiscono quattro variabili che fanno riferimento ai dati di origine che stai recuperando:

• bookSS memorizza un riferimento al foglio di lavoro da cui il codice sta leggendo i dati. Il codice trova il foglio di lavoro in base al suo ID. In questo esempio, abbiamo fornito l'ID di un foglio di lavoro di origine per leggerlo e lo apriamo utilizzando il metodo SpreadsheetApp.openById(id).
• bookSheet archivia un riferimento a un foglio all'interno di bookSS che contiene i dati che preferisci. Il codice identifica il foglio da leggere, in base al nome codelab-book-list.
• bookRange archivia un riferimento a un intervallo di dati in bookSheet. Il metodo Sheet.getDataRange() restituisce l'intervallo contenente tutte le celle non vuote del foglio. È un modo semplice per ottenere un intervallo che copra tutti i dati di un foglio senza includere righe e colonne vuote.
• bookListValues è un array 2D contenente tutti i valori recuperati dalle celle in bookRange. Il metodo Range.getValues() genera questo array leggendo i dati del foglio di origine.

3: copia i dati dall'origine alla destinazione

Nella sezione Codice successiva, i dati bookListValues vengono copiati in sheet, quindi viene rinominato anche il foglio:

• Sheet.getRange(row, column, numRows, numColumns) viene utilizzato per identificare dove copiare i dati in sheet.
• I metodi Range.getHeight() e Range.getWidth() vengono utilizzati per misurare le dimensioni dei dati e definire un intervallo di destinazione delle stesse dimensioni.
• Range.setValues(values) copia l'array 2D di bookListValues nell'intervallo di destinazione, sovrascrivendo eventuali dati già presenti.

4: formatta il foglio di destinazione

La Sheet.setName(name) viene utilizzata per modificare il nome del foglio di destinazione in Book-list. L'ultima riga della funzione utilizza Sheet.autoResizeColumns(startColumn, numColumns) per ridimensionare le prime tre colonne del foglio di destinazione e consentirti di leggere più facilmente i nuovi dati.

Risultati

Puoi vedere questa funzione in azione. In Fogli Google, seleziona Lista dei libri > Carica elenco dei libri per eseguire la funzione per riempire il foglio di lavoro:

Ora hai un foglio con un elenco di titoli di libri, autori e codici ISBN di 13 cifre. Nella sezione successiva scoprirai come modificare e aggiornare i dati di questo elenco di libri usando la manipolazione delle stringhe e i menu personalizzati.

6. Panoramica: pulire i dati del foglio di lavoro

Ora hai le informazioni del libro sul foglio. Ogni riga si riferisce a un libro specifico e ne elenca il titolo, l'autore e il numero ISBN in colonne separate. Tuttavia, puoi anche riscontrare alcuni problemi con questi dati non elaborati:

1. Per alcune righe, il titolo e l'autore vengono inseriti insieme nella colonna del titolo, collegata da una virgola o dalla stringa " da ".
2. In alcune righe manca il titolo o l'autore del libro.

Nelle sezioni successive potrai correggere questi problemi pulindo i dati. Per il primo numero, creerai funzioni che leggono la colonna del titolo e suddividono il testo ogni volta che viene trovata una virgola o un delimitatore, posizionando le sottostringhe dell'autore e del titolo corrispondenti nelle colonne corrette. Per il secondo problema, dovrai scrivere un codice che cercherà automaticamente le informazioni mancanti nei libri utilizzando un'API esterna e le aggiungerà nel tuo foglio.

7. Aggiungi voci del menu

Dovrai creare tre voci di menu per controllare le operazioni di pulizia dei dati che implementerai.

Implementazione

Aggiorna onOpen() per includere le voci di menu aggiuntive di cui avrai bisogno. Procedi nel seguente modo:

1. Nel progetto di script, aggiorna il codice onOpen() affinché corrisponda a quanto segue:

/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Book-list')
    .addItem('Load Book-list', 'loadBookList')
    .addSeparator()
    .addItem(
      'Separate title/author at first comma', 'splitAtFirstComma')
    .addItem(
      'Separate title/author at last "by"', 'splitAtLastBy')
    .addSeparator()
    .addItem(
      'Fill in blank titles and author cells', 'fillInTheBlanks')
    .addToUi();
}

2. Salva il progetto di script.
3. Nell'editor di script, seleziona onOpen dall'elenco delle funzioni e fai clic su Esegui. In questo modo verrà eseguito onOpen() per ricreare il menu del foglio di lavoro in modo da non dover ricaricare il foglio di lavoro.

In questo nuovo codice, il metodo Menu.addSeparator() crea un separatore orizzontale nel menu per mantenere organizzati i gruppi di voci correlate. Le nuove voci del menu vengono poi aggiunte sotto il riquadro, con le etichette Separate title/author at first comma, Separate title/author at last "by" e Fill in blank titles and author cells.

Nota: le voci di menu vengono visualizzate in Fogli nello stesso ordine in cui le aggiungi nel codice onOpen().

Risultati

Nel foglio di lavoro, fai clic sul menu Book-list per visualizzare le nuove voci:

Il clic su questi nuovi elementi causa un errore perché non hai implementato le funzioni corrispondenti. Ora puoi farlo.

8. Dividi il testo sui delimitatori

Il set di dati importato nel foglio di lavoro contiene alcune celle in cui l'autore e il titolo sono stati combinati in modo errato in una cella utilizzando una virgola:

La suddivisione delle stringhe di testo in colonne separate è un'attività comune dei fogli di lavoro. Fogli Google offre una funzione SPLIT() che divide le stringhe in colonne. Tuttavia, spesso i set di dati presentano problemi che non possono essere facilmente risolti con le funzioni integrate di Fogli. In questi casi, puoi scrivere codice Apps Script per eseguire le operazioni complesse necessarie per pulire e organizzare i tuoi dati.

Inizia a pulire i dati implementando prima una funzione chiamata splitAtFirstComma() che divide l'autore e il titolo nelle rispettive celle quando vengono trovate le virgole.

La funzione splitAtFirstComma() deve seguire questi passaggi:

1. Recupera l'intervallo che rappresenta le celle attualmente selezionate.
2. Controlla se le celle nell'intervallo hanno una virgola.
3. Se vengono trovate virgole, dividi la stringa in due (e solo due) sottostringhe in corrispondenza della posizione della prima virgola. Per semplificare le cose, puoi presumere che qualsiasi virgola indichi una stringa di tipo "[authors], [title]". Puoi anche presumere se nella cella sono presenti più virgole, dividendo la prima virgola della stringa.
4. Imposta le sottostringhe come nuovi contenuti delle rispettive celle del titolo e dell'autore.

Implementazione

Per implementare questi passaggi, utilizzerai gli stessi metodi del Servizio foglio di lavoro che hai utilizzato in precedenza, ma dovrai utilizzare JavaScript anche per manipolare i dati delle stringhe. Segui questi passaggi:

1. Nell'editor Apps Script, aggiungi la seguente funzione alla fine del tuo progetto script:

/**
 * Reformats title and author columns by splitting the title column
 * at the first comma, if present.
 */
function splitAtFirstComma(){
  // Get the active (currently highlighted) range.
  var activeRange = SpreadsheetApp.getActiveRange();
  var titleAuthorRange = activeRange.offset(
    0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);

  // Get the current values of the selected title column cells.
  // This is a 2D array.
  var titleAuthorValues = titleAuthorRange.getValues();

  // Update values where commas are found. Assumes the presence
  // of a comma indicates an "authors, title" pattern.
  for (var row = 0; row < titleAuthorValues.length; row++){
    var indexOfFirstComma =
        titleAuthorValues[row][0].indexOf(", ");

    if(indexOfFirstComma >= 0){
      // Found a comma, so split and update the values in
      // the values array.
      var titlesAndAuthors = titleAuthorValues[row][0];

      // Update the title value in the array.
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(indexOfFirstComma + 2);

      // Update the author value in the array.
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(0, indexOfFirstComma);
    }
  }

  // Put the updated values back into the spreadsheet.
  titleAuthorRange.setValues(titleAuthorValues);
}

2. Salva il progetto di script.

Revisione del codice

Esaminiamo il nuovo codice composto da tre sezioni principali:

1: recupera i valori dei titoli evidenziati

Le prime tre righe definiscono tre variabili che fanno riferimento ai dati correnti nel foglio:

• activeRange rappresenta l'intervallo evidenziato dall'utente quando è stata chiamata la funzione splitAtFirstComma(). Per semplificare l'esercizio, possiamo presumere che l'utente lo faccia solo quando evidenzi le celle nella colonna A.
• titleAuthorRange rappresenta un nuovo intervallo che copre le stesse celle di activeRange, ma include anche un'altra colonna a destra. titleAuthorRange viene creato con il metodo Range.offset(rowOffset, columnOffset, numRows, numColumns). Il codice richiede questo intervallo esteso perché richiede l'inserimento di qualsiasi autore che trova nella colonna del titolo.
• titleAuthorValues è un array di dati 2D estratto da titleAuthorRange che utilizza Range.getValues().

2: esamina ogni titolo e dividi il primo delimitatore di virgola trovato

La prossima sezione esamina i valori di titleAuthorValues per trovare le virgole. Un JavaScript per loop viene utilizzato per esaminare tutti i valori nella prima colonna di titleAuthorValues. Quando viene trovata una sottostringa di virgola (", ") utilizzando il metodo Stringa JavaScript indexOf(), il codice segue:

1. Il valore della stringa cellulare viene copiato nella variabile titlesAndAuthors.
2. La posizione della virgola viene determinata con il metodo Stringa JavaScript indexOf().
3. Il metodo Stringa JavaScript() viene chiamato due volte per ottenere la sottostringa prima del delimitatore di virgola e la stringa secondaria dopo il delimitatore.
4. Le sottostringhe vengono copiate nell'array 2D titleAuthorValues, sovrascrivendo i valori esistenti in quella posizione. Poiché stiamo utilizzando un pattern "[authors], [title]", l'ordine delle due sottostringhe viene invertito per inserire il titolo nella prima colonna e gli autori nella seconda.

Nota: quando il codice non trova una virgola, i dati nella riga rimangono invariati.

3: copia i nuovi valori nel foglio

Dopo aver esaminato tutti i valori delle celle del titolo, l'array 2D titleAuthorValues aggiornato viene copiato nel foglio di lavoro mediante il metodo Range.setValues(values).

Punto chiave: il codice avrebbe potuto aggiornare ogni cella del foglio di lavoro singolarmente ogni volta che trovava una virgola, chiamando Range.setValue(value) ogni volta che era necessario modificare una cella. tuttavia, il risultato è che il codice è molto più lento, poiché ogni chiamata Range.setValue(value) richiede una comunicazione con il server.

Quando si aggiornano i dati di Fogli, è quasi sempre meglio inserire prima i valori in un array 2D, aggiornare l'array e poi respingere i dati al foglio con una singola chiamata Range.setValues(values).

Risultati

Ora puoi vedere in azione gli effetti della funzione splitAtFirstComma(). Prova a eseguirla selezionando la voce di menu Separa titolo/autore nella prima virgola dopo aver selezionato...

...una cella:

...o più celle:

Nota: puoi selezionare qualsiasi colonna di testo nel foglio di lavoro. Utilizza questa funzione per trovare le virgole e dividere il testo in base alle regole impostate. Tuttavia, se il codice trova un delimitatore di virgola, sovrascriverà la cella a destra dell'originale.

Hai creato una funzione Apps Script che elabora i dati di Fogli. Ora, implementerai la seconda funzione di divisione.

9. Dividi testo su delimitatori "per"

Analizzando i dati originali, puoi osservare un altro problema. Proprio come alcuni dei dati formattano titoli e autori in una singola cella come "[authors], [title]", altre celle formattano l'autore e il titolo come "[title] di [autori]":

Implementazione

Puoi risolvere il problema utilizzando la stessa tecnica dell'ultima sezione, creando una funzione chiamata splitAtLastBy(). Questa funzione ha un lavoro simile a quello di splitAtFirstComma(): l'unica differenza reale è la ricerca di un pattern di testo leggermente diverso. Implementa questa funzione procedendo nel seguente modo:

1. Nell'editor Apps Script, aggiungi la seguente funzione alla fine del tuo progetto script:

/** 
 * Reformats title and author columns by splitting the title column
 * at the last instance of the string " by ", if present.
 */
function splitAtLastBy(){
  // Get the active (currently highlighted) range.
  var activeRange = SpreadsheetApp.getActiveRange();
  var titleAuthorRange = activeRange.offset(
    0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);

  // Get the current values of the selected title column cells.
  // This is a 2D array.
  var titleAuthorValues = titleAuthorRange.getValues();

  // Update values where " by " substrings are found. Assumes
  // the presence of a " by " indicates a "title by authors"
  // pattern.
  for(var row = 0; row < titleAuthorValues.length; row++){
    var indexOfLastBy =
        titleAuthorValues[row][0].lastIndexOf(" by ");
    
    if(indexOfLastBy >= 0){
      // Found a " by ", so split and update the values in
      // the values array.
      var titlesAndAuthors = titleAuthorValues[row][0];
      
      // Update the title value in the array.
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(0, indexOfLastBy);
      
      // Update the author value in the array.
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(indexOfLastBy + 4);
    }
  }

  // Put the updated values back into the spreadsheet.
  titleAuthorRange.setValues(titleAuthorValues);
}

2. Salva il progetto di script.

Revisione del codice

Esistono alcune differenze chiave tra questo codice e splitAtFirstComma():

1. La sottostringa " by " viene utilizzata come delimitatore di stringa, anziché ", ".
2. Qui viene utilizzato il metodo JavaScript String.lastIndexOf(substring) anziché String.indexOf(substring). Ciò significa se sono presenti più &by &stringhe di stringa nella stringa iniziale, tutte tranne l'ultima > by " si presume che facciano parte del titolo.
3. Dopo aver diviso la stringa, la prima sottostringa viene impostata come titolo e la seconda come autore (questo è l'ordine opposto di splitAtFirstComma()).

Risultati

Ora puoi vedere in azione gli effetti della funzione splitAtLastBy(). Prova a eseguirla selezionando la voce di menu Separa titolo/autore finalmente "da" dopo aver selezionato...

...una cella:

...o più celle:

Hai completato questa sezione del codelab. Ora puoi utilizzare Apps Script per leggere e modificare i dati delle stringhe in un foglio e utilizzare menu personalizzati per eseguire comandi Apps Script diversi.

Nella sezione successiva scoprirai come migliorare ulteriormente questo set di dati inserendo celle vuote con i dati ricavati da un'API pubblica.

10. Panoramica: recuperare dati dalle API pubbliche

Finora hai perfezionato il set di dati per risolvere alcuni problemi relativi alla formattazione dell'autore e del titolo, ma il set di dati è privo di alcune informazioni evidenziate nelle celle di seguito:

Non puoi ottenere i dati mancanti utilizzando le operazioni di stringa sui dati al momento. Dovrai invece recuperare i dati mancanti da un'altra origine. Puoi farlo in Apps Script richiedendo informazioni provenienti da API esterne che possono fornire dati aggiuntivi.

Le API sono interfacce di programmazione di applicazioni. È un termine generico, ma è sostanzialmente un servizio che i tuoi programmi e script possono chiamare per richiedere informazioni o intraprendere determinate azioni. In questa sezione chiami un'API disponibile pubblicamente per richiedere le informazioni dei libri che puoi inserire nelle celle libere del foglio.

Questa sezione ti spiega come:

• Richiesta di dati dei libri da un'origine API esterna.
• Estrai titolo e informazioni sull'autore dai dati restituiti e scrivili nel foglio di lavoro.

11. Recuperare i dati esterni con UrlFetch

Prima di approfondire il codice che funziona direttamente con il tuo foglio di lavoro, puoi scoprire di più sull'utilizzo di API esterne in Apps Script creando una funzione di supporto specifica per la richiesta di informazioni sui libri dalla API Open Library pubblica.

Nota: Open Library è un archivio Internet che cerca di creare una pagina per ogni pubblicazione di libri. Se ti interessa, puoi saperne di più sulla documentazione dell'API sul relativo sito web ufficiale.

La nostra funzione helper, fetchBookData_(ISBN), utilizza un numero ISBN di 13 cifre di un libro come parametro e restituisce i dati sul libro. Si connette e recupera informazioni dall'API Open Library, quindi analizza l'oggetto JSON restituito.

Implementazione

Implementa questa funzione helper procedendo nel seguente modo:

1. Nell'editor Apps Script, aggiungi il seguente codice alla fine dello script:

/**
 * Helper function to retrieve book data from the Open Library
 * public API.
 *
 * @param {number} ISBN - The ISBN number of the book to find.
 * @return {object} The book's data, in JSON format.
 */
function fetchBookData_(ISBN){
  // Connect to the public API.
  var url = "https://openlibrary.org/api/books?bibkeys=ISBN:"
      + ISBN + "&jscmd=details&format=json";
  var response = UrlFetchApp.fetch(
      url, {'muteHttpExceptions': true});
  
  // Make request to API and get response before this point.
  var json = response.getContentText();
  var bookData = JSON.parse(json); 
  
  // Return only the data we're interested in.
  return bookData['ISBN:' + ISBN];
}

2. Salva il progetto di script.

Nota: in Apps Script, i nomi delle funzioni che terminano con _ (un trattino basso) sono considerati privati. Altri script non possono chiamare queste funzioni se presenti in una libreria o dai client durante la comunicazione server-client. Tuttavia, poiché si tratta di argomenti avanzati, se sai che una funzione verrà utilizzata solo dallo script corrente, è buona norma terminare il nome di una funzione con _.

Revisione del codice

Questo codice è diviso in due sezioni principali:

1: la richiesta API

Nelle prime due righe, fetchBookData_(ISBN) si connette all'API Open Library pubblica utilizzando l'endpoint URL di API e il servizio di recupero URL di Apps Script.

La variabile url è solo una stringa URL, come un indirizzo web. Rimanda a una posizione sui server di Open Library. Sono inoltre inclusi tre parametri (bibkeys, jscmd e format) che indicano ai server della Libreria aperta quali informazioni vengono richieste e come strutturare la risposta. In questo caso, devi fornire il codice ISBN del libro e chiedere la restituzione di informazioni dettagliate in formato JSON.

Nota: ogni API pubblica definisce i propri formati per effettuare richieste e restituire risultati. Per comunicare in modo efficace con un'API, devi rispettare i formati definiti dal codice. Per la Libreria aperta, i formati delle richieste e delle risposte sono descritti nella documentazione dell'API Open Library Books. Il formato della risposta utilizzato è JSON (JavaScript Object Notation), che rappresenta solo un modo particolare per organizzare i dati. JSON è simile a HTML o XML, ma tende a essere più facile da leggere per gli utenti.

Una volta creata la stringa URL, il codice invia una richiesta alla posizione e riceve una risposta. Per farlo, utilizza il metodo UrlFetchApp.fetch(url, params). Invia una richiesta di informazioni all'URL esterno che fornisci e memorizza la risposta risultante nella variabile response. Oltre all'URL, il codice imposta il parametro facoltativo muteHttpExceptions su true. Questa impostazione significa che il codice non verrà interrotto se la richiesta restituisce un errore API. Viene invece restituita la risposta di errore.

La richiesta restituisce un oggetto HTTPResponse che viene memorizzato nella variabile response. Le risposte HTTP includono un codice di risposta, intestazioni HTTP e il contenuto principale della risposta. L'informazione di interesse è il contenuto JSON principale, pertanto il codice deve estrarlo e quindi analizzare il file JSON per individuare e restituire le informazioni desiderate.

2: Analizza la risposta API e restituisci le informazioni di interesse

Nelle ultime tre righe di codice, il metodo HTTPResponse.getContentText() restituisce i contenuti principali della risposta come stringa. Questa stringa è in formato JSON, ma l'API Open Library definisce il contenuto e il formato esatti. Il metodo JSON.parse(jsonString) converte la stringa JSON in un oggetto JavaScript in modo che diverse parti dei dati possano essere facilmente estratte. Infine, la funzione restituisce i dati corrispondenti al codice ISBN del libro.

Nota: se hai bisogno di acquisire familiarità con JSON, consulta la documentazione JavaScript per lavorare con JSON o il riferimento della classe JSON.

Risultati

Ora che hai implementato la funzionalità fetchBookData_(ISBN), altre funzioni del codice possono trovare informazioni per qualsiasi libro utilizzando il relativo codice ISBN. Utilizzerai questa funzione per inserire le celle del foglio di lavoro.

12. Scrivere dati API in un foglio di lavoro

Ora puoi implementare una funzione fillInTheBlanks() che svolge le seguenti operazioni:

1. Identifica i dati mancanti relativi a titolo e autore nell'intervallo di dati attivo.
2. Recupera i dati mancanti di un libro specifico chiamando l'API Open Library utilizzando il metodo helper fetchBookData_(ISBN).
3. Aggiorna il valore mancante per il titolo o l'autore nelle rispettive celle.

Implementazione

Implementa questa nuova funzione seguendo questi passaggi:

1. Nell'editor Apps Script, aggiungi il seguente codice alla fine del tuo progetto di script:

/**
 * Fills in missing title and author data using Open Library API
 * calls.
 */ 
function fillInTheBlanks(){
  // Constants that identify the index of the title, author,
  // and ISBN columns (in the 2D bookValues array below). 
  var TITLE_COLUMN = 0;
  var AUTHOR_COLUMN = 1;
  var ISBN_COLUMN = 2;

  // Get the existing book information in the active sheet. The data
  // is placed into a 2D array.
  var dataRange = SpreadsheetApp.getActiveSpreadsheet()
    .getDataRange();
  var bookValues = dataRange.getValues();

  // Examine each row of the data (excluding the header row).
  // If an ISBN is present, and a title or author is missing,
  // use the fetchBookData_(isbn) method to retrieve the
  // missing data from the Open Library API. Fill in the
  // missing titles or authors when they're found.
  for(var row = 1; row < bookValues.length; row++){   
    var isbn = bookValues[row][ISBN_COLUMN];
    var title = bookValues[row][TITLE_COLUMN];
    var author = bookValues[row][AUTHOR_COLUMN];
   
    if(isbn != "" && (title === "" || author === "") ){
      // Only call the API if you have an ISBN number and
      // either the title or author is missing.
      var bookData = fetchBookData_(isbn);

      // Sometimes the API doesn't return the information needed.
      // In those cases, don't attempt to update the row.
      if (!bookData || !bookData.details) {
        continue;
      }

      // The API might not return a title, so only fill it in
      // if the response has one and if the title is blank in
      // the sheet.
      if(title === "" && bookData.details.title){
        bookValues[row][TITLE_COLUMN] = bookData.details.title; 
      }

      // The API might not return an author name, so only fill it in
      // if the response has one and if the author is blank in
      // the sheet.
      if(author === "" && bookData.details.authors
          && bookData.details.authors[0].name){
        bookValues[row][AUTHOR_COLUMN] =
          bookData.details.authors[0].name; 
      }
    }
  }
  
  // Insert the updated book data values into the spreadsheet.
  dataRange.setValues(bookValues);
}

2. Salva il progetto di script.

Revisione del codice

Questo codice è diviso in tre sezioni:

1: leggi le informazioni esistenti del libro

Le prime tre righe della funzione definiscono costanti per rendere il codice più leggibile. Nelle prossime due righe, la variabile bookValues viene utilizzata per conservare una copia locale delle informazioni del libro del foglio. Il codice leggerà le informazioni da bookValues, utilizzerà l'API per compilare le informazioni mancanti e scriverà di nuovo questi valori nel foglio di lavoro.

Nota: questa disposizione è diversa da splitAtFirstComma() e splitAtLastBy(). In queste funzioni, il codice esaminava solo i titoli attualmente evidenziati e le relative righe. Stai esaminando l'intero foglio utilizzando Spreadsheet.getDataRange().

2: recupera le informazioni mancanti con la funzione helper

Il codice viene ripetuto continuamente ogni riga di bookValues per trovare titoli o autori mancanti. Per ridurre il numero di chiamate API e migliorarne l'efficienza, il codice chiama l'API solo se si verificano le seguenti condizioni:

1. La colonna ISBN della riga contiene un valore.
2. Il titolo o la cella dell'autore nella riga è vuota.

Se le condizioni sono vere, il codice chiama l'API utilizzando la funzione helper fetchBookData_(isbn) che hai implementato in precedenza e memorizza il risultato nella variabile bookData. Ora dovrebbe contenere le informazioni mancanti che vuoi inserire nel foglio.

L'unica attività rimanente è l'aggiunta delle informazioni relative a bookData al nostro foglio di lavoro. Tuttavia, c'è un'avvertenza. Purtroppo, le API pubbliche come l'API Open Library Book a volte non hanno le informazioni che richiedi oppure talvolta potrebbero esserci altri problemi che impediscono a Google di fornire le informazioni. Se ritieni che ogni richiesta API abbia esito positivo, il tuo codice non sarà abbastanza affidabile da gestire errori imprevisti.

Per assicurarti che il codice possa gestire gli errori dell'API, il codice deve verificare che la risposta dell'API sia valida prima di provare a utilizzarla. Quando il codice ha bookData, esegue un semplice controllo per verificare che bookData e bookData.details esistano prima di provare a leggere. Se uno di questi dati manca, significa che l'API non disponeva dei dati che vuoi. In questo caso, il comando continue indica al codice di ignorare la riga: non puoi compilare le celle mancanti, ma almeno lo script non verrà arrestato in modo anomalo.

3: scrivi di nuovo le informazioni aggiornate nel foglio

L'ultima parte del codice contiene controlli simili per verificare che l'API abbia restituito il titolo e le informazioni sull'autore. Il codice aggiorna l'array bookValues solo se il titolo o la cella dell'autore originali sono vuoti e l'API ha restituito un valore che puoi inserire lì.

Il ciclo termina dopo aver esaminato tutte le righe del foglio. L'ultimo passaggio consiste nel riscrivere l'array bookValues, ora aggiornato, nel foglio di lavoro utilizzando Range.setValues(values).

Risultati

Ora puoi completare la pulizia dei dati dei tuoi libri. Procedi nel seguente modo:

1. Se non l'hai ancora fatto, evidenzia l'intervallo A2:A15 nel tuo foglio e seleziona Elenco di libri > Separa il titolo/l'autore nella prima virgola per eliminare i problemi relativi alle virgole.
2. Se non l'hai ancora fatto, evidenzia l'intervallo A2:A15 nel tuo foglio e seleziona Lista dei libri > Separa il titolo/l'autore con l'ultimo titolo "da" per eliminare i problemi "di".
3. Per compilare tutte le celle rimanenti, seleziona Lista dei libri > Inserisci titoli e celle dell'autore vuoti:

13. Conclusione

Congratulazioni per aver completato questo codelab. Hai imparato a creare menu personalizzati per attivare parti diverse del codice Apps Script. Hai anche imparato a importare dati in Fogli Google utilizzando i servizi Apps Script e le API pubbliche. Si tratta di un'operazione comune nell'elaborazione dei fogli di lavoro e Apps Script consente di importare i dati da una vasta gamma di origini. Infine, hai visto come utilizzare i servizi Apps Script e JavaScript per leggere, elaborare e inserire dati del foglio di lavoro.

Hai trovato utile questo codelab?

Sì No

Che cosa hai imparato

• Come importare i dati da un foglio di lavoro Google.
• Come creare un menu personalizzato nella funzione onOpen().
• Come analizzare e manipolare i valori dei dati delle stringhe.
• Come chiamare le API pubbliche utilizzando il servizio di recupero URL.
• Come analizzare i dati degli oggetti JSON recuperati da un'origine API pubblica.

Concetti principali:

1. onOpen() è un semplice attivatore utilizzato per creare voci di menu. I trigger semplici presentano varie restrizioni che devi rispettare.
2. Quando leggi o scrivi dati delle celle del foglio di lavoro, è buona norma leggere o scrivere un intero intervallo contemporaneamente con Range.getValues() o Range.setValues(values). Non leggere o impostare ogni cella singolarmente, perché rallenta.
3. I nomi delle funzioni di Apps Script che terminano con _ (un trattino basso) sono considerati privati. Altri script non possono chiamare queste funzioni se presenti in una libreria o dai client mediante comunicazione server-client. Se sai che una funzione verrà utilizzata solo dallo script attuale, la sua best practice prevede di terminare il nome di una funzione con _.
4. Se utilizzi dati restituiti da API esterne, convalidali prima di provare a utilizzarli. In questo modo ridurrai gli errori nel codice se l'API restituirà un risultato imprevisto.

Termini chiave:

• Attivo (stato): indica il foglio di lavoro, il foglio, l'intervallo o la cella specificato che è l'utente del foglio di lavoro attualmente visualizzato o evidenziato.
• Cessa attiva: la singola cella evidenziata all'interno del foglio attivo con lo stato attivo del cursore.
• Intervallo attivo: un gruppo di una o più celle attualmente evidenziate nel foglio attivo.
• API: un'interfaccia di programmazione di un'applicazione è un servizio che i programmi o gli script possono chiamare per richiedere informazioni o per intraprendere determinate azioni.
• Autorizzazione: il processo con cui l'utente concede le autorizzazioni per consentire ad Apps Script di accedere ai dati dell'utente nei servizi Google pertinenti o che esegue altre azioni per conto dell'utente.
• Script associato a un contenitore: qualsiasi script associato e creato a partire da un documento Google Workspace, ad esempio un foglio Google o un documento Google.
• Funzione helper: una funzione, generalmente privata, chiamata da un altro utente per completare una piccola attività secondaria. Le funzioni helper vengono utilizzate quando l'attività secondaria deve essere completata molte volte in posizioni diverse.
• JSON: formato di file per la definizione degli oggetti di dati e delle loro caratteristiche nel testo.
• onOpen(): se definito in uno script, si tratta di un attivatore semplice che si attiva ogni volta che un file si apre o si ricarica.
• Funzioni private: una funzione che non può essere richiamata come parte di una libreria o da client tramite la comunicazione server-client. I nomi delle funzioni private di Apps Script terminano con _.
• Intervallo**: un gruppo di una o più celle adiacenti all'interno di un foglio. Il corso Range** consente di leggere e aggiornare gli intervalli all'interno di un foglio.
• Editor di script: l'editor di codice nel browser per Apps Script.
• Attivatori semplici: un sottotipo di attivatori in Apps Script con i nomi delle funzioni di attivazione riservate e determinate limitazioni.
• Foglio**: una singola pagina di un foglio di lavoro. Talvolta utilizzato per fare riferimento a un file di Fogli Google su Google Drive. Il corso Sheet** ti consente di accedere e modificare i fogli.
• SpreadsheetApp: questa classe funge da classe principale per il servizio Foglio di lavoro e fornisce un punto di partenza per il codice che legge o manipola i dati di Fogli Google.
• Foglio di lavoro**: un file di Fogli Google che si trova all'interno di Google Drive. Il corso Spreadsheet** consente di accedere e modificare i fogli di lavoro.
• Servizio Fogli di lavoro: un servizio Apps Script che consente agli script di creare, accedere e modificare i file di Fogli Google.
• Trigger: un evento associato a una funzione Apps Script. Quando si verifica l'evento (ad esempio l'apertura di un foglio di lavoro), l'attivatore "viene attivato" ed esegue automaticamente la funzione Apps Script associata.
• Servizio di recupero URL: il servizio Apps Script che consente agli script di connettersi agli endpoint URL per effettuare richieste e ricevere risposte.

Passaggi successivi

Il codelab successivo in questa playlist approfondirà il modo in cui formattare i dati all'interno di un foglio di lavoro.

Trova il prossimo codelab su Formattazione dei dati.