Elementi di base di Apps Script con Fogli Google #3: lavorare con i dati

Elementi di base di Apps Script con Fogli Google #3:
lavorare con i dati

Informazioni su questo codelab

subjectUltimo aggiornamento: set 18, 2024
account_circleScritto da: Google Workspace Developer Relations

1. Introduzione

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

Completando questo codelab, puoi imparare a utilizzare la manipolazione dei dati, i menu personalizzati e il recupero dei dati delle API pubbliche in Apps Script per migliorare la tua esperienza con Fogli. Continuerai a lavorare con le classi SpreadsheetApp, Spreadsheet, Sheet e Range introdotte nei codelab precedenti di questa playlist.

  • Come importare i 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.

Questo è il terzo codelab della playlist Nozioni di base di Apps Script con Fogli Google. Prima di iniziare questo codelab, assicurati di completare i precedenti:

  1. Macro e funzioni personalizzate
  2. Fogli di lavoro, fogli e intervalli
  • Una conoscenza degli argomenti di base di Apps Script esplorati nei codelab precedenti di questa playlist.
  • Familiarità di base con l'editor di Apps Script
  • Familiarità di base con Fogli Google
  • Possibilità di leggere i fogli Notazione A1
  • Familiarità di base con JavaScript e la relativa classe String

2. Configura

Gli esercizi di questo codelab richiedono un foglio di lavoro. Per creare un foglio di lavoro da utilizzare in questi esercizi:

  1. Crea un foglio di lavoro su Google Drive. Puoi farlo dall'interfaccia di Drive selezionando Nuovo > Fogli Google. Viene creato e aperto un nuovo foglio di lavoro. Il file viene salvato nella cartella 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 avere il seguente aspetto:

545c02912de7d112.png

  1. Per aprire l'editor di script, fai clic su Estensioni > Apps Script.
  2. 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 scoprire i menu personalizzati.

3. Panoramica: importa 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 Google, Presentazioni Google e Moduli Google. Quando definisci una voce di menu personalizzata, crei un'etichetta di testo e la connetti a una funzione Apps Script nel progetto di script. A questo punto, puoi aggiungere il menu all'interfaccia utente in modo che venga visualizzato in Fogli Google:

d6b694da6b8c6783.png

Quando un utente fa clic su una voce di menu personalizzata, viene eseguita la funzione Apps Script associata. Questo è un modo rapido per eseguire le funzioni di Apps Script senza dover aprire l'editor di script. Consente inoltre ad altri utenti del foglio di lavoro di eseguire il codice senza dover sapere nulla sul suo funzionamento o su quello di Apps Script. Per loro, è solo un'altra voce di menu.

Le voci di menu personalizzate sono definite nella funzione onOpen() trigger semplice, che verrà descritta nella sezione successiva.

4. La funzione onOpen()

I trigger semplici in Apps Script consentono di eseguire codice Apps Script specifico in risposta a determinate condizioni o eventi. Quando crei un attivatore, definisci l'evento che lo attiva e fornisci una funzione Apps Script da eseguire per l'evento.

onOpen() è un esempio di trigger semplice. Sono facili da configurare: tutto quello che devi fare è 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() {
 /* ... */ 
}

Implementazione

Creiamo un menu personalizzato.

  1. Sostituisci il codice nel progetto di 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();
}
  1. 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), aggiungono un elemento al menu (Load Book-list) e poi aggiungono il menu all'interfaccia del foglio di lavoro. Ciò avviene rispettivamente con i metodi createMenu(caption), addItem(caption, functionName) e addToUi().

Il metodo addItem(caption, functionName) crea una connessione tra l'etichetta della voce di menu e la funzione Apps Script che viene eseguita quando la voce di menu viene selezionata. In questo caso, la selezione della voce di menu Load Book-list fa sì che Fogli tenti di eseguire la funzione loadBookList() (che non esiste ancora).

Risultati

Esegui questa funzione ora per verificare che funzioni:

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

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

687dfb214f2930ba.png

Se fai clic su Elenco libri, viene visualizzato il menu risultante:

8a4a391fbabcb16a.png

