Apps Script-Grundlagen mit Google Sheets – Teil 1: Makros und benutzerdefinierte Funktionen

1. Willkommen bei Apps Script

Was ist Apps Script?

Apps Script ist eine Plattform zur schnellen Anwendungsentwicklung, mit der Sie Google Workspace automatisieren, anpassen und erweitern können. Mit Apps Script können Sie Zeit und Mühe sparen, indem Sie mühsame oder komplexe Aufgaben in Google Workspace optimieren.

Apps Script-Funktionen:

• Mit den integrierten Diensten von Apps Script können Sie Ihre Google Workspace-Anwendungsdaten mit Skripts lesen, aktualisieren und bearbeiten.
• Sie können Skripts mit dem In-Browser-Code-Editor von Apps Script erstellen. Es ist nicht erforderlich, eine Software zur Codeentwicklung zu installieren oder auszuführen.
• Sie können Benutzeroberflächen für Google Workspace-Editoren entwerfen, mit denen Sie Skripts direkt über Menüelemente, Dialogfelder und Seitenleisten aktivieren können.

In dieser Codelab-Playlist zu den Grundlagen von Apps Script mit Google Tabellen werden die Grundlagen von Apps Script und die Verwendung von Apps Script zur Verbesserung der Nutzung von Google Tabellen vermittelt. In diesem Codelab werden die Grundlagen von Apps Script vermittelt.

Der Tabellendienst

Mit Apps Script können Sie Google Tabellen erweitern, um Zeit und Aufwand zu sparen. Apps Script bietet den Tabellendienst, mit dem Skripts mit Ihren Google-Tabellen und den darin enthaltenen Daten interagieren können. Mit diesem Dienst können Sie die folgenden gängigen Tabellenkalkulationsaufgaben automatisieren:

• Tabellen erstellen oder ändern
• Zellendaten, Formeln und Formatierung lesen und aktualisieren.
• Benutzerdefinierte Schaltflächen und Menüs erstellen
• Daten aus anderen Google-Anwendungen oder Drittanbieterquellen importieren und exportieren.
• Tabellen freigeben und den Zugriff darauf steuern

Lerninhalte

In dieser Playlist werden alle Themen behandelt, die Sie für den Einstieg in Apps Script mit Google Tabellen benötigen:

1. Makros und benutzerdefinierte Funktionen
2. Tabellen, Tabellenblätter und Bereiche
3. Mit Daten arbeiten
4. Datenformatierung
5. Daten in Google Präsentationen in Diagrammen darstellen

Die Codelabs in dieser Playlist sollten in der richtigen Reihenfolge gelesen werden. Beginnen Sie also mit diesem Codelab und arbeiten Sie sie der Reihe nach durch, um den größtmöglichen Lerneffekt zu erzielen.

Im nächsten Abschnitt erfahren Sie mehr über den Inhalt dieses Codelabs.

2. Einführung

Willkommen zum ersten Codelab dieser Playlist. In diesem Codelab lernen Sie die Grundlagen der Verwendung von Apps Script mit Google Sheets kennen. In diesem Codelab geht es insbesondere um zwei wichtige Konzepte: Makros und benutzerdefinierte Funktionen.

Ein Makro ist eine Reihe von aufgezeichneten Aktionen in Google Sheets. Nach der Aufzeichnung können Sie ein Makro aktivieren, um diese Aktionen später über ein Menüelement oder eine Tastenkombination zu wiederholen. Sie können sowohl in Google Sheets als auch im Apps Script-Codeeditor eigene Makros erstellen und aktualisieren.

Im Apps Script-Codeeditor können Sie auch benutzerdefinierte Funktionen erstellen. Ähnlich wie bei den integrierten Funktionen, die in Google Tabellen verfügbar sind (z. B. SUM oder AVERAGE), können Sie mit Apps Script eigene benutzerdefinierte Funktionen für einfache und spezielle Vorgänge (z. B. Konvertierungen oder Stringverkettungen) schreiben. Nachdem Sie die Funktionen erstellt haben, können Sie sie in Google Tabellen wie eine integrierte Funktion aufrufen. Benutzerdefinierte Funktionen können auch in Zellformeln verwendet werden, die Sie schreiben. Bei Bedarf können Sie sie mit anderen Funktionen kombinieren.

Unten erfahren Sie, welche Konzepte und Anforderungen in diesem Codelab behandelt werden.

Lerninhalte

• So erstellen Sie ein Skript für Google Tabellen.
• So navigieren Sie im Apps Script-Editor.
• So erstellen und aktualisieren Sie Makros.
• So erstellen Sie Ihre erste benutzerdefinierte Funktion für Google Sheets.

Voraussetzungen

• Grundlegende Kenntnisse in JavaScript
• Grundlegende Kenntnisse von Google Sheets
• Kann A1-Notation in Google Sheets lesen

Hinweis:Apps Script basiert auf JavaScript. Wenn Sie noch nicht mit JavaScript vertraut sind, bietet Codecademy einen kostenlosen Einführungskurs in JavaScript an. Dieser Kurs wurde zwar nicht von Google entwickelt, sollte aber alle wichtigen Informationen enthalten.

