Concetti fondamentali di Apps Script con Fogli Google n. 4: formattazione dei dati

1. Introduzione

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

Completando questo codelab, imparerai a formattare i dati del tuo foglio di lavoro in Apps Script e a scrivere funzioni per creare fogli di lavoro organizzati con 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 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
3. Utilizzo dei dati

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

Prima di continuare, ti servirà un foglio di lavoro con alcuni dati. Come in precedenza, ti abbiamo fornito un foglio dati che puoi copiare per questi allenamenti. Segui questi passaggi:

1. Fai clic su questo link per copiare il foglio dati, quindi fai clic su Crea una copia. Il nuovo foglio di lavoro viene inserito nella cartella di Google Drive e denominato "Copia della formattazione dei dati".
2. Fai clic sul titolo del foglio di lavoro e modificalo da "Copia della formattazione dei dati" a "Formattazione dei dati". Il foglio dovrebbe essere simile a questo, 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 dati". Fai clic su Rinomina per salvare la modifica del titolo.

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

3. Creare un menu personalizzato

In Fogli Google puoi applicare diversi metodi di formattazione di base in Apps Script. I seguenti esercizi illustrano alcuni modi per formattare i dati. Per controllare le azioni di formattazione, creiamo un menu personalizzato con gli elementi di cui hai bisogno. La procedura di creazione di menu personalizzati è stata descritta nel codelab Utilizzo dei dati, ma lo riepilocheremo 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. Verrà eseguito onOpen() per ricreare il menu del foglio di lavoro, quindi non dovrai ricaricare il foglio di lavoro.

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), aggiungi voci di menu (Format row header, Format column header e Format dataset) al menu, quindi aggiungi il menu all'interfaccia di 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 una funzione Apps Script che viene eseguita quando viene selezionata la voce di menu. 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:

Il clic su questi elementi causa un errore perché non hai implementato le funzioni corrispondenti. Ora eseguiamo questa operazione.

4. Formattare una riga di intestazione

I set di dati nei fogli di lavoro spesso presentano righe di intestazione per identificare i dati di 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 hai modificato il suo codice. Qui, creerai una riga di intestazione da zero utilizzando Apps Script. La riga di intestazione che creerai sarà formattata in grassetto per il testo dell'intestazione, per colorare lo sfondo di colore blu-verde scuro, per colorare il testo bianco e aggiungere alcuni bordi solidi.

Implementazione

Per implementare l'operazione di formattazione, utilizzerai gli stessi metodi di Servizio foglio di lavoro che hai utilizzato in precedenza, ma ora utilizzerai anche alcuni metodi di formattazione del servizio. Segui questi passaggi:

1. Nell'editor Apps Script, aggiungi la seguente funzione alla fine del tuo progetto 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 per molte attività di formattazione, il codice Apps Script per implementare è semplice. Le prime due righe utilizzano i metodi che hai visto in precedenza per ottenere un riferimento al foglio attivo corrente (sheet) e alla riga superiore del foglio (headerRange)). Il metodo Sheet.getRange(row, column, numRows, numColumns) specifica la riga superiore, incluse solo le colonne che contengono dati. Il metodo Sheet.getLastColumn() restituisce l'indice della colonna dell'ultima colonna che contiene i dati nel foglio. Nel nostro esempio, la colonna E (url).

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

Il resto del codice chiama semplicemente vari metodi di Range per applicare le scelte di formattazione a tutte le celle di headerRange. Per semplificare la lettura del codice, utilizziamo il concatenamento dei metodi per chiamare ogni metodo di formattazione uno dopo l'altro.

• Range.setFontWeight(fontWeight) viene utilizzato per impostare lo spessore del carattere in 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 colore blu-verde scuro.
• setBorder(top, left, bottom, right, vertical, horizontal, color, style) aggiunge un bordo nero a tinta unita intorno alle celle dell'intervallo.