La sezione successiva crea il codice per la funzione loadBookList() e introduce un modo per interagire con i dati in Apps Script: leggere 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 ha una voce di menu: Load Book-list. La funzione chiamata quando selezioni la voce di menu Load Book-list, loadBookList(),, non esiste nello script, quindi selezionando Elenco libri > Carica elenco libri viene generato un errore:

b94dcef066e7041d.gif

Puoi correggere questo errore implementando la funzione loadBookList().

Implementazione

Vuoi che la nuova voce di menu riempia il foglio di lavoro con i dati da utilizzare, 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);
}
  1. 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. Tenendo a mente questi concetti, puoi dividere 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 memorizza nella variabile sheet. Questo è il foglio in cui verranno copiati i dati.

2. Identifica i dati di origine

Le righe successive stabiliscono quattro variabili che fanno riferimento ai dati di origine che stai recuperando:

  • bookSS memorizza un riferimento al foglio di lavoro da cui il codice legge 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 da cui leggere e abbiamo aperto il foglio di lavoro utilizzando il metodo SpreadsheetApp.openById(id).
  • bookSheet memorizza un riferimento a un foglio all'interno di bookSS che contiene i dati che ti interessano. Il codice identifica il foglio da cui leggere in base al nome, codelab-book-list.
  • bookRange memorizza 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 assicurarsi di ottenere un intervallo che copra tutti i dati di un foglio senza includere righe e colonne vuote.
  • bookListValues è un array bidimensionale contenente tutti i valori presi dalle celle di bookRange. Il metodo Range.getValues() genera questo array leggendo i dati dal foglio di origine.

3: Copia i dati dall'origine alla destinazione

La sezione di codice successiva copia i dati bookListValues in sheet e rinomina anche il foglio:

4. Formatta il foglio di destinazione

Sheet.setName(name) viene utilizzato 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, consentendoti di leggere più facilmente i nuovi dati.

Risultati

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

3c797e1e2b9fe641.gif

Ora hai un foglio con un elenco di titoli di libri, autori e numeri ISBN a 13 cifre. Nella sezione successiva, imparerai a modificare e aggiornare i dati in questo elenco di libri utilizzando la manipolazione delle stringhe e i menu personalizzati.

6. Panoramica: pulisci i dati del foglio di lavoro

Ora nel foglio sono presenti le informazioni sul libro. Ogni riga si riferisce a un libro specifico, di cui elenca titolo, autore e numero ISBN in colonne separate. Tuttavia, puoi anche notare alcuni problemi con questi dati non elaborati:

  1. Per alcune righe, il titolo e l'autore sono inseriti insieme nella colonna del titolo, collegati da una virgola o dalla stringa "di".
  2. In alcune righe mancano il titolo o l'autore del libro.

Nelle sezioni successive, correggerai questi problemi pulendo i dati. Per il primo problema, creerai funzioni che leggono la colonna del titolo e dividono il testo ogni volta che viene trovato un delimitatore virgola o " by ", inserendo le sottostringhe corrispondenti di autore e titolo nelle colonne corrette. Per il secondo problema, scriverai un codice che cerca automaticamente le informazioni mancanti sui libri utilizzando un'API esterna e le aggiunge al foglio.

7. Aggiungere voci di menu

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

Implementazione

Aggiorniamo onOpen() per includere le voci di menu aggiuntive di cui avrai bisogno. Segui questi passaggi:

  1. Nel progetto script, aggiorna il codice onOpen() in modo che 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();
}
  1. Salva il progetto di script.
  2. Nell'editor di script, seleziona onOpen dall'elenco delle funzioni e fai clic su Esegui. Verrà eseguito onOpen() per ricostruire il menu del foglio di lavoro, in modo da non doverlo ricaricare.

In questo nuovo codice, il metodo Menu.addSeparator() crea un divisore orizzontale nel menu per mantenere organizzati visivamente i gruppi di voci di menu correlate. I nuovi elementi del menu vengono quindi aggiunti sotto, con le etichette Separate title/author at first comma, Separate title/author at last "by" e Fill in blank titles and author cells.

Risultati

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

580c806ce8fd4872.png

Se fai clic su questi nuovi elementi, si verifica un errore perché non hai implementato le funzioni corrispondenti, quindi procediamo.

