Benutzerdefinierte Funktionen in Google Tabellen

Google Sheets bietet Hunderte von integrierten Funktionen wie AVERAGE, SUM und VLOOKUP. Wenn diese nicht ausreichen, können Sie mit Google Apps Script benutzerdefinierte Funktionen schreiben, z. B. zum Umrechnen von Metern in Meilen oder zum Abrufen von Live-Inhalten aus dem Internet. Diese Funktionen können Sie dann in Google Tabellen wie eine integrierte Funktion verwenden.

Erste Schritte

Benutzerdefinierte Funktionen werden mit Standard-JavaScript erstellt. Wenn Sie noch nicht mit JavaScript vertraut sind, bietet Codecademy einen guten Kurs für Anfänger. Hinweis: Dieser Kurs wurde nicht von Google entwickelt und steht nicht in Verbindung mit Google.

Hier ist eine einfache benutzerdefinierte Funktion namens DOUBLE, die einen Eingabewert mit 2 multipliziert:

/**
 * Multiplies an input value by 2.
 * @param {number} input The number to double.
 * @return The input multiplied by 2.
 * @customfunction
*/
function DOUBLE(input) {
  return input * 2;
}

Wenn Sie nicht wissen, wie man JavaScript schreibt, und keine Zeit haben, es zu lernen, sehen Sie im Add-on-Store nach, ob jemand anderes bereits die benutzerdefinierte Funktion erstellt hat, die Sie benötigen.

Benutzerdefinierte Funktion erstellen

So schreiben Sie eine benutzerdefinierte Funktion:

1. Erstellen Sie eine Tabelle in Google Sheets oder öffnen Sie eine vorhandene Tabelle.
2. Wählen Sie das Menüelement Erweiterungen > Apps Script aus.
3. Wenn der Skripteditor Code enthält, löschen Sie diesen. Für die Funktion DOUBLE oben müssen Sie den Code einfach kopieren und in den Skripteditor einfügen.
4. Klicken Sie oben auf „Speichern“ ().

Jetzt können Sie die benutzerdefinierte Funktion verwenden.

Benutzerdefinierte Funktion aus Google Workspace Marketplaceabrufen

Google Workspace Marketplace bietet mehrere benutzerdefinierte Funktionen als Add-ons für Google Sheets an. So verwenden Sie diese Add-ons oder sehen sie sich an:

1. Erstellen Sie eine Tabelle in Google Sheets oder öffnen Sie eine vorhandene Tabelle.
2. Klicken Sie oben auf Add-ons > Add-ons aufrufen.
3. Wenn sich das Menü Google Workspace Marketplace öffnet, klicken Sie oben rechts auf das Suchfeld.
4. Geben Sie „Benutzerdefinierte Funktion“ ein und drücken Sie die Eingabetaste.
5. Wenn Sie ein Add‑on mit benutzerdefinierten Funktionen finden, das Sie interessiert, klicken Sie auf Installieren, um es zu installieren.
6. Möglicherweise wird ein Dialogfeld angezeigt, in dem Sie aufgefordert werden, das Add-on zu autorisieren. Lesen Sie den Hinweis sorgfältig durch und klicken Sie dann auf Zulassen.
7. Das Add-on ist jetzt in der Tabelle verfügbar. Wenn Sie das Add-on in einer anderen Tabelle verwenden möchten, öffnen Sie die entsprechende Tabelle und klicken Sie oben auf Add-ons > Add-ons verwalten. Suchen Sie das Add-on, das Sie verwenden möchten, und klicken Sie auf „Optionen“  > In diesem Dokument verwenden.

Benutzerdefinierte Funktion verwenden

Nachdem Sie eine benutzerdefinierte Funktion geschrieben oder eine aus derGoogle Workspace Marketplaceinstalliert haben, ist die Verwendung genauso einfach wie bei einer integrierten Funktion:

1. Klicken Sie auf die Zelle, in der Sie die Funktion verwenden möchten.
2. Geben Sie ein Gleichheitszeichen (=) gefolgt vom Funktionsnamen und einem beliebigen Eingabewert ein, z. B. =DOUBLE(A1), und drücken Sie die Eingabetaste.
3. In der Zelle wird kurz Loading... angezeigt, dann wird das Ergebnis zurückgegeben.

