Grundlagen von Apps Script mit Google Tabellen 3: Mit Daten arbeiten

1. Einführung

Willkommen beim dritten Teil der Codelab-Playlist zu den Grundlagen von Apps Script.

Wenn Sie dieses Codelab abschließen, können Sie sich über die Datenbearbeitung, benutzerdefinierte Menüs und den Datenabruf aus öffentlichen APIs in Apps Script informieren. Du führst weiterhin die Kurse SpreadsheetApp, Spreadsheet, Sheet und Range aus den vorherigen Codelabs in dieser Playlist aus.

Lerninhalte

• Daten aus einer privaten oder freigegebenen Tabelle in Drive importieren
• Benutzerdefiniertes Menü mit der Funktion onOpen() erstellen
• Stringdaten in Google Tabellen-Zellen parsen und bearbeiten.
• JSON-Objektdaten aus einer öffentlichen API-Quelle abrufen und bearbeiten.

Hinweis

Dies ist das dritte 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

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

Für die Übungen in diesem Codelab ist eine Tabelle erforderlich. So erstellen Sie eine Tabelle für die folgenden Übungen:

1. Erstellen Sie in Google Drive eine Tabelle. Dazu können Sie dies über die Google Drive-Oberfläche tun, indem Sie Neu und auf Google Tabellen auswählen. Die neue Tabelle wird erstellt und geöffnet. Die Datei wird im Drive-Ordner gespeichert.
2. Klicken Sie auf den Tabellentitel und ändern Sie ihn von „Unbenannte Tabelle“ zu „Datenbearbeitung und benutzerdefinierte Menüs“. Die Tabelle sollte in etwa so aussehen:

3. Klicken Sie zum Öffnen des Skripteditors auf Erweiterungen> Apps Script
4. Klicken Sie auf den Titel des Apps Script-Projekts und ändern Sie es von „Unbenanntes Projekt“ in „Datenbearbeitung und benutzerdefinierte Menüs“. Klicke auf Umbenennen, um die Titeländerung zu speichern.

Mit einer leeren Tabelle und einem Projekt können Sie das Lab starten. Wechseln Sie zum nächsten Abschnitt, um mehr über benutzerdefinierte Menüs zu erfahren.

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.

3. Daten mit einem benutzerdefinierten Menüpunkt importieren

Mit Apps Script können Sie benutzerdefinierte Menüs definieren, die in Google Tabellen angezeigt werden. Sie können benutzerdefinierte Menüs auch in Google Docs, Google Präsentationen und Google Formulare verwenden. Wenn Sie einen benutzerdefinierten Menüpunkt definieren, erstellen Sie ein Textlabel und verknüpfen es mit einer Apps Script-Funktion in Ihrem Skriptprojekt. Anschließend können Sie das Menü der Benutzeroberfläche hinzufügen, damit es in Google Tabellen angezeigt wird:

Wenn ein Nutzer auf einen benutzerdefinierten Menüpunkt klickt, wird die damit verknüpfte Apps Script-Funktion ausgeführt. So lassen sich Apps Script-Funktionen schnell ausführen, ohne den Skripteditor öffnen zu müssen. Sie können damit auch andere Nutzer der Tabelle ausführen, ohne dass Sie sich mit der Funktionsweise oder mit Apps Script auskennen. Für sie ist es nur ein weiterer Menüpunkt.

Benutzerdefinierte Menüelemente werden in der onOpen()-Funktion Einfacher Trigger definiert, die Sie im nächsten Abschnitt sehen.

4. onOpen()-Funktion

Mit einfachen Triggern in Apps Script können bestimmte Apps Script-Codes als Reaktion auf bestimmte Bedingungen oder Ereignisse ausgeführt werden. Beim Erstellen eines Triggers definieren Sie, welches Ereignis zum Auslösen des Triggers führt, und stellen eine Apps Script-Funktion bereit, die für das Ereignis ausgeführt wird.

onOpen() ist ein Beispiel für einen einfachen Trigger. Sie sind einfach einzurichten: Sie müssen nur eine Apps Script-Funktion namens onOpen() schreiben und Apps Script wird jedes Mal ausgeführt, wenn die zugehörige Tabelle geöffnet oder neu geladen wird:

/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
 /* ... */ 
}

Schlüsselkonzept: Einfache Trigger können einfach eingerichtet und verwendet werden, haben jedoch einige Einschränkungen. Aufgrund dieser Einschränkungen wird onOpen() nur zum Erstellen von Menüelementen verwendet.

Trigger sind ein komplexes Thema und fallen daher nicht in dieses Codelab. Stellen Sie sich onOpen() einfach als Ereignis-Handler vor, der Menüpunkte beim Laden einer Tabelle hinzufügt.

Implementierung

Ein benutzerdefiniertes Menü erstellen.

1. Ersetzen Sie den Code in Ihrem Skriptprojekt durch Folgendes:

/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Book-list')
    .addItem('Load Book-list', 'loadBookList')
    .addToUi();
}

2. Speichern Sie das Skriptprojekt.

Codeüberprüfung

Lesen Sie diesen Code, um zu verstehen, wie er funktioniert. In onOpen() wird in der ersten Zeile die Methode getUi() 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 drei Zeilen wird das Menü (Book-list) erstellt, ein Menüpunkt (Load Book-list) zu diesem Menü hinzugefügt und das Menü zur Tabelle-Tabelle hinzugefügt. 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 der Apps Script-Funktion her, die ausgeführt wird, wenn der Menüpunkt ausgewählt ist. In diesem Fall wird durch die Auswahl des Menüpunkts Load Book-list versucht, die Funktion loadBookList() auszuführen (noch nicht vorhanden).

