Concetti fondamentali di Apps Script con Fogli Google 2: fogli di lavoro, fogli e intervalli

1. Introduzione

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

Obiettivi didattici

• Presentazione dei fogli di lavoro, dei fogli e degli intervalli in Apps Script.
• Come accedere, creare e rinominare il foglio di lavoro attivo (aperto) con la classe SpreadsheetApp e Spreadsheet.
• Come modificare il nome di un foglio e l'orientamento della colonna/riga 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 Concetti fondamentali di Apps Script with Fogli Google. Prima di iniziare, assicurati di completare il primo codelab: Macro e funzioni personalizzate.

Che cosa ti serve

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

La sezione successiva introduce le classi principali del servizio Fogli di lavoro.

2. Introduzione al servizio Fogli di lavoro

Sono quattro le nozioni di base del servizio Fogli di lavoro: SpreadsheetApp, Spreadsheet, Sheet e Range. Questa sezione descrive questi corsi e la relativa funzione di utilizzo.

La classe SpreadsheetApp

Prima di esaminare fogli di lavoro, fogli e intervalli, ti consigliamo di esaminare la relativa classe principale: SpreadsheetApp. Molti script iniziano richiamando metodi SpreadsheetApp, poiché possono fornire il punto di accesso iniziale ai file di Fogli Google. Puoi considerare SpreadsheetApp come la classe principale del Servizio fogli di lavoro. Il corso SpreadsheetApp non è stato esaminato in dettaglio qui. Tuttavia, più avanti in questo codelab, troverai sia esempi che esercizi per aiutarti a comprendere la lezione.

Fogli di lavoro, fogli e relativi corsi

Come termine di Fogli, un foglio di lavoro è un file di Fogli Google (archiviato su Google Drive) che contiene i dati organizzati per righe e colonne. Un foglio di lavoro è a volte indicato come "foglio Google", nello stesso modo in cui un documento viene definito "documento Google".

Puoi utilizzare la classe Spreadsheet per accedere ai dati del file di Fogli Google e modificarli. Puoi utilizzare questo corso anche per altre operazioni a livello di file, come l'aggiunta di collaboratori.

Un foglio** rappresenta la singola pagina di un foglio di lavoro, a volte denominata "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 lo spostamento di righe o colonne di dati.

Punto chiave: oltre al 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

Ogni foglio di un foglio di lavoro ha anche un ID univoco. Questo numero ID è casuale, ma puoi vederlo anche nell'URL del foglio di lavoro nel formato:

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

Puoi vedere entrambi gli ID nell'URL di esempio:

Poiché gli ID per fogli di lavoro e fogli di lavoro sono univoci (e i nomi dei fogli di lavoro non lo sono), ove possibile, è meglio utilizzare ID per specificare fogli di lavoro e fogli di lavoro. Ad esempio, in Google Drive puoi avere più fogli di lavoro con lo stesso nome. Utilizzando metodi 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 nella sezione Introduzione a Fogli Google.

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

La classe Range

La maggior parte delle operazioni di manipolazione dei dati (ad esempio, lettura, scrittura o formattazione dei dati delle celle) richiede la definizione delle celle a cui 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 ai numeri delle righe e delle colonne oppure usando 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 è la singola cella evidenziata all'interno del foglio attivo con lo stato attivo del cursore.

Molti metodi per la 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. Generando il foglio di lavoro attivo, puoi applicare le operazioni a tale foglio. Analogamente, metodi come il metodo Sheet.activate() possono cambiare l'oggetto attivo.

Attivare un foglio, un intervallo o una cella è equivalente a fare clic sul foglio, sull'intervallo o sulla cella nell'interfaccia di Fogli Google e quindi spostare il cursore su quella posizione.

Il resto del codelab mostra esempi di script adatti a queste classi e ai relativi metodi.

3. Configura

Prima di continuare, ti servirà un foglio di lavoro con alcuni dati. Ne abbiamo fornito uno per te: fai clic su questo link per copiare la scheda tecnica, quindi su Crea una copia.

Una copia del foglio di lavoro di esempio che puoi utilizzare viene inserita nella cartella di Google Drive e denominata "Copia del 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 crea automaticamente un progetto e un file dello script.

La sezione seguente mostra come utilizzare il corso Spreadsheet per migliorare questo foglio di lavoro.

4. Accedere e modificare i fogli di lavoro

In questa sezione puoi scoprire come utilizzare i corsi SpreadsheetApp e Spreadsheet per accedere ai fogli di lavoro e modificarli. In particolare, gli esercizi spiegano come rinominare un foglio di lavoro e duplicare i fogli all'interno di uno stesso.

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 a automatizzare operazioni più elaborate.

Rinominare il foglio di lavoro attivo

Supponiamo che tu voglia modificare il nome predefinito, "Copia del foglio di lavoro senza titolo", in un titolo che rispecchi meglio lo scopo del tuo 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 lo script, fai clic su Salva .
3. Per rinominare il progetto Apps Script, fai clic su Progetto senza titolo e inserisci "Prezzi di Avocado" come nuovo nome del progetto, quindi 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 ricevi un messaggio "Questa app non è verificata", fai clic su Avanzate, quindi fai clic su Vai ai prezzi di Avocado (non sicuro). Nella schermata successiva, fai clic su Consenti.

Nota: se utilizzi un account gmail.com, potresti visualizzare una finestra di dialogo dell'app non verificata quando utilizzi 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.

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

