Grundlagen von Apps Script mit Google Tabellen 4: Datenformatierung

1. Einführung

Willkommen zum vierten Teil der Codelab-Playlist zu den Grundlagen von Apps Script.

Wenn Sie dieses Codelab abschließen, können Sie Ihre Tabellendaten in Apps Script formatieren und Funktionen zum Erstellen organisierter Tabellen mit formatierten Daten erstellen, die aus einer öffentlichen API abgerufen werden.

Lerninhalte

• Sie können verschiedene Formatierungen für Google Tabellen in Apps Script anwenden.
• Liste von JSON-Objekten und ihren Attributen in einer strukturierten Datentabelle mit Apps Script umwandeln

Hinweis

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

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

Voraussetzungen

• Ein Verständnis der grundlegenden Apps Script-Themen, die in den vorherigen Codelabs dieser Playlist behandelt wurden.
• Grundkenntnisse des Apps Script-Editors
• Grundkenntnisse in Google Tabellen
• Lesefunktion für Google Tabellen A1 Notation
• Grundkenntnisse in JavaScript und der String-Klasse

2. Einrichten

Bevor Sie fortfahren, benötigen Sie eine Tabelle mit einigen Daten. Wie zuvor haben wir ein Datenblatt zur Verfügung gestellt, das Sie für diese Übungen kopieren können. Gehen Sie folgendermaßen vor:

1. Klicken Sie auf diesen Link, um das Datenblatt zu kopieren, und dann auf Kopie erstellen. Die neue Tabelle wird in Ihren Google Drive-Ordner verschoben und mit dem Namen „Kopie der Datenformatierung“ versehen.
2. Klicken Sie auf den Titel der Tabelle und ändern Sie sie von „Kopie der Datenformatierung“ zu „Datenformatierung“. Ihre Tabelle sollte so aussehen, einschließlich einiger grundlegender Informationen zu den ersten drei Star Wars-Filmen:

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 die Bezeichnung von „Unbenanntes Projekt“ in „"Data Formatierung“. Klicke auf Umbenennen, um die Titeländerung zu speichern.

Mit dieser Tabelle und dem Projekt können Sie das Codelab starten. Gehen Sie zum nächsten Abschnitt, um mehr über die grundlegende Formatierung in Apps Script zu erfahren.

3. Benutzerdefiniertes Menü erstellen

In Apps Script können Sie mehrere grundlegende Formatierungsmethoden auf Ihre Tabellen anwenden. In den folgenden Übungen werden einige Möglichkeiten zur Formatierung von Daten veranschaulicht. Zum Steuern der Formatierung können Sie ein benutzerdefiniertes Menü mit den Elementen erstellen, die Sie benötigen. Der Vorgang zum Erstellen benutzerdefinierter Menüs wurde im Codelab Mit Daten arbeiten beschrieben. Hier noch einmal zusammengefasst.

Implementierung

Ein benutzerdefiniertes Menü erstellen.

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 das Skriptprojekt.
3. Wählen Sie im Skripteditor in der Liste der Funktionen onOpen aus und klicken Sie auf Ausführen. Dadurch wird onOpen() ausgeführt, um das Tabellenmenü neu zu erstellen, sodass Sie die Tabelle nicht aktualisieren müssen.

Codeüberprüfung

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

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

Die Methode addItem(caption, functionName) stellt eine Verbindung zwischen dem Label des Menüelements und einer Apps Script-Funktion her, die ausgeführt wird, wenn der Menüpunkt ausgewählt ist. Wenn Sie z. B. den Menüpunkt Format row header auswählen, wird in Google Tabellen versucht, 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 zurückgegeben, da Sie die entsprechenden Funktionen nicht implementiert haben. Lassen Sie es sich also als Nächstes tun.

4. Kopfzeile formatieren

Datensätze in Tabellen haben häufig Überschriften, mit denen die Daten in den einzelnen Spalten identifiziert werden. Es empfiehlt sich, Kopfzeilen zu formatieren, um sie visuell vom Rest der Daten in der Tabelle zu trennen.