L'ultimo metodo ha diversi parametri, perciò vediamo come stanno andando. I primi quattro parametri qui (tutti impostati su true) indicano ad Apps Script che il bordo deve essere aggiunto sopra, sotto e a sinistra e a destra dell'intervallo. Il quinto e il sesto parametro (null e null) indirizzano Apps Script per 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. Infine, l'ultimo parametro specifica il tipo di stile del bordo da utilizzare tra le opzioni disponibili in SpreadsheetApp.BorderStyle.

Risultati

Per visualizzare la funzione di formattazione in azione, procedi nel seguente modo:

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

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.

Il risultato dovrebbe essere simile all'esempio seguente:

Hai automatizzato un'attività di formattazione. Nella sezione successiva viene applicata 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 fare anche un'intestazione di colonna. Le intestazioni di colonna aumentano la leggibilità di alcuni set di dati. Ad esempio, la colonna titles in questo foglio di lavoro può essere migliorata con le seguenti scelte di formato:

• Grassetto del testo
• Corsivo del testo
• Aggiunta di bordi celle
• Inserimento di link ipertestuali, utilizzando i contenuti della colonna url. Dopo aver aggiunto questi link ipertestuali, puoi rimuovere la colonna url per ripulire il foglio.

Ora implementerai una funzione formatColumnHeader() per applicare queste modifiche alla prima colonna del foglio. Per rendere il codice un po' più facile da leggere, implementerai anche due funzioni helper.

Implementazione

Come prima, devi aggiungere una funzione per automatizzare la formattazione dell'intestazione di colonna. Segui questi passaggi:

1. Nell'editor Apps Script, aggiungi la seguente funzione formatColumnHeader() alla fine del tuo 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 helper alla fine del tuo progetto 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 separatamente il codice di ognuna di queste tre funzioni:

formatColumnHeader()

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

• Il foglio attivo viene memorizzato in sheet.
• Il numero di righe nell'intestazione della colonna viene calcolato e salvato in numRows. In questo caso il codice viene sottratto da uno, in modo che il numero di 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 i caratteri in grassetto all'intervallo di intestazione della colonna, proprio come in formatRowHeader(). In questo caso, per rendere il testo in corsivo viene utilizzata anche la formula Range.setFontStyle(fontStyle).

L'aggiunta di link ipertestuali alla colonna di intestazione è più complessa, quindi formatColumnHeader() chiama hyperlinkColumnHeaders_(headerRange, numRows) per occuparsi dell'attività. Ciò consente di mantenere il codice ordinato e leggibile.

hyperlinkColumnHeaders_(headerRange, numRows)

Questa funzione helper identifica per prima cosa gli indici di colonna dell'intestazione (si presume che sia l'indice 1) e la colonna url. Chiama il metodo columnIndexOf_('url') per ottenere l'indice della colonna URL. Se non viene trovata una colonna url, il metodo termina senza modificare i dati.

La funzione riceve un nuovo intervallo (urlRange) che copre gli URL corrispondenti alle righe della colonna di intestazione. Per questa operazione viene utilizzato il metodo Range.offset(rowOffset, columnOffset), che garantisce che le due dimensioni abbiano le stesse dimensioni. Dopodiché vengono recuperati i valori di entrambe le colonne headerColumn e url (headerValues e urlValues).

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

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

columnIndexOf_(colName)

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

La funzione esamina quindi ogni nome in ordine. Se ne trova uno che corrisponde al nome che stai cercando, si interrompe 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 visualizzare la funzione di formattazione in azione, procedi nel seguente modo:

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

Il risultato dovrebbe essere simile all'esempio seguente:

Hai automatizzato un'altra attività di formattazione. Con le intestazioni di colonna e di riga formattate, la sezione seguente mostra come formattare i dati.

6. Formattare il set di dati

Ora che disponi delle intestazioni, creiamo una funzione che formatti il resto dei dati del foglio. Useremo le seguenti opzioni di formattazione:

• Colori di sfondo alternati alle righe (noti come banding)
• Modifica dei formati delle date
• Applicazione di bordi
• Ridimensionamento automatico di tutte le colonne e le righe

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

Implementazione

Come in precedenza, aggiungi una funzione per automatizzare la formattazione dei dati. Segui questi passaggi:

