Elementi di base di Apps Script con Fogli Google #4: formattazione dei dati

1. Introduzione

Ti diamo il benvenuto nella quarta parte della playlist di codelab Fundamentals of Apps Script con Fogli Google.

Completando questo codelab, puoi imparare a formattare i dati del foglio di lavoro in Apps Script e scrivere funzioni per creare fogli di lavoro organizzati e pieni di dati formattati recuperati da un'API pubblica.

Obiettivi didattici

• Come applicare varie operazioni di formattazione di Fogli Google in Apps Script.
• Come trasformare un elenco di oggetti JSON e i relativi attributi in un foglio di dati organizzato con Apps Script.

Prima di iniziare

Questo è il quarto 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
3. Utilizzare i dati

Che cosa ti serve

• 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

Prima di continuare, devi avere un foglio di lavoro con alcuni dati. Come in precedenza, abbiamo fornito un foglio dati che puoi copiare per questi esercizi. Procedi in questo modo:

1. Fai clic su questo link per copiare il foglio dati, quindi su Crea una copia. Il nuovo foglio di lavoro viene inserito nella cartella Google Drive e denominato "Copia di Formattazione dati".
2. Fai clic sul titolo del foglio di lavoro e modificalo da "Copia di Formattazione dati" a "Formattazione dati". Il foglio dovrebbe avere questo aspetto, con alcune informazioni di base sui primi tre film di Star Wars:

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

Con questo foglio di lavoro e questo progetto, puoi iniziare il codelab. Passa alla sezione successiva per iniziare a scoprire la formattazione di base in Apps Script.

3. Creare un menu personalizzato

Puoi applicare diversi metodi di formattazione di base in Apps Script ai tuoi fogli. Gli esercizi seguenti mostrano alcuni modi per formattare i dati. Per controllare meglio le azioni di formattazione, creiamo un menu personalizzato con gli elementi che ti serviranno. La procedura per creare menu personalizzati è stata descritta nel codelab Utilizzo dei dati, ma la riassumeremo di nuovo qui.

Implementazione

Creiamo un menu personalizzato.

1. Nell'editor Apps Script, sostituisci il codice nel progetto di script con il seguente:

/**
 * A special function that runs when the spreadsheet is opened
 * or reloaded, used to add a custom menu to the spreadsheet.
 */