8. Suddividi il testo in base ai delimitatori virgola

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

ca91c43c4e51d6b5.png

La suddivisione delle stringhe di testo in colonne separate è un'attività comune dei fogli di lavoro. Fogli Google fornisce una funzione SPLIT() che divide le stringhe in colonne. Tuttavia, i set di dati spesso presentano problemi che non possono essere risolti facilmente 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 dati.

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

La funzione splitAtFirstComma() deve eseguire i seguenti passaggi:

  1. Ottieni l'intervallo che rappresenta le celle attualmente selezionate.
  2. Controlla se le celle nell'intervallo contengono una virgola.
  3. Dove vengono trovate le virgole, dividi la stringa in due (e solo due) sottostringhe nella posizione della prima virgola. Per semplificare le cose, puoi presupporre che ogni virgola indichi un pattern di stringa "[autori], [titolo]". Puoi anche presumere che, se nella cella sono presenti più virgole, sia opportuno dividere la stringa in corrispondenza della prima virgola.
  4. Imposta le sottostringhe come nuovi contenuti delle rispettive celle di titolo e autore.

Implementazione

Per implementare questi passaggi, utilizzerai gli stessi metodi del servizio Fogli che hai utilizzato in precedenza, ma dovrai anche utilizzare JavaScript per manipolare i dati delle stringhe. Procedi in questo modo:

  1. Nell'editor di Apps Script, aggiungi la seguente funzione alla fine del progetto di 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);
}
  1. Salva il progetto di script.

Revisione del codice

Esaminiamo il nuovo codice, che è composto da tre sezioni principali:

1: Recupera i valori dei titoli evidenziati

Le prime tre righe stabiliscono tre variabili che fanno riferimento ai dati attuali nel foglio:

  • activeRange rappresenta l'intervallo attualmente evidenziato dall'utente quando è stata chiamata la funzione splitAtFirstComma(). Per semplificare questo esercizio, possiamo supporre che l'utente esegua questa operazione solo quando evidenzia le celle della colonna A.
  • titleAuthorRange rappresenta un nuovo intervallo che copre le stesse celle di activeRange, ma include anche una colonna in più a destra. titleAuthorRange viene creato utilizzando il metodo Range.offset(rowOffset, columnOffset, numRows, numColumns). Il codice ha bisogno di questo intervallo espanso perché deve avere un posto in cui inserire gli autori trovati nella colonna del titolo.
  • titleAuthorValues è un array bidimensionale di dati estratti da titleAuthorRange utilizzando Range.getValues().

2. Esamina ogni titolo e dividilo in base al primo delimitatore virgola trovato

La sezione successiva esamina i valori in titleAuthorValues per trovare le virgole. Un ciclo for JavaScript viene utilizzato per esaminare tutti i valori nella prima colonna di titleAuthorValues. Quando viene trovata una sottostringa con virgola (", ") utilizzando il metodo JavaScript String indexOf(), il codice esegue le seguenti operazioni:

  1. Il valore stringa della cella viene copiato nella variabile titlesAndAuthors.
  2. La posizione della virgola viene determinata utilizzando il metodo JavaScript String indexOf().
  3. Il metodo JavaScript String slice() viene chiamato due volte per ottenere la sottostringa prima del delimitatore virgola e la sottostringa dopo il delimitatore.
  4. Le sottostringhe vengono copiate nuovamente nella matrice bidimensionale titleAuthorValues, sovrascrivendo i valori esistenti in quella posizione. Poiché stiamo presupponendo un pattern "[autori], [titolo] ", 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, lascia invariati i dati nella riga.

3. Copia i nuovi valori nel foglio

Una volta esaminati tutti i valori delle celle del titolo, l'array bidimensionale titleAuthorValues aggiornato viene copiato nuovamente nel foglio di lavoro utilizzando il metodo Range.setValues(values).

Risultati

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

…una cella:

a24763b60b305376.gif

… o più celle:

89c5c89b357d3713.gif

Ora hai creato una funzione Apps Script che elabora i dati di Fogli. Successivamente, implementerai la seconda funzione di suddivisione.

9. Dividi il testo in base ai delimitatori &quot;by&quot;

