Benutzerdefinierte Funktionen in Google Tabellen

Google Sheets bietet Hunderte von integrierten Funktionen wie AVERAGE, SUM und VLOOKUP. Wenn diese für Ihre Anforderungen nicht ausreichen, können Sie mit Apps Script benutzerdefinierte Funktionen schreiben und sie in Google Sheets wie eine integrierte Funktion verwenden.

Beispiele für benutzerdefinierte Funktionen finden Sie in den folgenden Anleitungen:

Erste Schritte

Benutzerdefinierte Funktionen werden mit Standard-JavaScript erstellt. Wenn Sie noch keine Erfahrung mit JavaScript haben, bietet Codecademy einen Kurs für Anfänger an. Dieser Kurs wurde nicht von Google entwickelt und steht nicht in Verbindung mit Google.

Hier ist eine 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 Sie JavaScript schreiben, und keine Zeit haben, es zu lernen, suchen Sie im Google Workspace Add-on-Store nach einem Add-on, das die gewünschte benutzerdefinierte Funktion bereits enthält.

Benutzerdefinierte Funktion erstellen

So schreiben Sie eine benutzerdefinierte Funktion:

  1. Erstellen oder öffnen Sie eine Tabelle in Google Sheets.
  2. Wählen Sie im Menü Erweiterungen > Apps Script aus.
  3. Wenn der Script-Editor Code enthält, löschen Sie diesen. Kopieren Sie den Code für die zuvor gezeigte Funktion DOUBLE und fügen Sie ihn in den Script-Editor ein.
  4. Klicken Sie oben auf „Speichern“ .

Jetzt können Sie die benutzerdefinierte Funktion verwenden.

Benutzerdefinierte Funktion aus dem Google Workspace Marketplace abrufen

Im Google Workspace Marketplace finden Sie mehrere benutzerdefinierte Funktionen als Google Workspace-Add-ons für Google Sheets. So verwenden oder testen Sie diese Add-ons:

  1. Erstellen oder öffnen Sie eine Tabelle in Google Sheets.
  2. Klicken Sie oben auf Add-ons > Add-ons aufrufen.
  3. Wenn der Google Workspace Marketplace geöffnet wird, klicken Sie auf das Suchfeld oben rechts.
  4. Geben Sie „benutzerdefinierte Funktion“ ein und drücken Sie die Eingabetaste.
  5. Wenn Sie ein Add-on für eine benutzerdefinierte Funktion finden, das Sie interessiert, klicken Sie auf Installieren , um es zu installieren.
  6. Möglicherweise werden Sie in einem Dialogfeld darüber informiert, dass für das Add-on eine Autorisierung erforderlich ist. 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 andere Tabelle und klicken Sie oben auf Add-ons > Add-ons verwalten. Suchen Sie das gewünschte Add-on und klicken Sie auf Optionen > In diesem Dokument verwenden.

Benutzerdefinierte Funktion verwenden

Nachdem Sie eine benutzerdefinierte Funktion geschrieben oder eine aus dem Google Workspace Marketplace installiert haben, wird sie wie eine integrierte Funktion verwendet:

  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.

Funktionsnamen

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

Argumente

Wie eine integrierte Funktion kann auch eine benutzerdefinierte Funktion Argumente als Eingabewerte verwenden:

  • 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 folgenden Screenshot werden die Argumente in =DOUBLE(A1:B2) von Apps Script als double([[1,3],[2,4]]) interpretiert. Der zuvor beschriebene Beispielcode für DOUBLE DOUBLE müsste geändert werden, um ein Array als Eingabe zu akzeptieren.


  • 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 volatilen integrierten Funktionen zurückzugeben, wird Loading... unbegrenzt angezeigt.

  • Um eine Neuberechnung auszulösen, müssen Sie eine referenzierte Zelle oder einen Zellbereich direkt als Argument an die benutzerdefinierte Funktion übergeben. Andernfalls wird die benutzerdefinierte Funktion erst neu berechnet, wenn Sie die Funktion bearbeiten oder den Wert einer referenzierten Zelle ändern. Wenn Sie die Methode getValue in benutzerdefinierten Funktionen verwenden, wird der referenzierte Bereich nicht direkt als Argument an die benutzerdefinierte Funktion übergeben.