function onOpen() {
  // Get the spreadsheet's user-interface object.
  var ui = SpreadsheetApp.getUi();

  // Create and add a named menu and its items to the menu bar.
  ui.createMenu('Quick formats')
   .addItem('Format row header', 'formatRowHeader')
   .addItem('Format column header', 'formatColumnHeader')
   .addItem('Format dataset', 'formatDataset') 
  .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 viene eseguito onOpen() per ricostruire il menu del foglio di lavoro, in modo da non doverlo ricaricare.

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 righe successive creano un menu (Quick formats), aggiungono voci di menu (Format row header, Format column header e Format dataset) al menu 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 una funzione Apps Script che viene eseguita quando la voce di menu viene selezionata. Ad esempio, se selezioni la voce di menu Format row header, Fogli tenta di eseguire la funzione formatRowHeader() (che non esiste ancora).

Risultati

Nel foglio di lavoro, fai clic sul menu Quick formats per visualizzare le nuove voci di menu:

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

4. Formattare una riga di intestazione

I set di dati nei fogli di lavoro spesso hanno righe di intestazione per identificare i dati in ogni colonna. È consigliabile formattare le righe di intestazione per separarle visivamente dal resto dei dati nel foglio di lavoro.

Nel primo codelab hai creato una macro per l'intestazione e ne hai modificato il codice. Qui formatterai una riga di intestazione da zero utilizzando Apps Script. La riga di intestazione che creerai metterà in grassetto il testo dell'intestazione, colorerà lo sfondo di un blu-verde scuro, colorerà il testo di bianco e aggiungerà alcuni bordi solidi.

Implementazione

Per implementare l'operazione di formattazione, utilizzerai gli stessi metodi del servizio Fogli che hai utilizzato in precedenza, ma ora utilizzerai anche alcuni dei metodi di formattazione del servizio. Procedi in questo modo:

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

/**
 * Formats top row of sheet using our header row style.
 */
function formatRowHeader() {
  // Get the current active sheet and the top row's range.
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
 
  // Apply each format to the top row: bold white text,
  // blue-green background, and a solid black border
  // around the cells.
  headerRange
    .setFontWeight('bold')
    .setFontColor('#ffffff')
    .setBackground('#007272')
    .setBorder(
      true, true, true, true, null, null,
      null,
      SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

}

2. Salva il progetto di script.

Revisione del codice

Come molte attività di formattazione, il codice Apps Script per implementarla è semplice. Le prime due righe utilizzano metodi già visti per ottenere un riferimento al foglio attivo corrente (sheet) e alla prima riga del foglio (headerRange)). Il metodo Sheet.getRange(row, column, numRows, numColumns) specifica la prima riga, incluse solo le colonne con dati. Il metodo Sheet.getLastColumn() restituisce l'indice della colonna dell'ultima colonna che contiene dati nel foglio. Nel nostro esempio, è la colonna E (url).

Nota: qui presupponiamo che la riga di intestazione che vogliamo formattare si trovi sempre nella riga 1, che i nostri dati inizino in A1 e che si estendano su un certo numero di colonne contigue. Per questo esercizio va bene, ma in generale è meglio evitare queste ipotesi consentendo agli utenti di specificare le celle da formattare (nel caso in cui la struttura del foglio di lavoro sia diversa).

Il resto del codice chiama semplicemente vari metodi Range per applicare le scelte di formattazione a tutte le celle di headerRange. Per mantenere il codice facile da leggere, utilizziamo l'incatenamento dei metodi per chiamare ogni metodo di formattazione uno dopo l'altro:

• Range.setFontWeight(fontWeight) viene utilizzato per impostare lo spessore del carattere su grassetto.
• Range.setFontColor(color) viene utilizzato per impostare il colore del carattere su bianco.
• Range.setBackground(color) viene utilizzato per impostare il colore di sfondo su un blu-verde scuro.
• setBorder(top, left, bottom, right, vertical, horizontal, color, style) inserisce un bordo nero pieno intorno alle celle dell'intervallo.

L'ultimo metodo ha diversi parametri, quindi esaminiamo la funzione di ciascuno. I primi quattro parametri (tutti impostati su true) indicano ad Apps Script che il bordo deve essere aggiunto sopra, sotto, a sinistra e a destra dell'intervallo. Il quinto e il sesto parametro (null e null) indicano ad Apps Script di evitare di modificare le linee del bordo all'interno dell'intervallo selezionato. Il settimo parametro (null) indica che il colore del bordo deve essere nero per impostazione predefinita. Infine, l'ultimo parametro specifica il tipo di stile del bordo da utilizzare, scelto tra le opzioni fornite da SpreadsheetApp.BorderStyle.

Risultati

Per vedere la funzione di formattazione in azione:

1. Se non l'hai ancora fatto, salva il progetto di script nell'editor di Apps Script.
2. Fai clic sulla voce di menu Formati rapidi > Formatta intestazione riga.

Nota:se utilizzi un account gmail.com, la prima volta che utilizzi lo script potrebbe essere visualizzata la finestra di dialogo "Questa app non è verificata". Google utilizza queste informazioni per avvisare gli utenti che potrebbero utilizzare codice di autori sconosciuti o non attendibili. Se visualizzi questa finestra di dialogo, puoi procedere perché 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 sicuri).
3. Fai clic su Consenti.

Durante questo codelab, potresti ricevere diverse richieste di autorizzazione. Puoi scoprire di più su questa procedura in Autorizzazione per i servizi Google.

I risultati dovrebbero essere simili ai seguenti:

Ora hai automatizzato un'attività di formattazione. La sezione successiva applica la stessa tecnica per creare uno stile di formato diverso per le intestazioni di colonna.

5. Formattare un'intestazione di colonna

Se puoi creare un'intestazione di riga personalizzata, puoi creare anche un'intestazione di colonna. Le intestazioni di colonna aumentano la leggibilità di determinati set di dati. Ad esempio, la colonna titoli in questo foglio di lavoro può essere migliorata con le seguenti opzioni di formato:

• Mettere in grassetto il testo
• Mettere il testo in corsivo
• Aggiungere bordi alle celle
• Inserimento di link ipertestuali utilizzando i contenuti della colonna url. Dopo aver aggiunto questi link ipertestuali, puoi rimuovere la colonna URL per semplificare il foglio.

Successivamente, implementerai una funzione formatColumnHeader() per applicare queste modifiche alla prima colonna del foglio. Per semplificare la lettura del codice, implementerai anche due funzioni di assistenza.

Implementazione

Come in precedenza, devi aggiungere una funzione per automatizzare la formattazione dell'intestazione della colonna. Procedi in questo modo:

1. Nell'editor di Apps Script, aggiungi la seguente funzione formatColumnHeader() alla fine del progetto di script:

/**
 * Formats the column header of the active sheet.
 */ 