Ergebnisse

Führen Sie jetzt diese Funktion aus, um zu prüfen, ob sie funktioniert:

1. Aktualisieren Sie die Tabelle in Google Tabellen. Hinweis: Dadurch wird der Tab normalerweise mit Ihrem Skripteditor geschlossen.
2. Öffnen Sie den Skripteditor wieder. Wählen Sie dazu Tools > Skripteditor aus.

Nach dem Aktualisieren der Tabelle sollte das neue Book-list-Menü in der Menüleiste angezeigt werden:

Wenn Sie auf Buchliste klicken, wird das Menü angezeigt:

Im nächsten Abschnitt wird der Code für die Funktion loadBookList() erstellt. Außerdem erfahren Sie, wie Sie mit Daten in Apps Script interagieren und andere Tabellen lesen können.

5. Tabellendaten importieren

Nachdem Sie nun ein benutzerdefiniertes Menü erstellt haben, können Sie Funktionen erstellen, die ausgeführt werden sollen, indem Sie auf den Menüpunkt klicken.

Derzeit enthält das benutzerdefinierte Menü "Book-list" einen Menüpunkt: Load Book-list. Die Funktion wird aufgerufen, wenn Sie den Menüpunkt Load Book-list auswählen. loadBookList(), ist nicht in Ihrem Skript vorhanden. Wenn Sie hier die Option Buchliste & gt; Buchliste laden auswählen, wird ein Fehler ausgegeben:

Du kannst diesen Fehler beheben, indem du die Funktion loadBookList() implementierst.

Implementierung

Sie möchten, dass Daten im neuen Menü mit Daten aus der Tabelle gefüllt werden. Also implementieren Sie loadBookList(), um Buchdaten aus einer anderen Tabelle zu lesen und in diese zu kopieren:

1. Fügen Sie Ihrem Skript unter onOpen() den folgenden Code hinzu:

/** 
 * Creates a template book list based on the
 * provided 'codelab-book-list' sheet.
 */
function loadBookList(){
  // Gets the active sheet.
  var sheet = SpreadsheetApp.getActiveSheet();
  
  // Gets a different spreadsheet from Drive using
  // the spreadsheet's ID. 
  var bookSS = SpreadsheetApp.openById(
    "1c0GvbVUDeBmhTpq_A3vJh2xsebtLuwGwpBYqcOBqGvo" 
  );

  // Gets the sheet, data range, and values of the
  // spreadsheet stored in bookSS.
  var bookSheet = bookSS.getSheetByName("codelab-book-list");
  var bookRange = bookSheet.getDataRange();
  var bookListValues = bookRange.getValues();

  // Add those values to the active sheet in the current
  // spreadsheet. This overwrites any values already there.
  sheet.getRange(1, 1, bookRange.getHeight(), bookRange.getWidth()) 
    .setValues(bookListValues);
  
  // Rename the destination sheet and resize the data
  // columns for easier reading.
  sheet.setName("Book-list");
  sheet.autoResizeColumns(1, 3);
}

2. Speichern Sie das Skriptprojekt.

Codeüberprüfung

Wie funktioniert diese Funktion? Für die Funktion loadBookList() werden hauptsächlich Methoden aus den Klassen Spreadsheet, Sheet und Range verwendet, die in den vorigen Codelabs eingeführt wurden. Unter Berücksichtigung dieser Konzepte kannst du den loadBookList()-Code in folgende vier Abschnitte unterteilen:

1: Zieltabelle bestimmen

Die erste Zeile verwendet SpreadsheetApp.getActiveSheet(), um einen Verweis auf das aktuelle Tabellenblatt-Objekt zu erhalten und in der Variablen sheet zu speichern. Das ist das Tabellenblatt, in das die Daten kopiert werden.

2: Quelldaten identifizieren

In den nächsten Zeilen werden vier Variablen festgelegt, die auf die Quelldaten verweisen, die Sie abrufen:

• bookSS speichert einen Verweis auf die Tabelle, aus der der Code Daten liest. Der Code findet die Tabelle anhand ihrer Tabellen-ID. In diesem Beispiel haben wir die ID einer Quelltabelle angegeben, aus der gelesen werden soll, und die Tabelle mit der Methode SpreadsheetApp.openById(id) öffnen.
• bookSheet speichert einen Verweis auf ein Tabellenblatt in bookSS, das die gewünschten Daten enthält. Der Code identifiziert das Tabellenblatt, aus dem gelesen werden soll, anhand seines Namens: codelab-book-list.
• bookRange speichert einen Verweis auf einen Datenbereich in bookSheet. Die Methode Sheet.getDataRange() gibt den Bereich zurück, der alle nicht leeren Zellen in der Tabelle enthält. So können Sie ganz einfach einen Bereich abrufen, der alle Daten in einem Tabellenblatt abdeckt, ohne leere Zeilen und Spalten enthalten zu müssen.
• bookListValues ist ein 2D-Array, das alle Werte aus den Zellen in bookRange enthält. Die Methode Range.getValues() generiert dieses Array durch Lesen von Daten aus der Quelltabelle.

3: Daten von der Quelle in das Ziel kopieren