Richtlinien für benutzerdefinierte Funktionen

Bevor Sie eine eigene benutzerdefinierte Funktion schreiben, sollten Sie einige Richtlinien beachten.

Benennung

Zusätzlich zu den Standardkonventionen für die Benennung von JavaScript-Funktionen sollten Sie Folgendes beachten:

• Der Name einer benutzerdefinierten Funktion muss sich von den Namen integrierter Funktionen wie SUM() unterscheiden.
• Der Name einer benutzerdefinierten Funktion darf nicht mit einem Unterstrich (_) enden, da dieser in Apps Script eine private Funktion kennzeichnet.
• Der Name einer benutzerdefinierten Funktion muss mit der Syntax function myFunction() und nicht mit var myFunction = new Function() deklariert werden.
• Die Groß- und Kleinschreibung spielt keine Rolle, obwohl die Namen von Tabellenkalkulationsfunktionen traditionell in Großbuchstaben geschrieben werden.

Argumente

Wie eine integrierte Funktion kann eine benutzerdefinierte Funktion Argumente als Eingabewerte annehmen:

• Wenn Sie Ihre Funktion mit einem Verweis auf eine einzelne Zelle als Argument aufrufen (z. B. =DOUBLE(A1)), ist das Argument der Wert der Zelle.

• Wenn Sie Ihre Funktion mit einem Verweis auf einen Zellbereich als Argument aufrufen (z. B. =DOUBLE(A1:B10)), ist das Argument ein zweidimensionales Array der Werte der Zellen. Im Screenshot unten werden die Argumente in =DOUBLE(A1:B2) beispielsweise von Apps Script als double([[1,3],[2,4]]) interpretiert. Der DOUBLE-Beispielcode oben muss so geändert werden, dass ein Array als Eingabe akzeptiert wird.

  
  

• Argumente für benutzerdefinierte Funktionen müssen deterministisch sein. Das bedeutet, dass integrierte Tabellenkalkulationsfunktionen, die bei jeder Berechnung ein anderes Ergebnis zurückgeben, z. B. NOW() oder RAND(), nicht als Argumente für eine benutzerdefinierte Funktion zulässig sind. Wenn eine benutzerdefinierte Funktion versucht, einen Wert basierend auf einer dieser flüchtigen integrierten Funktionen zurückzugeben, wird Loading... auf unbestimmte Zeit angezeigt.

Rückgabewerte

Jede benutzerdefinierte Funktion muss einen Wert zurückgeben, der angezeigt werden kann:

• Wenn eine benutzerdefinierte Funktion einen Wert zurückgibt, wird dieser in der Zelle angezeigt, aus der die Funktion aufgerufen wurde.
• Wenn eine benutzerdefinierte Funktion ein zweidimensionales Array mit Werten zurückgibt, werden die Werte in benachbarte Zellen übertragen, sofern diese leer sind. Wenn dadurch vorhandene Zellinhalte überschrieben würden, gibt die benutzerdefinierte Funktion stattdessen einen Fehler aus. Ein Beispiel finden Sie im Abschnitt Benutzerdefinierte Funktionen optimieren.
• Eine benutzerdefinierte Funktion kann sich nicht auf andere Zellen als die auswirken, in die sie einen Wert zurückgibt. Mit einer benutzerdefinierten Funktion können also nicht beliebige Zellen bearbeitet werden, sondern nur die Zellen, aus denen sie aufgerufen wird, und die angrenzenden Zellen. Wenn Sie beliebige Zellen bearbeiten möchten, verwenden Sie stattdessen ein benutzerdefiniertes Menü, um eine Funktion auszuführen.
• Ein benutzerdefinierter Funktionsaufruf muss innerhalb von 30 Sekunden zurückgegeben werden. Andernfalls wird in der Zelle #ERROR! angezeigt und die Zellennote lautet Exceeded maximum execution time (line 0)..

Datentypen

In Google Tabellen werden Daten je nach Art der Daten in verschiedenen Formaten gespeichert. Wenn diese Werte in benutzerdefinierten Funktionen verwendet werden, behandelt Apps Script sie als den entsprechenden Datentyp in JavaScript. Das sind die häufigsten Missverständnisse:

