Grundlagen von Apps Script mit Google Sheets – Teil 4: Datenformatierung

1. Einführung

Willkommen zum vierten Teil der Codelab-Playlist „Grundlagen von Apps Script mit Google Sheets“.

In diesem Codelab erfahren Sie, wie Sie Ihre Tabellendaten in Apps Script formatieren und Funktionen schreiben, um organisierte Tabellen mit formatierten Daten zu erstellen, die aus einer öffentlichen API abgerufen werden.

Lerninhalte

• So wenden Sie verschiedene Formatierungsvorgänge in Google Sheets mit Apps Script an.
• Wie Sie eine Liste von JSON-Objekten und ihren Attributen mit Apps Script in ein organisiertes Tabellenblatt mit Daten umwandeln.

Hinweis

Dies ist das vierte Codelab in der Playlist „Grundlagen von Apps Script mit Google Tabellen“. Bevor Sie mit diesem Codelab beginnen, sollten Sie die vorherigen Codelabs durcharbeiten:

1. Makros und benutzerdefinierte Funktionen
2. Tabellen, Tabellenblätter und Bereiche
3. Mit Daten arbeiten

Voraussetzungen

• Grundkenntnisse der grundlegenden Apps Script-Themen, die in den vorherigen Codelabs dieser Playlist behandelt werden.
• Grundlegende Kenntnisse des Apps Script-Editors
• Grundlegende Kenntnisse von Google Sheets
• Kann A1-Notation in Google Sheets lesen
• Grundkenntnisse in JavaScript und der String-Klasse

2. Einrichten

Bevor Sie fortfahren, benötigen Sie eine Tabelle mit Daten. Wie bisher haben wir ein Datenblatt bereitgestellt, das Sie für diese Übungen kopieren können. Und so gehts:

1. Klicken Sie auf diesen Link, um das Datenblatt zu kopieren, und klicken Sie dann auf Kopie erstellen. Die neue Tabelle wird in Ihrem Google Drive-Ordner gespeichert und erhält den Namen „Kopie von Datenformatierung“.
2. Klicken Sie auf den Tabellentitel und ändern Sie ihn von „Kopie von Datenformatierung“ in „Datenformatierung“. Ihr Tabellenblatt sollte so aussehen und einige grundlegende Informationen zu den ersten drei Star Wars-Filmen enthalten:

3. Wählen Sie Erweiterungen> Apps Script aus, um den Skripteditor zu öffnen.
4. Klicken Sie auf den Titel des Apps Script-Projekts und ändern Sie ihn von „Unbenanntes Projekt“ in „Datenformatierung“. Klicken Sie auf Umbenennen, um die Änderung des Titels zu speichern.

Mit dieser Tabelle und diesem Projekt können Sie mit dem Codelab beginnen. Im nächsten Abschnitt erfahren Sie mehr über die grundlegende Formatierung in Apps Script.

3. Benutzerdefiniertes Menü erstellen

Sie können in Apps Script verschiedene grundlegende Formatierungsmethoden auf Ihre Tabellen anwenden. In den folgenden Übungen werden einige Möglichkeiten zum Formatieren von Daten gezeigt. Damit Sie Ihre Formatierungsaktionen besser steuern können, erstellen wir ein benutzerdefiniertes Menü mit den benötigten Elementen. Die Vorgehensweise zum Erstellen benutzerdefinierter Menüs wurde im Codelab Mit Daten arbeiten beschrieben. Wir fassen sie hier noch einmal zusammen.

Implementierung

Erstellen wir ein benutzerdefiniertes Menü.

1. Ersetzen Sie im Apps Script-Editor den Code in Ihrem Skriptprojekt durch Folgendes:

/**  * 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. Speichern Sie Ihr Skriptprojekt.
3. Wählen Sie im Skripteditor onOpen aus der Funktionsliste aus und klicken Sie auf Ausführen. Dadurch wird onOpen() ausgeführt, um das Tabellenblattmenü neu zu erstellen. Sie müssen das Tabellenblatt also nicht neu laden.

Code Review

Sehen wir uns diesen Code an, um zu verstehen, wie er funktioniert. In onOpen() wird in der ersten Zeile mit der Methode getUi() ein Ui-Objekt abgerufen, das die Benutzeroberfläche der aktiven Tabelle darstellt, an die dieses Skript gebunden ist.

In den nächsten Zeilen wird ein Menü (Quick formats) erstellt, dem Menü werden Menüpunkte (Format row header, Format column header und Format dataset) hinzugefügt und das Menü wird dann der Tabellenkalkulationsoberfläche hinzugefügt. Dazu werden die Methoden createMenu(caption), addItem(caption, functionName) und addToUi() verwendet.

Mit der Methode addItem(caption, functionName) wird eine Verbindung zwischen dem Label des Menüelements und einer Apps Script-Funktion hergestellt, die ausgeführt wird, wenn das Menüelement ausgewählt wird. Wenn Sie beispielsweise das Menüelement Format row header auswählen, versucht Google Tabellen, die Funktion formatRowHeader() auszuführen, die noch nicht vorhanden ist.

Ergebnisse

Klicken Sie in der Tabelle auf das Dreipunkt-Menü Quick formats, um die neuen Menüpunkte aufzurufen:

Wenn Sie auf diese Elemente klicken, wird ein Fehler ausgegeben, da Sie die entsprechenden Funktionen noch nicht implementiert haben. Das holen wir jetzt nach.

4. Kopfzeile formatieren

Datensätze in Tabellen enthalten oft Kopfzeilen, um die Daten in jeder Spalte zu identifizieren. Es empfiehlt sich, Überschriftenzeilen so zu formatieren, dass sie sich optisch vom Rest der Daten in der Tabelle abheben.

Im ersten Codelab haben Sie ein Makro für Ihre Kopfzeile erstellt und den Code angepasst. Hier formatieren Sie eine Kopfzeile von Grund auf mit Apps Script. In der Kopfzeile, die Sie erstellen, wird der Überschriftentext fett formatiert, der Hintergrund dunkelblaugrün und der Text weiß gefärbt und es werden durchgezogene Rahmenlinien hinzugefügt.

Implementierung

Zur Implementierung des Formatierungsvorgangs verwenden Sie dieselben Spreadsheet-Dienst-Methoden wie zuvor, aber jetzt auch einige der Formatierungsmethoden des Dienstes. Und so gehts:

1. Fügen Sie im Apps Script-Editor die folgende Funktion am Ende Ihres Skriptprojekts ein:

/**  * 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. Speichern Sie Ihr Skriptprojekt.

Code Review

Wie bei vielen Formatierungsaufgaben ist der Apps Script-Code für die Implementierung unkompliziert. In den ersten beiden Zeilen werden Methoden verwendet, die Sie bereits kennen, um einen Verweis auf das aktuelle aktive Tabellenblatt (sheet) und die oberste Zeile des Tabellenblatts (headerRange)) abzurufen. Mit der Methode Sheet.getRange(row, column, numRows, numColumns) wird die oberste Zeile angegeben, wobei nur die Spalten mit Daten berücksichtigt werden. Die Methode Sheet.getLastColumn() gibt den Spaltenindex der letzten Spalte zurück, die Daten im Tabellenblatt enthält. In unserem Beispiel ist das Spalte E (url).

Im restlichen Code werden einfach verschiedene Range-Methoden aufgerufen, um Formatierungsoptionen auf alle Zellen in headerRange anzuwenden. Damit der Code gut lesbar bleibt, verwenden wir Method Chaining, um jede Formatierungsmethode nacheinander aufzurufen:

• Mit Range.setFontWeight(fontWeight) wird die Schriftstärke auf „fett“ festgelegt.
• Mit Range.setFontColor(color) wird die Schriftfarbe auf Weiß festgelegt.
• Mit Range.setBackground(color) wird die Hintergrundfarbe auf ein dunkles Blaugrün festgelegt.
• Mit setBorder(top, left, bottom, right, vertical, horizontal, color, style) wird ein durchgezogener schwarzer Rahmen um die Zellen des Bereichs eingefügt.

Die letzte Methode hat mehrere Parameter. Sehen wir uns an, was jeder Parameter bewirkt. Die ersten vier Parameter (alle auf true gesetzt) weisen Apps Script an, den Rahmen über, unter, links und rechts vom Bereich hinzuzufügen. Mit dem fünften und sechsten Parameter (null und null) wird Apps Script angewiesen, keine Grenzlinien im ausgewählten Bereich zu ändern. Der siebte Parameter (null) gibt an, dass die Farbe des Rahmens standardmäßig auf Schwarz festgelegt werden soll. Mit dem letzten Parameter wird der Typ des zu verwendenden Rahmenstils angegeben. Er wird aus den Optionen von SpreadsheetApp.BorderStyle übernommen.

Ergebnisse

So können Sie Ihre Formatierungsfunktion in Aktion sehen:

1. Falls noch nicht geschehen, speichern Sie Ihr Skriptprojekt im Apps Script-Editor.
2. Klicken Sie auf das Menüelement Schnellformatierungen > Zeilenüberschrift formatieren.

Die Ergebnisse sollten so aussehen:

Sie haben jetzt eine Formatierungsaufgabe automatisiert. Im nächsten Abschnitt wird dieselbe Technik verwendet, um einen anderen Formatstil für Spaltenüberschriften zu erstellen.

5. Spaltenüberschrift formatieren

Wenn Sie einen personalisierten Zeilenheader erstellen können, können Sie auch einen Spaltenheader erstellen. Spaltenüberschriften erhöhen die Lesbarkeit bestimmter Datasets. Die Spalte titles in dieser Tabelle kann beispielsweise mit den folgenden Formatierungsoptionen optimiert werden:

• Text fett formatieren
• Text kursivieren
• Zellenrahmen hinzufügen
• Hyperlinks einfügen, indem der Inhalt der Spalte url verwendet wird. Nachdem Sie diese Hyperlinks hinzugefügt haben, können Sie die Spalte url entfernen, um die Tabelle übersichtlicher zu gestalten.

Als Nächstes implementieren Sie eine formatColumnHeader()-Funktion, um diese Änderungen auf die erste Spalte im Tabellenblatt anzuwenden. Um den Code etwas lesbarer zu machen, implementieren Sie außerdem zwei Hilfsfunktionen.

Implementierung

Wie zuvor müssen Sie eine Funktion hinzufügen, um die Formatierung der Spaltenüberschriften zu automatisieren. Und so gehts:

1. Fügen Sie im Apps Script-Editor die folgende formatColumnHeader()-Funktion am Ende Ihres Skriptprojekts ein:

/**  * 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. Fügen Sie am Ende Ihres Skriptprojekts nach der Funktion formatColumnHeader() die folgenden Hilfsfunktionen hinzu:

/**  * 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. Speichern Sie Ihr Skriptprojekt.

Code Review

Sehen wir uns den Code in jeder dieser drei Funktionen separat an:

formatColumnHeader()

Wie Sie wahrscheinlich schon erwartet haben, werden in den ersten Zeilen dieser Funktion Variablen festgelegt, die auf das Tabellenblatt und den Bereich verweisen, die uns interessieren:

• Das aktive Tabellenblatt wird in sheet gespeichert.
• Die Anzahl der Zeilen in der Spaltenüberschrift wird berechnet und in numRows gespeichert. Hier wird eins subtrahiert, damit die Zeilenanzahl die Spaltenüberschrift nicht enthält: title.
• Der Bereich, der die Spaltenüberschrift umfasst, wird in columnHeaderRange gespeichert.

Der Code wendet dann die Rahmen und die Fettschrift auf den Spaltenüberschriftenbereich an, genau wie in formatRowHeader(). Hier wird Range.setFontStyle(fontStyle) auch verwendet, um den Text kursiv zu formatieren.

Das Hinzufügen der Hyperlinks zur Kopfspalte ist komplexer, daher ruft formatColumnHeader() hyperlinkColumnHeaders_(headerRange, numRows) auf, um die Aufgabe zu erledigen. So bleibt der Code übersichtlich und lesbar.

hyperlinkColumnHeaders_(headerRange, numRows)

Diese Hilfsfunktion ermittelt zuerst die Spaltenindexe des Headers (angenommen, Index 1) und der Spalte url. Dazu wird columnIndexOf_('url') aufgerufen, um den Index der URL-Spalte abzurufen. Wenn keine url-Spalte gefunden wird, wird die Methode beendet, ohne dass Daten geändert werden.

Die Funktion ruft einen neuen Bereich (urlRange) ab, der die URLs für die Zeilen der Kopfspalte enthält. Dies geschieht mit der Methode Range.offset(rowOffset, columnOffset), die dafür sorgt, dass die beiden Bereiche dieselbe Größe haben. Die Werte in den Spalten headerColumn und url werden dann abgerufen (headerValues und urlValues).

Die Funktion durchläuft dann alle Zellen mit Spaltenüberschriften und ersetzt sie durch eine =HYPERLINK()-Sheets-Formel, die mit dem Inhalt der Spaltenüberschrift und der Spalte url erstellt wird. Die geänderten Headerwerte werden dann mit Range.setValues(values) in das Tabellenblatt eingefügt.

Schließlich wird Sheet.deleteColumn(columnPosition) aufgerufen, um die Spalte url zu entfernen und so das Tabellenblatt übersichtlich zu halten und redundante Informationen zu vermeiden.

columnIndexOf_(colName)

Diese Hilfsfunktion ist eine einfache Dienstprogrammfunktion, die in der ersten Zeile des Blatts nach einem bestimmten Namen sucht. In den ersten drei Zeilen werden Methoden verwendet, die Sie bereits kennen, um eine Liste mit Spaltenüberschriften aus Zeile 1 der Tabelle abzurufen. Diese Namen werden in der Variablen „columnNames“ gespeichert.

Die Funktion prüft dann jeden Namen der Reihe nach. Wenn eine Übereinstimmung mit dem gesuchten Namen gefunden wird, wird die Suche beendet und der Index der Spalte zurückgegeben. Wenn das Ende der Namensliste erreicht wird, ohne dass der Name gefunden wird, wird -1 zurückgegeben, um anzuzeigen, dass der Name nicht gefunden wurde.

Ergebnisse

So können Sie Ihre Formatierungsfunktion in Aktion sehen:

1. Falls noch nicht geschehen, speichern Sie Ihr Skriptprojekt im Apps Script-Editor.
2. Klicken Sie auf das Menüelement Schnellformatierungen > Spaltenüberschrift formatieren.

Die Ergebnisse sollten so aussehen:

Sie haben nun eine weitere Formatierungsaufgabe automatisiert. Nachdem Sie die Spalten- und Zeilenüberschriften formatiert haben, erfahren Sie im nächsten Abschnitt, wie Sie die Daten formatieren.

6. Dataset formatieren

Nachdem Sie nun Header haben, erstellen wir eine Funktion, die die restlichen Daten in Ihrem Tabellenblatt formatiert. Wir verwenden die folgenden Formatierungsoptionen:

• Abwechselnde Hintergrundfarben für Zeilen (auch als Streifenmuster bezeichnet)
• Datumsformate ändern
• Rahmenlinien anwenden
• Alle Spalten und Zeilen automatisch anpassen

Als Nächstes erstellen Sie eine Funktion formatDataset() und eine zusätzliche Hilfsmethode, um diese Formate auf Ihre Tabellendaten anzuwenden.

Implementierung

Fügen Sie wie zuvor eine Funktion hinzu, um die Datenformatierung zu automatisieren. Und so gehts:

1. Fügen Sie im Apps Script-Editor die folgende formatDataset()-Funktion am Ende Ihres Skriptprojekts ein:

/**  * 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. Fügen Sie am Ende Ihres Skriptprojekts nach der Funktion formatDataset() die folgende Hilfsfunktion hinzu:

/**   * 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. Speichern Sie Ihr Skriptprojekt.

Code Review

Sehen wir uns den Code in den beiden Funktionen separat an:

formatDataset()

Diese Funktion folgt einem ähnlichen Muster wie die vorherigen Formatierungsfunktionen, die Sie bereits implementiert haben. Zuerst werden Variablen abgerufen, die Verweise auf das aktive Tabellenblatt (sheet) und den Datenbereich (fullDataRange) enthalten.

Zweitens wird mit der Methode Range.offset(rowOffset, columnOffset, numRows, numColumns) ein Bereich (noHeadersRange) erstellt, der alle Daten im Tabellenblatt umfasst, mit Ausnahme der Spalten- und Zeilenüberschriften. Mit dem Code wird dann geprüft, ob für diesen neuen Bereich bereits ein Banding vorhanden ist (mit Range.getBandings()). Das ist erforderlich, da Apps Script einen Fehler ausgibt, wenn Sie versuchen, ein neues Banding anzuwenden, obwohl bereits eines vorhanden ist. Wenn kein Banding vorhanden ist, fügt die Funktion ein hellgraues Banding mit Range.applyRowBanding(bandingTheme, showHeader, showFooter) hinzu. Andernfalls wird die Funktion fortgesetzt.

Im nächsten Schritt wird die Hilfsfunktion formatDates_(colIndex) aufgerufen, um die Datumsangaben in der Spalte mit dem Label „release_date“ zu formatieren (siehe unten). Die Spalte wird mit der Hilfsfunktion columnIndexOf_(colName) angegeben, die Sie zuvor implementiert haben.

Schließlich wird die Formatierung abgeschlossen, indem eine weitere Umrandung (wie zuvor) hinzugefügt wird. Außerdem werden alle Spalten und Zeilen automatisch an die darin enthaltenen Daten angepasst. Dazu werden die Methoden Sheet.autoResizeColumns(columnPosition) und Sheet.autoResizeColumns(columnPosition) verwendet.

formatDates_(colIndex)

Diese Hilfsfunktion wendet mit dem angegebenen Spaltenindex ein bestimmtes Datumsformat auf eine Spalte an. Konkret werden Datumsangaben als „Monat Tag, Jahr (Wochentag)“ formatiert.

Zuerst wird geprüft, ob der angegebene Spaltenindex gültig ist (d. h. 0 oder größer). Andernfalls wird die Funktion beendet, ohne dass etwas passiert. Diese Prüfung verhindert Fehler, die z. B. auftreten können, wenn das Tabellenblatt keine Spalte „release_date“ enthält.

Sobald der Spaltenindex validiert wurde, ruft die Funktion den Bereich ab, der diese Spalte umfasst (ohne die Kopfzeile), und wendet die Formatierung mit Range.setNumberFormat(numberFormat) an.

Ergebnisse

So können Sie Ihre Formatierungsfunktion in Aktion sehen:

1. Falls noch nicht geschehen, speichern Sie Ihr Skriptprojekt im Apps Script-Editor.
2. Klicken Sie auf das Menüelement Schnellformatierungen > Dataset formatieren.

Die Ergebnisse sollten so aussehen:

Sie haben eine weitere Formatierungsaufgabe automatisiert. Nachdem Sie nun über diese Formatierungsbefehle verfügen, fügen wir weitere Daten hinzu, auf die sie angewendet werden können.

7. API-Daten abrufen und formatieren

Bisher haben Sie in diesem Codelab gesehen, wie Sie Apps Script als alternative Methode zum Formatieren Ihrer Tabelle verwenden können. Als Nächstes schreiben Sie Code, mit dem Daten aus einer öffentlichen API abgerufen, in Ihre Tabelle eingefügt und so formatiert werden, dass sie lesbar sind.

Im letzten Codelab haben Sie gelernt, wie Sie Daten aus einer API abrufen. Hier verwenden Sie dieselben Techniken. In dieser Übung verwenden wir die öffentliche Star Wars API (SWAPI), um Ihre Tabelle zu füllen. Sie verwenden die API, um Informationen zu den Hauptfiguren der ersten drei Star Wars-Filme abzurufen.

Ihr Code ruft die API auf, um eine große Menge an JSON-Daten abzurufen, parst die Antwort, platziert die Daten in einem neuen Tabellenblatt und formatiert das Tabellenblatt.

Implementierung

In diesem Abschnitt fügen Sie einige zusätzliche Menüpunkte hinzu. Jeder Menüpunkt ruft ein Wrapper-Script auf, das elementspezifische Variablen an die Hauptfunktion (createResourceSheet_()) übergibt. Sie implementieren diese Funktion und drei zusätzliche Hilfsfunktionen. Wie bisher helfen die Hilfsfunktionen dabei, logisch abgegrenzte Teile der Aufgabe zu isolieren und den Code lesbar zu halten.

Führen Sie die folgenden Schritte aus:

1. Aktualisieren Sie im Apps Script-Editor die onOpen()-Funktion in Ihrem Skriptprojekt, sodass sie der folgenden entspricht:

/**  * 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. Speichern Sie Ihr Skriptprojekt.
3. Wählen Sie im Skripteditor onOpen aus der Funktionsliste aus und klicken Sie auf Ausführen. Dadurch wird onOpen() ausgeführt, um das Tabellenmenü mit den neuen Optionen neu zu erstellen, die Sie hinzugefügt haben.
4. Klicken Sie neben Dateien auf „Datei hinzufügen“  > Script, um eine Apps Script-Datei zu erstellen.
5. Geben Sie „API“ als Namen des neuen Skripts ein und drücken Sie die Eingabetaste. (Apps Script fügt dem Skriptdateinamen automatisch die Erweiterung .gs hinzu.)
6. Ersetzen Sie den Code in der neuen Datei API.gs durch Folgendes:

/**  * 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. Fügen Sie am Ende der Skriptprojektdatei API.gs die folgenden Hilfsfunktionen hinzu:

/**   * 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. Speichern Sie Ihr Skriptprojekt.

Code Review

Sie haben gerade viel Code hinzugefügt. Sehen wir uns die einzelnen Funktionen an, um zu verstehen, wie sie funktionieren:

onOpen()

Hier haben Sie Ihrem Quick formats-Menü einige Menüpunkte hinzugefügt. Sie haben eine Trennlinie festgelegt und dann mit der Methode Menu.addSubMenu(menu) eine verschachtelte Menüstruktur mit drei neuen Elementen erstellt. Die neuen Elemente werden mit der Methode Menu.addItem(caption, functionName) hinzugefügt.

Wrapper-Funktionen

Die hinzugefügten Menüpunkte haben alle eine ähnliche Funktion: Sie versuchen, ein Tabellenblatt mit Daten zu erstellen, die aus der SWAPI abgerufen werden. Der einzige Unterschied besteht darin, dass sie sich jeweils auf einen anderen Film beziehen.

Es wäre praktisch, eine einzelne Funktion zum Erstellen des Sheets zu schreiben und einen Parameter zu verwenden, um festzulegen, welcher Film verwendet werden soll. Mit der Methode Menu.addItem(caption, functionName) können Sie jedoch keine Parameter übergeben, wenn sie über das Menü aufgerufen wird. Wie vermeiden Sie es also, denselben Code dreimal zu schreiben?

Die richtige Antwort ist Wrapper-Funktionen. Das sind einfache Funktionen, die Sie aufrufen können und die sofort eine andere Funktion mit bestimmten Parametern aufrufen.

Im Code werden drei Wrapper-Funktionen verwendet: createPeopleSheetIV(), createPeopleSheetV() und createPeopleSheetVI(). Die Menüpunkte sind mit diesen Funktionen verknüpft. Wenn ein Menüpunkt angeklickt wird, wird die Wrapper-Funktion ausgeführt und ruft sofort die Hauptfunktion createResourceSheet_(resourceType, idNumber, episodeNumber) zum Erstellen des Tabellenblatts auf. Dabei werden die für den Menüpunkt geeigneten Parameter übergeben. In diesem Fall wird die Tabellenblatt-Builder-Funktion aufgefordert, ein Tabellenblatt mit Daten zu den Hauptfiguren aus einem der Star Wars-Filme zu erstellen.

createResourceSheet_(resourceType, idNumber, episodeNumber)

Dies ist die Hauptfunktion zum Erstellen von Tabellenblättern für diese Übung. Mithilfe einiger Hilfsfunktionen werden die API-Daten abgerufen, geparst, ein Tabellenblatt erstellt, die API-Daten in das Tabellenblatt geschrieben und das Tabellenblatt dann mit den Funktionen formatiert, die Sie in den vorherigen Abschnitten erstellt haben. Sehen wir uns die Details an:

Zuerst wird mit fetchApiResourceObject_(url) eine Anfrage an die API gesendet, um grundlegende Filminformationen abzurufen. Die API-Antwort enthält eine Sammlung von URLs, mit denen der Code weitere Informationen zu bestimmten Personen (hier als Ressourcen bezeichnet) aus den Filmen abrufen kann. Der Code erfasst alles im Array resourceUrls.

Als Nächstes wird der Code wiederholt fetchApiResourceObject_(url) verwendet, um die API für jede Ressourcen-URL in resourceUrls aufzurufen. Die Ergebnisse werden im Array resourceDataList gespeichert. Jedes Element dieses Arrays ist ein Objekt, das eine andere Figur aus dem Film beschreibt.

Die Ressourcendatenobjekte haben mehrere gemeinsame Schlüssel, die Informationen zu diesem Zeichen zuordnen. Der Schlüssel „name“ entspricht beispielsweise dem Namen der Figur im Film. Wir gehen davon aus, dass die Schlüssel für jedes Ressourcendatenobjekt identisch sind, da sie gemeinsame Objektstrukturen verwenden sollen. Die Liste der Schlüssel wird später benötigt. Daher wird sie mit der JavaScript-Methode Object.keys() in resourceObjectKeys gespeichert.

Als Nächstes ruft die Builder-Funktion die Hilfsfunktion createNewSheet_(name) auf, um das Tabellenblatt zu erstellen, in dem die neuen Daten platziert werden. Durch den Aufruf dieser Hilfsfunktion wird auch das neue Tabellenblatt aktiviert.

Nachdem das Tabellenblatt erstellt wurde, wird die Hilfsfunktion fillSheetWithData_(resourceSheet, objectKeys, resourceDataList) aufgerufen, um alle API-Daten in das Tabellenblatt einzufügen.

Schließlich werden alle Formatierungsfunktionen aufgerufen, die Sie zuvor erstellt haben, um dieselben Formatierungsregeln auf die neuen Daten anzuwenden. Da die neue Tabelle die aktive ist, kann der Code diese Funktionen ohne Änderungen wiederverwenden.

fetchApiResourceObject_(url)

Diese Hilfsfunktion ähnelt der Hilfsfunktion fetchBookData_(ISBN), die im vorherigen Codelab Mit Daten arbeiten verwendet wurde. Die angegebene URL wird verwendet und mit der Methode UrlFetchApp.fetch(url, params) wird eine Antwort abgerufen. Die Antwort wird dann mit den Methoden HTTPResponse.getContextText() und JSON.parse(json) in ein JSON-Objekt geparst. Das resultierende JSON-Objekt wird dann zurückgegeben.

createNewSheet_(name)

Diese Hilfsfunktion ist recht einfach. Zuerst wird geprüft, ob in der Tabelle ein Tabellenblatt mit dem angegebenen Namen vorhanden ist. Wenn dies der Fall ist, wird das Tabellenblatt aktiviert und zurückgegeben.

Wenn das Tabellenblatt nicht vorhanden ist, wird es mit Spreadsheet.insertSheet(sheetName) erstellt, aktiviert und zurückgegeben.

fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)

Diese Hilfsfunktion ist dafür verantwortlich, das neue Tabellenblatt mit API-Daten zu füllen. Als Parameter werden das neue Tabellenblatt, die Liste der Objektschlüssel und die Liste der API-Ressourcenobjekte übergeben. Jeder Objektschlüssel steht für eine Spalte im neuen Tabellenblatt und jedes Ressourcenobjekt für eine Zeile.

Zuerst wird die Anzahl der Zeilen und Spalten berechnet, die zum Darstellen der neuen API-Daten erforderlich sind. Dies ist die Größe der Ressourcen- bzw. Schlüsselliste. Die Funktion definiert dann einen Ausgabebereich (resourceRange), in dem die Daten platziert werden, und fügt eine zusätzliche Zeile für die Spaltenüberschriften hinzu. Die Variable resourceValues enthält ein 2D-Werte-Array, das aus resourceRange extrahiert wurde.

Die Funktion durchläuft dann jeden Objektschlüssel in der Liste objectKeys. Der Schlüssel wird als Spaltenüberschrift festgelegt und dann wird in einer zweiten Schleife jedes Ressourcenobjekt durchlaufen. Für jedes (Zeile, Spalte)-Paar werden die entsprechenden API-Informationen in das resourceValues[row][column]-Element kopiert.

Nachdem resourceValues ausgefüllt wurde, wird das Zieltabellenblatt mit Sheet.clear() gelöscht, falls es Daten aus vorherigen Menüelementklicks enthält. Schließlich werden die neuen Werte in das Tabellenblatt geschrieben.

Ergebnisse

So können Sie die Ergebnisse Ihrer Arbeit sehen:

1. Falls noch nicht geschehen, speichern Sie Ihr Skriptprojekt im Apps Script-Editor.
2. Klicken Sie auf das Menüelement Schnelle Formatierungen > Charakterblatt erstellen > Episode IV.

Die Ergebnisse sollten so aussehen:

Sie haben nun Code geschrieben, um Daten in Google Tabellen zu importieren und automatisch zu formatieren.

8. Fazit

Herzlichen Glückwunsch zum Abschluss dieses Codelabs. Sie haben einige der Formatierungsoptionen für Google Tabellen gesehen, die Sie in Ihre Apps Script-Projekte einbinden können, und eine beeindruckende Anwendung erstellt, mit der große API-Datasets importiert und formatiert werden.

Das haben Sie gelernt

• So wenden Sie verschiedene Formatierungsvorgänge in Google Sheets mit Apps Script an.
• So erstellen Sie Untermenüs mit der Funktion onOpen().
• So formatieren Sie eine abgerufene Liste von JSON-Objekten mit Apps Script in ein neues Datenblatt.

Nächste Schritte

Im nächsten Codelab in dieser Playlist erfahren Sie, wie Sie mit Apps Script Daten in einem Diagramm visualisieren und Diagramme in Google-Präsentationen exportieren.

Das nächste Codelab finden Sie unter Daten in Google Präsentationen in Diagrammen darstellen und präsentieren.