Im nächsten Codeabschnitt werden die bookListValues-Daten in sheet kopiert, und dann wird das Tabellenblatt umbenannt:

• Anhand von Sheet.getRange(row, column, numRows, numColumns) wird festgelegt, wo die Daten in sheet kopiert werden sollen.
• Mit den Methoden Range.getHeight() und Range.getWidth() wird die Größe der Daten gemessen und ein Zielbereich derselben Größe definiert.
• Range.setValues(values) kopiert das 2D-Array von bookListValues in den Zielbereich und überschreibt dabei alle Daten.

4: Tabellenblatt formatieren

Sheet.setName(name) wird verwendet, um den Namen der Zieltabelle in Book-list zu ändern. In der letzten Zeile der Funktion wird Sheet.autoResizeColumns(startColumn, numColumns) verwendet, um die Größe der ersten drei Spalten in der Zieltabelle anzupassen. So können Sie die neuen Daten einfacher lesen.

Ergebnisse

Sie können diese Funktion in Aktion sehen. Wählen Sie in Google Tabellen Buchliste > Buchliste laden aus, um die Funktion zum Füllen der Tabelle auszuführen:

Sie haben nun ein Tabellenblatt mit einer Liste der Buchtitel, Autoren und 13-stelligen ISBN-Nummern. Im nächsten Abschnitt erfahren Sie, wie Sie die Daten in dieser Buchliste mithilfe von Stringmanipulationen und benutzerdefinierten Menüs ändern und aktualisieren.

6. Tabellendaten bereinigen

In Ihrem Tabellenblatt befinden sich jetzt Buchinformationen. Jede Zeile bezieht sich auf ein bestimmtes Buch, wobei Titel, Autor und ISBN in separaten Spalten aufgeführt sind. Es gibt jedoch auch Probleme mit diesen Rohdaten:

1. Bei einigen Zeilen werden Titel und Autor zusammen in die Spalte "Title" (Titel) platziert, und zwar durch ein Komma oder den String "quot;" von " verknüpft.
2. In einigen Zeilen fehlt der Titel oder der Autor des Buchs.

In den nächsten Abschnitten können Sie diese Probleme beheben, indem Sie die Daten bereinigen. Für das erste Problem erstellen Sie Funktionen, die die Titelspalte lesen und den Text aufteilen, wenn ein Komma oder &t-Trennzeichen gefunden wird. Dabei werden die entsprechenden Autoren- und Titelstrings in den richtigen Spalten platziert. Für das zweite Problem schreiben Sie Code, der mit einer externen API automatisch nach fehlenden Buchinformationen sucht, und diese in Ihr Tabellenblatt einfügen.

7. Artikel auf Speisekarte hinzufügen

Sie möchten drei Menüpunkte erstellen, um die Datenbereinigungen zu verwalten, die Sie implementieren möchten.

Implementierung

Aktualisieren Sie onOpen(), um die erforderlichen zusätzlichen Gerichte hinzuzufügen. Gehen Sie so vor:

1. Aktualisieren Sie im Skriptprojekt Ihren onOpen()-Code auf Folgendes:

/**
 * A special function that runs when the spreadsheet is first
 * opened or reloaded. onOpen() is used to add custom menu
 * items to the spreadsheet.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Book-list')
    .addItem('Load Book-list', 'loadBookList')
    .addSeparator()
    .addItem(
      'Separate title/author at first comma', 'splitAtFirstComma')
    .addItem(
      'Separate title/author at last "by"', 'splitAtLastBy')
    .addSeparator()
    .addItem(
      'Fill in blank titles and author cells', 'fillInTheBlanks')
    .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.

In diesem neuen Code wird mit der Methode Menu.addSeparator() eine horizontale Trennlinie im Menü erstellt, um die Gruppen ähnlicher Menüelemente visuell zu organisieren. Die neuen Menüpunkte werden darunter mit den Labels Separate title/author at first comma, Separate title/author at last "by" und Fill in blank titles and author cells hinzugefügt.

Hinweis: Die Menüpunkte werden in Google Tabellen in der Reihenfolge angezeigt, in der Sie sie im Code onOpen() hinzufügen.

Ergebnisse

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

Wenn Sie auf diese neuen Elemente klicken, wird ein Fehler zurückgegeben, da Sie die entsprechenden Funktionen nicht implementiert haben. Lassen Sie es sich also als Nächstes tun.

8. Text in Kommas trennen

Das Dataset, das Sie in die Tabelle importiert haben, enthält einige Zellen, in denen der Autor und der Titel in einer Zelle durch ein Komma falsch kombiniert werden:

Das Aufteilen von Textstrings in separate Spalten ist eine gängige Tabellenaufgabe. Google Tabellen bietet eine SPLIT()-Funktion, mit der Strings in Spalten unterteilt werden. Allerdings haben Datensätze häufig Probleme, die sich mit den integrierten Funktionen von Google Tabellen nicht einfach beheben lassen. In diesen Fällen können Sie Apps Script-Code schreiben, um die komplexen Vorgänge auszuführen, die zum Bereinigen und Organisieren Ihrer Daten erforderlich sind.

Beginnen Sie mit der Bereinigung Ihrer Daten, indem Sie zuerst eine Funktion namens splitAtFirstComma() implementieren, die den Autor und den Titel in die entsprechenden Zellen unterteilt, wenn Kommas gefunden werden.

Die Funktion splitAtFirstComma() sollte folgende Schritte ausführen:

1. Bereich für die aktuell ausgewählten Zellen abrufen.
2. Prüfen Sie, ob die Zellen im Bereich ein Komma haben.
3. Wenn Kommas vorhanden sind, teilen Sie den String in zwei (und nur zwei) Teilstrings am Speicherort des ersten Kommas auf. Um das Ganze zu vereinfachen, können Sie ein beliebiges Komma verwenden, das auf das Stringmuster [author], [title] verweist. Wenn es in der Zelle mehrere Kommas gibt, kann auch das erste Komma im String geteilt werden.
4. Lege die Teilstrings als neuen Inhalt der entsprechenden Titel- und Autorenzellen fest.

Implementierung

Zum Implementieren dieser Schritte verwenden Sie dieselben Methoden wie beim Tabellendienst, die Sie zuvor verwendet haben. Sie müssen zur Bearbeitung der String-Daten aber auch JavaScript verwenden. Gehen Sie folgendermaßen vor:

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

/**
 * Reformats title and author columns by splitting the title column
 * at the first comma, if present.
 */