Sie haben die Einführungen abgeschlossen. Im nächsten Abschnitt erfahren Sie, wie Sie mit Makros arbeiten.

3. Makro in Google Sheets erstellen

Bei der Arbeit mit Tabellenkalkulationen kann es vorkommen, dass Sie sich in einer Schleife sich wiederholender Aktionen befinden, z. B. beim Kopieren von Zellwerten, Formatieren oder Erstellen von Formeln. Das kann mühsam sein und zu Fehlern führen. Um wiederholte Aktionen zu automatisieren, bietet Google Tabellen Makros. Mit Makros können Sie eine Reihe von Aktionen in einem Tabellenblatt „aufzeichnen“. Mit einem aufgezeichneten Makro können Sie dieselben Aktionen an anderer Stelle in einer Tabelle mit einem einfachen Tastenkürzel wiederholen.

In diesem Abschnitt erfahren Sie, wie Sie ein Makro in Google Sheets erstellen. Im nächsten Abschnitt erfahren Sie, wie Makros mit Apps Script erstellt werden.

Hinweis

Bevor Sie fortfahren, benötigen Sie eine Tabelle mit Daten. Wir haben eines für Sie bereitgestellt: Klicken Sie auf diesen Link, um das Datenblatt zu kopieren, und klicken Sie dann auf Kopie erstellen.

Eine Kopie der Beispieltabellenkalkulation zur Verwendung wird in Ihrem Google Drive-Ordner mit dem Namen „Copy of Top 10 Highest Grossing Films (2018)“ (Kopie der 10 erfolgreichsten Filme von 2018) gespeichert.

Makro erstellen

Nachdem Sie nun eine Tabelle haben, mit der Sie arbeiten können, können Sie ein Makro in Google Sheets aufzeichnen. In diesem Beispiel erstellen Sie ein Makro, mit dem eine Kopfzeile für Ihre Daten formatiert wird. Kontobudget:

1. Klicken Sie auf Zelle A1, um den Cursor in die Zeile zu setzen. Das ist Ihre Kopfzeile.
2. Wählen Sie im Menü Erweiterungen> Makros> Makros aufzeichnen aus.

Sobald Sie die Aufzeichnung starten, werden alle Aktionen, die Sie in der Tabelle ausführen, in Google Tabellen gespeichert: Zellen markieren, Daten hinzufügen, zu anderen Tabellenblättern wechseln, formatieren usw. Diese Aktionen werden zum „Script“, das wiederholt wird, wenn Sie das Makro später speichern und aktivieren.

3. Wählen Sie im Dialogfeld „Makro“ die Option Relativer Bezug aus.

Schlüsselbegriffe:In Makros können zwei Arten von Bezügen verwendet werden, um in Google Tabellen auf bestimmte Zellen zu verweisen. Absolute Verweise sind an die genauen aufgezeichneten Positionen gebunden (z. B. Zelle A5), während relative Verweise auf die aktuelle Auswahl des Nutzers als Ausgangspunkt angewendet werden (z. B. die vierte Zelle unter der aktuellen Auswahl).

In diesem Codelab sollen Sie relative Verweise für Ihre Makros verwenden.

4. Wählen Sie Zeile 1 aus.

5. Ändern Sie die Füllfarbe der oberen Zeile von Weiß in Dunkelmagenta 3.

6. Ändern Sie die Textfarbe der oberen Zeile von Schwarz in Weiß.

7. Um den Text fett zu formatieren, drücken Sie Strg + B (oder Cmd + B auf macOS).
8. Um die obere Zeile zu fixieren, wählen Sie Ansicht > Fixieren > 1 Zeile aus.

9. Klicken Sie im Makro-Dialogfeld auf Speichern. In einem neuen Dialogfeld werden Sie aufgefordert, das Makro zu benennen. Geben Sie den Namen „Kopfzeile“ ein und klicken Sie auf Speichern.

Sie haben über die Benutzeroberfläche von Google Tabellen ein Makro erstellt, das speziell für die Formatierung von Headern entwickelt wurde.

Makro aktivieren

So wenden Sie Ihr neues Makro in Google Sheets an:

1. Klicken Sie auf „Tabelle hinzufügen“ , um eine Tabelle zu erstellen.

2. Fügen Sie dem neuen Tab etwas Text in die Zellen A1:C2 hinzu. Hier sind einige Beispiele für Eingaben:

3. Markieren Sie die erste Zeile.

4. Wenn Sie das Makro auf den ausgewählten Bereich anwenden möchten, klicken Sie auf Erweiterungen> Makros> Kopfzeile.
5. Autorisieren Sie das Makro, indem Sie der Anleitung auf dem Bildschirm folgen.

Hinweis:Wenn Sie ein gmail.com-Konto verwenden, wird beim ersten Ausführen des Skripts möglicherweise auch ein Dialogfeld für nicht bestätigte Apps angezeigt. Google verwendet diese Informationen, um Nutzer zu warnen, die möglicherweise Code von unbekannten oder nicht vertrauenswürdigen Autoren verwenden. Wenn dieses Dialogfeld angezeigt wird, können Sie fortfahren, da Sie der Autor des Skripts sind. Folgen Sie der Anleitung auf dem Bildschirm, um das Skript weiterhin zu autorisieren. Der Vorgang, bei dem diese Berechtigung eingeholt wird, wird als Autorisierung bezeichnet.