• Zeiten und Datumsangaben in Google Sheets werden in Apps Script zu Date-Objekten. Wenn in der Tabelle und im Skript unterschiedliche Zeitzonen verwendet werden (ein seltenes Problem), muss die benutzerdefinierte Funktion dies berücksichtigen.
• Dauerwerte in Google Sheets werden ebenfalls zu Date-Objekten, die Verarbeitung kann jedoch kompliziert sein.
• Prozentwerte in Google Tabellen werden in Apps Script zu Dezimalzahlen. Eine Zelle mit dem Wert 10% wird in Apps Script beispielsweise zu 0.1.

Automatische Vervollständigung

Google Sheets unterstützt die automatische Vervollständigung für benutzerdefinierte Funktionen, ähnlich wie für integrierte Funktionen. Wenn Sie einen Funktionsnamen in eine Zelle eingeben, wird eine Liste mit integrierten und benutzerdefinierten Funktionen angezeigt, die mit Ihrer Eingabe übereinstimmen.

Benutzerdefinierte Funktionen werden in dieser Liste angezeigt, wenn ihr Script ein JsDoc-Tag @customfunction enthält, wie im Beispiel DOUBLE() unten.

/**
 * Multiplies the input value by 2.
 *
 * @param {number} input The value to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return input * 2;
}

Erweitert

Google Apps Script-Dienste verwenden

Benutzerdefinierte Funktionen können bestimmte Google Apps Script-Dienste aufrufen, um komplexere Aufgaben auszuführen. Eine benutzerdefinierte Funktion kann beispielsweise den Language-Dienst aufrufen, um einen englischen Ausdruck ins Spanische zu übersetzen.

Im Gegensatz zu den meisten anderen Arten von Apps-Script-Projekten werden Nutzer bei benutzerdefinierten Funktionen nie aufgefordert, den Zugriff auf personenbezogene Daten zu autorisieren. Folglich können sie nur Dienste aufrufen, die keinen Zugriff auf personenbezogene Daten haben, insbesondere die folgenden:

Unterstützte Dienste	Hinweise
Cache	Funktioniert, ist aber in benutzerdefinierten Funktionen nicht besonders nützlich
HTML	Kann HTML generieren, aber nicht anzeigen (selten nützlich)
JDBC
Sprache
Sperren	Funktioniert, ist aber in benutzerdefinierten Funktionen nicht besonders nützlich
Maps	Routen können berechnet, aber keine Karten angezeigt werden
Properties	getUserProperties() ruft nur die Eigenschaften des Tabelleneigentümers ab. Tabellenbearbeiter können keine Nutzerattribute in einer benutzerdefinierten Funktion festlegen.
Tabelle	Schreibgeschützt (die meisten get*()-Methoden können verwendet werden, aber nicht set*()). Andere Tabellen können nicht geöffnet werden (SpreadsheetApp.openById() oder SpreadsheetApp.openByUrl()).
URL Fetch
Dienstprogramme
XML

Wenn Ihre benutzerdefinierte Funktion die Fehlermeldung You do not have permission to call X service. ausgibt, ist für den Dienst eine Nutzerautorisierung erforderlich und er kann daher nicht in einer benutzerdefinierten Funktion verwendet werden.

Wenn Sie einen anderen Dienst als die oben aufgeführten verwenden möchten, erstellen Sie ein benutzerdefiniertes Menü, in dem eine Apps Script-Funktion ausgeführt wird, anstatt eine benutzerdefinierte Funktion zu schreiben. Eine Funktion, die über ein Menü ausgelöst wird, fordert den Nutzer bei Bedarf zur Autorisierung auf und kann daher alle Apps Script-Dienste verwenden.

Freigabe

Benutzerdefinierte Funktionen sind anfangs an die Tabelle gebunden, in der sie erstellt wurden. Das bedeutet, dass eine benutzerdefinierte Funktion, die in einer Tabelle geschrieben wurde, nicht in anderen Tabellen verwendet werden kann, es sei denn, Sie verwenden eine der folgenden Methoden:

• Klicken Sie auf Erweiterungen > Apps Script, um den Skripteditor zu öffnen. Kopieren Sie dann den Skripttext aus der ursprünglichen Tabelle und fügen Sie ihn in den Skripteditor einer anderen Tabelle ein.
• Erstellen Sie eine Kopie der Tabelle, die die benutzerdefinierte Funktion enthält, indem Sie auf Datei > Kopie erstellen klicken. Wenn eine Tabelle kopiert wird, werden auch alle daran angehängten Skripts kopiert. Jeder, der Zugriff auf die Tabelle hat, kann das Script kopieren. Mitbearbeiter mit nur Lesezugriff können den Skripteditor in der Originaltabelle nicht öffnen. Wenn sie jedoch eine Kopie erstellen, werden sie zum Inhaber der Kopie und können das Script sehen.)
• Veröffentlichen Sie das Skript als Editor-Add-on für Google Sheets.

Alle containergebundenen Skripts haben dieselben Zugriffslisten wie ihre Container. Das bedeutet, dass jeder, der Bearbeitungsberechtigungen für die Tabelle hat, auch den damit verknüpften Apps Script-Code bearbeiten kann.

Optimierung

Jedes Mal, wenn eine benutzerdefinierte Funktion in einer Tabelle verwendet wird, führt Google Sheets einen separaten Aufruf des Apps Script-Servers aus. Wenn Ihre Tabelle Dutzende, Hunderte oder sogar Tausende von benutzerdefinierten Funktionsaufrufen enthält, kann dieser Vorgang sehr lange dauern. Bei einigen Projekten mit vielen oder komplexen benutzerdefinierten Funktionen kann es zu einer vorübergehenden Verzögerung bei der Ausführung kommen.

Wenn Sie eine benutzerdefinierte Funktion also mehrmals für einen großen Datenbereich verwenden möchten, sollten Sie die Funktion so ändern, dass sie einen Bereich als Eingabe in Form eines zweidimensionalen Arrays akzeptiert und ein zweidimensionales Array zurückgibt, das in die entsprechenden Zellen überlaufen kann.

Die oben gezeigte Funktion DOUBLE() kann beispielsweise so umgeschrieben werden, dass sie eine einzelne Zelle oder einen Zellbereich akzeptiert:

/**
 * Multiplies the input value by 2.
 *
 * @param {number|Array<Array<number>>} input The value or range of cells
 *     to multiply.
 * @return The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return Array.isArray(input) ?
      input.map(row => row.map(cell => cell * 2)) :
      input * 2;
}

Bei der oben beschriebenen Vorgehensweise wird die map-Methode des Array-Objekts von JavaScript verwendet, um die einzelnen Zeilen des zweidimensionalen Zellarrays abzurufen. Anschließend wird map noch einmal verwendet, um den Wert jeder Zelle zu verdoppeln. Sie gibt ein zweidimensionales Array mit den Ergebnissen zurück. So können Sie DOUBLE nur einmal aufrufen, aber die Berechnung für eine große Anzahl von Zellen gleichzeitig durchführen lassen, wie im Screenshot unten zu sehen ist. Sie könnten dasselbe auch mit verschachtelten if-Anweisungen anstelle des map-Aufrufs erreichen.

Die benutzerdefinierte Funktion unten ruft auf ähnliche Weise effizient Live-Inhalte aus dem Internet ab und verwendet ein zweidimensionales Array, um mit nur einem Funktionsaufruf zwei Spalten mit Ergebnissen anzuzeigen. Wenn für jede Zelle ein eigener Funktionsaufruf erforderlich wäre, würde der Vorgang erheblich länger dauern, da der Apps Script-Server den XML-Feed jedes Mal herunterladen und parsen müsste.

/**
 * Show the title and date for the first page of posts on the
 * Developer blog.
 *
 * @return Two columns of data representing posts on the
 *     Developer blog.
 * @customfunction
 */
function getBlogPosts() {
  var array = [];
  var url = 'https://gsuite-developers.googleblog.com/atom.xml';
  var xml = UrlFetchApp.fetch(url).getContentText();
  var document = XmlService.parse(xml);
  var root = document.getRootElement();
  var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom');
  var entries = document.getRootElement().getChildren('entry', atom);
  for (var i = 0; i < entries.length; i++) {
    var title = entries[i].getChild('title', atom).getText();
    var date = entries[i].getChild('published', atom).getValue();
    array.push([title, date]);
  }
  return array;
}

Diese Techniken können auf fast jede benutzerdefinierte Funktion angewendet werden, die in einer Tabelle wiederholt verwendet wird. Die Implementierungsdetails variieren jedoch je nach Verhalten der Funktion.