Elementi di base di Apps Script con Fogli Google n. 2: fogli di lavoro, fogli e intervalli

1. Introduzione

Ti diamo il benvenuto nella seconda parte della playlist di codelab Fundamentals of Apps Script with Google Sheets. Il codelab precedente si è concentrato sui concetti di editor di script, macro e funzioni personalizzate. Questo codelab approfondisce il servizio Fogli di lavoro che puoi utilizzare per leggere, scrivere e manipolare i dati in Fogli Google.

Obiettivi didattici

• Come vengono rappresentati fogli di lavoro, fogli e intervalli in Apps Script.
• Come accedere, creare e rinominare il foglio di lavoro attivo (aperto) con le classi SpreadsheetApp e Spreadsheet.
• Come modificare il nome di un foglio e l'orientamento di colonne/righe di un intervallo utilizzando la classe Sheet.
• Come specificare, attivare, spostare e ordinare un gruppo di celle o un intervallo di dati utilizzando la classe Range.

Prima di iniziare

Questo è il secondo codelab della playlist Nozioni di base di Apps Script con Fogli Google. Prima di iniziare, assicurati di completare il primo codelab: Macro e funzioni personalizzate.

Che cosa ti serve

• Una conoscenza degli argomenti di base di Apps Script esplorati nel codelab precedente 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

La sezione successiva introduce le classi principali del servizio Fogli.

2. Introduzione al servizio Fogli

Quattro classi comprendono le basi del servizio Fogli: SpreadsheetApp, Spreadsheet, Sheet e Range. Questa sezione descrive queste classi e il loro utilizzo.

Classe SpreadsheetApp

Prima di approfondire i fogli di lavoro, i fogli e gli intervalli, devi esaminare la classe principale: SpreadsheetApp. Molti script iniziano chiamando i metodi SpreadsheetApp, in quanto possono fornire il punto di accesso iniziale ai tuoi file Fogli Google. Puoi considerare SpreadsheetApp come la classe principale del servizio Fogli. Il corso SpreadsheetApp non viene analizzato in dettaglio qui. Tuttavia, più avanti in questo codelab puoi trovare esempi ed esercizi per aiutarti a comprendere questa classe.

Fogli di lavoro, fogli e relativi corsi

In Fogli, un foglio di lavoro è un file Fogli Google (memorizzato in Google Drive) che contiene dati organizzati per righe e colonne. Un foglio di lavoro viene a volte chiamato "foglio Google", proprio come un documento viene chiamato "documento Google".

Puoi utilizzare la classe Spreadsheet per accedere ai dati dei file Fogli Google e modificarli. Puoi utilizzare questa classe anche per altre operazioni a livello di file, ad esempio l'aggiunta di collaboratori.

Un foglio** rappresenta la singola pagina di un foglio di lavoro, a volte chiamata "scheda". Ogni foglio di lavoro può contenere uno o più fogli. Puoi utilizzare la classe Sheet** per accedere e modificare i dati e le impostazioni a livello di foglio, ad esempio spostare righe o colonne di dati.

Punto chiave: oltre a un nome, i fogli di lavoro hanno anche una stringa ID univoca per quel foglio. L'ID è presente nell'URL del foglio di lavoro nel formato:

https://docs.google.com/spreadsheets/d/spreadsheetID/edit#gid=0

Anche ogni foglio di un foglio di lavoro ha un numero ID univoco per quel foglio di lavoro. Questo numero ID è casuale, ma puoi visualizzarlo anche nell'URL del foglio di lavoro nel formato:

https://docs.google.com/spreadsheets/d/spreadsheetID/edit#gid=sheetID

Puoi vedere entrambi gli ID in questo URL di esempio:

Poiché gli ID per i fogli di lavoro e i fogli sono univoci (e i nomi dei fogli di lavoro non lo sono), è meglio utilizzare gli ID per specificare i fogli di lavoro e i fogli, se possibile. Ad esempio, puoi avere più fogli di lavoro con lo stesso nome in Google Drive. Utilizzando metodi di script come SpreadsheetApp.openById(id) per aprire i fogli di lavoro, puoi evitare di modificare accidentalmente il file sbagliato.