6. Wiederholen Sie Schritt 4, um das Makro noch einmal auszuführen. Durch die Autorisierung wird die erste Ausführung beendet.

Herzlichen Glückwunsch! Sie haben gelernt, wie Sie Makros in Google Sheets anwenden. Ihre Tabelle sollte so aussehen:

Mit Makros können Sie Tabellen effizient erstellen. Im nächsten Teil dieses Codelabs erfahren Sie, wie Sie Ihre Makros noch leistungsfähiger machen können. Das Geheimnis: Wenn Sie ein Makro aufzeichnen, schreiben Sie eigentlich Apps Script-Code. Im Hintergrund wird in Google Tabellen der Code erstellt, der den Makroaktionen entspricht. Im nächsten Abschnitt erfahren Sie, wie Sie den Code direkt mit dem In-Browser-Editor von Apps Script ändern.

4. Makros im Script-Editor

Wenn Sie ein Makro erstellen, werden Ihre Aktionen in Google Sheets als Apps Script-Funktion gespeichert. Wenn Sie das Makro aktivieren, ruft Google Sheets die Apps Script-Funktion auf, um diese Aktionen in derselben Reihenfolge auszuführen.

Der Script-Editor

Nachdem Sie ein Makro erstellt haben, können Sie sich den Code ansehen. Wenn Sie das Makroskript aufrufen möchten, klicken Sie auf Erweiterungen> Apps Script, um den Code-Editor für Apps Script im Browser zu öffnen.

Mit dem Script-Editor können Sie Code in Apps Script schreiben und diese Scripts auf Google-Servern ausführen.

Hinweis:Die Makros und Skripts, die Sie in diesem Codelab erstellen, sind an die Google-Tabellen-Datei angehängt, in der sie ausgeführt werden. Sie können jederzeit über das Menü Erweiterungen > Apps Script in Google Sheets darauf zugreifen. Scripts, die auf diese Weise an ein Google-Tabellenblatt angehängt werden, werden als containergebunden bezeichnet.

Analyse von macros.gs

Überprüfen Sie das aktuelle Skript. In Google Sheets wurde die Skriptdatei macros.gs erstellt, als Sie das Makro Header aufgezeichnet haben. Sie enthält eine entsprechende Apps Script-Funktion mit dem Namen Header. Wenn Sie das Makro Header aktivieren, wird diese Funktion in Google Sheets ausgeführt.

Das Bild unten zeigt die Struktur Ihrer Makrofunktion in Apps Script. Wenn Sie die Schritte in einer anderen Reihenfolge aufgezeichnet oder während der Aufzeichnung in der Tabelle geklickt haben, sieht Ihr Code möglicherweise etwas anders aus.

Die erste Zeile ist ein Anmerkungskommentar, der sich auf die Autorisierung auswirkt:

/** @OnlyCurrentDoc */

Die meisten Skripts fragen den Nutzer vor der Ausführung nach einigen Berechtigungen. Mit diesen Berechtigungen wird festgelegt, was das Skript tun darf. Wenn der Kommentar @OnlyCurrentDoc in einem Skriptprojekt vorhanden ist, fragt Apps Script nur nach der Berechtigung, auf die aktuelle Tabelle zuzugreifen und sie zu aktualisieren. Ohne diesen Kommentar würde Apps Script die Berechtigung zum Zugriff auf alle Tabellen des Nutzers und zum Aktualisieren dieser Tabellen anfordern. Es empfiehlt sich immer, diese Anmerkung einzufügen, wenn Sie nur mit einer einzelnen Datei arbeiten. Der Makrorekorder fügt diesen Kommentar automatisch für Sie hinzu.

Um zu verstehen, wie Apps Script die Anweisungen Ihres Makros darstellt, können Sie sich die Funktion ansehen:

function Header(){
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, sheet.getMaxColumns()).activate();
  spreadsheet.getActiveRangeList().setBackground('#4c1130')
  .setFontColor('#ffffff')
  .setFontWeight('bold');
  spreadsheet.getActiveSheet().setFrozenRows(1);
};

Dieser Code wird ausgeführt, wenn Sie das Makro Header aktivieren. Nach function wird mit dem Label Header() der Name der Funktion und ihre Parameter definiert. Beachten Sie, dass für Header() keine Parameter erforderlich sind, da für Makrofunktionen in Apps Script keine Eingaben erforderlich sind. Die geschweiften Klammern umschließen immer den Hauptteil einer Funktion in Apps Script.

In späteren Codelabs in dieser Playlist werden die Klassen und Konzepte erläutert, die für die Erstellung des Makros erforderlich sind. Sehen Sie sich vorerst die folgenden Codebeschreibungen an, um einen allgemeinen Überblick über die Komponenten und ihre Rolle beim Erstellen Ihres Makros zu erhalten. Sehen Sie sich die erste Zeile an:

var spreadsheet = SpreadsheetApp.getActive();

Hier gibt getActive() ein Objekt zurück, das die aktuelle aktive Tabellendatei in Google Tabellen darstellt, und weist es der neuen Variablen spreadsheet zu.