Rückgabewerte

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

  • Wenn eine benutzerdefinierte Funktion einen Wert zurückgibt, wird der Wert in der Zelle angezeigt, aus der die Funktion aufgerufen wurde.
  • Wenn eine benutzerdefinierte Funktion ein zweidimensionales Array von Werten zurückgibt, werden die Werte in benachbarte Zellen übertragen, solange diese Zellen leer sind. Wenn dadurch vorhandene Zellinhalte überschrieben würden, gibt die benutzerdefinierte Funktion stattdessen einen Fehler aus. Ein Beispiel finden Sie im Abschnitt zur Optimierung benutzerdefinierter Funktionen.
  • Eine benutzerdefinierte Funktion kann nur Zellen beeinflussen, an die sie einen Wert zurückgibt. Mit anderen Worten: Eine benutzerdefinierte Funktion kann keine beliebigen Zellen bearbeiten, 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 Zellnotiz lautet Exceeded maximum execution time (line 0).

Datentypen

In Google Sheets 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. Dies sind die häufigsten Bereiche, in denen es zu Verwirrung kommt:

  • Uhrzeiten 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, aber die Arbeit mit ihnen kann kompliziert sein.
  • Prozentwerte in Google Sheets 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 Skript ein JSDoc @customfunction Tag enthält, wie im DOUBLE() Beispiel.

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

Erweitert

In diesem Abschnitt werden erweiterte Themen zu benutzerdefinierten Funktionen behandelt.

Google Apps Script-Dienste verwenden

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

Im Gegensatz zu den meisten anderen Arten von Apps Script-Skripten werden Nutzer bei benutzerdefinierten Funktionen nie aufgefordert, den Zugriff auf personenbezogene Daten zu autorisieren. Daher 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 Kann Wegbeschreibungen berechnen, aber keine Karten anzeigen
Properties getUserProperties() ruft nur die Eigenschaften des Tabelleneigentümers ab. Tabellenbearbeiter können in einer benutzerdefinierten Funktion keine Nutzereigenschaften 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 Greifen Sie auf Ressourcen im Web zu, indem Sie URLs abrufen.
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 in der vorherigen Liste verwenden möchten, erstellen Sie ein benutzerdefiniertes Menü, das stattdessen eine Apps Script-Funktion ausführt. 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.

Benutzerdefinierte Funktionen freigeben

Benutzerdefinierte Funktionen sind zunächst 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 Script-Editor zu öffnen. Kopieren Sie dann den Skripttext aus der ursprünglichen Tabelle und fügen Sie ihn in den Script-Editor 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 Skript kopieren. Mitbearbeiter, die nur Lesezugriff haben, können den Script-Editor in der ursprünglichen Tabelle nicht öffnen. Wenn sie jedoch eine Kopie erstellen, werden sie zum Eigentümer der Kopie und können das Skript sehen.
  • Veröffentlichen Sie das Skript als Add-on für Google Sheets Editor.

Alle containergebundenen Skripts haben dieselben Zugriffslisten wie ihre Container. Das bedeutet, dass jeder, der die Tabelle bearbeiten darf, auch jeden daran angehängten Apps Script-Code bearbeiten kann. Weitere Informationen finden Sie unter Zugriff auf gebundene Skripts.

Optimierung

Jedes Mal, wenn eine benutzerdefinierte Funktion in einer Tabelle verwendet wird, ruft Google Sheets den Apps Script-Server auf. Wenn Ihre Tabelle Dutzende (oder Hunderte oder Tausende) von Aufrufen benutzerdefinierter Funktionen enthält, kann dieser Vorgang langsam sein. 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 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 dann ein zweidimensionales Array zurückgibt, das in die entsprechenden Zellen übertragen werden kann.

Die zuvor 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 dieser Methode wird die map Methode des Array-Objekts von JavaScript auf das zweidimensionale Array von Zellen angewendet, um jede Zeile abzurufen. Dann wird map noch einmal verwendet, um den Wert jeder Zelle zu verdoppeln. Es wird ein zweidimensionales Array mit den Ergebnissen zurückgegeben. 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 folgenden Screenshot zu sehen. Sie können dasselbe auch mit verschachtelten if-Anweisungen anstelle des map-Aufrufs erreichen.

Ebenso ruft die folgende benutzerdefinierte Funktion 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 Methoden 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.