Scopri di più sugli ID in Fogli Google consultando la Guida introduttiva a Fogli Google.

In sintesi, la classe Spreadsheet opera sulla raccolta di fogli e definisce un file Fogli Google in Google Drive. La classe Sheet opera su singoli fogli all'interno di un foglio di lavoro.

The Range

La maggior parte delle operazioni di manipolazione dei dati (ad esempio, lettura, scrittura o formattazione dei dati delle celle) richiede di definire a quali celle si applica l'operazione. Puoi utilizzare la classe Range per selezionare insiemi specifici di celle all'interno di un foglio. Le istanze di questa classe rappresentano un intervallo, ovvero un gruppo di una o più celle adiacenti in un foglio. Puoi specificare gli intervalli in base al numero di riga e colonna o utilizzando la notazione A1.

Termine chiave: in Apps Script, il foglio attivo è quello attualmente aperto e visibile nell'interfaccia di Fogli Google. Analogamente, l'intervallo attivo è il gruppo di una o più celle attualmente evidenziate nel foglio attivo. La cella attiva è l'unica cella evidenziata nel foglio attivo su cui è posizionato il cursore.

Molti metodi della classe restituiscono l'oggetto attivo della classe richiesta. Ad esempio, l'esecuzione di SpreadsheetApp.getActiveSpreadsheet() restituisce il foglio di lavoro attivo o corrente dell'utente. Se ottieni il foglio di lavoro attivo, puoi applicare operazioni a quel foglio di lavoro. Allo stesso modo, metodi come Sheet.activate() possono modificare l'oggetto attivo.

L'attivazione di un foglio, un intervallo o una cella equivale a fare clic su quel foglio, intervallo o cella nell'interfaccia di Fogli Google, spostando così il cursore in quella posizione.

Il resto del codelab mostra esempi di script che funzionano con queste classi e i relativi metodi.

3. Configura

Prima di continuare, devi avere un foglio di lavoro con alcuni dati. Ne abbiamo fornito uno: fai clic su questo link per copiare il foglio di dati e poi fai clic su Crea una copia.

Una copia del foglio di lavoro di esempio da utilizzare viene inserita nella cartella di Google Drive e denominata "Copia di Foglio di lavoro senza titolo". Utilizza questo foglio di lavoro per completare gli esercizi di questo codelab.

Ti ricordiamo che puoi aprire l'editor di script da Fogli Google facendo clic su Estensioni > Apps Script.

Quando apri un progetto Apps Script nell'editor di script per la prima volta, l'editor di script crea sia un progetto di script sia un file di script.

La sezione successiva mostra come utilizzare la classe Spreadsheet per migliorare questo foglio di lavoro.

4. Accedere ai fogli di lavoro e modificarli

In questa sezione puoi imparare a utilizzare le classi SpreadsheetApp e Spreadsheet per accedere ai fogli di lavoro e modificarli. In particolare, gli esercizi ti insegnano come rinominare un foglio di lavoro e come duplicare i fogli all'interno di un foglio di lavoro.

Si tratta di operazioni semplici, ma spesso fanno parte di un flusso di lavoro più ampio e complesso. Una volta che avrai capito come automatizzare queste attività con il codice dello script, sarà più facile imparare ad automatizzare operazioni più elaborate.

Rinomina il foglio di lavoro attivo

Supponiamo che tu voglia modificare il nome predefinito "Copia di Foglio di lavoro senza titolo" con un titolo che rifletta meglio lo scopo del foglio di lavoro. Puoi farlo con le classi SpreadsheetApp e Spreadsheet.

1. Nell'editor di script, sostituisci il blocco di codice myFunction() predefinito con il seguente codice:

function renameSpreadsheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  mySS.rename("2017 Avocado Prices in Portland, Seattle");
}