var sheet = spreadsheet.getActiveSheet();
sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, sheet.getMaxColumns()).activate();

Diese Zeilen entsprechen dem Klicken auf die erste Zeile, um sie zu markieren. Das wird als Aktivierung bezeichnet. In der ersten Zeile wird das aktuelle Tabellenblatt in der Variablen sheet gespeichert. In der zweiten Zeile wird die gesamte erste Zeile mit der Methode getRange() abgerufen und dann wird activate() aufgerufen, um sie zu aktivieren. Die erste Zeile wird mit den entsprechenden Zeilen- und Spaltennummern angegeben. Der spreadsheet.getCurrentCell().getRow()-Aufruf gibt die Nummer der aktuellen Zeile zurück, während sheet.getMaxColumns() die maximale Anzahl von Spalten im Tabellenblatt zurückgibt.

spreadsheet.getActiveRangeList().setBackground('#4c1130')
.setFontColor('#ffffff')
.setFontWeight('bold');

Dieser Codeabschnitt ist etwas komplexer. Um Methoden mit spreadsheet effizient aufzurufen, werden im Code drei Methoden auf getActiveRangeList() gestapelt, damit die spreadsheet-Methode nicht mehr als einmal aufgerufen wird. Wenn Sie mehr mit Apps Script programmieren, werden Sie mit dieser Konvention, mehrere Methoden für eine Klasse aufzurufen (auch als Methodenverkettung bezeichnet), vertrauter. Vorläufig finden Sie im Codeblock kurze Erläuterungen zu den einzelnen Methoden:

• getActiveRangeList() gibt die aktuelle aktive RangeList in spreadsheet zurück. In diesem Fall ist es einfach die erste Zeile, die durch die vorherige Zeile aktiviert wurde.
• Sowohl die Methode setBackground(color) als auch die Methode setFontColor(color) ändern die Farbattribute der Zellen im aktiven Bereich.
• Mit setFontWeight(fontWeight) wird die Schriftstärke für Zellen im aktiven Bereich angepasst.

In der letzten Zeile wird die erste Zeile des Makros fixiert:

spreadsheet.getActiveSheet().setFrozenRows(1);

Das ist das Skript, das Sie beim Aufzeichnen des Makros generiert haben. Sie müssen sich keine Sorgen machen, wenn Ihnen einige der oben genannten Begriffe oder Methoden nicht vertraut sind. Die Beschreibung soll Ihnen einige der Ideen näherbringen, auf die sich Apps Script in einer typischen Makrofunktion konzentriert, und die Themen, die in zukünftigen Codelabs behandelt werden.

Im nächsten Abschnitt geht es darum, den Code der Funktion Header() zu bearbeiten, um zu zeigen, wie Sie Makros mit dem Skripteditor weiter personalisieren können.

Makros mit Apps Script anpassen

Im Apps Script-Editor wird das Makro angezeigt, das Sie zuvor in Google Sheets erstellt haben. Durch Anpassen des Inhalts des Funktionsrumpfs können Sie die Anweisungen Ihres Makros weiter anpassen, um andere oder zusätzliche Aktionen auszuführen. In den folgenden Übungen wird gezeigt, wie Sie Makros mit dem Skripteditor bearbeiten können.

Betroffene Zellen ändern

Angenommen, Sie möchten Ihr Makro so ändern, dass es sich nur auf die ersten 10 Spalten der ersten Zeile anstatt auf die gesamte Zeile auswirkt. Sie können das Makro löschen und neu aufzeichnen. Mit dem Apps Script-Editor können Sie diese Änderungen jedoch direkt vornehmen. So geht es:

1. Ersetzen Sie im Script-Editor sheet.getMaxColumns() durch 10. Durch diese Änderung wird der Bereich der Zellen geändert, auf die sich das Makro in der Tabelle auswirkt.

/** @OnlyCurrentDoc */

function Header(){
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, 10).activate();
    /* sheet.getMaxColumns() replaced with 10.*/
  spreadsheet.getActiveRangeList().setBackground('#4c1130')
  .setFontColor('#ffffff')
  .setFontWeight('bold');
  spreadsheet.getActiveSheet().setFrozenRows(1);
}

2. Klicken Sie auf „Speichern“ , um das Script zu speichern.
3. Wenn Sie Ihr Projekt umbenennen möchten, geben Sie „Makros und benutzerdefinierte Funktionen“ als neuen Projektnamen ein und klicken Sie auf Umbenennen.
4. Klicken Sie in Google Tabellen auf „Tabelle hinzufügen“ , um ein Tabellenblatt zu erstellen.

5. Wählen Sie im Skripteditor in der Funktionsliste Header aus und klicken Sie auf Ausführen.

In der neuen Tabelle sollte das folgende Ergebnis angezeigt werden:

Wenn Sie den aktiven oder Zielbereich ändern, wirkt sich das Makro nur noch auf einen Teil der ersten Zeile aus. Viele Apps Script-Methoden verwenden einen Bereich oder die A1-Notation als Parameter, um anzugeben, auf welche Zellen sich die Aktion bezieht.

Als Nächstes erfahren Sie, wie Sie die Farben Ihrer Makros anpassen.

Farben des Makros ändern