1. Nell'editor Apps Script, aggiungi la seguente funzione formatDataset() alla fine del tuo 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 tuo progetto 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 separatamente il codice di ognuna di queste due funzioni:

formatDataset()

Questa funzione segue un modello simile a quello delle funzioni di formato precedenti che hai già implementato. Innanzitutto, contiene variabili che contengono riferimenti al foglio attivo (foglio) e all'intervallo di dati (fullDataRange).

In secondo luogo, utilizza il metodo Range.offset(rowOffset, columnOffset, numRows, numColumns) per creare un intervallo (noHeadersRange) che copra tutti i dati nel foglio, escluse le intestazioni di colonna e di riga. Il codice verifica quindi se il nuovo intervallo contiene criteri a bande esistenti (utilizzando Range.getBandings()). Questa operazione è necessaria perché Apps Script genera un errore se provi ad applicare un nuovo gruppo di bandi se ne esiste uno. Se non esiste alcun cinturino, la funzione aggiunge un banding 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 denominata "release_date' (descritta di seguito). La colonna viene specificata utilizzando la funzione helper columnIndexOf_(colName) che hai implementato in precedenza.

Infine, la formattazione viene terminata aggiungendo un altro bordo (come in precedenza) e ridimensiona automaticamente ogni colonna e riga in modo che si adatti ai dati che contiene 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 della colonna fornito. In particolare, formatta i valori di data come "Giorno del mese, Anno (Giorno della settimana)".

Nota: ci sono molti formati di data e numero diversi che puoi utilizzare negli script e nei fogli di lavoro. Per ulteriori informazioni, consulta la guida relativa ai formati data e numero.

Innanzitutto, la funzione verifica che l'indice della colonna fornito sia valido (ovvero, 0 o superiore). In caso contrario, viene restituito senza alcun intervento. Questo controllo impedisce gli errori che potrebbero verificarsi se, ad esempio, la scheda del foglio non contiene la colonna"release_date&#39".

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

Risultati

Per visualizzare la funzione di formattazione in azione, procedi nel seguente modo:

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

Il risultato dovrebbe essere simile all'esempio seguente:

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

7. Recupera e formatta i dati API

Finora in questo codelab hai visto come utilizzare Apps Script come metodo alternativo per formattare il foglio di lavoro. Dopodiché scrivi 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 come estrarre i dati da un'API. Qui utilizzerai le stesse tecniche. Nel corso di questo esercizio, utilizzeremo l'API Star Wars (SWAPI) pubblica per completare il tuo foglio di lavoro. Nello specifico, utilizzerai l'API per ottenere informazioni sui personaggi più famosi presenti nei tre film originali di Star Wars.

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

Implementazione

In questa sezione aggiungerai alcune voci di menu aggiuntive. Ogni voce di menu chiama uno script wrapper che passa le variabili specifiche degli elementi alla funzione principale (createResourceSheet_()). Implementare questa funzione e altre tre funzioni helper. Come nell'attività precedente, le funzioni helper consentono di isolare le parti compartimentali dell'attività in modo logico e contribuiscono a rendere 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, onOpen() può ricreare il menu del foglio di lavoro con le nuove opzioni che hai aggiunto.
4. Per creare un file Apps Script, accanto a File fai clic su Aggiungi un file > Script.
5. Assegna al nuovo script il nome "API" e premi Invio. Apps Script aggiunge automaticamente un'estensione .gs al nome del file dello 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 helper 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 ciascuna funzione singolarmente per comprenderne il funzionamento:

onOpen()

Qui hai aggiunto alcune voci al 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 tramite il metodo Menu.addItem(caption, functionName).

Funzioni di wrapper

Le voci di menu aggiunte stanno funzionando in modo simile: stanno cercando di creare un foglio con dati estratti da SWAPI. L'unica differenza è che ciascuno si concentra su un film diverso.

Sarebbe pratico scrivere una singola funzione per creare il foglio e fare in modo che la funzione accetti un parametro per stabilire quale pellicola usare. Tuttavia, il metodo Menu.addItem(caption, functionName) non ti consente di trasmettervi parametri quando viene chiamato dal menu. Quindi, come puoi evitare di scrivere lo stesso codice tre volte?

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