2. Per salvare il copione, fai clic su Salva .
3. Per rinominare il progetto Apps Script, fai clic su Progetto senza titolo, inserisci "Prezzi degli avocado" come nuovo nome del progetto e fai clic su Rinomina.
4. Per eseguire lo script, seleziona renameSpreadsheet dall'elenco delle funzioni e fai clic su Esegui.
5. Autorizza la macro seguendo le istruzioni sullo schermo. Se visualizzi il messaggio "Questa app non è verificata", fai clic su Avanzate, quindi su Vai a Prezzi dell'avocado (non sicuro). Nella schermata successiva, fai clic su Consenti.

Nota:se utilizzi un account gmail.com, la prima volta che utilizzi lo script potrebbe essere visualizzata una finestra di dialogo relativa a un'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.

Una volta eseguita la funzione, il nome file del foglio di lavoro dovrebbe cambiare:

Diamo un'occhiata al codice che hai inserito. Il metodo getActiveSpreadsheet() restituisce un oggetto che rappresenta il foglio di lavoro attivo, ovvero la copia del foglio di lavoro dell'esercizio che hai creato. Questo oggetto foglio di lavoro è archiviato nella variabile mySS. Chiamare rename(newName) il giorno mySS modifica il nome del file del foglio di lavoro in Google Drive in "Prezzi degli avocado nel 2017 a Portland e Seattle".

Poiché la variabile mySS è un riferimento al foglio di lavoro, puoi rendere il codice più pulito ed efficiente chiamando i metodi Spreadsheet su mySS anziché chiamare getActiveSpreadsheet() ripetutamente.

Duplica il foglio attivo

Nel foglio di lavoro attuale, hai un solo foglio. Puoi chiamare il metodo Spreadsheet.duplicateActiveSheet() per creare una copia del foglio:

1. Aggiungi la seguente nuova funzione sotto la funzione renameSpreadsheet() già presente nel progetto di script:

function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();
}

2. Salva il progetto di script.
3. Per eseguire lo script, seleziona duplicateAndOrganizeActiveSheet dall'elenco delle funzioni e fai clic su Esegui.

Torna a Fogli per visualizzare una nuova scheda "Copia di Sheet_Originale" aggiunta al foglio di lavoro.

In questa nuova funzione, il metodo duplicateActiveSheet() crea, attiva e restituisce il foglio duplicato nel foglio di lavoro. Il foglio risultante è archiviato in duplicateSheet, ma il codice non fa ancora nulla con questa variabile.

Nella sezione successiva, utilizzerai la classe Sheet per rinominare e formattare il foglio duplicato.

5. Formattare il foglio con la classe Sheet

La classe Sheet fornisce metodi che consentono agli script di leggere e aggiornare i fogli. In questa sezione, puoi scoprire come modificare il nome di un foglio e la larghezza delle colonne con i metodi della classe Sheet.

Nota**: le classi Spreadsheet** e Sheet condividono diversi metodi comuni specifici per i fogli, come Spreadsheet.deleteColumn(columnPosition) e Sheet.deleteColumn(columnPosition).

Quando chiami Spreadsheet.deleteColumn(columnPosition), il metodo rimuove le colonne dal foglio attualmente attivo (aperto) all'interno del foglio di lavoro. Puoi ottenere lo stesso risultato chiamando il numero getActiveSheet() e poi il numero Sheet.deleteColumn(columnPosition). La versione Spreadsheet ti fa semplicemente risparmiare tempo.

Modificare il nome del foglio

Rinominare i fogli è semplice come rinominare il foglio di lavoro in renameSpreadsheet(). È necessaria una sola chiamata al metodo.

1. In Fogli Google, fai clic sul foglio Sheet_Original per attivarlo.
2. In Apps Script, modifica la funzione duplicateAndOrganizeActiveSheet() in modo che corrisponda a quanto segue:

function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());
}

3. Salva ed esegui la funzione.

In Fogli Google, quando esegui la funzione viene creato un foglio duplicato e rinominato:

Nel codice aggiunto, il metodo setName(name) modifica il nome di duplicateSheet utilizzando getSheetID() per ottenere il numero ID univoco del foglio. L'operatore + concatena l'ID foglio alla fine della stringa "Sheet_".