Diamo un'occhiata al codice inserito. Il metodo getActiveSpreadsheet() restituisce un oggetto che rappresenta il foglio di lavoro attivo, ovvero la copia del foglio di lavoro di allenamento che hai creato. Questo oggetto del foglio di lavoro viene memorizzato nella variabile mySS. Chiamata a rename(newName) il giorno mySS modifica il nome del file del foglio di lavoro in Google Drive in "Prezzi Avocado 2017 a Portland, 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.

Duplicare il foglio attivo

Nel foglio di lavoro corrente 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 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 Foglio_Originale" aggiunta al tuo foglio di lavoro.

Con questa nuova funzione, il metodo duplicateActiveSheet() crea, attiva e restituisce il foglio duplicato nel foglio di lavoro. Il foglio risultante è memorizzato in duplicateSheet, ma il codice non sta ancora facendo nulla con quella variabile.

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

5. Formattare il foglio con la classe Foglio

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

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

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

Modificare il nome del foglio

Cambiare il nome dei fogli è stato semplice: basta rinominare il foglio di lavoro in renameSpreadsheet(). È richiesta una sola chiamata di 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 crei la funzione, viene creato e rinominato un foglio duplicato:

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

Modificare le colonne e le righe di un foglio

Per formattare il foglio, puoi anche utilizzare la classe Sheet. 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 aggiunto utilizza autoResizeColumns(startColumn, numColumns) per ridimensionare le colonne del foglio in modo da consentirne la lettura. Il metodo setFrozenRows(rows) blocca il numero di righe specificato (in questo caso due) che mantiene 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 all'indice 0. Tuttavia, quando lavori con righe e colonne, l'indice inizia da 1. Questa logica si allinea alla formattazione tradizionale dei fogli di lavoro, in cui devi iniziare dalla riga 1 e dalla colonna A in Fogli.

Presta attenzione quando utilizzi metodi relativi a colonne e righe come autoResizeColumns(startColumn, numColumns). Nel nostro esercizio corrente, inserisci 1 (non 0) come parametro per fare riferimento alla colonna A di duplicateSheet.

Nella sezione successiva scoprirai gli intervalli e la manipolazione base dei dati.

6. Riorganizzare i dati con la classe Intervallo

La classe Range e i suoi metodi forniscono la maggior parte delle opzioni di manipolazione e formattazione dei dati nel Servizio fogli di lavoro.

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

Sposta intervalli

Puoi attivare e spostare intervalli di dati con i metodi del corso e la notazione A1, un modo breve per identificare insiemi specifici di celle all'interno dei fogli di lavoro. Se vuoi conoscerti nuovamente, puoi consultare questa descrizione della notazione A1.

Aggiorna il tuo metodo 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. Inserendo la notazione A1 "F2:F" come parametro del metodo, devi specificare la colonna F (esclusa F1). Se l'intervallo specificato esiste, il metodo getRange(a1Notation) restituisce l'istanza Range. Il codice archivia l'istanza nella variabile myRange per facilitare l'utilizzo.

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

Nota: quando scrivi i tuoi script, assicurati di testarli accuratamente per assicurarti che i dati finiscano dove vuoi. 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 questo esempio, la colonna C era vuota, ma in presenza di dati il metodo moveTo(target) l'avrebbe rimossa.

Ordina intervalli

La classe Range ti 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 della tabella utilizzando le informazioni sul prezzo nella colonna C:

Il nuovo codice utilizza getRange(a1Notation) per specificare un nuovo intervallo che copre A3:D55 (l'intera tabella escluse le intestazioni di colonna). Il codice chiama quindi il metodo sort(sortSpecObj) per ordinare la tabella. In questo caso, il parametro sortSpecObj è il numero di colonna da ordinare. Il metodo ordina l'intervallo in modo che i valori riportati nelle colonne vanno dal più basso al più alto (valori crescenti). Il metodo sort(sortSpecObj) può eseguire requisiti di ordinamento più complessi, ma non è necessario utilizzarli. Puoi visualizzare tutti i diversi modi in cui puoi chiamare gli intervalli di ordinamento nella documentazione di riferimento sui metodi.

Congratulazioni, hai completato tutti gli esercizi nel codelab. La sezione successiva esamina i punti chiave di questo codelab e mostra in anteprima il 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 di foglio di lavoro in Apps Script.

Puoi passare al codelab successivo.

Hai trovato utile questo codelab?

Sì No

Cosa abbiamo trattato

• Presentazione dei fogli di lavoro, dei fogli e degli intervalli in Apps Script.
• Alcuni utilizzi di base delle classi SpreadsheetApp, Spreadsheet, Sheet e Range

Concetti chiave:

1. In Apps Script, tutti gli array seguono la convenzione secondo cui l'elenco inizia all'indice 0. Tuttavia, le righe e le colonne vengono indicizzate a partire da 1.
2. In Apps Script, la scheda attualmente aperta, con l'intervallo evidenziato, la cella è considerata attiva. Molti metodi Apps Script accedono o modificano l'elemento attivo.
3. I corsi Spreadsheet e Sheet condividono diversi metodi specifici di un foglio. I metodi duplicati nella classe Spreadsheet sono solo per comodità degli utenti, poiché in genere scelgono 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 del foglio di lavoro e della classe del foglio utilizzano gli ID. Puoi trovare ulteriori informazioni su ID e URL in Introduzione a Fogli Google.

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.
• 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.
• Intervallo.** Un gruppo di una o più celle adiacenti in 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.
• Foglio**: una singola pagina di un foglio di lavoro (simile ai fogli di lavoro di Microsoft Excel). 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** ti 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.

Passaggi successivi

Il codelab successivo in questa playlist fornisce informazioni più dettagliate su come leggere, scrivere e modificare i dati all'interno di un foglio di lavoro.

Trova il prossimo codelab su Utilizzo dei dati.