Mit Apps Script können Sie die Füll- oder Textfarbe eines Bereichs ändern, um das Farbschema von Makros oder anderen Elementen in Google Sheets zu gestalten. In der folgenden Anleitung erfahren Sie, wie Sie die Farben Ihres Makros anpassen können.

In dieser Anleitung geht es darum, die Hintergrundfarbe Ihres Makros zu ändern:

1. Wechseln Sie in Google Tabellen zurück zum Tabellenblatt mit den Originaldaten (Tabellenblatt 1).
2. Klicken Sie auf die erste Zeile, um sie zu markieren.
3. Ersetzen Sie im Skripteditor die Hintergrundfarbe #4c1130 durch #afeeee. Diese Werte stellen verschiedene Farben in Hex-Triplett-Notation dar.

/** @OnlyCurrentDoc */

function Header(){
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, 10).activate();
  spreadsheet.getActiveRangeList().setBackground('#afeeee')
    /* #4c1130 replaced with #afeeee.*/
  .setFontColor('#ffffff')
  .setFontWeight('bold');
  spreadsheet.getActiveSheet().setFrozenRows(1);
}

4. Klicken Sie auf „Speichern“ , um das Script zu speichern.
5. Wählen Sie in der Funktionsliste Header aus und klicken Sie auf Ausführen.

In Google Tabellen wird die Hintergrundfarbe der ersten 10 Spalten in der ersten Zeile in ein benutzerdefiniertes Türkis geändert:

Wenn Sie die Hex-Farbnotation in den Parametern von setBackground(color) von #4c1130 (dunkles Magenta 3) zu #afeeee (helles Türkis, eine Option, die im Standardfarbmenü von Google Tabellen nicht verfügbar ist) ändern, ändern Sie das Farbattribut der Hintergrundfarbe des Makros.

Sie haben nun die vom Makro festgelegte Hintergrundfarbe geändert. Wenn Sie auch die Textfarbe ändern möchten, ändern Sie den zweiten Farbcode.

1. Klicken Sie in Google Tabellen auf die erste Zeile, um sicherzugehen, dass sie weiterhin markiert ist.
2. Ersetzen Sie im Skripteditor die Schriftfarbe #ffffff durch #191970. Dadurch wird im Makro die Schriftfarbe Marineblau festgelegt.

/** @OnlyCurrentDoc */

function Header(){
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(
    spreadsheet.getCurrentCell().getRow(),
    1, 1, 10).activate();
  spreadsheet.getActiveRangeList().setBackground('#afeeee')
  .setFontColor('#191970')/* #ffffff replaced with #191970.*/
  .setFontWeight('bold');
  spreadsheet.getActiveSheet().setFrozenRows(1);
}

3. Klicken Sie auf „Speichern“ , um das Script zu speichern.
4. Wählen Sie in der Funktionsliste Header aus und klicken Sie auf Ausführen.

Kehren Sie zu Google Tabellen zurück. Die Textfarbe der Kopfzeile ist jetzt dunkelblau.

Sie haben jetzt gesehen, wie Makros funktionieren: Sheets-Aktionen werden als Apps Script-Code aufgezeichnet. Im nächsten Abschnitt erfahren Sie, wie Apps Script Ihnen bei der Arbeit mit Google Sheets helfen kann: benutzerdefinierte Funktionen.

5. Erstes Skript schreiben: Benutzerdefinierte Funktionen

Wie die meisten Tabellenkalkulationsanwendungen bietet Google Sheets mehrere integrierte Formelfunktionen wie =SUM(), mit denen sich Tabellendaten schnell berechnen lassen. Benutzerdefinierte Funktionen sind Funktionen, die Sie mit Apps Script angeben. Nachdem Sie eine benutzerdefinierte Funktion definiert haben, können Sie sie überall in Ihrer Tabelle verwenden, genau wie eine integrierte Funktion.

In diesem Abschnitt erfahren Sie, wie Sie in Apps Script eine benutzerdefinierte Funktion erstellen, die eine Währungsumrechnung durchführt.

Skriptdatei erstellen

Gehen Sie so vor, um ein neues Skript zu erstellen, das Sie später für Ihre erste benutzerdefinierte Funktion verwenden können. Verwenden Sie dazu dieselbe Tabelle und dasselbe Skriptprojekt aus dem Abschnitt „Makros“:

1. Wenn Sie eine Apps Script-Datei erstellen möchten, kehren Sie zum Scripteditor zurück.
2. Klicken Sie neben Dateien auf „Datei hinzufügen“  > Script.
3. Geben Sie der neuen Scriptdatei den Namen customFunctions und drücken Sie die Eingabetaste. (Apps Script fügt dem Skriptdateinamen automatisch die Erweiterung .gs hinzu.)

Im Editor wird ein neuer Tab mit dem Namen customFunctions.gs geöffnet.

Nachdem Sie ein Skript speziell für benutzerdefinierte Funktionen erstellt haben, können Sie es mit Code füllen.

US-Dollar in Schweizer Franken umrechnen