function formatColumnHeader() {  
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Get total number of rows in data range, not including
  // the header row.
  var numRows = sheet.getDataRange().getLastRow() - 1;
  
  // Get the range of the column header.
  var columnHeaderRange = sheet.getRange(2, 1, numRows, 1);
  
  // Apply text formatting and add borders.
  columnHeaderRange
    .setFontWeight('bold')
    .setFontStyle('italic')
    .setBorder(
      true, true, true, true, null, null,
      null,
      SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
 
  // Call helper method to hyperlink the first column contents
  // to the url column contents.
  hyperlinkColumnHeaders_(columnHeaderRange, numRows); 
}

2. Aggiungi le seguenti funzioni di assistenza alla fine del progetto di script, dopo la funzione formatColumnHeader():

/**
 * Helper function that hyperlinks the column header with the
 * 'url' column contents. The function then removes the column.
 *
 * @param {object} headerRange The range of the column header
 *   to update.
 * @param {number} numRows The size of the column header.
 */
function hyperlinkColumnHeaders_(headerRange, numRows) {
  // Get header and url column indices.
  var headerColIndex = 1; 
  var urlColIndex = columnIndexOf_('url');  
  
  // Exit if the url column is missing.
  if(urlColIndex == -1)
    return; 
  
  // Get header and url cell values.
  var urlRange =
    headerRange.offset(0, urlColIndex - headerColIndex);
  var headerValues = headerRange.getValues();
  var urlValues = urlRange.getValues();
  
  // Updates header values to the hyperlinked header values.
  for(var row = 0; row < numRows; row++){
    headerValues[row][0] = '=HYPERLINK("' + urlValues[row]
      + '","' + headerValues[row] + '")';
  }
  headerRange.setValues(headerValues);
  
  // Delete the url column to clean up the sheet.
  SpreadsheetApp.getActiveSheet().deleteColumn(urlColIndex);
}

/**
 * Helper function that goes through the headers of all columns
 * and returns the index of the column with the specified name
 * in row 1. If a column with that name does not exist,
 * this function returns -1. If multiple columns have the same
 * name in row 1, the index of the first one discovered is
 * returned.
 * 
 * @param {string} colName The name to find in the column
 *   headers. 
 * @return The index of that column in the active sheet,
 *   or -1 if the name isn't found.
 */ 
function columnIndexOf_(colName) {
  // Get the current column names.
  var sheet = SpreadsheetApp.getActiveSheet();
  var columnHeaders =
    sheet.getRange(1, 1, 1, sheet.getLastColumn());
  var columnNames = columnHeaders.getValues();
  
  // Loops through every column and returns the column index
  // if the row 1 value of that column matches colName.
  for(var col = 1; col <= columnNames[0].length; col++)
  {
    if(columnNames[0][col-1] === colName)
      return col; 
  }

  // Returns -1 if a column named colName does not exist. 
  return -1; 
}

3. Salva il progetto di script.

Revisione del codice

Esaminiamo il codice di ciascuna di queste tre funzioni separatamente:

formatColumnHeader()

Come probabilmente ti aspetti, le prime righe di questa funzione impostano le variabili che fanno riferimento al foglio e all'intervallo che ci interessano:

• Il foglio attivo è memorizzato in sheet.
• Il numero di righe nell'intestazione della colonna viene calcolato e salvato in numRows. Qui il codice sottrae uno in modo che il conteggio delle righe non includa l'intestazione di colonna: title.
• L'intervallo che copre l'intestazione di colonna è memorizzato in columnHeaderRange.

Il codice applica quindi i bordi e il grassetto all'intervallo di intestazioni di colonna, proprio come in formatRowHeader(). Qui, Range.setFontStyle(fontStyle) viene utilizzato anche per mettere il testo in corsivo.

L'aggiunta degli hyperlink alla colonna dell'intestazione è più complessa, quindi formatColumnHeader() chiama hyperlinkColumnHeaders_(headerRange, numRows) per occuparsi dell'attività. In questo modo, il codice rimane ordinato e leggibile.

hyperlinkColumnHeaders_(headerRange, numRows)

Questa funzione helper identifica innanzitutto gli indici di colonna dell'intestazione (presunta essere l'indice 1) e della colonna url. Chiama columnIndexOf_('url') per ottenere l'indice della colonna dell'URL. Se non viene trovata una colonna url, il metodo viene chiuso senza modificare alcun dato.

La funzione ottiene un nuovo intervallo (urlRange) che copre gli URL corrispondenti alle righe della colonna di intestazione. Ciò avviene con il metodo Range.offset(rowOffset, columnOffset), che garantisce che i due intervalli abbiano le stesse dimensioni. Vengono quindi recuperati i valori delle colonne headerColumn e url (headerValues e urlValues).

La funzione esegue quindi un ciclo su ogni valore della cella di intestazione della colonna e lo sostituisce con una formula di Fogli =HYPERLINK() creata con l'intestazione e i contenuti della colonna url. I valori dell'intestazione modificati vengono poi inseriti nel foglio utilizzando Range.setValues(values).

Infine, per mantenere pulito il foglio ed eliminare le informazioni ridondanti, viene chiamata la funzione Sheet.deleteColumn(columnPosition) per rimuovere la colonna url.

columnIndexOf_(colName)

Questa funzione helper è una semplice funzione di utilità che cerca un nome specifico nella prima riga del foglio. Le prime tre righe utilizzano metodi che hai già visto per ottenere un elenco dei nomi delle intestazioni delle colonne dalla riga 1 del foglio di lavoro. Questi nomi vengono memorizzati nella variabile columnNames.