Se esamini i dati originali, puoi notare un altro problema. Proprio come alcuni formati di dati includono titoli e autori in una singola cella come "[autori], [titolo]", altre celle formattano autore e titolo come "[titolo] di [autori]":

41f0dd5ac63b62f4.png

Implementazione

Puoi risolvere questo problema utilizzando la stessa tecnica della sezione precedente, creando una funzione chiamata splitAtLastBy(). Questa funzione ha un compito simile a splitAtFirstComma(): l'unica vera differenza è che cerca un pattern di testo leggermente diverso. Implementa questa funzione nel seguente modo:

  1. Nell'editor di Apps Script, aggiungi la seguente funzione alla fine del progetto di 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);
}
  1. Salva il progetto di script.

Revisione del codice

Esistono alcune differenze fondamentali 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 che se nella stringa iniziale sono presenti più sottostringhe " by ", tutte tranne l'ultima " by " vengono considerate parte del titolo.
  3. Dopo aver suddiviso la stringa, la prima sottostringa viene impostata come titolo e la seconda come autore (l'ordine è inverso rispetto a splitAtFirstComma()).

Risultati

Ora puoi vedere gli effetti della funzione splitAtLastBy() in azione. Prova a eseguirlo selezionando la voce di menu Separa titolo/autore all'ultimo "di" dopo aver selezionato…

…una cella:

4e6679e134145975.gif

… o più celle:

3c879c572c61e62f.gif

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

Nella sezione successiva, imparerai a migliorare ulteriormente questo set di dati compilando le celle vuote con i dati estratti da un'API pubblica.

10. Panoramica: recuperare dati dalle API pubbliche

Finora hai perfezionato il set di dati per risolvere alcuni problemi di formattazione di titoli e autori, ma nel set di dati mancano ancora alcune informazioni, evidenziate nelle celle seguenti:

af0dba8cb09d1a49.png

Non puoi ottenere i dati mancanti utilizzando operazioni sulle stringhe sui dati che hai attualmente. Dovrai invece recuperare i dati mancanti da un'altra origine. Puoi farlo in Apps Script richiedendo informazioni da API esterne che possono fornire dati aggiuntivi.

Le API sono interfacce di programmazione di un'applicazione. È un termine generico, ma si tratta essenzialmente di un servizio che i tuoi programmi e script possono chiamare per richiedere informazioni o per eseguire determinate azioni. In questa sezione, chiami un'API disponibile pubblicamente per richiedere informazioni sui libri che puoi inserire nelle celle vuote del foglio.

Questa sezione ti insegna a:

  • Richiedi dati sui libri da un'origine API esterna.
  • Estrai le informazioni su titolo e autore dai dati restituiti e scrivile nel foglio di lavoro.

11. Recuperare dati esterni con UrlFetch

Prima di approfondire il codice che funziona direttamente con il foglio di lavoro, puoi scoprire come utilizzare le API esterne in Apps Script creando una funzione di assistenza specificamente per richiedere informazioni sui libri dall'API Open Library pubblica.

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

Implementazione

Implementa questa funzione helper nel seguente modo:

  1. Nell'editor di 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];
}
  1. Salva il progetto di script.

Revisione del codice

Questo codice è suddiviso in due sezioni principali:

1: La richiesta API

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

La variabile url è solo una stringa URL, come un indirizzo web. Punta a una posizione sui server di Open Library. Include anche tre parametri (bibkeys, jscmd e format) che indicano ai server di Open Library quali informazioni stai richiedendo e come strutturare la risposta. In questo caso, fornisci il numero ISBN del libro e chiedi che le informazioni dettagliate vengano restituite in formato JSON.

Una volta creata la stringa URL, il codice invia una richiesta alla posizione e riceve una risposta. utilizzando 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 si interromperà se la richiesta genera un errore API. Viene invece restituita la risposta di errore.

La richiesta restituisce un oggetto HTTPResponse archiviato nella variabile response. Le risposte HTTP includono un codice di risposta, le intestazioni HTTP e il contenuto principale della risposta. Le informazioni di interesse qui sono i contenuti JSON principali, quindi il codice deve estrarli e poi analizzare il JSON per individuare e restituire le informazioni desiderate.

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

