Google Apps Script verwenden, um Google Sheets zu erweitern Benutzerdefinierte Menüs, Dialogfelder und Seitenleisten in Google Sheets hinzufügen Benutzerdefinierte Funktionen für Google Tabellen schreiben und in andere Google-Dienste wie Google Kalender, Google Drive und Gmail einbinden.
Die meisten für Google Tabellen entwickelten Scripts bearbeiten Arrays, um mit den Zellen, Zeilen und Spalten in einer Tabelle zu interagieren. Wenn Sie sich mit Arrays in JavaScript nicht auskennen, bietet Codecademy ein hervorragendes Trainingsmodul für Arrays. Dieser Kurs wurde nicht von Google entwickelt und steht in keiner Verbindung zu Google.
Eine kurze Einführung in die Verwendung von Apps Script mit Google Tabellen finden Sie in der 5‑Minuten-Kurzanleitung für Makros, Menüs und benutzerdefinierte Funktionen.
Jetzt starten
Apps Script enthält spezielle APIs zum programmatischen Erstellen, Lesen und Bearbeiten von Tabellen. Apps Script interagiert auf zwei Arten mit Google Sheets: Jedes Script kann eine Tabelle erstellen oder ändern, wenn der Nutzer des Scripts die entsprechenden Berechtigungen dafür hat. Außerdem kann ein Script an eine Tabelle gebunden werden. Gebundene Skripts haben besondere Möglichkeiten, die Benutzeroberfläche zu ändern oder zu reagieren, wenn die Tabelle geöffnet wird. Wenn Sie ein gebundenes Skript erstellen möchten, wählen Sie in Google Tabellen Erweiterungen > Apps Script aus.
Der Tabellendienst behandelt Google Tabellen als Raster und arbeitet mit zweidimensionalen Arrays. Um Daten aus der Tabelle abzurufen, müssen Sie auf die Tabelle zugreifen, in der die Daten gespeichert sind, den Bereich mit den Daten abrufen und dann die Werte der Zellen abrufen. Apps Script erleichtert den Datenzugriff, indem strukturierte Daten in der Tabelle gelesen und JavaScript-Objekte dafür erstellt werden.
Daten lesen
Angenommen, Sie haben eine Liste mit Produktnamen und Produktnummern, die Sie in einer Tabelle speichern, wie im folgenden Bild dargestellt.
Im folgenden Beispiel wird gezeigt, wie Produktnamen und Produktnummern abgerufen und protokolliert werden.
function logProductInfo() {
const sheet = SpreadsheetApp.getActiveSheet();
const data = sheet.getDataRange().getValues();
for (let i = 0; i < data.length; i++) {
Logger.log('Product name: ' + data[i][0]);
Logger.log('Product number: ' + data[i][1]);
}
}
Logs ansehen
Wenn Sie die protokollierten Daten aufrufen möchten, klicken Sie oben im Skripteditor auf Ausführungslog.
Daten schreiben
Wenn Sie Daten wie einen neuen Produktnamen und eine neue Produktnummer in der Tabelle speichern möchten, fügen Sie den folgenden Code am Ende des Skripts ein.
function addProduct() {
const sheet = SpreadsheetApp.getActiveSheet();
sheet.appendRow(['Cotton Sweatshirt XL', 'css004']);
}
Mit dem oben stehenden Code wird eine neue Zeile mit den angegebenen Werten unten in die Tabelle eingefügt. Wenn Sie diese Funktion ausführen, wird der Tabelle eine neue Zeile hinzugefügt.
Benutzerdefinierte Menüs und Benutzeroberflächen
Sie können Google Tabellen anpassen, indem Sie benutzerdefinierte Menüs, Dialogfelder und Seitenleisten hinzufügen. Die Grundlagen zum Erstellen von Menüs finden Sie im Leitfaden zu Menüs. Informationen zum Anpassen des Inhalts eines Dialogfelds finden Sie im Leitfaden zum HTML-Dienst.
Sie können eine Scriptfunktion an ein Bild oder eine Zeichnung in einer Tabelle anhängen. Die Funktion wird ausgeführt, wenn ein Nutzer auf das Bild oder die Zeichnung klickt. Weitere Informationen
Wenn Sie Ihre benutzerdefinierte Benutzeroberfläche als Teil eines Add-ons veröffentlichen möchten, folgen Sie dem Styleguide, um die Konsistenz mit dem Stil und Layout des Sheets-Editors zu wahren.
Mit Google Formulare verbinden
Verbinden Sie Google Formulare mit Google Sheets über die Dienste Formulare und Tabellenkalkulation. Mit dieser Funktion wird automatisch ein Google-Formular auf Grundlage der Daten in einer Tabelle erstellt.
Mit Apps Script können Sie auch Trigger wie onFormSubmit verwenden, um eine bestimmte Aktion auszuführen, nachdem ein Nutzer auf das Formular geantwortet hat. Kurzanleitung zum Verwalten von Antworten für Formulare (5 Minuten)
Daten formatieren
Die Klasse Range hat Methoden wie setBackground, mit denen Sie auf das Format einer Zelle oder eines Zellenbereichs zugreifen und es ändern können. Im folgenden Beispiel wird der Schriftstil eines Bereichs festgelegt:
function formatMySpreadsheet() {
// Set the font style of the cells in the range of B2:C2 to be italic.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const cell = sheet.getRange('B2:C2');
cell.setFontStyle('italic');
}
Datenvalidierung
Sie können in Google Tabellen auf vorhandene Datenvalidierungsregeln zugreifen oder neue Regeln erstellen. Im folgenden Beispiel wird gezeigt, wie Sie eine Regel zur Datenvalidierung festlegen, die in einer Zelle nur Zahlen zwischen 1 und 100 zulässt.
function validateMySpreadsheet() {
// Set a rule for the cell B4 to be a number between 1 and 100.
const cell = SpreadsheetApp.getActive().getRange('B4');
const rule = SpreadsheetApp.newDataValidation()
.requireNumberBetween(1, 100)
.setAllowInvalid(false)
.setHelpText('Number must be between 1 and 100.')
.build();
cell.setDataValidation(rule);
}
Weitere Informationen zum Arbeiten mit Regeln zur Datenvalidierung finden Sie unter SpreadsheetApp.newDataValidation, DataValidationBuilder und Range.setDataValidation.
Diagramme
Diagramme in eine Tabelle einbetten, die die Daten in einem bestimmten Bereich darstellen Im folgenden Beispiel wird ein eingebettetes Balkendiagramm generiert. Dabei wird davon ausgegangen, dass Sie in den Zellen A1:B15 Daten haben, die in einem Diagramm dargestellt werden können:
function newChart() {
// Generate a chart representing the data in the range of A1:B15.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheets()[0];
const chart = sheet.newChart()
.setChartType(Charts.ChartType.BAR)
.addRange(sheet.getRange('A1:B15'))
.setPosition(5, 5, 0, 0)
.build();
sheet.insertChart(chart);
}
Weitere Informationen zum Einbetten eines Diagramms in Ihre Tabelle finden Sie unter EmbeddedChart und in den Artikeln zu bestimmten Diagramm-Buildern, z. B. EmbeddedPieChartBuilder.
Benutzerdefinierte Funktionen in Google Sheets
Eine benutzerdefinierte Funktion ähnelt einer integrierten Tabellenkalkulationsfunktion wie =SUM(A1:A5). Der Unterschied besteht darin, dass Sie das Verhalten der Funktion mit Apps Script definieren. Sie können beispielsweise eine benutzerdefinierte Funktion in2mm() erstellen, die einen Wert von Zoll in Millimeter umwandelt. Anschließend können Sie die Formel in Ihrer Tabelle verwenden, indem Sie =in2mm(A1) oder =in2mm(10) in eine Zelle eingeben.
Weitere Informationen zu benutzerdefinierten Funktionen finden Sie im 5‑Minuten-Schnellstart zu Menüs und benutzerdefinierten Funktionen oder im ausführlicheren Leitfaden zu benutzerdefinierten Funktionen.
Makros
Makros sind eine weitere Möglichkeit, Apps Script-Code über die Benutzeroberfläche von Google Sheets auszuführen. Im Gegensatz zu benutzerdefinierten Funktionen werden sie über ein Tastenkürzel oder das Google Sheets-Menü aktiviert. Weitere Informationen finden Sie unter Sheets-Makros.
Add-ons für Google Sheets
Add‑ons sind speziell verpackte Apps Script-Projekte, die in Google Tabellen ausgeführt werden und aus dem Google Tabellen-Add‑on-Store installiert werden können. Wenn Sie ein Script für Google Tabellen entwickelt haben und es freigeben möchten, können Sie es mit Apps Script als Add-on veröffentlichen, damit andere Nutzer es installieren können.
Leistung und Skalierung
Wenn Ihre Datasets größer werden, kann es zu Leistungsproblemen kommen. So optimieren Sie Ihre Tabellen und Skripts:
- Best Practices befolgen: Im Leitfaden zu Best Practices finden Sie Tipps zur Minimierung von Dienstaufrufen und zur Verwendung von Batchvorgängen.
- Formeln optimieren: Wenn Ihre Tabelle aufgrund komplexer Formeln (z. B.
VLOOKUP,ARRAYFORMULAoderIMPORTRANGE) langsam ist, können Sie diese Berechnungen mit Apps Script im Arbeitsspeicher ausführen und die Ergebnisse in Batches zurückschreiben. - Datenbankalternativen in Betracht ziehen: Bei sehr großen Datasets (bis zu 10 Millionen Zellen) oder bei der Eingabe von Daten mit hoher Häufigkeit (z.B. viele verbundene Formulare) sollten Sie Google Cloud SQL mit JDBC oder BigQuery verwenden.
Trigger
Skripts, die an eine Google-Tabellen-Datei gebunden sind, können einfache Trigger wie die Funktionen onOpen() und onEdit() verwenden, um automatisch zu reagieren, wenn ein Nutzer mit Bearbeitungszugriff auf die Tabelle diese öffnet oder bearbeitet.
Wie einfache Trigger ermöglichen installierbare Trigger, dass in Google Tabellen eine Funktion automatisch ausgeführt wird, wenn ein bestimmtes Ereignis eintritt.
Installierbare Trigger bieten jedoch mehr Flexibilität als einfache Trigger und unterstützen die folgenden Ereignisse: „open“, „edit“, „change“, „form submit“ und „time-driven“ (clock).