La funzione esamina quindi ogni nome in ordine. Se ne trova una che corrisponde al nome cercato, si arresta e restituisce l'indice della colonna. Se raggiunge la fine dell'elenco dei nomi senza trovare il nome, restituisce -1 per segnalare che il nome non è stato trovato.

Risultati

Per vedere la funzione di formattazione in azione:

1. Se non l'hai ancora fatto, salva il progetto di script nell'editor di Apps Script.
2. Fai clic sulla voce di menu Formati rapidi > Formatta intestazione colonna.

I risultati dovrebbero essere simili ai seguenti:

Ora hai automatizzato un'altra attività di formattazione. Dopo aver formattato le intestazioni di colonne e righe, la sezione successiva mostra come formattare i dati.

6. Formattare il set di dati

Ora che hai le intestazioni, creiamo una funzione che formatti il resto dei dati nel foglio. Utilizzeremo le seguenti opzioni di formattazione:

• Colori di sfondo alternati delle righe (noti come banding)
• Modificare i formati di data
• Applicazione dei bordi
• Ridimensionamento automatico di tutte le colonne e le righe

Ora creerai una funzione formatDataset() e un metodo helper aggiuntivo per applicare questi formati ai dati del foglio.

Implementazione

Come prima, aggiungi una funzione per automatizzare la formattazione dei dati. Procedi in questo modo:

1. Nell'editor di Apps Script, aggiungi la seguente funzione formatDataset() alla fine del progetto di script:

/**
 * Formats the sheet data, excluding the header row and column.
 * Applies the border and banding, formats the 'release_date'
 * column, and autosizes the columns and rows.
 */