function splitAtFirstComma(){
  // Get the active (currently highlighted) range.
  var activeRange = SpreadsheetApp.getActiveRange();
  var titleAuthorRange = activeRange.offset(
    0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);

  // Get the current values of the selected title column cells.
  // This is a 2D array.
  var titleAuthorValues = titleAuthorRange.getValues();

  // Update values where commas are found. Assumes the presence
  // of a comma indicates an "authors, title" pattern.
  for (var row = 0; row < titleAuthorValues.length; row++){
    var indexOfFirstComma =
        titleAuthorValues[row][0].indexOf(", ");

    if(indexOfFirstComma >= 0){
      // Found a comma, so split and update the values in
      // the values array.
      var titlesAndAuthors = titleAuthorValues[row][0];

      // Update the title value in the array.
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(indexOfFirstComma + 2);

      // Update the author value in the array.
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(0, indexOfFirstComma);
    }
  }

  // Put the updated values back into the spreadsheet.
  titleAuthorRange.setValues(titleAuthorValues);
}

2. Speichern Sie das Skriptprojekt.

Codeüberprüfung

Sehen wir uns den neuen Code an, der aus drei Hauptbereichen besteht:

1: Werte für hervorgehobene Titel abrufen

In den ersten drei Zeilen werden drei Variablen erstellt, die auf die aktuellen Daten im Tabellenblatt verweisen:

• activeRange stellt den Bereich dar, den der Nutzer derzeit beim Aufrufen der splitAtFirstComma()-Funktion hervorgehoben hat. Um diese Übung zu vereinfachen, nehmen wir an, dass der Nutzer dies nur tut, wenn Zellen in Spalte A hervorgehoben werden.
• titleAuthorRange steht für einen neuen Bereich, der dieselben Zellen wie activeRange abdeckt, aber rechts eine weitere Spalte enthält. titleAuthorRange wird mit der Methode Range.offset(rowOffset, columnOffset, numRows, numColumns) erstellt. Der Code muss diesen erweiterten Bereich verwenden, damit alle gefundenen Autoren in die Spalte „Titel“ aufgenommen werden können.
• titleAuthorValues ist ein 2D-Array aus Daten, die mithilfe von Range.getValues() aus titleAuthorRange extrahiert werden.

2: Jeder Titel prüfen und aufteilen, wenn das erste Komma als Trennzeichen gefunden wurde

Im nächsten Abschnitt werden die Werte in titleAuthorValues untersucht, um Kommas zu finden. Mit einem JavaScript for Loop werden alle Werte in der ersten Spalte von titleAuthorValues untersucht. Wenn ein Komma-String (", ") mit der Methode JavaScript String indexOf() gefunden wird, geht der Code so vor:

1. Der Zellenstringwert wird in die Variable titlesAndAuthors kopiert.
2. Der Kommapunkt wird mit der Methode JavaScript String indexOf() bestimmt.
3. Die Methode JavaScript String string() wird zweimal aufgerufen, um den Teilstring vor dem Komma und den Teilstring nach dem Trennzeichen abzurufen.
4. Die Teilstrings werden in das 2D-Array „titleAuthorValues“ kopiert und überschreiben die vorhandenen Werte an dieser Position. Da wir auf der Grundlage eines [author], [title]-Musters umgekehrt sind, wird die Reihenfolge der beiden Teilstrings umgekehrt, damit der Titel in der ersten Spalte und die Autoren in der zweiten Spalte angezeigt werden.

Hinweis: Wenn der Code kein Komma findet, bleiben die Daten in der Zeile unverändert.

3: Neue Werte zurück in das Tabellenblatt kopieren

Nachdem Sie alle Zellenwerte für den Titel überprüft haben, wird das aktualisierte 2D-Array „titleAuthorValues“ mit der Methode Range.setValues(values) zurück in die Tabelle kopiert.

Wichtiger Hinweis: Der Code kann jede Tabellenzelle einzeln aktualisiert werden, da er jedes Komma gefunden hat. Dabei wird jedes Mal, wenn eine Zelle geändert werden muss, Range.setValue(value) aufgerufen. Allerdings würde dieser Code wesentlich langsamer werden, da für jeden Range.setValue(value)-Aufruf eine Serverkommunikation erforderlich ist.

Beim Aktualisieren von Tabellendaten ist es praktisch immer besser, die Werte in ein 2D-Array aufzunehmen, das Array zu aktualisieren und die Daten dann mit einem einzigen Range.setValues(values)-Aufruf zurück in das Tabellenblatt zu übertragen.