Im ersten Codelab haben Sie ein Makro für den Header erstellt und den Code angepasst. Hier formatieren Sie eine Kopfzeile von Grund auf mithilfe von Apps Script. In der Kopfzeile, die Sie erstellen, wird der Text fett formatiert, der Hintergrund dunkelblaugrün, die Farbe des Texts und ein durchgehender Rand hinzugefügt.

Implementierung

Zum Implementieren des Formatierungsvorgangs greifen wir auf dieselben Methoden des Tabellendienstes zurück, die du zuvor verwendet hast. Jetzt werden aber auch einige Formatierungsmethoden des Dienstes verwendet. Gehen Sie folgendermaßen vor:

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

/**
 * 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 das Skriptprojekt.

Codeüberprüfung

Genau wie bei vielen Formatierungsaufgaben ist der Apps Script-Code einfach zu implementieren. In den ersten beiden Zeilen werden die zuvor beschriebenen Methoden verwendet, um einen Verweis auf das aktuell aktive Tabellenblatt (sheet) und die oberste Zeile des Tabellenblatts (headerRange)) zu erhalten. Die Methode Sheet.getRange(row, column, numRows, numColumns) gibt die oberste Zeile an, einschließlich der Spalten mit Daten. Die Methode Sheet.getLastColumn() gibt den Spaltenindex der letzten Spalte zurück, die Daten im Tabellenblatt enthält. In unserem Beispiel ist das die Spalte E (url).

Hinweis: Hier wird davon ausgegangen, dass die Kopfzeile, die wir formatieren möchten, immer in Zeile 1 enthalten ist, die Daten in A1 beginnen und sich auf eine beliebige Anzahl zusammenhängender Spalten erstrecken. Für diese Übung ist das in Ordnung. Aber im Allgemeinen sollten Sie diese Annahmen vermeiden, indem Sie den Nutzern erlauben, anzugeben, welche Zellen formatiert werden sollen (falls die Struktur ihrer Tabelle abweicht).

Im restlichen Code werden einfach verschiedene Range-Methoden aufgerufen, um die Formatierungsoptionen auf alle Zellen in headerRange anzuwenden. Damit der Code leicht zu lesen ist, verwenden wir die Methodenverkettung, um jede Formatierungsmethode nacheinander aufzurufen:

• Mit Range.setFontWeight(fontWeight) kann die Schriftstärke fett dargestellt werden.
• Mit Range.setFontColor(color) wird die Schriftfarbe auf Weiß gesetzt.
• Mit Range.setBackground(color) wird die Hintergrundfarbe auf Dunkelblau/Grün eingestellt.
• Bei setBorder(top, left, bottom, right, vertical, horizontal, color, style) wird ein durchgehender schwarzer Rahmen um die Zellenzellen herum angezeigt.

Die letzte Methode enthält mehrere Parameter. Sehen wir uns die jeweiligen Einstellungen genauer an. Mit den ersten vier Parametern, die hier auf true gesetzt sind, wird Apps Script mitgeteilt, dass der Rand über, unter und rechts des Bereichs hinzugefügt werden soll. Der fünfte und sechste Parameter (null und null) leitet Apps Script weiter, damit innerhalb des ausgewählten Bereichs keine Rahmenlinien geändert werden. Der siebte Parameter (null) gibt an, dass die Farbe der Rahmenlinie standardmäßig schwarz sein soll. Schließlich gibt der letzte Parameter die Art des Rahmens an, der aus den Optionen von SpreadsheetApp.BorderStyle verwendet wird.

Ergebnisse

Sie können die Formatierungsfunktion dann in der Praxis sehen. Gehen Sie dazu so vor:

1. Speichern Sie das Skriptprojekt im Apps Script-Editor, sofern Sie dies noch nicht getan haben.
2. Klicken Sie auf den Menüpunkt Kurzformate &Kopfzeilen formatieren.

Hinweis: Wenn Sie ein gmail.com-Konto verwenden, wird Ihnen bei der ersten Verwendung des Skripts möglicherweise ein Dialogfeld angezeigt, das nicht bestätigt wurde. Dadurch werden von Google Nutzer gewarnt, die Code von unbekannten oder nicht vertrauenswürdigen Autoren verwenden. Wenn Sie dieses Dialogfeld sehen, können Sie fortfahren, da Sie der Autor des Skripts sind. Folgen Sie der Anleitung auf dem Bildschirm, um mit der Autorisierung des Skripts fortzufahren:

1. Klicken Sie im Dialogfeld der nicht bestätigten App auf Erweitert.
2. Klicken Sie auf Datenmanipulation und benutzerdefinierte Menüs aufrufen (nicht sicher).
3. Klicken Sie auf Zulassen.

In diesem Codelab erhältst du möglicherweise mehrere Berechtigungsanfragen. Weitere Informationen zu diesem Vorgang finden Sie unter Autorisierung für Google-Dienste.

Die Ergebnisse sollten in etwa so aussehen:

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

5. Spaltenüberschrift formatieren

Wenn Sie eine personalisierte Kopfzeile erstellen möchten, können Sie auch eine Spaltenüberschrift hinzufügen. Spaltenüberschriften erhöhen die Lesbarkeit für bestimmte Datasets. Die Spalte titles [Titel] in dieser Tabelle kann beispielsweise mit den folgenden Formatoptionen erweitert werden:

• Fettdruck
• Text kursiv markieren
• Rahmenlinien hinzufügen
• Hyperlinks mithilfe des Spalteninhalts url einfügen Wenn Sie diese Hyperlinks hinzugefügt haben, können Sie die Spalte url entfernen, damit das Tabellenblatt übersichtlicher wird.

Als Nächstes implementieren Sie die Funktion formatColumnHeader(), um diese Änderungen auf die erste Spalte des Tabellenblatts anzuwenden. Damit der Code etwas leichter lesbar ist, können Sie zwei Hilfsfunktionen implementieren.

Implementierung

Wie zuvor müssen Sie eine Funktion hinzufügen, um die Formatierung der Spaltenüberschrift zu automatisieren. Gehen Sie folgendermaßen vor:

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

/**
 * 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 des 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 das Skriptprojekt.

Codeüberprüfung

Lassen Sie sich den Code in jeder der drei Funktionen separat ansehen:

formatColumnHeader()

Wie Sie sich vermutlich schon erhofft haben, setzen die ersten Zeilen dieser Funktion die Variablen, die auf das Tabellenblatt und den Bereich verweisen, an dem wir interessiert sind:

• Das aktive Tabellenblatt wird in sheet gespeichert.
• Die Anzahl der Zeilen in der Spaltenüberschrift wird berechnet und in numRows gespeichert. Hier wird vom Code ein Wert abgezogen, sodass die Anzahl der Zeilen in der Spaltenüberschrift title nicht enthalten ist.
• Der Bereich, der von der Spaltenüberschrift abgedeckt ist, wird in columnHeaderRange gespeichert.

Im Code werden dann die Grenzen und die Fettformatierung auf den Spaltenüberschrift angewendet, genau wie bei formatRowHeader(). Hier wird auch Range.setFontStyle(fontStyle) verwendet, um den Text kursiv darzustellen.

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

hyperlinkColumnHeaders_(headerRange, numRows)

Diese Hilfsfunktion identifiziert zuerst die Spaltenindizes der Kopfzeile (bei Index 1) und die Spalte url. Sie ruft columnIndexOf_('url') auf, um den Index der URL-Spalte abzurufen. Wird eine Spalte „url“ nicht gefunden, wird die Methode geschlossen, ohne dass Daten geändert werden.

Die Funktion erhält einen neuen Bereich (urlRange), der die URLs für die Spaltenüberschriften in der Kopfzeile abdeckt. Hierzu wird die Methode Range.offset(rowOffset, columnOffset) verwendet. Sie garantiert, dass die beiden Bereiche dieselbe Größe haben. Die Werte in den Spalten headerColumn und url werden dann abgerufen (headerValues und urlValues).

Die Funktion geht dann über jeden Zellenspaltenwert und ersetzt sie durch eine =HYPERLINK()-Tabellenformel, die mit dem Titel und den Spalteninhalten von url erstellt wurde. Die geänderten Header-Werte werden dann mit Range.setValues(values) in das Tabellenblatt eingefügt.

Und zum Schutz der Tabelle und zur Vermeidung redundanter Informationen wird Sheet.deleteColumn(columnPosition) aufgerufen, um die Spalte url zu entfernen.

columnIndexOf_(colName)

Mit dieser einfachen Funktion können Sie in der ersten Zeile der Tabelle nach einem bestimmten Namen suchen. Für die ersten drei Zeilen können Sie eine Liste der Spaltenüberschriften aus Zeile 1 der Tabelle abrufen. Diese Namen sind in der Variablen „columnNames“ gespeichert.

Die Funktion überprüft jeden Namen in der angegebenen Reihenfolge. Wenn der Name gefunden wird, nach dem gesucht wird, wird der Eintrag beendet und der Index der Spalte zurückgegeben. Wenn der Name das Ende der Namensliste erreicht, ohne ihn zu finden, wird -1 zurückgegeben, um anzuzeigen, dass der Name nicht gefunden wurde.

Ergebnisse

Sie können die Formatierungsfunktion dann in der Praxis sehen. Gehen Sie dazu so vor:

1. Speichern Sie das Skriptprojekt im Apps Script-Editor, sofern Sie dies noch nicht getan haben.
2. Klicken Sie auf den Menüpunkt Schnellformate und Spalte „Format““.

Die Ergebnisse sollten in etwa so aussehen:

Sie haben jetzt eine weitere Formatierungsaufgabe automatisiert. Wenn die Spaltenüberschriften und Zeilentitel formatiert sind, wird im nächsten Abschnitt die Formatierung der Daten erläutert.

6. Dataset formatieren

Mit den Headern können Sie jetzt eine Funktion erstellen, mit der die restlichen Daten des Tabellenblatts formatiert werden. Wir verwenden die folgenden Formatierungsoptionen:

• Abwechselnde Hintergrundfarben von Zeilen (auch Bänder genannt)
• Datumsformate ändern
• Grenzen anwenden
• Alle Spalten und Zeilen automatisieren

Sie erstellen jetzt die Funktion formatDataset() und eine zusätzliche Hilfsmethode, um diese Formate auf die Tabellendaten anzuwenden.

Implementierung

Fügen Sie wie zuvor eine Funktion hinzu, um die Datenformatierung zu automatisieren. Gehen Sie folgendermaßen vor:

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

/**
 * 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 des 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 das Skriptprojekt.

Codeüberprüfung

Lassen Sie uns den Code in jeder dieser beiden Funktionen separat prüfen:

formatDataset()

Die Funktion ähnelt den vorherigen Formatfunktionen, die Sie bereits implementiert haben. Sie erhält zuerst Variablen, um Verweise auf das aktive Tabellenblatt (Tabellenblatt) und den Datenbereich (fullDataRange) zu speichern.

Danach wird mit der Methode Range.offset(rowOffset, columnOffset, numRows, numColumns) ein Bereich erstellt (noHeadersRange), der alle Daten in der Tabelle abdeckt, mit Ausnahme der Spaltenüberschriften und Zeilenköpfe. Anhand des Codes wird dann überprüft, ob der neue Bereich bereits vorhanden ist (mit Range.getBandings()). Das ist erforderlich, da Apps Script einen Fehler ausgibt, wenn Sie ein neues Banding anwenden. Falls sie nicht vorhanden sind, fügt die Funktion mit Range.applyRowBanding(bandingTheme, showHeader, showFooter) ein hellgraues Banding hinzu. Andernfalls läuft die Funktion weiter.

Im nächsten Schritt wird die Hilfsfunktion formatDates_(colIndex) zum Formatieren der Datumsangaben in der Spalte „release_date'“ (unten beschrieben) aufgerufen. Diese Spalte wird mit der zuvor implementierten Hilfsfunktion columnIndexOf_(colName) angegeben.

Anschließend fügen Sie wie gewohnt einen weiteren Rahmen hinzu und passen die Größe jeder Spalte und Zeile automatisch an die Daten an, die sie enthalten. Hierzu werden die Methoden Sheet.autoResizeColumns(columnPosition) und Sheet.autoResizeColumns(columnPosition) verwendet.

formatDates_(colIndex)

Mit dieser Hilfsfunktion wird mithilfe des angegebenen Spaltenindex ein bestimmtes Datumsformat auf eine Spalte angewendet. Insbesondere werden Datumswerte als „Monat Tag, Jahr (Wochentag)“ formatiert.

Hinweis: Es gibt viele verschiedene Datums- und Zahlenformate, die Sie in Ihren Skripts und Tabellen verwenden können. Weitere Informationen finden Sie im Leitfaden zu Datums- und Zahlenformaten.

Zuerst prüft die Funktion, ob der angegebene Spaltenindex gültig ist (d. h. 0 oder größer). Falls nicht, wird sie ohne Aktion zurückgegeben. Durch diese Prüfung werden Fehler verhindert, die entstehen können, wenn das Tabellenblatt beispielsweise keine Spalte release_date enthält.

Sobald der Spaltenindex validiert wurde, wird der Bereich abgerufen, der die Spalte abdeckt (mit Ausnahme der Kopfzeile) und die Formatierung wird mit Range.setNumberFormat(numberFormat) angewendet.

Ergebnisse

Sie können die Formatierungsfunktion dann in der Praxis sehen. Gehen Sie dazu so vor:

1. Speichern Sie das Skriptprojekt im Apps Script-Editor, sofern Sie dies noch nicht getan haben.
2. Klicken Sie auf den Menüpunkt Schnellformate und Format: Dataset formatieren.

Die Ergebnisse sollten in etwa so aussehen:

Sie haben eine weitere Formatierungsaufgabe automatisiert. Nachdem Sie die Formatierungsbefehle nun erhalten haben, fügen Sie weitere Daten für die Anwendung hinzu.

7. API-Daten abrufen und formatieren

In diesem Codelab wissen Sie jetzt, wie Sie Apps Script als alternatives Mittel zur Formatierung Ihrer Tabelle verwenden können. Als Nächstes schreiben Sie Code, der Daten aus einer öffentlichen API abruft, in Ihre Tabelle einfügt und so formatiert, dass sie lesbar ist.

Im letzten Codelab haben Sie gelernt, wie Sie Daten aus einer API abrufen. Sie verwenden hier dieselben Verfahren. In dieser Übung verwenden wir die öffentliche Star Wars API (SWAPI), um Ihre Tabelle zu füllen. Insbesondere verwendest du die API, um Informationen zu den Hauptfiguren zu erhalten, die in den ursprünglichen drei Star Wars-Filmen zu sehen sind.

Durch Ihren Code wird die API aufgerufen, um eine große Menge an JSON-Daten zu erhalten, die Antwort zu parsen, die Daten in einem neuen Tabellenblatt zu platzieren und dann das Tabellenblatt zu formatieren.

Implementierung

In diesem Abschnitt fügen Sie einige zusätzliche Menüoptionen hinzu. Für jedes Menüelement wird ein Wrapper-Skript aufgerufen, das artikelspezifische Variablen an die Hauptfunktion (createResourceSheet_()) übergibt. Sie implementieren diese Funktion und drei weitere Hilfsfunktionen. Wie bisher helfen die Hilfsfunktionen dabei, logische Teil der Aufgabe zu isolieren und den Code lesbar zu machen.

Führen Sie folgende Schritte aus:

1. Aktualisieren Sie im Apps Script-Editor die onOpen()-Funktion in Ihrem Skriptprojekt so, dass sie mit dem folgenden Code übereinstimmt:

/**
 * 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 das Skriptprojekt.
3. Wählen Sie im Skripteditor in der Liste der Funktionen onOpen aus und klicken Sie auf Ausführen. Dadurch wird onOpen() ausgeführt, um das Tabellenmenü mit den neu hinzugefügten Optionen neu zu erstellen.
4. Um eine Apps Script-Datei zu erstellen, klicken Sie neben Dateien auf "Datei hinzufügen" > Skript.
5. Geben Sie dem neuen Skript einen Namen für die API und drücken Sie die Eingabetaste. In Apps Script wird automatisch eine .gs-Erweiterung an den Namen der Skriptdatei angehängt.
6. Ersetzen Sie den Code in der neuen API.gs-Datei 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 das Skriptprojekt.

Codeüberprüfung

Sie haben gerade viel Code hinzugefügt. Sehen wir uns die einzelnen Funktionen einzeln an, um ihre Funktionsweise zu verstehen:

onOpen()

Hier haben Sie Ihrem Menü „Quick formats“ einige Menüpunkte hinzugefügt. Sie haben eine Trennzeichenzeile festgelegt und dann die Methode Menu.addSubMenu(menu) verwendet, um eine verschachtelte Menüstruktur mit drei neuen Elementen zu erstellen. Die neuen Elemente werden mit der Methode Menu.addItem(caption, functionName) hinzugefügt.

Wrapper-Funktionen

Die hinzugefügten Elemente auf der Speisekarte sind ähnlich: Sie versuchen, ein Tabellenblatt mit Daten zu erstellen, die aus SWAPI abgerufen wurden. Der einzige Unterschied ist, dass sie sich jeweils auf einen anderen Film konzentrieren.

Es wäre praktisch, eine einzelne Funktion zum Erstellen der Tabelle zu schreiben. Dabei könnte die Funktion einen Parameter akzeptieren, um zu bestimmen, welcher Film verwendet werden soll. Mit der Menu.addItem(caption, functionName)-Methode können Sie jedoch Parameter übergeben, wenn sie vom Menü aufgerufen werden. Wie können Sie also vermeiden, denselben Code dreimal zu schreiben?

Die Antwort ist die Wrapper-Funktionen. Dies sind einfache Funktionen, die Sie direkt aufrufen können, um eine andere Funktion mit bestimmten festgelegten Parametern aufzurufen.

Der Code verwendet hier drei Wrapper-Funktionen: createPeopleSheetIV(), createPeopleSheetV() und createPeopleSheetVI(). Die Menüpunkte sind mit diesen Funktionen verknüpft. Wenn ein Menüelement angeklickt wird, wird die Wrapper-Funktion ausgeführt und sofort das Hauptelement zur Tabellenerstellung createResourceSheet_(resourceType, idNumber, episodeNumber) aufgerufen. Dabei werden die für den Menüpunkt geeigneten Parameter weitergegeben. In diesem Fall würden Sie die Funktion zum Erstellen von Tabellen bitten, ein Tabellenblatt mit wichtigen Zeichendaten aus einem der Star Wars-Filme zu erstellen.

Hinweis: Wenn Sie möchten, können Sie weitere Wrapper-Funktionen erstellen, um verschiedene Tabellenblatttypen mit Daten zu füllen. Du musst nur wissen, für welche Art von Ressource du fragst und welche Film-ID du verwenden möchtest. Beispiele für Ressourcentypen sind „planets'“ und „starships'“. Die API akzeptiert die Nummern 1–7 als Film-IDs.

createResourceSheet_(resourceType, idNumber, episodeNumber)

Dies ist die Hauptfunktion des Tabellenblatt-Builders für diese Übung. Mithilfe einiger Hilfsfunktionen ruft er die API-Daten ab, parst sie, erstellt ein Tabellenblatt, schreibt die API-Daten in das Tabellenblatt und formatiert das Tabellenblatt dann mit den Funktionen, die Sie in den vorherigen Abschnitten erstellt haben. Hier die Einzelheiten:

Zuerst stellt die Funktion fetchApiResourceObject_(url) bereit, um eine Anfrage an die API zum Abrufen grundlegender Filminformationen zu senden. Die API-Antwort enthält eine Sammlung von URLs, die vom Code verwendet werden können, um mehr Informationen zu bestimmten Personen (hier als Ressourcen bezeichnet) aus den Filmen zu erhalten. Der Code erfasst alles im Array resourceUrls.

Als Nächstes verwendet der Code fetchApiResourceObject_(url), 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 des Films beschreibt.

Die Ressourcendatenobjekte haben mehrere gängige Schlüssel, die Informationen zu diesem Zeichen zuordnen. Der Schlüssel „name'“ ist beispielsweise dem Namen der Figur im Film zugeordnet. Wir gehen davon aus, dass die Schlüssel für jedes Ressourcendatenobjekt identisch sind, da sie für die Verwendung gängiger Objektstrukturen vorgesehen sind. Die Schlüsselliste wird später benötigt. Daher wird im Code die Schlüsselliste 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 das die neuen Daten eingefügt werden. Durch Aufrufen dieser Hilfsfunktion wird auch das neue Tabellenblatt aktiviert.

Nachdem die Tabelle erstellt wurde, wird die Hilfsfunktion fillSheetWithData_(resourceSheet, objectKeys, resourceDataList) aufgerufen, um alle API-Daten zur Tabelle hinzuzufügen.

Außerdem werden alle zuvor erstellten Formatierungsfunktionen aufgerufen, damit auf die neuen Daten dieselben Formatierungsregeln angewendet werden. Da das neue Tabellenblatt das aktive Tabellenblatt ist, kann der Code diese Funktionen ohne Änderungen wiederverwenden.

fetchApiResourceObject_(url)

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

Hinweis: Wenn Sie häufig mit APIs arbeiten, kann diese Funktion nützlich sein, wenn Sie API-Informationen abrufen möchten. Da es sich für eine allgemeine API eignet, können Sie fast alle API-URLs einsetzen.

createNewSheet_(name)

Diese Hilfsfunktion ist recht einfach. Es wird zuerst geprüft, ob ein Tabellenblatt mit dem angegebenen Namen in der Tabelle vorhanden ist. Ist das der Fall, wird die Tabelle durch die Funktion aktiviert und zurückgegeben.

Ist das Tabellenblatt nicht vorhanden, wird es von der Funktion mit Spreadsheet.insertSheet(sheetName) erstellt, aktiviert und das neue Tabellenblatt zurückgegeben.

fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)

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

Zuerst berechnet die Funktion die Anzahl der Zeilen und Spalten, die zum Anzeigen 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. Dabei wird eine zusätzliche Zeile hinzugefügt, die die Spaltenüberschriften enthält. Die Variable resourceValues enthält ein aus resourceRange extrahiertes 2D-Wert-Array.

Die Funktion geht dann über jeden Objektschlüssel in der objectKeys-Liste. Der Schlüssel wird als Spaltenüberschrift festgelegt und dann wird in einem Ressourcenobjekt eine zweite Schleife wiedergegeben. Für jedes Paar (Zeile, Spalte) werden die entsprechenden API-Informationen in das Element resourceValues[row][column] kopiert.

Hinweis: Beachten Sie, dass Zeilen und Spalten in Tabellen 1-indexiert sind, während JavaScript-Arrays 0-indexiert sind. Aufgrund dieses Unterschieds müssen wir bei der Programmierung in Apps Script häufig 1 Indexe hinzufügen oder davon subtrahieren.

Nach dem Ausfüllen von resourceValues wird die Zieltabelle mit Sheet.clear() gelöscht, falls sie Daten aus vorherigen Klicks auf Menüpunkte enthält. Abschließend werden die neuen Werte in das Tabellenblatt geschrieben.

Ergebnisse

Sie können die Ergebnisse Ihrer Arbeit folgendermaßen abrufen:

1. Speichern Sie das Skriptprojekt im Apps Script-Editor, sofern Sie dies noch nicht getan haben.
2. Klicken Sie auf den Menüpunkt Kurzformate > Zeichenblatt erstellen > Folge IV.

Die Ergebnisse sollten in etwa so aussehen:

Sie haben jetzt Code zum Importieren von Daten in Google Tabellen geschrieben und automatisch formatiert.

8. Fazit

Glückwunsch! Du hast dieses Codelab abgeschlossen. Sie haben einige der Formatierungsoptionen für Google Tabellen gesehen, die Sie in Ihre Apps Script-Projekte aufnehmen können. Wir haben eine beeindruckende Anwendung erstellt, mit der ein großes API-Dataset importiert und formatiert wird.

War dieses Codelab hilfreich?

Ja Nein

Das haben Sie gelernt

• So wenden Sie verschiedene Tabellenformatierungsvorgänge mit Apps Script an.
• Untermenüs mit der Funktion onOpen() erstellen
• Mit Apps Script können Sie eine abgerufene Liste von JSON-Objekten in einem neuen Tabellenblatt mit Daten formatieren.

Wichtige Begriffe:

• Aktiv (Status): Gibt an, dass die Tabelle, das Tabellenblatt, der Bereich oder die Zelle derzeit vom Nutzer der Tabelle angezeigt oder hervorgehoben wird.
• Aktive Zelle: Die hervorgehobene Zelle im aktiven Tabellenblatt mit dem Cursorfokus.
• Aktivbereich: Eine Gruppe von einer oder mehreren Zellen, die momentan im aktiven Tabellenblatt markiert sind.
• API: Eine API ist eine Anwendung, die von Programmen oder Skripts aufgerufen werden kann, um Informationen anzufordern oder bestimmte Aktionen auszuführen.
• Autorisierung: Der Vorgang, bei dem der Nutzer Berechtigungen erteilt, damit Apps Script in den entsprechenden Google-Diensten auf Nutzerdaten zugreifen oder auf andere Weise Aktionen im Namen des Nutzers ausführen kann.
• Containergebundenes Skript: Das ist jedes Skript, das an ein Google Workspace-Dokument gebunden ist, z. B. eine Google-Tabelle oder ein Google-Dokument.
• Hilfefunktion: Eine private Funktion, die von einer anderen aufgerufen wird, um eine kleine Unteraufgabe auszuführen. Mit Hilfefunktionen werden Aufgaben an verschiedenen Stellen mehrmals erledigt.
• JSON: Dateiformat zum Definieren von Datenobjekten und ihren Eigenschaften aus Text
• onOpen(): Ist ein Skript definiert, wird ein einfacher Trigger ausgelöst, der ausgelöst wird, wenn eine Datei geöffnet oder aktualisiert wird.
• Private Funktionen: Eine Funktion, die als Teil einer Bibliothek oder von Clients über die Server-Client-Kommunikation aufgerufen werden kann. Private Funktionen von Apps Script enden auf _.
• Bereich**: Eine Gruppe von einer oder mehreren nebeneinanderliegenden Zellen in einem Tabellenblatt. Mit der Klasse Range** können Sie Bereiche in einem Tabellenblatt lesen und aktualisieren.
• Skripteditor: Der browserinterne Code-Editor für Apps Script.
• Einfache Trigger: Ein Untertyp von Triggern in Apps Script mit reservierten Triggernamen und bestimmten Einschränkungen.
• Tabelle**: Eine einzelne Seite einer Tabelle. Wird manchmal verwendet, um auf eine Google Tabellen-Datei in Google Drive zu verweisen. Mit der Klasse Sheet** können Sie Tabellenblätter aufrufen und bearbeiten.
• SpreadsheetApp: Diese Klasse dient als übergeordnete Klasse für den Tabellendienst und bietet einen Ausgangspunkt für das Lesen oder Bearbeiten von Google Tabellen-Daten.
• Tabelle**: Eine Google Tabellen-Datei in Google Drive. Mit der Klasse Spreadsheet** können Sie Tabellen aufrufen und bearbeiten.
• Tabellendienst: Apps Script, mit dem Skripts erstellt, abgerufen und bearbeitet werden können
• Trigger: Ein Ereignis, das mit einer Apps Script-Funktion verknüpft ist. Wenn das Ereignis eintritt (z. B. beim Öffnen einer Tabelle), wird der Trigger ausgelöst und die zugehörige Apps Script-Funktion wird automatisch ausgeführt.
• URL-Abrufdienst: Dies ist der Apps Script-Dienst, mit dem sich Skripts mit URL-Endpunkten verbinden lassen, um Anfragen zu senden und Antworten zu erhalten.
• Wrapper-Funktionen: Einfache Dienstfunktionen, mit denen komplexere Funktionen aufgerufen und dennoch bestimmte Parameter übergeben werden.

Weitere Informationen

Im nächsten Codelab in dieser Playlist wird gezeigt, wie Sie mit Apps Script Daten in einem Diagramm visualisieren und in Google-Präsentationen exportieren können.

Das nächste Codelab finden Sie im Hilfeartikel Diagramme und Präsentationen in Google Präsentationen erstellen.