Angenommen, Sie möchten die Daten für „Top 10 Highest Grossing Films 2018“ so ändern, dass nicht nur die weltweiten Bruttoeinnahmen in US-Dollar, sondern auch in Schweizer Franken angezeigt werden. Mit benutzerdefinierten Funktionen ist das ganz einfach. In der folgenden Übung wird gezeigt, wie Sie eine benutzerdefinierte Funktion erstellen, um Ihre Dollarwerte mathematisch in Franc-Werte umzuwandeln.

Bevor Sie Ihre erste benutzerdefinierte Funktion schreiben können, müssen Sie Ihr Dataset so ändern, dass die Funktion eine korrekte Ausgabe erzeugen kann. Gehen Sie dazu so vor:

1. Klicken Sie in Google Tabellen mit der rechten Maustaste auf Spalte H.
2. Klicken Sie im angezeigten Menü auf 1 Spalte rechts einfügen.

3. Geben Sie in Zelle I1 die Spaltenüberschrift „Worldwide_Gross (Swiss francs)“ ein.

Sie haben jetzt eine Spalte, in der die Ergebnisse Ihrer benutzerdefinierten Conversion-Funktion gespeichert werden können. Als Nächstes können Sie mit dem Skripteditor Ihre erste benutzerdefinierte Funktion erstellen.

4. Ersetzen Sie in customFunctions.gs den Code für myFunction() durch den folgenden Code:

/**
 * Converts US dollars to Swiss francs.
 *
 * @param {number} dollars The total number of dollars.
 * @return {number} swissFrancs The converted total of Swiss francs.
 * @customfunction
 */
function USDTOCHF(dollars){
  var swissFrancs = dollars * .99; 
  return swissFrancs;
}

Dieser Code konvertiert US-Dollar in Schweizer Franken. Folgen Sie der Anleitung unten, um eine benutzerdefinierte Funktion in Google Sheets auszuführen.

5. Klicken Sie auf „Speichern“ , um das Script zu speichern.
6. Wählen Sie in Google Sheets die Zelle I2 aus.
7. Geben Sie in der Funktionsleiste =USDTOCHF(H2) ein.

So wenden Sie die Formel auf die restlichen Zellen in der Spalte an:

8. Bewegen Sie den Mauszeiger in die untere rechte Ecke der Zelle I2 und wählen Sie das kleine blaue Kästchen aus. Der Mauszeiger sollte sich in  verwandeln, wenn Sie auf das blaue Kästchen zeigen.
9. Ziehen Sie das blaue Kästchen nach unten, um den Bereich I3:I11 zu markieren.

In Spalte I sind jetzt die in Schweizer Franken umgerechneten US-Dollar-Werte aus Spalte H aufgeführt.

Herzlichen Glückwunsch, Sie haben Ihre erste benutzerdefinierte Funktion erstellt. Im nächsten Abschnitt wird der Code erläutert, aus dem USDTOCHF() besteht.

Analyse von USDTOCHF()

In den ersten Kommentaren wird der Zweck des Codes beschrieben:

/**
 * Converts US dollars to Swiss francs.
 *
 * @param {number} dollars The total number of dollars.
 * @return {number} swissFrancs The provided value in Swiss francs.
 * @customfunction
 */

Kommentarblöcke wie dieser werden häufig in der Programmierung verwendet, um zu erklären, was Funktionen tun.

In diesem Kommentar sind zwei Teile zu sehen: die Funktionsbeschreibung (zum Umrechnen von Dollar in Franken) und Anmerkungen, die die Parameter und den Rückgabetyp der Funktion beschreiben.

Mit den Anmerkungen nutzt Apps Script JSDoc, um Ihnen beim Dokumentieren und Erstellen von Autovervollständigungshinweisen für Ihren Code zu helfen. Im Folgenden erfahren Sie, wie die einzelnen Anmerkungen in USDTOCHF() Sie bei der Entwicklung von Apps Script unterstützen:

• @param: Mit der Annotation @param können Sie jeden Parameter beschreiben, der an die Funktion übergeben wird.
• @return: Mit der Annotation @return können Sie beschreiben, was die Funktion zurückgibt.
• @customfunction: Sie sollten @customfunction immer im Dokumentationskommentar einer benutzerdefinierten Funktion hinzufügen. Mit dieser Anmerkung wird Google Sheets angewiesen, Ihre benutzerdefinierte Funktion automatisch zu vervollständigen, so wie Google Sheets integrierte Funktionen automatisch vervollständigt, wenn Sie einen Funktionsnamen in eine Zelle eingeben (siehe unten):

Der Text, der im Pop-up-Fenster für die automatische Vervollständigung angezeigt wird, stimmt genau mit dem Beschreibungstext überein, den Sie in den Kommentarblock eingefügt haben. Sie können die Verwendung Ihrer benutzerdefinierten Funktionen erleichtern, indem Sie darauf achten, dass die Beschreibungen gut formuliert und vollständig sind.

Sehen Sie sich als Nächstes den Code in der Funktion USDTOCHF() an:

function USDTOCHF(dollars){
  var swissFrancs = dollars * .99; 
  return swissFrancs;
}