Ergebnisse

Du kannst jetzt die Auswirkungen der Funktion splitAtFirstComma() in Aktion sehen. Führen Sie ihn aus, indem Sie nach der Auswahl den Menüpunkt Separater Titel/Autor bei erstem Komma auswählen...

...eine Zelle:

...oder mehrere Zellen:

Hinweis: Sie können in Ihrer Tabelle eine beliebige Textspalte auswählen. Mit dieser Funktion können Sie Kommas suchen und den Text gemäß den von Ihnen festgelegten Regeln aufteilen. Findet der Code jedoch ein Komma als Trennzeichen, wird die Zelle rechts neben dem Original überschrieben.

Sie haben jetzt eine Apps Script-Funktion entwickelt, die Google Tabellen-Daten verarbeitet. Als Nächstes implementieren Sie die zweite Split-Funktion.

9. Text auf Trennzeichen nach Trennzeichen aufteilen

Unter den ursprünglichen Daten können Sie ein weiteres Problem erkennen. Genau wie bei einigen der Datenformate „Titel“ und „Autoren“ in einer einzigen Zelle als "[author], [title]&quot, verwenden andere Zellen den Autor und „Titel“ als "[title] von [author]":

Implementierung

Sie können dieses Problem mit der gleichen Methode wie im letzten Abschnitt lösen, indem Sie eine Funktion namens „splitAtLastBy()“ erstellen. Die Funktion hat einen ähnlichen Job wie splitAtFirstComma() – der einzige Unterschied besteht darin, dass sie nach einem etwas anderen Textmuster sucht. Gehen Sie folgendermaßen vor, um diese Funktion zu implementieren:

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

/** 
 * Reformats title and author columns by splitting the title column
 * at the last instance of the string " by ", if present.
 */
function splitAtLastBy(){
  // Get the active (currently highlighted) range.
  var activeRange = SpreadsheetApp.getActiveRange();
  var titleAuthorRange = activeRange.offset(
    0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);

  // Get the current values of the selected title column cells.
  // This is a 2D array.
  var titleAuthorValues = titleAuthorRange.getValues();

  // Update values where " by " substrings are found. Assumes
  // the presence of a " by " indicates a "title by authors"
  // pattern.
  for(var row = 0; row < titleAuthorValues.length; row++){
    var indexOfLastBy =
        titleAuthorValues[row][0].lastIndexOf(" by ");
    
    if(indexOfLastBy >= 0){
      // Found a " by ", so split and update the values in
      // the values array.
      var titlesAndAuthors = titleAuthorValues[row][0];
      
      // Update the title value in the array.
      titleAuthorValues[row][0] =
        titlesAndAuthors.slice(0, indexOfLastBy);
      
      // Update the author value in the array.
      titleAuthorValues[row][1] =
        titlesAndAuthors.slice(indexOfLastBy + 4);
    }
  }

  // Put the updated values back into the spreadsheet.
  titleAuthorRange.setValues(titleAuthorValues);
}

2. Speichern Sie das Skriptprojekt.

Codeüberprüfung

Zwischen diesem Code und splitAtFirstComma() gibt es einige wichtige Unterschiede:

1. Der Teilstring „by“ wird statt als „,“ als Stringtrennzeichen verwendet.
2. Hier wird die JavaScript-Methode String.lastIndexOf(substring) anstelle von String.indexOf(substring) verwendet. Wenn es also mehrere Teilstrings im ersten String gibt, wird angenommen, dass alle Teilstrings im ersten String Teil des Titels sind.
3. Nachdem Sie den String geteilt haben, wird der erste Teilstring als Titel und der zweite Teil als Autor festgelegt. Das ist die entgegengesetzte Reihenfolge gegenüber splitAtFirstComma().

Ergebnisse

Du kannst jetzt die Auswirkungen der Funktion splitAtLastBy() in Aktion sehen. Führen Sie den Dienst aus, indem Sie nach der Auswahl den Menüpunkt Separater Titel/Autor bei gleichzeitiger Verwendung des Textes auswählen.

...eine Zelle:

...oder mehrere Zellen:

Sie haben diesen Abschnitt des Codelabs abgeschlossen. Sie können jetzt Apps Script verwenden, um Stringdaten in einem Tabellenblatt zu lesen und zu ändern, und benutzerdefinierte Menüs verwenden, um verschiedene Apps Script-Befehle auszuführen.

Im nächsten Abschnitt erfahren Sie, wie Sie dieses Dataset weiter verbessern können, indem Sie leere Zellen mit Daten aus einer öffentlichen API ausfüllen.

10. Übersicht: Daten aus öffentlichen APIs abrufen

Bisher haben Sie Ihr Dataset optimiert, um Formatierungsprobleme in Titeln und Autoren zu beheben. Im Dataset fehlen jedoch noch Informationen, die in den Zellen unten markiert sind:

Sie können die fehlenden Daten nicht mithilfe von String-Vorgängen mit den aktuell vorhandenen Daten abrufen. Stattdessen müssen Sie die fehlenden Daten aus einer anderen Quelle abrufen. Verwenden Sie dazu in Apps Script Informationen von externen APIs, die zusätzliche Daten bereitstellen können.