In questo esempio il codice utilizza tre funzioni wrapper: createPeopleSheetIV(), createPeopleSheetV() e createPeopleSheetVI(). Le voci di menu sono collegate a queste funzioni. Quando viene fatto clic su un elemento del menu, la funzione wrapper viene eseguita e richiama immediatamente la funzione principale del generatore di fogli createResourceSheet_(resourceType, idNumber, episodeNumber), trasmettendo i parametri appropriati per l'elemento del menu. In questo caso, significa che devi chiedere alla funzione di creazione del foglio di creare un foglio in cui inserire i dati principali dei personaggi di uno dei film di Star Wars.

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

createResourceSheet_(resourceType, idNumber, episodeNumber)

Questa è la funzione di creazione del foglio principale per questo esercizio. Con l'aiuto di alcune funzioni helper, riceve i dati API, li analizza, crea un foglio, scrive i dati API nel foglio e poi formatta il foglio utilizzando le funzioni che hai creato 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 API include una raccolta di URL che il codice può utilizzare per ottenere maggiori dettagli su persone specifiche (note come risorse) dai film. Il codice raccoglie tutto nell'array resourceUrls.

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

Gli oggetti dei dati delle risorse hanno diverse chiavi comuni che mappano le informazioni su quel carattere. Ad esempio, la chiave "name' viene mappata al nome del personaggio del film. Supponiamo che le chiavi per ogni oggetto dati di risorse siano tutte identiche, poiché sono state ideate per utilizzare strutture di oggetti comuni. L'elenco delle chiavi è necessario in un secondo momento, quindi il codice memorizza l'elenco delle chiavi in resourceObjectKeys utilizzando il metodo JavaScript Object.keys().

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

Dopo aver creato il foglio, viene richiamata la funzione helper fillSheetWithData_(resourceSheet, objectKeys, resourceDataList) per aggiungere tutti i dati API al foglio.

Infine, tutte le funzioni di formattazione create in precedenza vengono chiamate 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 Utilizzo dei dati. 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 JavaScript JSON.parse(json). Viene restituito l'oggetto JSON risultante.

Nota: se lavori molto con le API, questa funzione può essere utile quando devi recuperare le informazioni delle API. È generalmente compatibile con quasi tutti gli URL delle API, pertanto potresti salvarlo per un uso futuro.

createNewSheet_(name)

Questa funzione helper è abbastanza semplice. Verifica innanzitutto se nel foglio di lavoro è presente 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 restituisce il nuovo foglio.

fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)

Questa funzione helper è responsabile del riempimento del nuovo foglio di dati API. Prende come parametri il nuovo foglio, l'elenco di chiavi dell'oggetto e l'elenco di oggetti risorsa dell'API come parametri. 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. Indica la dimensione dell'elenco delle risorse e delle 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 2D estratto da resourceRange.

La funzione esegue il loop su ogni chiave dell'oggetto nell'elenco objectKeys. La chiave è impostata come intestazione della colonna e viene eseguito un secondo loop attraverso 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 di Fogli vengono indicizzate, mentre gli array JavaScript sono indicizzati da 0. A causa di questa differenza, spesso dobbiamo aggiungere o sottrarre 1 dagli indici durante la programmazione in Apps Script.

Dopo che resourceValues è stato compilato, il foglio di destinazione viene cancellato utilizzando Sheet.clear() se contiene dati provenienti da clic su voci di menu precedenti. Infine, i nuovi valori vengono scritti nel foglio.

Risultati

Per visualizzare i risultati del tuo lavoro, procedi come segue:

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

Il risultato dovrebbe essere simile all'esempio seguente:

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 di grandi dimensioni 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 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 chiamata 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.
• Funzioni wrapper: sono semplici funzioni di utilità utilizzate per richiamare funzioni più complesse mentre trasmettono loro un insieme specifico di parametri.

Passaggi successivi

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

Trova il prossimo codelab su Grafico e presentazione dei dati in Presentazioni.