Wie bereits erwähnt, wird mit USDTOCHF() die numerische Variable „dollars“ mit einem festen Wechselkurs multipliziert und ein in Schweizer Franken umgerechneter Wert in der numerischen Variablen swissFrancs zurückgegeben. Der Eingabeparameter ist der Wert, der in der Zelle enthalten ist, die beim Hinzufügen der benutzerdefinierten Funktion zu einer Zelle angegeben wurde. In diesem Beispiel stammen die eingegebenen Dollarbeträge aus Spalte H. Der Ausgabewert swissFrancs wird in die Zelle der Funktion (in diesem Beispiel Spalte I) eingefügt.

Benutzerdefinierte Funktionen können mit numerischen oder String-Werten verwendet werden, wie Sie im nächsten Abschnitt sehen.

Stringpräfix verketten

Angenommen, Sie möchten, dass die numerische Ausgabe der Funktion USDTOCHF() das Präfix für Schweizer Franken CHF enthält. Mit Apps Script können Sie dazu den Verkettungsoperator (+), verwenden, wie in der folgenden Anleitung beschrieben:

1. Aktualisieren Sie im Skripteditor die Annotation @return, damit ein String anstelle einer Zahl zurückgegeben wird.
2. Ändern Sie return swissFrancs zu return 'CHF' + swissFrancs.

Der Operator + fügt den String CHF vor dem Wert in swissFrancs ein. Ihr Code sollte jetzt so aussehen:

/**
 * Converts US dollars to Swiss francs.
 *
 * @param {number} dollars The total number of dollars.
 * @return {string} swissFrancs The provided value in Swiss francs.
 * @customfunction
 */
function USDTOCHF(dollars){
  var swissFrancs = dollars * .99;
  return 'CHF' + swissFrancs;
}

3. Klicken Sie auf „Speichern“ , um das Script zu speichern.

Dem String für Schweizer Franken wird jetzt den Werten in Spalte I vorangestellt:

Ihre benutzerdefinierte Funktion konvertiert jetzt nicht nur US-Dollar in Schweizer Franken, sondern gibt die Währung auch mit einem String-Präfix aus.

Erweitert: Externe Daten abrufen

Das ist ein guter Anfang für eine einfache benutzerdefinierte Funktion, aber in diesem Beispiel wird davon ausgegangen, dass der Wechselkurs von Dollar zu Schweizer Franken konstant ist. Angenommen, Sie möchten stattdessen den aktuellen Wechselkurs verwenden, sodass die Werte bei jedem Neuladen des Tabellenblatts neu berechnet werden, um die aktuelle Umrechnung darzustellen. Dazu benötigen Sie eine Möglichkeit, den aktuellen Wechselkurs zu ermitteln. Diese Informationen sind in Google Sheets nicht ohne Weiteres verfügbar. Glücklicherweise können Sie Apps Script verwenden, um sie abzurufen.

Mit dem folgenden Code können Sie den aktuellen Umrechnungskurs von Schweizer Franken in US-Dollar abrufen:

function USDTOCHF(dollars){
  // Gets a cache that is common to all users of the script.
  var cache = CacheService.getScriptCache();

  // Accesses the memory location (rates.CHF) of the script cache.
  var rate = cache.get('rates.CHF');

  // If a cache miss occurs, the program fetches the current
  // CHF rate from an API and stores the rate in the cache
  // for later convenience.
  if (!rate) {
    var response =
UrlFetchApp.fetch('https://api.exchangeratesapi.io/latest?base=USD');
    var result = JSON.parse(response.getContentText());
    rate = result.rates.CHF;
    cache.put('rates.CHF', rate);
  }
  // Converts dollars to CHF according to the latest rate.
  var swissFrancs = dollars * rate;
  // Returns the CHF value.
  return 'CHF' + swissFrancs;
}

Mit diesem Code wird der aktuelle Wechselkurs von einem Finanzinformationsserver mithilfe einer Wechselkurs-API eines Drittanbieters abgerufen. Dazu werden Apps Script-Dienste wie UrlFetchApp und CacheService verwendet. Diese erweiterten Konzepte werden in diesem Codelab nicht behandelt. Sie können jedoch die Vielseitigkeit von Apps Script zum Automatisieren komplexer Aufgaben in Google Tabellen erkennen.

Richtlinien für benutzerdefinierte Funktionen

Herzlichen Glückwunsch zum Abschluss der Übungen zu benutzerdefinierten Funktionen. Wenn Sie benutzerdefinierte Funktionen in Ihren Projekten verwenden, ist es wichtig, dass Sie sich über die Einschränkungen im Klaren sind. In der folgenden Liste sind die Einschränkungen zusammengefasst, die im Leitfaden Benutzerdefinierte Funktionen in Google Tabellen beschrieben werden:

• Erstellen Sie keine benutzerdefinierten Funktionen, die eine Nutzerautorisierung erfordern. Erstellen Sie benutzerdefinierte Funktionen für einfachere Aufgaben wie Berechnungen von Beispieldaten oder Textbearbeitung. Weitere Informationen finden Sie unter Apps Script-Dienste verwenden.
• Benennen Sie eine benutzerdefinierte Funktion nicht so wie eine andere integrierte Funktion und lassen Sie den Namen nicht mit einem Unterstrich enden. Lesen Sie die Hinweise zur Namensgebung.
• Übergeben Sie keine variablen Argumente an benutzerdefinierte Funktionen. Sie können nur deterministische (feste) Werte als Argumente an benutzerdefinierte Funktionen übergeben. Wenn Sie variable Argumente wie das Ergebnis von =RAND() übergeben, funktioniert die benutzerdefinierte Funktion nicht mehr. Richtlinien für Argumente
• Erstellen Sie keine Funktionen, deren Ausführung länger als 30 Sekunden dauert. Wenn es länger dauert, tritt ein Fehler auf. Halten Sie den Funktionscode daher einfach und beschränken Sie den Umfang. Die Berechnungen in benutzerdefinierten Funktionen sollten so einfach wie möglich gehalten werden. Richtlinien für Rückgabewerte