APIs sind Schnittstellen zur Anwendungsprogrammierung. Es ist ein allgemeiner Begriff, der aber im Grunde genommen ein Dienst ist, den Ihre Programme und Skripts zum Anfordern von Informationen oder bestimmten Aktionen aufrufen können. In diesem Abschnitt rufen Sie eine öffentlich verfügbare API auf, um Buchinformationen anzufordern, die Sie in die leeren Zellen in Ihrem Tabellenblatt einfügen können.

In diesem Abschnitt finden Sie Anleitungen für folgende Aktionen:

• Buchdaten aus einer externen API-Quelle anfordern.
• Extrahiert Titel und Autoreninformationen aus den zurückgegebenen Daten und schreibt sie in die Tabelle.

11. Externe Daten mit UrlFetch abrufen

Bevor Sie sich näher mit dem Code befassen, der direkt in Ihrer Tabelle verwendet werden kann, können Sie mehr über die Verwendung mit externen APIs in Apps Script erfahren. Erstellen Sie dazu eine Hilfefunktion zum Anfordern von Buchinformationen aus der öffentlichen Open Library API.

Hinweis: Mediathek öffnen ist ein Internetarchiv für alle Buchpublikationen. Weitere Informationen zur API-Dokumentation findest du auf der offiziellen Website.

Unsere Hilfsfunktion fetchBookData_(ISBN) verwendet eine 13-stellige ISBN eines Buchs als Parameter und gibt Daten zu diesem Buch zurück. Sie stellt eine Verbindung zu der Open Library API her und ruft sie ab. Analysiert dann das zurückgegebene JSON-Objekt.

Implementierung

Implementieren Sie diese Hilfsfunktion, indem Sie Folgendes tun:

1. Fügen Sie im Apps Script-Editor den folgenden Code am Ende des Skripts ein:

/**
 * Helper function to retrieve book data from the Open Library
 * public API.
 *
 * @param {number} ISBN - The ISBN number of the book to find.
 * @return {object} The book's data, in JSON format.
 */
function fetchBookData_(ISBN){
  // Connect to the public API.
  var url = "https://openlibrary.org/api/books?bibkeys=ISBN:"
      + ISBN + "&jscmd=details&format=json";
  var response = UrlFetchApp.fetch(
      url, {'muteHttpExceptions': true});
  
  // Make request to API and get response before this point.
  var json = response.getContentText();
  var bookData = JSON.parse(json); 
  
  // Return only the data we're interested in.
  return bookData['ISBN:' + ISBN];
}

2. Speichern Sie das Skriptprojekt.

Hinweis:In Apps Script werden Funktionsnamen, die mit _ enden (Unterstrich), als privat betrachtet. Andere Skripts können diese Funktionen nicht aufrufen, wenn sie in einer Bibliothek oder von Clients während der Server-Client-Kommunikation zur Verfügung stehen. Dies sind jedoch fortgeschrittene Themen. Wenn Sie also wissen, dass eine Funktion nur für das aktuelle Skript verwendet wird, sollten Sie den Namen einer Funktion mit _ beenden.

Codeüberprüfung

Dieser Code ist in zwei Hauptbereiche unterteilt:

1: Die API-Anfrage

In den ersten beiden Zeilen stellt fetchBookData_(ISBN) über den API-URL-Endpunkt und den URL-Abrufdienst von Apps Script eine Verbindung zur öffentlichen Open Library API her.

Die Variable url ist nur ein URL-String wie eine Webadresse. Sie verweist auf einen Speicherort auf den Servern der offenen Bibliothek. Außerdem gibt es drei Parameter (bibkeys, jscmd und format) mit Informationen zu den von Ihnen angeforderten Informationen und einer Struktur für die Open Library-Server. In diesem Fall geben Sie die ISBN-Nummer des Buchs an und fordern detaillierte Informationen an, die im JSON-Format zurückgegeben werden.

Hinweis: Jede öffentliche API definiert ihre eigenen Formate für Anfragen und Rückgabeergebnisse. Damit Ihr Code mit einer API effektiv kommunizieren kann, müssen Sie die von ihm definierten Formate einhalten. In der offenen Bibliothek werden die Anfrage- und Antwortformate in der Dokumentation zur Open Library Books API beschrieben. Das verwendete Antwortformat ist JSON (JavaScript Object Notation), das nur eine bestimmte Art zum Organisieren von Daten ist. JSON ist ähnlich wie HTML oder XML, aber für den Menschen einfacher zu lesen.

Nachdem Sie den URL-String erstellt haben, sendet der Code eine Anfrage an den Standort und erhält eine Antwort. Hierzu wird die Methode UrlFetchApp.fetch(url, params) verwendet. Er sendet eine Informationsanfrage an die von Ihnen angegebene externe URL und speichert die resultierende Antwort in der Variable response. Zusätzlich zur URL wird durch den Code der optionale Parameter muteHttpExceptions auf true gesetzt. Diese Einstellung bedeutet, dass Ihr Code nicht angehalten wird, wenn die Anfrage zu einem API-Fehler führt. Stattdessen wird die Fehlerantwort zurückgegeben.

Die Anfrage gibt ein HTTPResponse-Objekt zurück, das in der Variable response gespeichert ist. HTTP-Antworten enthalten einen Antwortcode, HTTP-Header und den Hauptantwortinhalt. Die Informationen sind hier die wichtigsten JSON-Inhalte, d. h., der Code muss sie extrahieren und dann die JSON-Datei parsen, um die gewünschten Informationen zu finden und zurückzugeben.

