1. Einführung
Willkommen zum dritten Teil der Codelab-Playlist „Grundlagen von Apps Script mit Google Sheets“.
In diesem Codelab erfahren Sie, wie Sie Datenbearbeitung, benutzerdefinierte Menüs und das Abrufen öffentlicher API-Daten in Apps Script verwenden können, um die Nutzung von Google Tabellen zu optimieren. Sie arbeiten weiterhin mit den Klassen SpreadsheetApp
, Spreadsheet
, Sheet
und Range
, die in den vorherigen Codelabs dieser Playlist eingeführt wurden.
Lerninhalte
- So importieren Sie Daten aus einer persönlichen oder gemeinsam genutzten Tabelle in Drive.
- So erstellen Sie ein benutzerdefiniertes Menü mit der Funktion
onOpen()
. - So parsen und bearbeiten Sie Stringdatenwerte in Google-Tabellenzellen.
- Wie Sie 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, sollten Sie die vorherigen Codelabs durcharbeiten:
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
Für die Übungen in diesem Codelab benötigen Sie eine Tabelle. So erstellen Sie eine Tabelle für diese Übungen:
- Erstellen Sie eine Tabelle in Google Drive. Wählen Sie dazu in der Drive-Oberfläche die Option Neu > Google Tabellen aus. Dadurch wird eine neue Tabelle erstellt und geöffnet. Die Datei wird in Ihrem Drive-Ordner gespeichert.
- Klicken Sie auf den Tabellentitel und ändern Sie ihn von „Unbenannte Tabelle“ in „Datenbearbeitung und benutzerdefinierte Menüs“. Ihr Tabellenblatt sollte so aussehen:
- Klicken Sie auf Erweiterungen> Apps Script, um den Skripteditor zu öffnen.
- Klicken Sie auf den Titel des Apps Script-Projekts und ändern Sie ihn von „Unbenanntes Projekt“ in „Datenbearbeitung und benutzerdefinierte Menüs“. Klicken Sie auf Umbenennen, um die Änderung des Titels zu speichern.
Jetzt können Sie mit dem Lab beginnen. Im nächsten Abschnitt erfahren Sie mehr über benutzerdefinierte Menüs.
3. Übersicht: Daten mit einem benutzerdefinierten Menüpunkt importieren
Mit Apps Script können Sie benutzerdefinierte Menüs definieren, die in Google Tabellen angezeigt werden können. Sie können benutzerdefinierte Menüs auch in Google Docs, Google Präsentationen und Google Formulare verwenden. Wenn Sie ein benutzerdefiniertes Menüelement 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 ein benutzerdefiniertes Menüelement klickt, wird die Apps Script-Funktion ausgeführt, die Sie damit verknüpft haben. So können Sie Apps Script-Funktionen schnell ausführen, ohne den Script-Editor öffnen zu müssen. Außerdem können andere Nutzer der Tabelle Ihren Code ausführen, ohne etwas darüber zu wissen, wie er oder Apps Script funktioniert. Für sie ist es nur ein weiterer Menüpunkt.
Benutzerdefinierte Menüpunkte werden in der onOpen()
-Funktion einfacher Trigger definiert, die im nächsten Abschnitt beschrieben wird.
4. Die Funktion onOpen()
Einfache Trigger in Apps Script ermöglichen es, bestimmten Apps Script-Code als Reaktion auf bestimmte Bedingungen oder Ereignisse auszuführen. Wenn Sie einen Trigger erstellen, definieren Sie, welches Ereignis den Trigger auslöst, und geben eine Apps Script-Funktion an, 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 mit dem Namen onOpen()
schreiben. Apps Script führt sie dann jedes Mal aus, 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() {
/* ... */
}
Implementierung
Erstellen wir ein benutzerdefiniertes Menü.
- 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();
}
- Speichern Sie Ihr Skriptprojekt.
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 drei Zeilen wird das Menü (Book-list
) erstellt, dem Menü wird ein Menüpunkt (Load Book-list
) hinzugefügt und das Menü wird 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üpunkts und der Apps Script-Funktion hergestellt, die ausgeführt wird, wenn der Menüpunkt ausgewählt wird. Wenn Sie in diesem Fall das Menüelement Load Book-list
auswählen, versucht Google Tabellen, die Funktion loadBookList()
auszuführen, die noch nicht vorhanden ist.
Ergebnisse
Führen Sie diese Funktion jetzt aus, um zu sehen, ob sie funktioniert:
- Laden Sie die Tabelle in Google Sheets neu. Hinweis: Dadurch wird in der Regel der Tab mit dem Script-Editor geschlossen.
- Öffnen Sie den Skripteditor noch einmal, indem Sie Tools > Skripteditor auswählen.
Nach dem Neuladen der Tabelle sollte das neue Book-list
-Menü in der Menüleiste angezeigt werden:
Wenn Sie auf Buchliste klicken, wird das folgende Menü angezeigt:
Im nächsten Abschnitt wird der Code für die Funktion loadBookList()
erstellt. Außerdem wird eine Möglichkeit vorgestellt, wie Sie in Apps Script mit Daten interagieren können: das Lesen anderer Tabellen.
5. Tabellendaten importieren
Nachdem Sie ein benutzerdefiniertes Menü erstellt haben, können Sie Funktionen erstellen, die durch Klicken auf das Menüelement ausgeführt werden.
Das benutzerdefinierte Menü Book-list
hat derzeit ein Menüelement: Load Book-list.
Die Funktion, die aufgerufen wird, wenn Sie das Menüelement Load Book-list
auswählen, loadBookList(),
, ist in Ihrem Skript nicht vorhanden. Wenn Sie also Book-list > Load Book-list auswählen, wird ein Fehler ausgegeben:
Sie können diesen Fehler beheben, indem Sie die Funktion loadBookList()
implementieren.
Implementierung
Das neue Menüelement soll die Tabelle mit Daten füllen, mit denen Sie arbeiten können. Daher implementieren Sie loadBookList()
, um Buchdaten aus einer anderen Tabelle zu lesen und in diese zu kopieren:
- 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);
}
- Speichern Sie Ihr Skriptprojekt.
Code Review
Wie funktioniert diese Funktion? Die Funktion loadBookList()
verwendet hauptsächlich Methoden aus den Klassen Spreadsheet
, Sheet
und Range
, die in den vorherigen Codelabs eingeführt wurden. Mit diesen Konzepten im Hinterkopf können Sie den loadBookList()
-Code in die folgenden vier Abschnitte unterteilen:
1. Zieltabellenblatt identifizieren
In der ersten Zeile wird mit SpreadsheetApp.getActiveSheet()
ein Verweis auf das aktuelle Tabellenblattobjekt abgerufen und in der Variablen sheet
gespeichert. Das ist das Tabellenblatt, in das die Daten kopiert werden.
2. Quelldaten ermitteln
In den nächsten Zeilen werden vier Variablen festgelegt, die auf die abzurufenden Quelldaten verweisen:
- In
bookSS
wird ein Verweis auf die Tabelle gespeichert, aus der der Code Daten liest. Im Code wird die Tabelle anhand ihrer Tabellen-ID gesucht. In diesem Beispiel haben wir die ID einer Quelltabelle angegeben, aus der gelesen werden soll, und die Tabelle mit der MethodeSpreadsheetApp.openById(id)
geöffnet. bookSheet
speichert einen Verweis auf ein Tabellenblatt inbookSS
, das die gewünschten Daten enthält. Der Code identifiziert das Blatt, aus dem gelesen werden soll, anhand seines Namenscodelab-book-list
.bookRange
speichert einen Verweis auf einen Datenbereich inbookSheet
. Die MethodeSheet.getDataRange()
gibt den Bereich zurück, der alle nicht leeren Zellen im Tabellenblatt enthält. So können Sie ganz einfach einen Bereich festlegen, der alle Daten in einem Tabellenblatt abdeckt, ohne leere Zeilen und Spalten einzubeziehen.bookListValues
ist ein zweidimensionales Array mit allen Werten aus den Zellen inbookRange
. Mit der MethodeRange.getValues()
wird dieses Array generiert, indem Daten aus dem Quelltabellenblatt gelesen werden.
3. Daten von der Quelle zum Ziel kopieren
Im nächsten Codeabschnitt werden die bookListValues
-Daten in sheet
kopiert und das Tabellenblatt wird umbenannt:
- Mit
Sheet.getRange(row, column, numRows, numColumns)
wird angegeben, wohin die Daten insheet
kopiert werden sollen. - Mit den Methoden
Range.getHeight()
undRange.getWidth()
wird die Größe der Daten gemessen und ein Zielbereich mit denselben Dimensionen definiert. - Mit
Range.setValues(values)
wird das 2D-Array vonbookListValues
in den Zielbereich kopiert und alle dort bereits vorhandenen Daten werden überschrieben.
4. Zieltabellenblatt formatieren
Mit Sheet.setName(name)
wird der Name des Zielblatts in Book-list
geändert. In der letzten Zeile der Funktion wird Sheet.autoResizeColumns(startColumn, numColumns)
verwendet, um die Größe der ersten drei Spalten im Zieltabellenblatt zu ändern, damit Sie die neuen Daten leichter lesen können.
Ergebnisse
Sie können sich diese Funktion in Aktion ansehen. Wählen Sie in Google Sheets Buchliste > Buchliste laden aus, um die Funktion zum Ausfüllen der Tabelle auszuführen:
Sie haben jetzt ein Tabellenblatt mit einer Liste von Buchtiteln, Autoren und 13-stelligen ISBN-Nummern. Im nächsten Abschnitt erfahren Sie, wie Sie die Daten in dieser Buchliste mithilfe von Stringbearbeitung und benutzerdefinierten Menüs ändern und aktualisieren.
6. Übersicht: Tabellendaten bereinigen
Sie haben jetzt Buchinformationen in Ihrem Tabellenblatt. Jede Zeile bezieht sich auf ein bestimmtes Buch und enthält in separaten Spalten den Titel, den Autor und die ISBN-Nummer. Es gibt aber auch einige Probleme mit diesen Rohdaten:
- In einigen Zeilen werden Titel und Autor in der Titelspalte zusammen aufgeführt und durch ein Komma oder den String „by“ getrennt.
- Bei einigen Zeilen fehlen der Titel oder der Autor des Buchs.
In den nächsten Abschnitten beheben Sie diese Probleme, indem Sie die Daten bereinigen. Für das erste Problem erstellen Sie Funktionen, die die Spalte „Titel“ lesen und den Text aufteilen, wenn ein Komma oder das Trennzeichen „ by “ gefunden wird. Die entsprechenden Unterzeichenfolgen für Autor und Titel werden dann in die richtigen Spalten eingefügt. Für das zweite Problem schreiben Sie Code, der automatisch mithilfe einer externen API nach fehlenden Buchinformationen sucht und diese Informationen in Ihr Tabellenblatt einfügt.
7. Speisekarteneinträge hinzufügen
Sie müssen drei Menüpunkte erstellen, um die von Ihnen implementierten Vorgänge zur Datenbereinigung zu steuern.
Implementierung
Wir aktualisieren onOpen()
, damit die zusätzlichen Menüelemente enthalten sind, die Sie benötigen. Gehen Sie dazu so vor:
- Aktualisieren Sie in Ihrem Skriptprojekt den
onOpen()
-Code, sodass er dem folgenden Code entspricht:
/**
* 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();
}
- Speichern Sie Ihr Skriptprojekt.
- Wählen Sie im Skripteditor
onOpen
aus der Funktionsliste aus und klicken Sie auf Ausführen. Dadurch wirdonOpen()
ausgeführt, um das Tabellenmenü neu zu erstellen. Sie müssen die Tabelle also nicht neu laden.
Im neuen Code wird mit der Methode Menu.addSeparator()
eine horizontale Trennlinie im Menü erstellt, um Gruppen verwandter Menüelemente visuell zu organisieren. Die neuen Menüpunkte werden dann 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.
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 ausgegeben, da Sie die entsprechenden Funktionen noch nicht implementiert haben. Das holen wir jetzt nach.
8. Text anhand von Kommas aufteilen
In dem Dataset, das Sie in Ihre Tabelle importiert haben, sind in einigen Zellen Autor und Titel durch ein Komma getrennt in einer Zelle zusammengefasst:
Das Aufteilen von Textstrings in separate Spalten ist eine häufige Tabellenkalkulationsaufgabe. Google Sheets bietet die Funktion SPLIT()
, mit der Strings in Spalten aufgeteilt werden. Datensätze weisen jedoch häufig Probleme auf, 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 Autor und Titel in die entsprechenden Zellen aufteilt, wenn Kommas gefunden werden.
Die Funktion splitAtFirstComma()
sollte die folgenden Schritte ausführen:
- Gibt den Bereich zurück, der die aktuell ausgewählten Zellen darstellt.
- Prüfen Sie, ob Zellen im Bereich ein Komma enthalten.
- Wenn Kommas vorhanden sind, teilen Sie den String an der Stelle des ersten Kommas in zwei (und nur zwei) Teilstrings auf. Um die Sache zu vereinfachen, können Sie davon ausgehen, dass jedes Komma auf das Stringmuster [authors], [title] hinweist. Wenn in der Zelle mehrere Kommas vorkommen, können Sie davon ausgehen, dass es angemessen ist, den String am ersten Komma aufzuteilen.
- Legen Sie die Teilstrings als neuen Inhalt der entsprechenden Titel- und Autorzellen fest.
Implementierung
Für diese Schritte verwenden Sie dieselben Tabellendienst-Methoden wie zuvor. Außerdem müssen Sie JavaScript verwenden, um die Stringdaten zu bearbeiten. Und so gehts:
- Fügen Sie im Apps Script-Editor die folgende Funktion am Ende Ihres Skriptprojekts ein:
/**
* 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);
}
- Speichern Sie Ihr Skriptprojekt.
Code Review
Sehen wir uns den neuen Code an, der aus drei Hauptabschnitten besteht:
1. Hervorgehobene Titelwerte abrufen
In den ersten drei Zeilen werden drei Variablen festgelegt, die sich auf die aktuellen Daten im Tabellenblatt beziehen:
activeRange
steht für den Bereich, den der Nutzer gerade markiert hat, als die FunktionsplitAtFirstComma()
aufgerufen wurde. Der Einfachheit halber können wir davon ausgehen, dass der Nutzer dies nur beim Markieren von Zellen in Spalte A tut.titleAuthorRange
ist ein neuer Bereich, der dieselben Zellen wieactiveRange
umfasst, aber auch eine weitere Spalte rechts davon.titleAuthorRange
wird mit der MethodeRange.offset(rowOffset, columnOffset, numRows, numColumns)
erstellt. Der Code benötigt diesen erweiterten Bereich, um alle Autoren, die in der Spalte „Titel“ gefunden werden, dort einzufügen.titleAuthorValues
ist ein zweidimensionales Array mit Daten, die mitRange.getValues()
austitleAuthorRange
extrahiert wurden.
2. Jeden Titel untersuchen und am ersten Komma trennen
Im nächsten Abschnitt werden die Werte in titleAuthorValues
auf Kommas untersucht. Mit einer JavaScript-For-Schleife werden alle Werte in der ersten Spalte von titleAuthorValues
untersucht. Wenn mit der Methode JavaScript String indexOf() ein Komma-Teilstring (", "
) gefunden wird, führt der Code Folgendes aus:
- Der Zellstringwert wird in die Variable
titlesAndAuthors
kopiert. - Die Position des Kommas wird mit der Methode JavaScript String indexOf() ermittelt.
- Die Methode JavaScript String slice() wird zweimal aufgerufen, um den Teilstring vor dem Komma als Trennzeichen und den Teilstring nach dem Trennzeichen abzurufen.
- Die Teilstrings werden zurück in das 2D-Array „titleAuthorValues“ kopiert und überschreiben die vorhandenen Werte an dieser Position. Da wir von einem Muster [authors], [title] ausgehen, wird die Reihenfolge der beiden Teilstrings umgekehrt, sodass der Titel in der ersten Spalte und die Autoren in der zweiten Spalte stehen.
Hinweis: Wenn im Code kein Komma gefunden wird, bleiben die Daten in der Zeile unverändert.
3. Neue Werte zurück in das Tabellenblatt kopieren
Nachdem alle Titelzellenwerte geprüft wurden, wird das aktualisierte zweidimensionale Array „titleAuthorValues“ mit der Methode Range.setValues(values)
wieder in die Tabelle kopiert.
Ergebnisse
Sie können sich jetzt die Auswirkungen der Funktion splitAtFirstComma()
ansehen. Versuchen Sie, das Skript auszuführen, indem Sie nach der Auswahl von… den Menüpunkt Separate title/author at first comma (Titel/Autor beim ersten Komma trennen) auswählen.
…eine Zelle:
…oder mehrere Zellen:
Sie haben jetzt eine Apps Script-Funktion erstellt, die Google Sheets-Daten verarbeitet. Als Nächstes implementieren Sie die zweite Splitterfunktion.
9. Text anhand von „by“-Trennzeichen aufteilen
Wenn Sie sich die Originaldaten ansehen, stellen Sie ein weiteres Problem fest. In einigen Datenformaten werden Titel und Autoren in einer einzelnen Zelle als [authors], [title] formatiert, in anderen Zellen als [title] von [authors]:
Implementierung
Sie können dieses Problem mit derselben Methode wie im letzten Abschnitt lösen, indem Sie eine Funktion namens splitAtLastBy()
erstellen. Diese Funktion hat eine ähnliche Aufgabe wie splitAtFirstComma()
. Der einzige wirkliche Unterschied besteht darin, dass sie nach einem etwas anderen Textmuster sucht. So implementieren Sie diese Funktion:
- Fügen Sie im Apps Script-Editor die folgende Funktion am Ende Ihres Skriptprojekts ein:
/**
* 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);
}
- Speichern Sie Ihr Skriptprojekt.
Code Review
Es gibt einige wichtige Unterschiede zwischen diesem Code und splitAtFirstComma()
:
- Der Teilstring „
by
“ wird anstelle von „,
“ als Stringbegrenzer verwendet. - Hier wird die JavaScript-Methode
String.lastIndexOf(substring)
anstelle vonString.indexOf(substring)
verwendet. Wenn also mehrere Teilstrings „by
“ im ursprünglichen String vorhanden sind, werden alle bis auf den letzten „by
“ als Teil des Titels betrachtet. - Nach dem Aufteilen des Strings wird der erste Teilstring als Titel und der zweite als Autor festgelegt. Das ist die umgekehrte Reihenfolge von
splitAtFirstComma()
.
Ergebnisse
Sie können sich jetzt die Auswirkungen der Funktion splitAtLastBy()
ansehen. Versuchen Sie, das Tool auszuführen, indem Sie nach der Auswahl von... das Menüelement Separate title/author at last "by" (Titel/Autor am letzten „von“ trennen) auswählen.
…eine Zelle:
…oder mehrere Zellen:
Sie haben diesen Abschnitt des Codelabs abgeschlossen. Sie können jetzt mit Apps Script String-Daten in einem Tabellenblatt lesen und ä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 füllen.
10. Übersicht: Daten aus öffentlichen APIs abrufen
Bisher haben Sie Ihren Datensatz optimiert, um einige Probleme mit der Formatierung von Titeln und Autoren zu beheben. Im Datensatz fehlen jedoch noch einige Informationen, die in den Zellen unten hervorgehoben sind:
Sie können die fehlenden Daten nicht durch String-Operationen für die vorhandenen Daten abrufen. Stattdessen müssen Sie die fehlenden Daten aus einer anderen Quelle beziehen. In Apps Script können Sie dazu Informationen von externen APIs anfordern, die zusätzliche Daten liefern können.
APIs sind Application Programming Interfaces. Es handelt sich um einen allgemeinen Begriff, aber im Grunde ist es ein Dienst, den Ihre Programme und Skripts aufrufen können, um Informationen anzufordern oder bestimmte Aktionen auszuführen. 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 erfahren Sie, wie Sie Folgendes tun:
- Buchdaten von einer externen API-Quelle anfordern
- Extrahieren Sie Titel- und Autoreninformationen aus den zurückgegebenen Daten und schreiben Sie sie in Ihre Tabelle.
11. Externe Daten mit UrlFetch abrufen
Bevor Sie sich mit Code befassen, der direkt mit Ihrer Tabelle funktioniert, können Sie sich mit der Arbeit mit externen APIs in Apps Script vertraut machen, indem Sie eine Hilfsfunktion speziell zum Anfordern von Buchinformationen von der öffentlichen Open Library API erstellen.
Unsere Hilfsfunktion fetchBookData_(ISBN)
verwendet die 13-stellige ISBN eines Buchs als Parameter und gibt Daten zu diesem Buch zurück. Sie stellt eine Verbindung zur Open Library API her, ruft Informationen daraus ab und parst das zurückgegebene JSON-Objekt.
Implementierung
So implementieren Sie diese Hilfsfunktion:
- Fügen Sie im Apps Script-Editor am Ende des Skripts den folgenden Code 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];
}
- Speichern Sie Ihr Skriptprojekt.
Code Review
Dieser Code ist in zwei Hauptabschnitte unterteilt:
1. Die API-Anfrage
In den ersten beiden Zeilen stellt fetchBookData_(ISBN)
mithilfe des URL-Endpunkts der API und des URL Fetch Service von Apps Script eine Verbindung zur öffentlichen Open Library API her.
Die Variable url
ist nur ein URL-String, wie eine Webadresse. Er verweist auf einen Speicherort auf den Open Library-Servern. Sie enthält auch drei Parameter (bibkeys
, jscmd
und format
), die den Open Library-Servern mitteilen, welche Informationen Sie anfordern und wie die Antwort strukturiert werden soll. In diesem Fall geben Sie die ISBN des Buchs an und bitten darum, detaillierte Informationen im JSON-Format zurückzugeben.
Nachdem Sie den URL-String erstellt haben, sendet der Code eine Anfrage an den Standort und empfängt eine Antwort. Dazu verwenden Sie die Methode UrlFetchApp.fetch(url, params)
. Es wird eine Informationsanfrage an die von Ihnen angegebene externe URL gesendet und die resultierende Antwort in der Variablen response
gespeichert. Zusätzlich zur URL wird mit dem Code der optionale Parameter muteHttpExceptions
auf true
festgelegt. 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 Variablen response
gespeichert wird. HTTP-Antworten enthalten einen Antwortcode, HTTP-Header und den Hauptinhalt der Antwort. Die relevanten Informationen sind hier der Haupt-JSON-Inhalt. Der Code muss diesen extrahieren und dann das JSON 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 Methode HTTPResponse.getContentText()
den Hauptinhalt der Antwort als String zurück. Dieser String ist im JSON-Format, aber der genaue Inhalt und das genaue Format werden von der Open Library API definiert. Mit der Methode JSON.parse(jsonString)
wird der JSON-String in ein JavaScript-Objekt konvertiert, sodass verschiedene Teile der Daten einfach extrahiert werden können. Schließlich gibt die Funktion die Daten zurück, die der ISBN des Buchs entsprechen.
Ergebnisse
Nachdem Sie fetchBookData_(ISBN)
implementiert haben, können andere Funktionen in Ihrem Code Informationen zu jedem Buch anhand seiner ISBN finden. Sie verwenden diese Funktion, 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:
- Suchen Sie im aktiven Datenbereich nach den fehlenden Titel- und Autorendaten.
- Rufen Sie die fehlenden Daten eines bestimmten Buchs ab, indem Sie die Open Library API mit der Hilfsmethode
fetchBookData_(ISBN)
aufrufen. - Aktualisieren Sie die fehlenden Titel- oder Autorwerte in den entsprechenden Zellen.
Implementierung
So implementieren Sie diese neue Funktion:
- Fügen Sie im Apps Script-Editor am Ende Ihres Skriptprojekts den folgenden Code 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);
}
- Speichern Sie Ihr Skriptprojekt.
Code Review
Dieser Code ist in drei Abschnitte unterteilt:
1. Vorhandene Buchinformationen lesen
In den ersten drei Zeilen der Funktion werden Konstanten definiert, um den Code lesbarer zu machen. In den nächsten beiden Zeilen wird die Variable bookValues
verwendet, um eine lokale Kopie der Buchinformationen des Tabellenblatts zu speichern. Der Code liest Informationen aus bookValues
, verwendet die API, um fehlende Informationen zu ergänzen, und schreibt diese Werte zurück in die Tabelle.
2. Fehlende Informationen mit der Hilfsfunktion abrufen
Der Code durchläuft jede Zeile in bookValues
, um nach fehlenden Titeln oder Autoren zu suchen. Um die Anzahl der API-Aufrufe zu reduzieren und gleichzeitig die Effizienz zu steigern, wird die API nur aufgerufen, wenn die folgenden Bedingungen erfüllt sind:
- Die ISBN-Spalte der Zeile enthält einen Wert.
- Entweder ist die Zelle für den Titel oder den Autor in der Zeile 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
. Sie sollte jetzt die fehlenden Informationen enthalten, die Sie in das Tabellenblatt einfügen möchten.
Jetzt müssen wir nur noch die bookData
-Informationen in unsere Tabelle einfügen. Es gibt jedoch eine Einschränkung. Leider enthalten öffentliche APIs wie die Open Library Book API manchmal nicht die von Ihnen angeforderten Informationen oder es gibt ein anderes Problem, das die Bereitstellung der Informationen verhindert. Wenn Sie davon ausgehen, dass jede API-Anfrage erfolgreich ist, ist Ihr Code nicht robust 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 er versucht, sie zu verwenden. Sobald der Code bookData
hat, wird eine einfache Prüfung durchgeführt, um zu bestätigen, dass bookData
und bookData.details
vorhanden sind, bevor versucht wird, daraus zu lesen. Wenn eines der beiden fehlt, bedeutet das, dass die API nicht die gewünschten Daten hatte. In diesem Fall wird mit dem Befehl continue
angegeben, dass die Zeile übersprungen werden soll. Sie können die fehlenden Zellen nicht ausfüllen, aber Ihr Skript stürzt zumindest nicht ab.
3. Aktualisierte Informationen in das Tabellenblatt schreiben
Im letzten Teil des Codes werden ähnliche Prüfungen durchgeführt, um die von der API zurückgegebenen Titel- und Autoreninformationen zu überprüfen. Der Code aktualisiert das bookValues
-Array nur, wenn die ursprüngliche Zelle für Titel oder Autor leer ist und die API einen Wert zurückgegeben hat, den Sie dort einfügen können.
Die Schleife wird beendet, nachdem alle Zeilen im Tabellenblatt untersucht wurden. Im letzten Schritt wird das aktualisierte bookValues
-Array mit Range.setValues(values)
zurück in die Tabelle geschrieben.
Ergebnisse
Jetzt können Sie die Bereinigung Ihrer Buchdaten abschließen. Gehen Sie dazu so vor:
- Markieren Sie in der Tabelle den Bereich A2:A15 und wählen Sie Book-list > Separate title/author at first comma (Buchliste > Titel/Autor beim ersten Komma trennen) aus, um die Kommaprobleme zu beheben.
- Falls noch nicht geschehen, markieren Sie den Bereich A2:A15 in Ihrer Tabelle und wählen Sie Book-list > Separate title/author at last "by" (Buchliste > Titel/Autor beim letzten „by“ trennen) aus, um die Probleme mit „by“ zu beheben.
- Wenn Sie alle verbleibenden Zellen ausfüllen möchten, wählen Sie Buchliste > Leere Titel- und Autorzellen ausfüllen aus:
13. Fazit
Herzlichen Glückwunsch zum Abschluss dieses Codelabs. Sie haben gelernt, wie Sie benutzerdefinierte Menüs erstellen, um verschiedene Teile Ihres Apps Script-Codes zu aktivieren. Außerdem haben Sie gelernt, wie Sie Daten mithilfe von Apps Script-Diensten und öffentlichen APIs in Google Sheets importieren. Das ist ein häufiger Vorgang bei der Tabellenkalkulationsverarbeitung und mit Apps Script können Sie Daten aus einer Vielzahl von Quellen importieren. Schließlich haben Sie gesehen, wie Sie Apps Script-Dienste und JavaScript verwenden können, um Tabellendaten zu lesen, zu verarbeiten und einzufügen.
War dieses Codelab hilfreich?
Das haben Sie gelernt
- Daten aus einer Google-Tabelle importieren
- So erstellen Sie ein benutzerdefiniertes Menü in der Funktion
onOpen()
. - So parsen und bearbeiten Sie String-Datenwerte.
- Öffentliche APIs mit dem URL Fetch Service aufrufen
- So parsen Sie JSON-Objektdaten, die aus einer öffentlichen API-Quelle abgerufen wurden.
Nächste Schritte
Im nächsten Codelab in dieser Playlist wird genauer darauf eingegangen, wie Daten in einer Tabelle formatiert werden.
Das nächste Codelab finden Sie unter Datenformatierung.