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. 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 großartigen Kurs für Einsteiger. Hinweis: Dieser Kurs wurde nicht von Google entwickelt und steht nicht in Verbindung mit Google.

Hier sehen Sie eine einfache benutzerdefinierte Funktion mit dem Namen 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 mit dem Schreiben von JavaScript vertraut sind und keine Zeit zum Lernen haben, können Sie im Add-on-Store nachsehen, ob die benötigte benutzerdefinierte Funktion bereits von einem anderen Nutzer 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. Kopieren Sie für die obige Funktion DOUBLE einfach den Code und fügen Sie 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. Wenn sich 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 für eine benutzerdefinierte Funktion finden, klicken Sie auf Installieren.
6. Möglicherweise wird in einem Dialogfeld angezeigt, dass das Add-on autorisiert werden muss. Wenn ja, lesen Sie den Hinweis sorgfältig und klicken Sie dann auf Zulassen.
7. Das Add-on ist in der Tabelle verfügbar. Wenn Sie das Add-on in einer anderen Tabelle verwenden möchten, öffnen Sie diese 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

Sobald Sie eine benutzerdefinierte Funktion geschrieben oder eine aus demGoogle Workspace Marketplaceinstalliert haben, kann sie genauso einfach verwendet werden 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. 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 ist Folgendes zu beachten:

• Der Name einer benutzerdefinierten Funktion muss sich von den Namen der integrierten 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() deklariert werden, nicht mit var myFunction = new Function().
• Die Großschreibung spielt keine Rolle, obwohl die Namen von Tabellenfunktionen traditionell in Großbuchstaben geschrieben werden.

Argumente

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

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

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

  
  

• Argumente von benutzerdefinierten Funktionen müssen deterministisch sein. Integrierte Tabellenfunktionen, die bei jeder Berechnung ein anderes Ergebnis zurückgeben, wie NOW() oder RAND(), sind also 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... auf unbestimmte Zeit angezeigt.

Rückgabewerte

Jede benutzerdefinierte Funktion muss einen anzuzeigenden Wert zurückgeben:

• 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 Wertearray zurückgibt, laufen die Werte in benachbarte Zellen über, 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 Zellen auswirken, 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 ihre benachbarten Zellen. Zum Bearbeiten beliebiger Zellen können Sie stattdessen ein benutzerdefiniertes Menü verwenden, 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 entsprechender Datentyp in JavaScript behandelt. Die häufigsten Gründe für Missverständnisse sind:

• Uhrzeiten und Datumsangaben in Google Tabellen werden in Apps Script zu Date-Objekten. Wenn Tabelle und Skript unterschiedliche Zeitzonen verwenden (ein seltenes Problem), muss die benutzerdefinierte Funktion dies ausgleichen.
• Dauerwerte in Google Tabellen werden ebenfalls zu Date-Objekten, aber die Arbeit mit ihnen kann kompliziert sein.
• In Google Tabellen werden Prozentsätze 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, wird eine Liste mit 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 DOUBLE()-Beispiel 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

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 Satz ins Spanische zu übersetzen.

Im Gegensatz zu den meisten anderen Arten von Apps Scripts werden Nutzer bei benutzerdefinierten Funktionen niemals dazu 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 bei benutzerdefinierten Funktionen nicht besonders nützlich
HTML	Kann HTML-Code generieren, aber nicht anzeigen (selten nützlich)
JDBC
Sprache
Sperren	Funktioniert, ist aber bei benutzerdefinierten Funktionen nicht besonders nützlich
Maps	Wegbeschreibungen können berechnet, aber keine Karten angezeigt werden
Properties	getUserProperties() ruft nur die Eigenschaften des Tabelleninhabers ab. Tabelleneditoren 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. zurückgibt, erfordert der Dienst eine Nutzerautorisierung und kann daher nicht in einer benutzerdefinierten Funktion verwendet werden.

Wenn Sie einen anderen als die oben genannten 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 um eine Autorisierung auf. Dadurch können alle Apps Script-Dienste verwendet werden.

Inhalte teilen

Benutzerdefinierte Funktionen sind 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, 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 in der ursprünglichen Tabelle nicht ö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 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, den an die Tabelle angehängten Apps Script-Code bearbeiten kann.

Optimierung

Jedes Mal, wenn eine benutzerdefinierte Funktion in einer Tabellenkalkulation verwendet wird, ruft Google Tabellen den Apps Script-Server separat auf. Wenn Ihre Tabelle Dutzende (oder Hunderte oder Tausende!) von benutzerdefinierten Funktionsaufrufen enthält, kann dieser Vorgang recht langsam sein.

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 ü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;
}

Bei diesem Ansatz wird die map-Methode des JavaScript-Objekts Array 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. Auf diese Weise können Sie DOUBLE nur einmal aufrufen, aber eine große Anzahl von Zellen gleichzeitig berechnen lassen, wie im Screenshot unten gezeigt. (Sie könnten dasselbe mit verschachtelten if-Anweisungen anstelle des map-Aufrufs erreichen.)

Auf ähnliche Weise ruft die folgende benutzerdefinierte Funktion Live-Inhalte effizient aus dem Internet ab und verwendet ein zweidimensionales Array, um zwei Ergebnisspalten mit nur einem einzigen Funktionsaufruf anzuzeigen. Wenn jede Zelle einen eigenen Funktionsaufruf benötigt, würde der Vorgang erheblich länger dauern, 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 können jedoch je nach Verhalten der Funktion variieren.