2: API-Antwort parsen und relevante Informationen zurückgeben

In den letzten drei Codezeilen gibt die HTTPResponse.getContentText()-Methode den Hauptinhalt der Antwort als String zurück. Dieser String liegt im JSON-Format vor, aber die Open Library API definiert den genauen Inhalt und das Format. Mit der Methode JSON.parse(jsonString) wird der JSON-String in ein JavaScript-Objekt konvertiert. So können schnell verschiedene Teile der Daten extrahiert werden. Schließlich gibt die Funktion die Daten zurück, die der ISBN des Buchs entsprechen.

Hinweis: Wenn Sie sich mit JSON vertraut machen möchten, lesen Sie die JavaScript-Dokumentation zum Arbeiten mit JSON oder zur Referenz zur JSON-Klasse.

Ergebnisse

Nachdem Sie fetchBookData_(ISBN) implementiert haben, können andere Funktionen im Code Informationen zu jedem Buch anhand der ISBN-Nummer finden. Sie können diese Funktion verwenden, um die Zellen in Ihrer Tabelle auszufüllen.

12. API-Daten in eine Tabelle schreiben

Sie können jetzt eine fillInTheBlanks()-Funktion implementieren, die Folgendes ausführt:

1. Identifizieren Sie die fehlenden Titel- und Autorendaten innerhalb des aktiven Datenbereichs.
2. Um bestimmte Daten aus einem Buch abzurufen, rufe die Open Library API mit der Hilfsmethode fetchBookData_(ISBN) auf.
3. Aktualisieren Sie die fehlenden Titel- oder Autorenwerte in den entsprechenden Zellen.

Implementierung

So implementieren Sie die neue Funktion:

1. Fügen Sie im Apps Script-Editor den folgenden Code am Ende des Skriptprojekts ein:

/**
 * Fills in missing title and author data using Open Library API
 * calls.
 */ 
function fillInTheBlanks(){
  // Constants that identify the index of the title, author,
  // and ISBN columns (in the 2D bookValues array below). 
  var TITLE_COLUMN = 0;
  var AUTHOR_COLUMN = 1;
  var ISBN_COLUMN = 2;

  // Get the existing book information in the active sheet. The data
  // is placed into a 2D array.
  var dataRange = SpreadsheetApp.getActiveSpreadsheet()
    .getDataRange();
  var bookValues = dataRange.getValues();

  // Examine each row of the data (excluding the header row).
  // If an ISBN is present, and a title or author is missing,
  // use the fetchBookData_(isbn) method to retrieve the
  // missing data from the Open Library API. Fill in the
  // missing titles or authors when they're found.
  for(var row = 1; row < bookValues.length; row++){   
    var isbn = bookValues[row][ISBN_COLUMN];
    var title = bookValues[row][TITLE_COLUMN];
    var author = bookValues[row][AUTHOR_COLUMN];
   
    if(isbn != "" && (title === "" || author === "") ){
      // Only call the API if you have an ISBN number and
      // either the title or author is missing.
      var bookData = fetchBookData_(isbn);

      // Sometimes the API doesn't return the information needed.
      // In those cases, don't attempt to update the row.
      if (!bookData || !bookData.details) {
        continue;
      }

      // The API might not return a title, so only fill it in
      // if the response has one and if the title is blank in
      // the sheet.
      if(title === "" && bookData.details.title){
        bookValues[row][TITLE_COLUMN] = bookData.details.title; 
      }

      // The API might not return an author name, so only fill it in
      // if the response has one and if the author is blank in
      // the sheet.
      if(author === "" && bookData.details.authors
          && bookData.details.authors[0].name){
        bookValues[row][AUTHOR_COLUMN] =
          bookData.details.authors[0].name; 
      }
    }
  }
  
  // Insert the updated book data values into the spreadsheet.
  dataRange.setValues(bookValues);
}

2. Speichern Sie das Skriptprojekt.

Codeüberprüfung

Dieser Code ist in drei Abschnitte unterteilt:

1: Vorhandene Buchinformationen lesen

Die ersten drei Zeilen der Funktion definieren Konstanten, um die Lesbarkeit des Codes zu verbessern. In den nächsten beiden Zeilen wird die Variable bookValues verwendet, um eine lokale Kopie der Tabellenblattinformationen zu verwalten. Der Code liest Informationen aus bookValues aus, verwendet die API, um fehlende Informationen einzugeben, und schreibt diese Werte zurück in die Tabelle.

Hinweis: Diese Anordnung unterscheidet sich von splitAtFirstComma() und splitAtLastBy(). Bei diesen Funktionen wurden im Code nur die derzeit hervorgehobenen Titel und Zeilen berücksichtigt. Hier prüfen Sie die gesamte Tabelle mit Spreadsheet.getDataRange().

2: Mit der Hilfsfunktion fehlende Informationen abrufen

Der Code befindet sich in bookValues in einer Zeile, um fehlende Titel oder Autoren zu finden. Um die Anzahl der API-Aufrufe zu reduzieren und gleichzeitig die Effizienz zu verbessern, ruft der Code die API nur dann auf, wenn Folgendes zutrifft:

1. Die ISBN-Spalte der Zeile enthält einen Wert.
2. Entweder die Titel- oder Autorenzelle in der Zeile ist leer.

