Benutzerdefinierte Funktionen in Google Tabellen

Google Tabellen 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. um Meter in Meilen umzuwandeln oder Liveinhalte aus dem Internet abzurufen. Sie können diese dann in Google Tabellen wie eine integrierte Funktion verwenden.

Erste Schritte

Benutzerdefinierte Funktionen werden mit Standard-JavaScript erstellt. Wenn Sie neu bei JavaScript sind, bietet Codecademy einen tollen Kurs für Anfänger. Hinweis: Dieser Kurs wurde nicht von Google entwickelt und ist nicht mit Google verbunden.

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 Sie JavaScript schreiben, und keine Zeit zum Lernen haben, sehen Sie im Add-on-Shop nach, ob jemand anderes 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 im Menü Erweiterungen > Apps Script aus.
3. Wenn der Skripteditor Code enthält, löschen Sie diesen. Kopieren Sie den Code für die Funktion DOUBLE oben und fügen Sie ihn in den Script-Editor ein.
4. Klicken Sie oben auf „Speichern“ (save).

Jetzt können Sie die benutzerdefinierte Funktion verwenden.

Benutzerdefinierte Funktion aus der Google Workspace Marketplaceabrufen

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

1. Erstellen oder öffnen Sie eine Tabelle in Google Tabellen.
2. Klicken Sie oben auf Add-ons > Add-ons aufrufen.
3. Klicke oben rechts im geöffneten Dreistrich-Menü Google Workspace Marketplace auf das Suchfeld.
4. Geben Sie „benutzerdefinierte Funktion“ ein und drücken Sie die Eingabetaste.
5. Wenn Sie ein Add-on für benutzerdefinierte Funktionen finden, das Sie interessiert, klicken Sie auf Installieren, um es zu installieren.
6. In einem Dialogfeld wird möglicherweise angezeigt, dass für das Add-on eine Autorisierung erforderlich ist. Lesen Sie in diesem Fall 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“ more_vert > In diesem Dokument verwenden.

Benutzerdefinierte Funktion verwenden

Nachdem Sie eine benutzerdefinierte Funktion geschrieben oder eine aus derGoogle Workspace Marketplaceinstalliert haben, ist sie genauso 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 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, sollten Sie sich mit einigen Richtlinien vertraut machen.

Benennung

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

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

Argumente

Wie bei einer integrierten Funktion können auch bei einer benutzerdefinierten Funktion Argumente als Eingabewerte verwendet werden:

• 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 oben muss geändert werden, damit ein Array als Eingabe akzeptiert wird.

  
  

• Argumente für benutzerdefinierte Funktionen müssen deterministisch sein. Das heißt, integrierte Tabellenfunktionen, die jedes Mal ein anderes Ergebnis zurückgeben, z. B. NOW() oder RAND(), sind nicht als Argumente für benutzerdefinierte Funktionen zulässig. Wenn eine benutzerdefinierte Funktion versucht, einen Wert auf der Grundlage einer dieser instabilen integrierten Funktionen zurückzugeben, wird Loading... angezeigt.

Rückgabewerte

Jede benutzerdefinierte Funktion muss einen Wert zurückgeben, der so aussieht:

• Wenn eine benutzerdefinierte Funktion einen Wert zurückgibt, wird dieser in der Zelle angezeigt, von der aus die Funktion aufgerufen wurde.
• Wenn eine benutzerdefinierte Funktion ein zweidimensionales Array von Werten zurückgibt, werden die Werte in benachbarte Zellen übernommen, sofern diese Zellen leer sind. Wenn dadurch der vorhandene Zelleninhalt überschrieben würde, löst 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, für die sie einen Wert zurückgibt. Mit einer benutzerdefinierten Funktion können also nicht beliebige Zellen bearbeitet werden, sondern nur die Zellen, von denen aus sie aufgerufen wird, und die benachbarten 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! und in der Zellennotiz Exceeded maximum execution time (line 0). angezeigt.

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 entsprechenden Datentyp in JavaScript. Das sind die häufigsten Missverständnisse:

• Uhrzeiten und Datumsangaben in Google Tabellen werden in Apps Script zu Datum-Objekten. Wenn in der Tabelle und im Script unterschiedliche Zeitzonen verwendet werden (ein seltenes Problem), muss die benutzerdefinierte Funktion dies berücksichtigen.
• Auch Dauerwerte in Google Tabellen werden zu Date-Objekten. Die Arbeit mit ihnen kann jedoch kompliziert sein.
• Prozentwerte in Google Tabellen werden in Apps Script zu Dezimalzahlen. Beispiel: Eine Zelle mit dem Wert 10% wird in Apps Script zu 0.1.

Automatische Vervollständigung

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

Benutzerdefinierte Funktionen werden in dieser Liste angezeigt, wenn ihr Script ein JsDoc-@customfunction-Tag enthält, wie im Beispiel für 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 Dienst Language aufrufen, um einen englischen Ausdruck ins Spanische zu übersetzen.

Im Gegensatz zu den meisten anderen Arten von Apps-Scripts 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. Dazu gehören:

Wenn bei Ihrer benutzerdefinierten Funktion die Fehlermeldung You do not have permission to call X service. ausgegeben wird, erfordert der Dienst eine Nutzerautorisierung und 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. Bei einer Funktion, die über ein Menü ausgelöst wird, wird der Nutzer bei Bedarf um Autorisierung gebeten und kann dann alle Apps Script-Dienste verwenden.

Teilen

Benutzerdefinierte Funktionen sind zu Beginn an die Tabelle gebunden, in der sie erstellt wurden. Das bedeutet, dass eine benutzerdefinierte Funktion, die in einer 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 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 mit der benutzerdefinierten Funktion. Klicken Sie dazu auf Datei > Kopie erstellen. Wenn eine Tabelle kopiert wird, werden auch alle angehängten Scripts kopiert. Jeder, der Zugriff auf die Tabelle hat, kann das Script kopieren. Mitbearbeiter mit Lesezugriff können den Script-Editor in der ursprünglichen Tabelle nicht öffnen. Wenn er jedoch eine Kopie erstellt, wird er zum Inhaber der Kopie und kann das Script sehen.)
• Veröffentlichen Sie das Script als Editor-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 Hunderte oder Tausende!) von benutzerdefinierten Funktionsaufrufen enthält, kann dieser Vorgang ziemlich langsam sein.

Wenn Sie eine benutzerdefinierte Funktion also mehrmals für einen großen Datenbereich verwenden möchten, sollten Sie sie 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 DOUBLE()-Funktion 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 Array-Objekts in JavaScript verwendet, um DOUBLE rekursiv auf jeden Wert im zweidimensionalen Zellenarray anzuwenden. Sie gibt ein zweidimensionales Array zurück, das die Ergebnisse enthält. So können Sie DOUBLE nur einmal aufrufen, aber für eine große Anzahl von Zellen gleichzeitig berechnen lassen, wie im folgenden Screenshot dargestellt. Das gleiche Ergebnis lässt sich auch mit verschachtelten if-Anweisungen anstelle des map-Aufrufs erzielen.

Ähnlich ruft die folgende benutzerdefinierte Funktion Liveinhalte effizient aus dem Internet ab und verwendet ein zweidimensionales Array, um mit nur einem einzigen Funktionsaufruf zwei Ergebnisspalten anzuzeigen. Wenn für jede Zelle ein eigener Funktionsaufruf erforderlich wäre, würde der Vorgang wesentlich 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.