function formatDataset() {
  // Get the active sheet and data range.
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var fullDataRange = sheet.getDataRange();

  // Apply row banding to the data, excluding the header
  // row and column. Only apply the banding if the range
  // doesn't already have banding set.
  var noHeadersRange = fullDataRange.offset(
    1, 1,
    fullDataRange.getNumRows() - 1,
    fullDataRange.getNumColumns() - 1);

  if (! noHeadersRange.getBandings()[0]) {
    // The range doesn't already have banding, so it's
    // safe to apply it.
    noHeadersRange.applyRowBanding(
      SpreadsheetApp.BandingTheme.LIGHT_GREY,
      false, false);
  }

  // Call a helper function to apply date formatting
  // to the column labeled 'release_date'.
  formatDates_( columnIndexOf_('release_date') );
  
  // Set a border around all the data, and resize the
  // columns and rows to fit.
  fullDataRange.setBorder(
    true, true, true, true, null, null,
    null,
    SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

  sheet.autoResizeColumns(1, fullDataRange.getNumColumns());
  sheet.autoResizeRows(1, fullDataRange.getNumRows());
}

2. Aggiungi la seguente funzione helper alla fine del progetto di script, dopo la funzione formatDataset():

/** 
 * Helper method that applies a
 * "Month Day, Year (Day of Week)" date format to the
 * indicated column in the active sheet. 
 *
 * @param {number} colIndex The index of the column
 *   to format.
 */ 
function formatDates_(colIndex) {
  // Exit if the given column index is -1, indicating
  // the column to format isn't present in the sheet.
  if (colIndex < 0)
    return; 

  // Set the date format for the date column, excluding
  // the header row.
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.getRange(2, colIndex, sheet.getLastRow() - 1, 1)
    .setNumberFormat("mmmm dd, yyyy (dddd)");
}

3. Salva il progetto di script.

Revisione del codice

Esaminiamo il codice di ciascuna di queste due funzioni separatamente:

formatDataset()

Questa funzione segue un pattern simile alle funzioni di formattazione precedenti che hai già implementato. Innanzitutto, recupera le variabili per contenere i riferimenti al foglio attivo (sheet) e all'intervallo di dati (fullDataRange).

In secondo luogo, utilizza il metodo Range.offset(rowOffset, columnOffset, numRows, numColumns) per creare un intervallo (noHeadersRange) che copre tutti i dati del foglio, escluse le intestazioni di colonna e riga. Il codice verifica quindi se questo nuovo intervallo ha bande esistenti (utilizzando Range.getBandings()). Questa operazione è necessaria perché Apps Script genera un errore se provi ad applicare nuove bande dove ne esiste una. Se la banda non esiste, la funzione aggiunge una banda grigio chiaro utilizzando Range.applyRowBanding(bandingTheme, showHeader, showFooter). In caso contrario, la funzione continua.

Il passaggio successivo chiama la funzione helper formatDates_(colIndex) per formattare le date nella colonna etichettata "release_date" (descritta di seguito). La colonna viene specificata utilizzando la funzione helper columnIndexOf_(colName) implementata in precedenza.

Infine, la formattazione viene completata aggiungendo un altro bordo (come prima) e ridimensionando automaticamente ogni colonna e riga in modo che si adattino ai dati che contengono utilizzando i metodi Sheet.autoResizeColumns(columnPosition) e Sheet.autoResizeColumns(columnPosition).

formatDates_(colIndex)

Questa funzione helper applica un formato di data specifico a una colonna utilizzando l'indice di colonna fornito. Nello specifico, formatta i valori di data come "Mese Giorno, Anno (Giorno della settimana)".

Nota: esistono molti formati di date e numeri diversi che puoi utilizzare negli script e nei fogli di lavoro. Per ulteriori informazioni, consulta la guida Formati di data e numero.

Innanzitutto, la funzione verifica che l'indice di colonna fornito sia valido (ovvero 0 o maggiore). In caso contrario, viene restituito senza eseguire alcuna operazione. Questo controllo impedisce errori che potrebbero essere causati, ad esempio, se il foglio non avesse una colonna "release_date".

Una volta convalidato l'indice della colonna, la funzione recupera l'intervallo che copre la colonna (esclusa la riga di intestazione) e utilizza Range.setNumberFormat(numberFormat) per applicare la formattazione.

Risultati

Per vedere la funzione di formattazione in azione:

1. Se non l'hai ancora fatto, salva il progetto di script nell'editor di Apps Script.
2. Fai clic sulla voce di menu Formati rapidi > Formatta set di dati.

I risultati dovrebbero essere simili ai seguenti:

Hai automatizzato un'altra attività di formattazione. Ora che hai a disposizione questi comandi di formattazione, aggiungiamo altri dati a cui applicarli.

7. Recuperare e formattare i dati dell'API

Finora in questo codelab hai visto come utilizzare Apps Script come mezzo alternativo per formattare il foglio di lavoro. Successivamente, scriverai il codice che estrae i dati da un'API pubblica, li inserisce nel foglio di lavoro e li formatta in modo che siano leggibili.

Nell'ultimo codelab hai imparato a estrarre dati da un'API. Qui utilizzerai le stesse tecniche. In questo esercizio, utilizzeremo l'API Star Wars (SWAPI) pubblica per popolare il foglio di lavoro. Nello specifico, utilizzerai l'API per ottenere informazioni sui personaggi principali che appaiono nei tre film originali di Star Wars.

Il codice chiamerà l'API per ottenere una grande quantità di dati JSON, analizzare la risposta, inserire i dati in un nuovo foglio e formattarlo.

Implementazione

In questa sezione aggiungerai altre voci del menu. Ogni voce di menu chiama uno script wrapper che passa le variabili specifiche dell'elemento alla funzione principale (createResourceSheet_()). Implementerai questa funzione e tre funzioni helper aggiuntive. Come prima, le funzioni di supporto aiutano a isolare le parti logicamente compartimentate dell'attività e a mantenere il codice leggibile.

Esegui le seguenti azioni:

1. Nell'editor Apps Script, aggiorna la funzione onOpen() nel progetto di script in modo che corrisponda a quanto segue:

/**
 * A special function that runs when the spreadsheet is opened
 * or reloaded, used to add a custom menu to the spreadsheet.
 */
function onOpen() {
  // Get the Ui object.
  var ui = SpreadsheetApp.getUi();

  // Create and add a named menu and its items to the menu bar.
  ui.createMenu('Quick formats')
    .addItem('Format row header', 'formatRowHeader')
    .addItem('Format column header', 'formatColumnHeader')
    .addItem('Format dataset', 'formatDataset')
    .addSeparator()
    .addSubMenu(ui.createMenu('Create character sheet')
                .addItem('Episode IV', 'createPeopleSheetIV')
                .addItem('Episode V', 'createPeopleSheetV')
                .addItem('Episode VI', 'createPeopleSheetVI')
                )
    .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 viene eseguito onOpen() per ricompilare il menu del foglio di lavoro con le nuove opzioni che hai aggiunto.
4. Per creare un file Apps Script, fai clic su Aggiungi un file > Script accanto a File.
5. Assegna al nuovo script il nome "API" e premi Invio. Apps Script aggiunge automaticamente un'estensione .gs al nome del file di script.
6. Sostituisci il codice nel nuovo file API.gs con il seguente:

/**
 * Wrapper function that passes arguments to create a
 * resource sheet describing the characters from Episode IV.
 */
function createPeopleSheetIV() {
  createResourceSheet_('characters', 1, "IV");
}

/**
 * Wrapper function that passes arguments to create a
 * resource sheet describing the characters from Episode V.
 */
function createPeopleSheetV() {
  createResourceSheet_('characters', 2, "V");
}

/**
 * Wrapper function that passes arguments to create a
 * resource sheet describing the characters from Episode VI.
 */
function createPeopleSheetVI() {
  createResourceSheet_('characters', 3, "VI");
}

/** 
 * Creates a formatted sheet filled with user-specified
 * information from the Star Wars API. If the sheet with
 * this data exists, the sheet is overwritten with the API
 * information.
 *
 * @param {string} resourceType The type of resource.
 * @param {number} idNumber The identification number of the film.
 * @param {number} episodeNumber The Star Wars film episode number.
 *   This is only used in the sheet name.
 */
function createResourceSheet_(
    resourceType, idNumber, episodeNumber) { 
  
  // Fetch the basic film data from the API. 
  var filmData = fetchApiResourceObject_(
      "https://swapi.dev/api/films/" + idNumber);

  // Extract the API URLs for each resource so the code can
  // call the API to get more data about each individually.
  var resourceUrls = filmData[resourceType];
  
  // Fetch each resource from the API individually and push
  // them into a new object list.
  var resourceDataList = []; 
  for(var i = 0; i < resourceUrls.length; i++){
    resourceDataList.push(
      fetchApiResourceObject_(resourceUrls[i])
    ); 
  } 
  
  // Get the keys used to reference each part of data within
  // the resources. The keys are assumed to be identical for
  // each object since they're all the same resource type.
  var resourceObjectKeys = Object.keys(resourceDataList[0]);
  
  // Create the sheet with the appropriate name. It
  // automatically becomes the active sheet when it's created.
  var resourceSheet = createNewSheet_(
      "Episode " + episodeNumber + " " + resourceType);
  
  // Add the API data to the new sheet, using each object
  // key as a column header. 
  fillSheetWithData_(resourceSheet, resourceObjectKeys, resourceDataList);
  
  // Format the new sheet using the same styles the
  // 'Quick Formats' menu items apply. These methods all
  // act on the active sheet, which is the one just created.
  formatRowHeader();
  formatColumnHeader();   
  formatDataset();

}

6. Aggiungi le seguenti funzioni di assistenza alla fine del file di progetto dello script API.gs:

/** 
 * Helper function that retrieves a JSON object containing a
 * response from a public API.
 *
 * @param {string} url The URL of the API object being fetched.
 * @return {object} resourceObject The JSON object fetched
 *   from the URL request to the API.
 */
function fetchApiResourceObject_(url) {
  // Make request to API and get response.
  var response =
    UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  
  // Parse and return the response as a JSON object.
  var json = response.getContentText();
  var responseObject = JSON.parse(json); 
  return responseObject; 
}

/** 
 * Helper function that creates a sheet or returns an existing
 * sheet with the same name.
 *
 * @param {string} name The name of the sheet.
 * @return {object} The created or existing sheet
 *   of the same name. This sheet becomes active.
 */ 
function createNewSheet_(name) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Returns an existing sheet if it has the specified
  // name. Activates the sheet before returning.
  var sheet = ss.getSheetByName(name);
  if (sheet) {
    return sheet.activate();
  }
  
  // Otherwise it makes a sheet, set its name, and returns it.
  // New sheets created this way automatically become the active
  // sheet.
  sheet = ss.insertSheet(name); 
  return sheet; 
}

/** 
 * Helper function that adds API data to the sheet.
 * Each object key is used as a column header in the new sheet.
 *
 * @param {object} resourceSheet The sheet object being modified.
 * @param {object} objectKeys The list of keys for the resources.
 * @param {object} resourceDataList The list of API
 *   resource objects containing data to add to the sheet.
 */
function fillSheetWithData_(
    resourceSheet, objectKeys, resourceDataList) {
  // Set the dimensions of the data range being added to the sheet.
  var numRows = resourceDataList.length;
  var numColumns = objectKeys.length;
  
  // Get the resource range and associated values array. Add an
  // extra row for the column headers.
  var resourceRange =
    resourceSheet.getRange(1, 1, numRows + 1, numColumns);
  var resourceValues = resourceRange.getValues(); 
  
  // Loop over each key value and resource, extracting data to
  // place in the 2D resourceValues array.
  for (var column = 0; column < numColumns; column++) {

    // Set the column header.
    var columnHeader = objectKeys[column];
    resourceValues[0][column] = columnHeader;
    
    // Read and set each row in this column.
    for (var row = 1; row < numRows + 1; row++) {
      var resource = resourceDataList[row - 1];
      var value = resource[columnHeader];
      resourceValues[row][column] = value;
    }
  }
  
  // Remove any existing data in the sheet and set the new values.
  resourceSheet.clear()
  resourceRange.setValues(resourceValues);
}

7. Salva il progetto di script.

Revisione del codice

Hai appena aggiunto molto codice. Esaminiamo ogni funzione singolarmente per capire come funziona:

onOpen()

Qui hai aggiunto alcune voci di menu al tuo menu Quick formats. Hai impostato una linea di separazione e poi hai utilizzato il metodo Menu.addSubMenu(menu) per creare una struttura di menu nidificata con tre nuovi elementi. I nuovi elementi vengono aggiunti con il metodo Menu.addItem(caption, functionName).

Funzioni wrapper

Le voci di menu aggiunte fanno tutte qualcosa di simile: tentano di creare un foglio con i dati estratti da SWAPI. L'unica differenza è che ognuno si concentra su un film diverso.

Sarebbe comodo scrivere una singola funzione per creare il foglio e fare in modo che la funzione accetti un parametro per determinare quale film utilizzare. Tuttavia, il metodo Menu.addItem(caption, functionName) non consente di passare parametri quando viene chiamato dal menu. Quindi, come si evita di scrivere lo stesso codice tre volte?

La risposta è funzioni wrapper. Si tratta di funzioni leggere che puoi chiamare e che chiamano immediatamente un'altra funzione con parametri specifici impostati.

In questo caso, il codice utilizza tre funzioni wrapper: createPeopleSheetIV(), createPeopleSheetV() e createPeopleSheetVI(). Le voci di menu sono collegate a queste funzioni. Quando viene fatto clic su una voce di menu, la funzione wrapper viene eseguita e chiama immediatamente la funzione principale di creazione del foglio createResourceSheet_(resourceType, idNumber, episodeNumber), passando i parametri appropriati per la voce di menu. In questo caso, significa chiedere alla funzione di creazione del foglio di creare un foglio compilato con i dati dei personaggi principali di uno dei film di Star Wars.

Nota: se vuoi, puoi creare altre funzioni wrapper per compilare diversi tipi di fogli con i dati. Devi solo sapere quale tipo di risorsa richiedere e quale ID film utilizzare. Esempi di tipi di risorse includono "planets" e "starships" e l'API accetta i numeri da 1 a 7 come ID film.

createResourceSheet_(resourceType, idNumber, episodeNumber)

Questa è la funzione principale di creazione del foglio per questo esercizio. Con l'aiuto di alcune funzioni di supporto, recupera i dati dell'API, li analizza, crea un foglio, scrive i dati dell'API nel foglio e poi lo formatta utilizzando le funzioni create nelle sezioni precedenti. Esaminiamo i dettagli:

Innanzitutto, la funzione utilizza fetchApiResourceObject_(url) per effettuare una richiesta all'API per recuperare le informazioni di base sul film. La risposta dell'API include una raccolta di URL che il codice può utilizzare per ottenere maggiori dettagli su persone specifiche (note qui come risorse) dei film. Il codice raccoglie tutto nell'array resourceUrls.

Successivamente, il codice utilizza fetchApiResourceObject_(url) ripetutamente per chiamare l'API per ogni URL risorsa in resourceUrls. I risultati vengono memorizzati nell'array resourceDataList. Ogni elemento di questo array è un oggetto che descrive un personaggio diverso del film.

Gli oggetti dati delle risorse hanno diverse chiavi comuni che mappano le informazioni sul personaggio. Ad esempio, la chiave "name" corrisponde al nome del personaggio del film. Supponiamo che le chiavi per ogni oggetto di dati della risorsa siano tutte identiche, poiché sono destinate a utilizzare strutture di oggetti comuni. L'elenco delle chiavi è necessario in un secondo momento, quindi il codice lo memorizza in resourceObjectKeys utilizzando il metodo JavaScript Object.keys().

Successivamente, la funzione di creazione chiama la funzione helper createNewSheet_(name) per creare il foglio in cui verranno inseriti i nuovi dati. La chiamata di questa funzione helper attiva anche il nuovo foglio.

Dopo la creazione del foglio, viene chiamata la funzione helper fillSheetWithData_(resourceSheet, objectKeys, resourceDataList) per aggiungere tutti i dati dell'API al foglio.

Infine, vengono chiamate tutte le funzioni di formattazione create in precedenza per applicare le stesse regole di formattazione ai nuovi dati. Poiché il nuovo foglio è quello attivo, il codice può riutilizzare queste funzioni senza modifiche.

fetchApiResourceObject_(url)

Questa funzione helper è simile alla funzione helper fetchBookData_(ISBN) utilizzata nel codelab precedente Working with data. Prende l'URL specificato e utilizza il metodo UrlFetchApp.fetch(url, params) per ottenere una risposta. La risposta viene quindi analizzata in un oggetto JSON utilizzando i metodi HTTPResponse.getContextText() e JSON.parse(json) di JavaScript. Viene quindi restituito l'oggetto JSON risultante.

Nota: se utilizzi spesso le API, questa funzione può essere utile quando devi recuperare informazioni sulle API. È generalizzato per funzionare con quasi tutti gli URL API, quindi ti consigliamo di salvarlo per un uso futuro.

createNewSheet_(name)

Questa funzione helper è piuttosto semplice. Innanzitutto, verifica se nel foglio di lavoro esiste un foglio con il nome specificato. In questo caso, la funzione attiva il foglio e lo restituisce.

Se il foglio non esiste, la funzione lo crea con Spreadsheet.insertSheet(sheetName), lo attiva e lo restituisce.

fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)