Wenn die Bedingungen erfüllt sind, ruft der Code die API mit der zuvor implementierten Hilfsfunktion fetchBookData_(isbn) auf und speichert das Ergebnis in der Variablen bookData. Jetzt sollten die fehlenden Informationen vorhanden sein, die Sie in das Tabellenblatt einfügen möchten.

Die einzige Aufgabe besteht nur noch darin, die Informationen zu bookData in unsere Tabelle einzufügen. Allerdings gibt es dabei Einschränkungen. Leider haben öffentliche APIs wie die Open Library Book API manchmal nicht die von Ihnen angeforderten Informationen. Möglicherweise gibt es auch andere Probleme, die die Bereitstellung der Informationen verhindern. Wenn du davon ausgehst, dass jede API-Anfrage erfolgreich ist, ist dein Code nicht stabil genug, um unerwartete Fehler zu verarbeiten.

Damit Ihr Code API-Fehler verarbeiten kann, muss er prüfen, ob die API-Antwort gültig ist, bevor Sie sie verwenden. Sobald der Code bookData hat, führt er eine einfache Prüfung durch, um bookData und bookData.details zu bestätigen, bevor versucht wird, aus ihnen zu lesen. Fehlt einer der beiden Werte, verfügt die API nicht über die gewünschten Daten. In diesem Fall weist der Befehl continue den Code auf, diese Zeile zu überspringen. Du kannst die fehlenden Zellen zwar ausfüllen, aber mindestens dein Skript konnte nicht abstürzen.

3: Aktualisierte Informationen in das Tabellenblatt einfügen

Der letzte Teil des Codes enthält ähnliche Prüfungen, um zu überprüfen, ob der API Titel und Autorinformationen zurückgegeben wurden. Der Code aktualisiert das bookValues-Array nur, wenn der ursprüngliche Titel oder die ursprüngliche Zelle leer ist und die API einen Wert zurückgegeben hat, den du dort platzieren kannst.

Die Schleife wird beendet, nachdem alle Zeilen im Tabellenblatt untersucht wurden. Im letzten Schritt schreiben Sie das aktualisierte bookValues-Array über Range.setValues(values) zurück in die Tabelle.

Ergebnisse

Sie können jetzt die Bereinigung Ihrer Buchdaten abschließen. Gehen Sie so vor:

1. Wenn Sie es noch nicht getan haben, markieren Sie den Bereich A2:A15 in Ihrem Tabellenblatt und wählen Sie Buchliste & gt; Separater Titel/Autor bei erstem Komma trennen aus, um die Kommas-Probleme zu beheben.
2. Wenn Sie es noch nicht getan haben, markieren Sie den Bereich A2:A15 in Ihrem Tabellenblatt und wählen Sie Buchliste & gt; Separater Titel/Autor am letzten & aus, um die Probleme zu beheben.
3. Um alle verbleibenden Zellen auszufüllen, wählen Sie Buchliste > leere Titel und Autorenzellen ausfüllen aus:

13. Fazit

Glückwunsch! Du hast dieses Codelab abgeschlossen. Sie wissen jetzt, wie Sie benutzerdefinierte Menüs erstellen, um verschiedene Teile Ihres Apps Script-Codes zu aktivieren. Außerdem haben Sie gesehen, wie Sie mit Apps Script-Diensten und öffentlichen APIs Daten in Google Tabellen importieren können. Dies ist ein üblicher Vorgang bei der Tabellenverarbeitung. Mit Apps Script können Sie Daten aus einer Vielzahl von Quellen importieren. Zum Schluss haben Sie erfahren, wie Sie Apps Script-Dienste und JavaScript nutzen können, um Tabellendaten zu lesen, zu verarbeiten und einzufügen.

War dieses Codelab hilfreich?

Ja Nein

Das haben Sie gelernt

• Daten aus einer Google-Tabelle importieren
• So erstellen Sie ein benutzerdefiniertes Menü in der Funktion onOpen().
• Stringdatenwerte parsen und bearbeiten
• Wie Sie öffentliche APIs mit dem URL-Abrufdienst aufrufen
• Wie Sie JSON-Objektdaten aus einer öffentlichen API-Quelle parsen

Wichtige Konzepte:

1. onOpen() ist ein einfacher Trigger, mit dem Menüelemente erstellt werden. Für einfache Trigger gelten unterschiedliche Einschränkungen.
2. Wenn Sie Tabellenzellendaten lesen oder schreiben, sollten Sie mit Range.getValues() oder Range.setValues(values) einen ganzen Bereich auf einmal lesen oder schreiben. Jede Zelle nicht einzeln lesen oder einstellen, da es langsamer ist.
3. Namen von Apps Script-Funktionen, die mit _ enden (als Unterstrich), gelten als privat. Andere Skripts können diese Funktionen nicht aufrufen, wenn sie in einer Bibliothek vorhanden sind oder von Clients über die Server-Client-Kommunikation gesendet werden. Wenn Sie wissen, dass eine Funktion nur vom aktuellen Skript verwendet wird, sollten Sie den Namen einer Funktion mit _ beenden.
4. Wenn Sie Daten verwenden, die von externen APIs zurückgegeben werden, validieren Sie die Daten, bevor Sie sie verwenden. Dadurch werden Fehler in Ihrem Code reduziert, wenn die API ein unerwartetes Ergebnis zurückgibt.

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 durch 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.

Weitere Informationen

Das nächste Codelab in dieser Playlist erläutert die Formatierung von Daten in einer Tabelle.

Das nächste Codelab finden Sie unter Datenformatierung.