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:
- Erstellen oder eine Tabelle in Google Tabellen öffnen.
- Wählen Sie den Menüpunkt Erweiterungen > aus. Apps Script
- 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. - Klicken Sie oben auf „Speichern“ ( ).
Jetzt können Sie die benutzerdefinierte Funktion verwenden.
Benutzerdefinierte Funktion aus Google Workspace Marketplaceabrufen
Das Google Workspace Marketplace bietet mehrere benutzerdefinierte Funktionen wie Add-ons für Google Tabellen. So verwenden Sie diese Add-ons:
- Erstellen oder eine Tabelle in Google Tabellen öffnen.
- Klicken Sie oben auf Add-ons > Add-ons installieren
- Sobald der Google Workspace Marketplace wird geöffnet, klicken Sie rechts oben auf das Suchfeld.
- Geben Sie „benutzerdefinierte Funktion“ ein und drücken Sie die Eingabetaste.
- Wenn Sie ein interessantes Add-on für benutzerdefinierte Funktionen finden, klicken Sie auf Installieren. um sie zu installieren.
- 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.
- 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:
- Klicken Sie auf die Zelle, in der Sie die Funktion verwenden möchten.
- Geben Sie ein Gleichheitszeichen (
=
) gefolgt von dem Funktionsnamen und einem beliebigen Eingabewert ein: z. B.=DOUBLE(A1)
– und drücken Sie die Eingabetaste. - 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 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()
, nichtvar 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 (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 interpretiertdouble([[1,3],[2,4]])
. Beachten Sie, dass der Beispielcode fürDOUBLE
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()
oderRAND()
, 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, wirdLoading...
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 ein Fehler angezeigt:
Internal error executing the custom function.
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 zu0.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.
In dieser Liste werden benutzerdefinierte Funktionen angezeigt, 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. Jede Person, die Zugriff auf die Tabelle hat, kann die . (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.
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.)
Ebenso können mit der unten angegebenen benutzerdefinierten Funktion Live-Inhalte effizient 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 Verfahren lassen sich auf nahezu jede benutzerdefinierte Funktion anwenden, wiederholt über eine Tabelle hinweg, obwohl die Details zur Implementierung variieren je nach Verhalten der Funktion.