Benutzerdefinierte Funktionen in Google Tabellen

Mit Sammlungen den Überblick behalten Sie können Inhalte basierend auf Ihren Einstellungen speichern und kategorisieren.

Google Tabellen bietet Hunderte von integrierten Funktionen wie AVERAGE, SUM und VLOOKUP. Wenn diese für Ihre Anforderungen nicht ausreichen, können Sie Google Apps Script verwenden, um benutzerdefinierte Funktionen zu schreiben, z. B. um Meter in Meilen umzuwandeln oder Liveinhalte aus dem Internet abzurufen und sie dann wie eine integrierte Funktion in Google Tabellen zu verwenden.

Erste Schritte

Benutzerdefinierte Funktionen werden mit Standard-JavaScript erstellt. Wenn Sie mit JavaScript noch nicht vertraut sind, bietet Codecademy einen großartigen Kurs für Anfänger. Hinweis: Dieser Kurs wurde nicht von Google entwickelt und ist nicht mit Google verknüpft.

Hier sehen Sie eine einfache benutzerdefinierte Funktion namens DOUBLE, mit der ein Eingabewert mit 2 multipliziert wird:

/**
 * 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 zum Lernen haben, prüfen Sie im Add-on-Store, ob eine andere Person die benötigte benutzerdefinierte Funktion bereits erstellt hat.

Benutzerdefinierte Funktion erstellen

So schreiben Sie eine benutzerdefinierte Funktion:

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

Jetzt können Sie die benutzerdefinierte Funktion verwenden.

Benutzerdefinierte Funktion von Google Workspace Marketplaceabrufen

Google Workspace Marketplace bietet mehrere benutzerdefinierte Funktionen als Add-ons für Google Tabellen. So können Sie diese Add-ons verwenden oder ausprobieren:

  1. Erstellen oder öffnen Sie eine Tabelle in Google Tabellen.
  2. Klicken Sie oben auf Add-ons > Add-ons aufrufen.
  3. Klicken Sie auf das Suchfeld oben rechts, sobald sich Google Workspace Marketplace öffnet.
  4. Geben Sie „Benutzerdefinierte Funktion“ ein und drücken Sie die Eingabetaste.
  5. Wenn Sie ein benutzerdefiniertes Add-on für eine benutzerdefinierte Funktion finden möchten, klicken Sie auf Installieren.
  6. In einem Dialogfeld werden Sie möglicherweise aufgefordert, das Add-on zu autorisieren. Lesen Sie in diesem Fall die Hinweise sorgfältig und klicken Sie auf Zulassen.
  7. Das Add-on ist dann 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 Add-on, das Sie verwenden möchten, und klicken Sie auf „Optionen“ > In diesem Dokument verwenden.

Benutzerdefinierte Funktion verwenden

Sobald Sie eine benutzerdefinierte Funktion geschrieben oder überGoogle Workspace Marketplaceinstalliert haben, ist sie genauso einfach wie eine integrierte 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 und dann das Ergebnis zurückgegeben.

Richtlinien für benutzerdefinierte Funktionen

Bevor Sie Ihre eigene benutzerdefinierte Funktion schreiben, müssen Sie einige Richtlinien beachten.

Benennung

Zusätzlich zu den Standardkonventionen für das Benennen 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, der eine private Funktion in Apps Script kennzeichnet.
  • Der Name einer benutzerdefinierten Funktion muss mit der Syntax function myFunction() und nicht mit var myFunction = new Function() deklariert werden.
  • Die Großschreibung ist nicht relevant, obwohl die Namen von Tabellenfunktionen traditionell großgeschrieben werden.

Argumente

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

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

  • Wenn Sie die Funktion mit einem Verweis auf einen Zellenbereich als Argument aufrufen (z. B. =DOUBLE(A1:B10)), ist das Argument ein zweidimensionales Array der Zellenwerte. Im folgenden Screenshot werden die Argumente in =DOUBLE(A1:B2) von Apps Script als double([[1,3],[2,4]]) interpretiert. Der Beispielcode für DOUBLE von oben muss geändert werden, um ein Array als Eingabe zu akzeptieren.


  • Argumente von benutzerdefinierten Funktionen müssen deterministisch sein. Das heißt, dass integrierte Tabellenfunktionen, 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

Für jede benutzerdefinierte Funktion muss ein Wert zurückgegeben werden, der so dargestellt wird:

  • 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, laufen die Werte in angrenzende Zellen über, sofern diese Zellen leer sind. Wenn dies dazu führen würde, dass das Array vorhandene Zelleninhalte überschreibt, gibt die benutzerdefinierte Funktion stattdessen einen Fehler aus. Ein Beispiel finden Sie im Abschnitt Benutzerdefinierte Funktionen optimieren.
  • Eine benutzerdefinierte Funktion kann nur die Zellen beeinflussen, an die sie einen Wert zurückgibt. Eine benutzerdefinierte Funktion kann also keine beliebigen Zellen bearbeiten, sondern nur die Zellen, aus denen sie aufgerufen werden, und die angrenzenden Zellen. Verwenden Sie zum Bearbeiten beliebiger Zellen ein benutzerdefiniertes Menü, um stattdessen eine Funktion auszuführen.
  • Ein Aufruf einer benutzerdefinierten Funktion muss innerhalb von 30 Sekunden zurückgegeben werden. Andernfalls wird in der Zelle der folgende Fehler angezeigt: Internal error executing the custom function.

Datentypen

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

  • Uhrzeiten und Datumsangaben in Google Tabellen werden in Apps Script zu Date-Objekten. Wenn die Tabelle und das Skript unterschiedliche Zeitzonen haben (ein seltenes Problem), muss die benutzerdefinierte Funktion dies ausgleichen.
  • In Google Tabellen werden auch Werte für die Dauer zu Date-Objekten. Das Arbeiten mit diesen Elementen ist jedoch kompliziert.
  • Prozentwerte in Google Tabellen werden in Apps Script als Dezimalzahlen dargestellt. Beispiel: Eine Zelle mit dem Wert 10% wird in Apps Script zu 0.1.

Automatische Vervollständigung

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

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

/**
 * 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

Apps Script-Dienste verwenden

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

Im Gegensatz zu den meisten anderen Arten von Apps Scripts werden Nutzer von benutzerdefinierten Funktionen nie dazu aufgefordert, den Zugriff auf personenbezogene Daten zu autorisieren. Daher können sie nur Dienste aufrufen, die keinen Zugriff auf personenbezogene Daten haben, insbesondere folgende:

Unterstützte Dienste Hinweise
Cache Funktioniert, ist aber in benutzerdefinierten Funktionen nicht besonders nützlich
HTML Kann HTML generieren, aber nicht anzeigen (sehr nützlich)
JDBC
Sprache
Sperren Funktioniert, ist aber in benutzerdefinierten Funktionen nicht besonders nützlich
Maps Kann Routen berechnen, aber keine Karten anzeigen
Properties getUserProperties() ruft nur die Properties des Tabelleninhabers ab. Tabelleneditoren können in einer benutzerdefinierten Funktion keine Nutzereigenschaften festlegen.
Tabelle Nur Lesezugriff (kann die meisten get*()-Methoden verwenden, aber nicht set*()).
Andere Tabellen (SpreadsheetApp.openById() oder SpreadsheetApp.openByUrl()) können nicht geöffnet werden.
URL-Abruf
Dienstprogramme
XML

Wenn die benutzerdefinierte Funktion die Fehlermeldung You do not have permission to call X service. ausgibt, erfordert der Dienst eine Nutzerautorisierung und kann daher nicht in einer benutzerdefinierten Funktion verwendet werden.

Wenn Sie einen anderen Dienst als den oben aufgeführten verwenden möchten, erstellen Sie ein benutzerdefiniertes Menü, das eine Apps Script-Funktion ausführt, 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 folglich alle Apps Script-Dienste verwenden.

Umsatzbeteiligung

Benutzerdefinierte Funktionen sind an die Tabelle gebunden, in der sie erstellt wurden. Das bedeutet, dass eine in einer Tabelle geschriebene benutzerdefinierte Funktion in anderen Tabellen nur verwendet werden kann, wenn Sie eine der folgenden Methoden nutzen:

  • 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. Klicken Sie dazu auf Datei > Kopie erstellen. Beim Kopieren einer Tabelle werden auch alle angehängten Skripts kopiert. Jeder, der Zugriff auf die Tabelle hat, kann das Skript kopieren. Mitbearbeiter, die nur Lesezugriff haben, können den Skripteditor nicht in der Originaltabelle öffnen. Wenn sie jedoch eine Kopie erstellen, werden sie zum Inhaber der Kopie und können das Skript sehen.)
  • Veröffentlichen Sie das Skript als Add-on für Google Tabellen.

Optimierung

Jedes Mal, wenn eine benutzerdefinierte Funktion in einer Tabelle verwendet wird, sendet Google Tabellen einen separaten Aufruf an den Apps Script-Server. Wenn Ihre Tabelle Dutzende (oder sogar Hunderttausende) benutzerdefinierte Funktionsaufrufe enthält, kann dieser Vorgang ziemlich langwierig sein.

Wenn Sie also 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 überlaufen kann.

Die oben gezeigte Funktion DOUBLE() kann beispielsweise so umgeschrieben werden, dass sie eine einzelne Zelle oder einen Zellenbereich 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;
}

Beim obigen Ansatz wird die map-Methode des JavaScript-Array-Objekts verwendet, um DOUBLE für jeden Wert im zweidimensionalen Array von Zellen rekursiv aufzurufen. Sie gibt ein zweidimensionales Array zurück, das die Ergebnisse enthält. Sie können DOUBLE also nur einmal aufrufen, dies wird jedoch für eine große Anzahl von Zellen gleichzeitig berechnet, wie im Screenshot unten gezeigt. Dasselbe können Sie mit verschachtelten if-Anweisungen anstatt mit map-Aufrufen erreichen.

Ebenso ruft die benutzerdefinierte Funktion unten Liveinhalte effizient aus dem Internet ab und verwendet ein zweidimensionales Array, um zwei Ergebnisspalten mit nur einem Funktionsaufruf anzuzeigen. Wenn für jede Zelle ein eigener Funktionsaufruf erforderlich ist, dauert der Vorgang erheblich länger, da der Apps Script-Server den XML-Feed jedes Mal herunterladen und parsen muss.

/**
 * 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 wiederholt in einer Tabelle verwendet wird. Die Implementierungsdetails variieren jedoch je nach Verhalten der Funktion.