Modificare le colonne e le righe di un foglio

Puoi anche utilizzare la classe Sheet per formattare il foglio. Ad esempio, possiamo aggiornare la funzione duplicateAndOrganizeActiveSheet() per ridimensionare anche le colonne del foglio duplicato e aggiungere righe bloccate:

1. In Fogli Google, fai clic sul foglio Sheet_Original per attivarlo.
2. In Apps Script, modifica la funzione duplicateAndOrganizeActiveSheet() in modo che corrisponda a quanto segue:

function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);
}

3. Salva ed esegui la funzione.

In Fogli Google viene creato, rinominato, attivato e formattato un foglio duplicato:

Il codice che hai aggiunto utilizza autoResizeColumns(startColumn, numColumns) per ridimensionare le colonne del foglio per migliorarne la leggibilità. Il metodo setFrozenRows(rows) blocca il numero specificato di righe (due, in questo caso), mantenendo visibili le righe di intestazione mentre il lettore scorre verso il basso nel foglio di lavoro.

Nota:quando lavori con gli array in Apps Script, segui la convenzione secondo cui l'elenco inizia dall'indice 0. Tuttavia, quando lavori con righe e colonne, l'indice inizia da 1. Questa logica è in linea con la formattazione convenzionale dei fogli di lavoro, in cui si inizia dalla riga 1 e dalla colonna A in Fogli.

Tienilo presente quando utilizzi metodi relativi a colonne e righe, ad esempio autoResizeColumns(startColumn, numColumns). Nell'esercizio attuale, inserisci 1 (non 0) come parametro per fare riferimento alla colonna A di duplicateSheet.

Nella sezione successiva, imparerai a conoscere gli intervalli e la manipolazione di base dei dati.

6. Riorganizzare i dati con la classe Range

La classe Range e i relativi metodi forniscono la maggior parte delle opzioni di manipolazione e formattazione dei dati nel servizio Fogli.

Questa sezione introduce la manipolazione di base dei dati con gli intervalli. Questi esercizi si concentrano su come utilizzare gli intervalli in Apps Script, mentre altri codelab di questa playlist approfondiscono la manipolazione e la formattazione dei dati.

Spostare gli intervalli

Puoi attivare e spostare intervalli di dati con metodi di classe e notazione A1, una notazione abbreviata per identificare insiemi specifici di celle all'interno dei fogli di lavoro. Se hai bisogno di ripassare, puoi consultare questa descrizione della notazione A1.

Aggiorniamo il tuo metodo di duplicateAndOrganizeActiveSheet() per spostare anche alcuni dati:

1. In Fogli Google, fai clic sul foglio Sheet_Original per attivarlo.
2. In Apps Script, modifica la funzione duplicateAndOrganizeActiveSheet() in modo che corrisponda a quanto segue:

function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);

  // Move column F to column C.
  var myRange = duplicateSheet.getRange("F2:F");
  myRange.moveTo(duplicateSheet.getRange("C2"));
}

3. Salva ed esegui la funzione.

Quando esegui questa funzione, viene creato, attivato e formattato un foglio duplicato. Inoltre, i contenuti della colonna F vengono spostati nella colonna C:

Il nuovo codice utilizza il metodo getRange(a1Notation) per identificare l'intervallo di dati da spostare. Se inserisci la notazione A1 "F2:F" come parametro del metodo, specifichi la colonna F (esclusa F1). Se l'intervallo specificato esiste, il metodo getRange(a1Notation) restituisce la relativa istanza Range. Per facilità d'uso, il codice memorizza l'istanza nella variabile myRange.

Una volta identificato l'intervallo, il metodo moveTo(target) prende i contenuti di myRange (sia i valori che la formattazione) e li sposta. La destinazione (colonna C) è specificata utilizzando la notazione A1 "C2". Si tratta di una singola cella, non di una colonna. Quando sposti i dati, non devi abbinare le dimensioni agli intervalli di destinazione. Apps Script allinea semplicemente la prima cella di ciascuna.