Sie können Ihre Tabellen jetzt verbessern, indem Sie mit dem Script-Editor Makros verwenden und benutzerdefinierte Funktionen erstellen. Im nächsten Abschnitt können Sie sich ansehen, was Sie gelernt haben und was Sie als Nächstes tun können, um Ihre Scripting-Kenntnisse zu verbessern.

6. Fazit

Sie haben das erste Codelab zu den Grundlagen von Apps Script mit Google Tabellen abgeschlossen. Durch das Erstellen und Bearbeiten von Google Sheets-Makros und benutzerdefinierten Funktionen haben Sie die grundlegenden Apps Script-Konzepte kennengelernt. Im nächsten Codelab können Sie Ihre Apps Script-Kenntnisse weiter ausbauen.

War dieses Codelab hilfreich?

Ja Nein

Behandelte Themen

• Grundlegende Apps Script-Konzepte.
• Im Skripteditor navigieren
• So erstellen und aktualisieren Sie Google Sheets-Makros.
• Benutzerdefinierte Funktionen für Google Sheets erstellen

Wichtige Begriffe

• Absoluter Bezug: Eine Makroaufzeichnungseinstellung, die angibt, dass aufgezeichnete Makroaktionen auf die in der Aufzeichnung verwendeten Zellen angewendet werden.
• Aktiv (Status): Gibt an, dass die angegebene Tabelle, das angegebene Tabellenblatt, der angegebene Bereich oder die angegebene Zelle derzeit vom Nutzer der Tabelle aufgerufen oder markiert wird.
• Apps Script: Eine Plattform zur schnellen Anwendungsentwicklung, die auf JavaScript basiert und mit der Sie Google Workspace-Anwendungen schnell und einfach erweitern und Aufgaben automatisieren können.
• Autorisierung: Der Prozess, bei dem der Nutzer Berechtigungen erteilt, damit Apps Script auf Nutzerdaten in den relevanten Google-Diensten zugreifen oder anderweitig Aktionen im Namen des Nutzers ausführen kann.
• An Container gebundenes Skript: Ein Skript, das an ein Google Workspace-Dokument wie eine Google-Tabelle oder ein Google-Dokument gebunden ist und daraus erstellt wurde.
• Benutzerdefinierte Funktionen: Benutzerdefinierte Formeln in Apps Script, die in Tabellenkalkulationen für einfache Vorgänge verwendet werden.
• JSDoc: Eine Markup-Sprache, mit der Erläuterungen zu JavaScript-basierten Quelldateien geschrieben werden.
• Makro: Abkürzung für „Makrobefehl“. Eine aufgezeichnete Reihe von Google Sheets-Aktionen, die Sie über einen Menüpunkt oder eine Tastenkombination wiederholen können.
• Methodenverkettung: Ein Programmieridiom zum Aufrufen mehrerer Methodenaufrufe für ein Objekt, das darauf beruht, dass bei jedem Methodenaufruf das Objekt zurückgegeben wird.
• Bereich: Eine Gruppe von einer oder mehreren angrenzenden Zellen in einem Tabellenblatt.
• Relativer Bezug: Eine Makroaufzeichnungseinstellung, die angibt, dass aufgezeichnete Makroaktionen auf die Zellen angewendet werden, die sich relativ zur aktuellen Zellenauswahl befinden.
• Script-Editor: Der In-Browser-Code-Editor für Apps Script.
• Arbeitsblatt: Eine einzelne Seite einer Tabelle (ähnlich den Arbeitsmappen von Microsoft Excel). Wird manchmal verwendet, um auf eine Google-Tabellen-Datei in Google Drive zu verweisen.
• SpreadsheetApp: Diese Klasse dient als übergeordnete Klasse für den Tabellendienst und bietet einen Ausgangspunkt für Code, mit dem Google Sheets-Daten gelesen oder bearbeitet werden.
• Tabelle: Eine Google-Tabellen-Datei in Google Drive.
• Google Sheets: Ein Apps Script-Dienst, mit dem Skripts Google Sheets-Dateien erstellen, darauf zugreifen und sie ändern können.

Weitere Informationen zu Apps Script finden Sie in der Apps Script-Dokumentation.

Nächste Schritte

Im nächsten Codelab in dieser Playlist werden die wichtigsten Klassen und Begriffe des Spreadsheet-Dienstes von Apps Script vorgestellt. Mit diesem Dienst können Sie die Werte und die Darstellung von Daten in Google Sheets mithilfe von Apps Script genau steuern.

Das nächste Codelab finden Sie unter Tabellen, Blätter und Bereiche.