Questa funzione helper è responsabile del riempimento del nuovo foglio con i dati dell'API. Prende come parametri il nuovo foglio, l'elenco delle chiavi degli oggetti e l'elenco degli oggetti risorsa API. Ogni chiave oggetto rappresenta una colonna nel nuovo foglio e ogni oggetto risorsa rappresenta una riga.

Innanzitutto, la funzione calcola il numero di righe e colonne necessarie per presentare i nuovi dati dell'API. Queste sono le dimensioni dell'elenco di risorse e chiavi, rispettivamente. La funzione definisce quindi un intervallo di output (resourceRange) in cui verranno inseriti i dati, aggiungendo una riga aggiuntiva per contenere le intestazioni delle colonne. La variabile resourceValues contiene un array di valori bidimensionale estratto da resourceRange.

La funzione esegue quindi un ciclo su ogni chiave dell'oggetto nell'elenco objectKeys. La chiave viene impostata come intestazione di colonna, quindi un secondo ciclo esamina ogni oggetto risorsa. Per ogni coppia (riga, colonna), le informazioni API corrispondenti vengono copiate nell'elemento resourceValues[row][column].

Nota: ricorda che le righe e le colonne in Fogli sono indicizzate a partire da 1, mentre gli array JavaScript sono indicizzati a partire da 0. A causa di questa differenza, spesso dobbiamo aggiungere o sottrarre 1 dagli indici durante la programmazione in Apps Script.