Nota:quando scrivi i tuoi script, assicurati di testarli a fondo per verificare che i dati finiscano dove previsto. Apps Script sovrascrive i valori dei dati esistenti quando modifica i valori delle celle. Se non fai attenzione, potresti sovrascrivere i dati che intendi conservare. In questa colonna di esempio C era vuota, ma se ci fossero stati dati, il metodo moveTo(target) li avrebbe rimossi.

Ordina intervalli

La classe Range consente di leggere, aggiornare e organizzare gruppi di celle. Ad esempio, puoi ordinare un intervallo di dati utilizzando il metodo Range.sort(sortSpecObj):

1. In Fogli Google, fai clic sul foglio Sheet_Original per attivarlo.
2. In Apps Script, modifica la funzione duplicateAndOrganizeActiveSheet() in modo che corrisponda a quanto segue:

function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);

  // Move column F to column C.
  var myRange = duplicateSheet.getRange("F2:F");
  myRange.moveTo(duplicateSheet.getRange("C2"));

  // Sort all the data using column C (Price information).
  myRange = duplicateSheet.getRange("A3:D55");
  myRange.sort(3);
}

3. Salva ed esegui la funzione.

Ora la funzione, oltre alla formattazione precedente, ordina tutti i dati nella tabella utilizzando le informazioni sui prezzi nella colonna C:

Il nuovo codice utilizza getRange(a1Notation) per specificare un nuovo intervallo che copre A3:D55 (l'intera tabella esclusi i titoli delle colonne). Il codice chiama quindi il metodo sort(sortSpecObj) per ordinare la tabella. In questo caso, il parametro sortSpecObj è il numero della colonna in base alla quale ordinare i dati. Il metodo ordina l'intervallo in modo che i valori della colonna indicata vadano dal più basso al più alto (valori crescenti). Il metodo sort(sortSpecObj) può eseguire requisiti di ordinamento più complessi, ma non sono necessari qui. Puoi visualizzare tutti i diversi modi in cui puoi chiamare gli intervalli di ordinamento nella documentazione di riferimento del metodo.

Congratulazioni, hai completato tutti gli esercizi del codelab. La sezione successiva riepiloga i punti chiave di questo codelab e mostra un'anteprima del codelab successivo in questa playlist.

7. Conclusione

Hai raggiunto la fine di questo codelab. Ora puoi utilizzare e definire le classi e i termini essenziali del servizio Fogli in Apps Script.

Ora puoi passare al codelab successivo.

Hai trovato utile questo codelab?

Sì No

Argomenti trattati

• Come vengono rappresentati fogli di lavoro, fogli e intervalli in Apps Script.
• Alcuni utilizzi di base delle classi SpreadsheetApp, Spreadsheet, Sheet e Range.

Concetti fondamentali:

1. In Apps Script, tutti gli array seguono la convenzione secondo cui l'elenco inizia dall'indice 0. Righe e colonne, tuttavia, sono indicizzate a partire da 1.
2. In Apps Script, il foglio, l'intervallo evidenziato e la cella attualmente aperti sono considerati attivi. Molti metodi Apps Script accedono all'elemento attivo o lo modificano.
3. Le classi Spreadsheet e Sheet condividono diversi metodi specifici per i fogli. I metodi duplicati nella classe Spreadsheet sono solo per comodità dell'utente, in quanto in genere hanno come target il foglio attivo quando vengono chiamati.
4. Ogni foglio di lavoro e foglio ha un ID univoco, che puoi trovare nell'URL del browser. I metodi per fogli di lavoro e fogli utilizzano gli ID. Puoi scoprire di più su ID e URL nella Guida introduttiva a Fogli Google.

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.
• 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.
• 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.
• Foglio**: una singola pagina di un foglio di lavoro (simile alle cartelle di lavoro di Microsoft Excel). 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.

Passaggi successivi

Il prossimo codelab di questa playlist descrive in modo più dettagliato come leggere, scrivere e modificare i dati in un foglio di lavoro.

Trova il prossimo codelab in Utilizzo dei dati.