Nelle ultime tre righe di codice, il metodo HTTPResponse.getContentText() restituisce il contenuto principale 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 le diverse parti dei dati possano essere estratte facilmente. Infine, la funzione restituisce i dati corrispondenti al numero ISBN del libro.

Risultati

Ora che hai implementato fetchBookData_(ISBN), altre funzioni del tuo codice possono trovare informazioni su qualsiasi libro utilizzando il suo numero ISBN. Utilizzerai questa funzione per compilare le celle del foglio di lavoro.

12. Scrivere i dati dell&#39;API in un foglio di lavoro

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

  1. Identifica i dati mancanti relativi a titolo e autore all'interno dell'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 i valori mancanti di titolo o autore nelle rispettive celle.

Implementazione

Implementa questa nuova funzione nel seguente modo:

  1. Nell'editor Apps Script, aggiungi il seguente codice alla fine del 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);
}
  1. Salva il progetto di script.

Revisione del codice

Questo codice è suddiviso in tre sezioni:

1. Leggi le informazioni esistenti sul libro

Le prime tre righe della funzione definiscono le costanti per rendere il codice più leggibile. Nelle due righe successive, la variabile bookValues viene utilizzata per mantenere 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à nuovamente questi valori nel foglio di lavoro.

2: Recupera le informazioni mancanti utilizzando la funzione helper

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

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

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

L'unica attività rimasta è aggiungere le informazioni bookData al nostro foglio di lavoro. Tuttavia, c'è un'avvertenza. Purtroppo, le API pubbliche come l'API Open Library Book a volte non dispongono delle informazioni richieste o potrebbero presentare qualche altro problema che impedisce di fornire le informazioni. Se presumi che ogni richiesta API andrà a buon fine, il tuo codice non sarà abbastanza solido da gestire errori imprevisti.

Per assicurarti che il codice possa gestire gli errori dell'API, deve verificare che la risposta dell'API sia valida prima di tentare di utilizzarla. Una volta che il codice è bookData, esegue un semplice controllo per verificare che bookData e bookData.details esistano prima di tentare di leggerli. Se uno dei due è mancante, significa che l'API non aveva i dati che ti interessavano. In questo caso, il comando continue indica al codice di saltare la riga. Non puoi compilare le celle mancanti, ma almeno lo script non si arresta in modo anomalo.

3. Scrivi le informazioni aggiornate nel foglio

L'ultima parte del codice esegue controlli simili per verificare le informazioni su titolo e autore restituite dall'API. Il codice aggiorna l'array bookValues solo se la cella del titolo o dell'autore originale è vuota e l'API ha restituito un valore che puoi inserire.

Il ciclo termina dopo che sono state esaminate tutte le righe del foglio. L'ultimo passaggio consiste nello scrivere l'array bookValues ora aggiornato nel foglio di lavoro utilizzando Range.setValues(values).

Risultati

Ora puoi completare la pulizia dei dati del libro. Segui questi passaggi:

  1. Se non l'hai ancora fatto, evidenzia l'intervallo A2:A15 nel foglio e seleziona Elenco libri > Separa titolo/autore alla prima virgola per risolvere i problemi relativi alle virgole.
  2. Se non l'hai ancora fatto, evidenzia l'intervallo A2:A15 nel foglio e seleziona Elenco libri > Separa titolo/autore all'ultimo "di" per risolvere i problemi relativi a "di".
  3. Per compilare tutte le celle rimanenti, seleziona Elenco libri > Compila i titoli e le celle dell'autore vuoti:

826675a3437adbdb.gif

13. Conclusione

Congratulazioni per aver completato questo codelab. Hai imparato a creare menu personalizzati per attivare diverse parti del codice Apps Script. Hai anche visto come importare i 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 dati da un'ampia gamma di origini. Infine, hai visto come utilizzare i servizi Apps Script e JavaScript per leggere, elaborare e inserire i dati dei fogli di lavoro.

Hai trovato utile questo codelab?

Che cosa hai imparato

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

Passaggi successivi

Il prossimo codelab di questa playlist approfondisce la formattazione dei dati in un foglio di lavoro.

Trova il prossimo codelab in Formattazione dei dati.

bug_report Segnala un errore