Dopo aver compilato resourceValues, il foglio di destinazione viene cancellato utilizzando Sheet.clear() nel caso in cui contenga dati di clic precedenti sulle voci di menu. Infine, i nuovi valori vengono scritti nel foglio.

Risultati

Per vedere i risultati del tuo lavoro:

1. Se non l'hai ancora fatto, salva il progetto di script nell'editor di Apps Script.
2. Fai clic sulla voce di menu Formati rapidi > Crea scheda personaggio > Episodio IV.

I risultati dovrebbero essere simili ai seguenti:

Ora hai scritto il codice per importare i dati in Fogli e formattarli automaticamente.

8. Conclusione

Congratulazioni per aver completato questo codelab. Hai visto alcune delle opzioni di formattazione di Fogli che puoi includere nei tuoi progetti Apps Script e hai creato un'applicazione impressionante che importa e formatta un ampio set di dati API.

Hai trovato utile questo codelab?

Sì No

Che cosa hai imparato

• Come applicare varie operazioni di formattazione di Fogli con Apps Script.
• Come creare sottomenu con la funzione onOpen().
• Come formattare un elenco recuperato di oggetti JSON in un nuovo foglio di dati con Apps Script.

Termini chiave:

• Attivo (stato): indica che il foglio di lavoro, il foglio, l'intervallo o la cella specificati sono quelli che l'utente del foglio di lavoro sta attualmente visualizzando o evidenziando.
• Cella attiva: la singola cella evidenziata nel foglio attivo con il 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 programmi o script possono chiamare per richiedere informazioni o per eseguire determinate azioni.
• Autorizzazione: la procedura con cui l'utente concede le autorizzazioni per consentire ad Apps Script di accedere ai dati utente nei servizi Google pertinenti o di eseguire azioni per suo conto.
• Script associato al contenitore: qualsiasi script associato e creato da un documento Google Workspace, ad esempio un foglio Google o un documento Google.
• Funzione helper: una funzione, in genere privata, chiamata da un'altra per completare una piccola attività secondaria. Le funzioni helper vengono utilizzate quando la sottoattività deve essere completata più volte in posizioni diverse.
• JSON: formato di file per definire gli oggetti di dati e le relative caratteristiche dal testo.
• onOpen(): se definito in uno script, è un semplice trigger che si attiva ogni volta che un file si apre o si ricarica.
• Funzioni private: una funzione che non può essere chiamata come parte di una libreria o dai client tramite comunicazione client-server. Le funzioni private di Apps Script hanno nomi che terminano con _.
• Intervallo**: un gruppo di una o più celle adiacenti all'interno di un foglio. La classe 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.
• Trigger semplici: un sottotipo di trigger in Apps Script con nomi di funzioni trigger riservati e determinate limitazioni.
• Foglio**: una singola pagina di un foglio di lavoro. A volte utilizzato per fare riferimento a un file Fogli Google all'interno di Google Drive. La classe Sheet** consente di accedere ai fogli e modificarli.
• SpreadsheetApp: questa classe funge da classe principale per il servizio Fogli e fornisce un punto di partenza per il codice che legge o manipola i dati di Fogli Google.
• Foglio di lavoro**: un file Fogli Google che si trova in Google Drive. La classe Spreadsheet** consente di accedere ai fogli di lavoro e modificarli.
• Servizio fogli di lavoro: un servizio Apps Script che consente agli script di creare, accedere e modificare i file 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), il trigger "si attiva" 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.
• Funzioni wrapper: semplici funzioni di utilità utilizzate per chiamare funzioni più complesse passando loro un insieme specifico di parametri.

Passaggi successivi

Il codelab successivo di questa playlist mostra come utilizzare Apps Script per visualizzare i dati in un grafico ed esportare i grafici nelle presentazioni Google.

Trova il prossimo codelab in Grafici e presentazione dei dati in Presentazioni.