Benutzerdefinierte Funktionen in Google Tabellen

Google Tabellen bietet Hunderte von integrierten Funktionen wie AVERAGE, SUM und VLOOKUP Wenn diese nicht Ihre Anforderungen erfüllen, können Sie mit Google Apps Script benutzerdefinierte Funktionen – zum Beispiel Meter in Meilen umrechnen oder abrufen Liveinhalte aus dem Internet – und sie dann zu verwenden wie bei einer integrierten Funktion.

Erste Schritte

Benutzerdefinierte Funktionen werden mit Standard-JavaScript erstellt. Wenn Sie neu bei „Codecademy“ bietet guten Kurs für Einsteiger. Hinweis: Dieser Kurs wurde nicht von Google entwickelt und ist nicht in Verbindung mit Google.

Hier ist eine einfache benutzerdefinierte Funktion namens DOUBLE, mit der ein Eingabewert durch 2:

/**
 * 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 JavaScript-Kenntnisse und keine Zeit haben, finden Sie im Add-on-Store Sie können prüfen, 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 eine Tabelle in Google Tabellen öffnen.
  2. Wählen Sie den Menüpunkt Erweiterungen > aus. Apps Script
  3. Wenn der Skripteditor Code enthält, löschen Sie diesen. Für die obige DOUBLE-Funktion kopieren Sie 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 wie Add-ons für Google Tabellen. So verwenden Sie diese Add-ons:

  1. Erstellen oder eine Tabelle in Google Tabellen öffnen.
  2. Klicken Sie oben auf Add-ons > Add-ons installieren
  3. Sobald der Google Workspace Marketplace wird geöffnet, klicken Sie rechts oben auf das Suchfeld.
  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 sie zu installieren.
  6. Möglicherweise wird in einem Dialogfeld angezeigt, dass das Add-on autorisiert werden muss. Wenn 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. So verwenden Sie das Add-on in einem eine andere Tabellenkalkulation. Öffnen Sie sie 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.

Benutzerdefinierte Funktion verwenden

Sobald Sie eine benutzerdefinierte Funktion geschrieben oder eine aus der Google Workspace Marketplace, es ist so einfach zu bedienen wie ein 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 beachten Sie Folgendes:

  • Der Name einer benutzerdefinierten Funktion muss sich von den Namen der integrierten Funktionen wie SUM()
  • Der Name einer benutzerdefinierten Funktion darf nicht mit einem Unterstrich (_) enden. bezeichnet eine private Funktion in Apps Script.
  • Der Name einer benutzerdefinierten Funktion muss mit der Syntax deklariert werden function myFunction(), nicht var myFunction = new Function().
  • Die Großschreibung spielt keine Rolle, obwohl die Namen von Tabellenkalkulationsfunktionen sind traditionell großgeschrieben.

Argumente

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

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

  • Wenn Sie Ihre Funktion mit einem Bezug auf einen Zellenbereich als Argument (wie =DOUBLE(A1:B10)), ist das Argument ein zweidimensionales das Array der Zellen Werte. Im folgenden Screenshot sehen Sie beispielsweise Argumente in =DOUBLE(A1:B2) werden von Apps Script wie folgt interpretiert double([[1,3],[2,4]]). Beachten Sie, dass der Beispielcode für DOUBLE von oben geändert, um ein Array als Eingabe zu akzeptieren.


  • Argumente von benutzerdefinierten Funktionen müssen deterministisch. Das integrierte Tabellenkalkulationsfunktionen, die jedes Mal ein anderes Ergebnis zurückgeben, die von ihnen berechnet werden, wie NOW() oder RAND(), sind nicht als Argumente zulässig. einer benutzerdefinierten Funktion hinzu. Wenn eine benutzerdefinierte Funktion versucht, einen Wert basierend auf einer dieser veränderlichen integrierten Funktionen erscheint, wird Loading... angezeigt. auf unbestimmte Zeit.

Rückgabewerte

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

  • Wenn eine benutzerdefinierte Funktion einen Wert zurückgibt, wird dieser in der Zelle angezeigt. über den die Funktion aufgerufen wurde.
  • Gibt eine benutzerdefinierte Funktion ein zweidimensionales Array von Werten zurück, werden die Werte in benachbarte Zellen überlaufen, sofern diese leer sind. Wenn dies vorhandene Zelleninhalte durch das Array überschreiben, wird mit der benutzerdefinierten Funktion wird ein Fehler ausgegeben. 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, eine benutzerdefinierte Funktion kann keine beliebigen Zellen bearbeiten, nur die Zellen Zellen, von denen er aufgerufen wird, und deren angrenzenden Zellen. Um beliebige Zellen zu bearbeiten, Verwenden Sie stattdessen ein benutzerdefiniertes Menü, um eine Funktion auszuführen.
  • Ein Aufruf einer benutzerdefinierten Funktion muss innerhalb von 30 Sekunden zurückgegeben werden. Ist dies nicht der Fall, In der Zelle wird #ERROR! angezeigt und die Zellennotiz ist Exceeded maximum execution time (line 0).

Datentypen

Google Tabellen speichert Daten unterschiedliche Formate, je nachdem, die Art der Daten. Wenn diese Werte in benutzerdefinierten Funktionen verwendet werden, Das Skript behandelt sie wie die entsprechenden Datentyp in JavaScript. Dies sind die häufigsten Bereiche für Missverständnisse:

  • Datum und Uhrzeit in Google Tabellen werden zu Date-Objekte in Apps Script. Wenn die Tabellenkalkulation und der unterschiedliche Zeitzonen verwendet (seltenes Problem), führt die benutzerdefinierte Funktion kompensieren müssen.
  • Werte für die Dauer in Google Tabellen werden ebenfalls zu Date-Objekten, aber die Zusammenarbeit mit ihnen zu erschweren.
  • Prozentwerte in Google Tabellen werden in Apps Script zu Dezimalzahlen. Für 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 für integrierten Funktionen zur Verfügung stellen. Während Sie geben Sie einen Funktionsnamen in eine Zelle ein. Daraufhin wird eine Liste mit Funktionen, die Ihrer Eingabe entsprechen.

Benutzerdefinierte Funktionen erscheinen in dieser Liste, wenn ihr Skript ein JsDoc @customfunction 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 für komplexere Aufgaben Aufgaben. Eine benutzerdefinierte Funktion kann beispielsweise die Methode Sprachdienst zum Übersetzen eines englischen Textes ins Spanische.

Im Gegensatz zu den meisten anderen Apps Scripts werden Nutzer bei benutzerdefinierten Funktionen nie dazu aufgefordert, Zugriff auf personenbezogene Daten gewähren. Daher können sie nur Dienste anrufen, 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 Attribute der Tabelleneigentümer. Bearbeiter von Tabellen können keine Nutzereigenschaften in einem benutzerdefinierte Funktion hinzu.
Tabelle Schreibgeschützt (kann die meisten get*()-Methoden verwenden, aber nicht set*()).
Andere Tabellen (SpreadsheetApp.openById()) können nicht geöffnet werden. oder SpreadsheetApp.openByUrl()).
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 die in einer benutzerdefinierten Funktion verwendet werden.

Wenn Sie einen anderen als die oben aufgeführten Dienste nutzen möchten, erstellen Sie ein benutzerdefiniertes Menü zum Ausführen einer Apps Script-Funktion anstatt eine benutzerdefinierte Funktion zu schreiben. Eine Funktion, die über ein Menü ausgelöst wird bittet den Nutzer bei Bedarf um Autorisierung und kann folglich alle Apps Script-Dienste

Freigabe

Benutzerdefinierte Funktionen sind anfangs gebunden an den Tabelle, in der sie erstellt wurden. Das bedeutet, dass eine benutzerdefinierte Funktion, Tabelle kann nicht in anderen Tabellenkalkulationen verwendet werden, es sei denn, Sie verwenden eine der folgenden Methoden:

  • Klicken Sie auf Erweiterungen > Apps Script, um öffnen Sie den Script-Editor und kopieren Sie Skripttext aus der ursprünglichen Tabelle und fügen Sie ihn in den Skripteditor ein. einer anderen Tabellenkalkulation.
  • Erstellen Sie eine Kopie der Tabelle mit der benutzerdefinierten Funktion, indem Sie auf Datei > Kopie erstellen. Beim Kopieren einer Tabelle werden alle Skripts, die an werden auch kopiert. Jeder, der Zugriff auf die Tabelle hat, kann das Feld . (Mitbearbeiter, die nur Lesezugriff haben, können den Skripteditor nicht öffnen. in der ursprünglichen Tabelle. Wenn sie jedoch eine Kopie erstellen, Eigentümer der Kopie und kann das Skript sehen.)
  • Veröffentlichen Sie das Skript als Editor-Add-on für Google Tabellen.
<ph type="x-smartling-placeholder">

Optimierung

Jedes Mal, wenn eine benutzerdefinierte Funktion in einer Tabelle verwendet wird, erstellt Google Tabellen eine separaten Aufruf an den Apps Script-Server. Enthält Ihre Tabelle Dutzende (oder Hunderte oder Tausende!) benutzerdefinierter Funktionsaufrufe kann dieser Prozess durchaus langsam ist.

Wenn Sie also planen, eine benutzerdefinierte Funktion mehrmals auf einer ändern Sie die Funktion so, dass sie einen Bereich als in Form einer zweidimensionalen Matrix eingeben und dann eine zweidimensionale Array, das in die entsprechenden Zellen überlaufen kann.

Die oben gezeigte Funktion DOUBLE() kann beispielsweise so umgeschrieben werden, dass ein einer einzelnen Zelle oder eines Zellenbereichs:

/**
 * 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 Methode map-Methode des JavaScript-Objekts Array, um rekursiv DOUBLE für jeden Wert im zweidimensionalen Array von Zellen aufrufen. Es wird ein zweidimensionales Array, das die Ergebnisse enthält. So können Sie DOUBLE anrufen und zwar für eine große Anzahl von Zellen gleichzeitig, wie in Screenshot unten. (Sie könnten dasselbe mit der verschachtelten if-Datei anstelle des map-Aufrufs.)

Die benutzerdefinierte Funktion unten ruft auf effiziente Weise Live-Inhalte aus dem Internet und verwendet ein zweidimensionales Array, um zwei Ergebnisspalten mit nur einen einzigen Funktionsaufruf. Wenn für jede Zelle ein eigener Funktionsaufruf erforderlich ist, erheblich länger dauern, da der Apps Script-Server müssen den XML-Feed jedes Mal herunterladen und parsen.

/**
 * 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 nahezu jede benutzerdefinierte Funktion angewendet werden, wiederholt über eine Tabelle hinweg, obwohl die Details zur Implementierung variieren je nach Verhalten der Funktion.