Benutzerdefinierte Funktionen in Google Tabellen

Google Tabellen bietet Hunderte von integrierten Funktionen wie AVERAGE, SUM und VLOOKUP. Wenn diese für Ihre Anforderungen nicht ausreichen, können Sie mit Google Apps Script benutzerdefinierte Funktionen schreiben, z. B. Meter in Meilen umwandeln oder Live-Inhalte aus dem Internet abrufen. Anschließend können Sie sie wie eine integrierte Funktion in Google Tabellen verwenden.

Erste Schritte

Benutzerdefinierte Funktionen werden mit Standard-JavaScript erstellt. Wenn Sie mit JavaScript noch nicht vertraut sind, bietet Codecademy einen tollen Kurs für Anfänger. Hinweis: Dieser Kurs wurde nicht von Google entwickelt und ist 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 zum Lernen haben, sehen Sie im Add-on-Store nach, ob die von Ihnen benötigte benutzerdefinierte Funktion bereits von einer anderen Person erstellt wurde.

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 aus Google Workspace Marketplaceabrufen

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

1. Erstellen oder öffnen Sie eine Tabelle in Google Tabellen.
2. Klicken Sie oben auf Add-ons > Add-ons aufrufen.
3. Klicken Sie nach dem Öffnen von Google Workspace Marketplace auf das Suchfeld oben rechts.
4. Geben Sie „benutzerdefinierte Funktion“ ein und drücken Sie die Eingabetaste.
5. Wenn Sie ein interessantes Add-on für benutzerdefinierte Funktionen finden, klicken Sie auf Installieren, um es zu installieren.
6. Möglicherweise wird in einem Dialogfeld angezeigt, dass das Add-on autorisiert werden muss. Falls ja, lesen Sie sich den Hinweis sorgfältig durch und klicken Sie dann auf Zulassen.
7. Das Add-on wird in der Tabelle verfügbar gemacht. 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

Wenn Sie eine benutzerdefinierte Funktion geschrieben oder ausGoogle Workspace Marketplaceinstalliert haben, ist sie so einfach zu verwenden wie eine integrierte Funktion:

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

Richtlinien für benutzerdefinierte Funktionen

Bevor Sie Ihre 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, der eine private Funktion in Apps Script bezeichnet.
• Der Name einer benutzerdefinierten Funktion muss mit der Syntax function myFunction() und nicht var myFunction = new Function() angegeben werden.
• Die Großschreibung spielt keine Rolle, obwohl die Namen von Tabellenkalkulationsfunktionen traditionell großgeschrieben werden.

Argumente

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

• 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) beispielsweise von Apps Script als double([[1,3],[2,4]]) interpretiert. Der Beispielcode für DOUBLE aus dem obigen Beispiel muss geändert werden, damit ein Array als Eingabe akzeptiert wird.

  
  

• Argumente von benutzerdefinierten Funktionen müssen deterministisch sein. Integrierte Tabellenkalkulationsfunktionen wie NOW() oder RAND(), die bei jeder Berechnung ein anderes Ergebnis zurückgeben, sind nicht als Argumente für eine benutzerdefinierte Funktion zulässig. Wenn eine benutzerdefinierte Funktion versucht, einen Wert basierend auf einer dieser veränderlichen integrierten Funktionen zurückzugeben, wird Loading... unbegrenzt angezeigt.

Rückgabewerte

Jede benutzerdefinierte Funktion muss einen anzuzeigenden Wert zurückgeben, z. B.:

• Wenn eine benutzerdefinierte Funktion einen Wert zurückgibt, wird der Wert in der Zelle angezeigt, von der aus die Funktion aufgerufen wurde.
• Wenn eine benutzerdefinierte Funktion ein zweidimensionales Array von Werten zurückgibt, überlaufen die Werte in benachbarte Zellen, solange diese 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 sich nur auf die Zellen auswirken, an die sie einen Wert zurückgibt. Mit anderen Worten: Mit einer benutzerdefinierten Funktion können keine beliebigen Zellen, sondern nur die Zellen, von denen sie aufgerufen wird, und ihre angrenzenden Zellen bearbeitet werden. Verwenden Sie zum Bearbeiten beliebiger Zellen stattdessen ein benutzerdefiniertes Menü, um eine Funktion auszuführen.
• Ein Aufruf einer benutzerdefinierten Funktion muss innerhalb von 30 Sekunden zurückgegeben werden. Andernfalls wird in der Zelle ein 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, werden sie von Apps Script als entsprechenden Datentyp in JavaScript behandelt. Dies sind die häufigsten Bereiche für Missverständnisse:

• Uhrzeiten und Datumsangaben in Google Tabellen 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 kompensiert werden.
• Werte für die Dauer in Google Tabellen werden ebenfalls zu Date-Objekten. Die Arbeit mit ihnen kann jedoch kompliziert sein.
• Prozentwerte in Google Tabellen werden in Apps Script zu Dezimalzahlen. Beispielsweise wird eine Zelle mit dem Wert 10% in Apps Script zu 0.1.

Automatische Vervollständigung

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

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

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

Profi

Google Apps Script-Dienste verwenden

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

Im Gegensatz zu den meisten anderen Apps Scripts werden Nutzer bei benutzerdefinierten Funktionen niemals 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, aber nicht besonders nützlich bei benutzerdefinierten Funktionen
HTML	Kann HTML generieren, aber nicht anzeigen (selten nützlich)
JDBC
Sprache
Sperren	Funktioniert, aber nicht besonders nützlich bei benutzerdefinierten Funktionen
Maps	Wegbeschreibungen können berechnet, aber keine Karten angezeigt werden
Properties	getUserProperties() ruft nur die Eigenschaften des Eigentümers der Tabelle ab. Mitbearbeiter von Tabellen können keine Nutzereigenschaften in einer benutzerdefinierten Funktion festlegen.
Tabelle	Schreibgeschützt (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 Ihre 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 als die oben aufgeführten Dienste 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 an und kann folglich 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 eine Tabelle geschrieben wurde, nur dann in anderen Tabellen verwendet werden kann, wenn Sie eine der folgenden Methoden verwenden:

• Klicken Sie auf Erweiterungen > Apps Script, um den Script-Editor zu öffnen. Kopieren Sie dann den Scripttext aus der ursprünglichen Tabelle und fügen Sie ihn in den Script-Editor einer anderen Tabelle ein.
• Erstellen Sie eine Kopie der Tabelle mit der benutzerdefinierten Funktion, indem Sie auf Datei > Kopie erstellen klicken. Beim Kopieren einer Tabelle werden auch alle an die Tabelle angehängten Skripts kopiert. Jeder, der Zugriff auf die Tabelle hat, kann das Skript kopieren. Mitbearbeiter, die nur Lesezugriff haben, können den Skripteditor in der ursprünglichen Tabelle nicht öffnen. Wenn er jedoch eine Kopie erstellt, wird er zum Eigentümer der Kopie und kann das Skript sehen.
• Veröffentlichen Sie das Skript als Editor-Add-on für Google Tabellen.

Alle containergebundenen Skripts haben dieselben Zugriffslisten wie ihre Container. Das bedeutet, dass jeder, der zum Bearbeiten der Tabelle berechtigt ist, auch jeden an die Tabelle angehängten Apps Script-Code bearbeiten kann.

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 Hunderte oder Tausende!) von benutzerdefinierten Funktionsaufrufen enthält, kann dieser Vorgang ziemlich lange dauern.

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. Anschließend wird ein zweidimensionales Array zurückgegeben, das in die entsprechenden Zellen überlaufen kann.

Die oben gezeigte Funktion DOUBLE() kann beispielsweise so umgeschrieben werden, dass eine einzelne Zelle oder ein Zellenbereich akzeptiert wird:

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

Der obige Ansatz verwendet die map-Methode des JavaScript-Objekts Array, um DOUBLE für jeden Wert im zweidimensionalen Zellenarray rekursiv aufzurufen. Sie gibt ein zweidimensionales Array zurück, das die Ergebnisse enthält. Auf diese Weise können Sie DOUBLE nur einmal aufrufen, dann aber für eine große Anzahl von Zellen gleichzeitig berechnen lassen, wie im folgenden Screenshot gezeigt. (Sie können dasselbe mit verschachtelten if-Anweisungen anstelle des Aufrufs map erreichen.)

In ähnlicher Weise ruft die benutzerdefinierte Funktion unten effizient Live-Inhalte aus dem Internet ab und verwendet ein zweidimensionales Array, um zwei Ergebnisspalten mit nur einem einzigen Funktionsaufruf anzuzeigen. Wenn für jede Zelle ein eigener Funktionsaufruf erforderlich ist, 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 wiederholt in einer Tabelle verwendet wird. Die Details der Implementierung hängen jedoch vom Verhalten der Funktion ab.