Anfang November 2017 hat das Excel-Entwicklerteam bei Microsoft die Preview zu benutzerdefinierten JavaScript-Funktionen in Excel 2016 freigegeben. Die Preview kann derzeit – also Anfang Dezember 2017 – nur in der Insider (Fast) Version von Microsoft Office 2016 verwendet werden. Nutzern der Insider-Version bzw. ehemals Office Insider Fast stehen die neuesten z.B. für Excel verfügbaren Features zur Verfügung.
In diesem Artikel beschreibe ich, wie Sie eine benutzerdefinierte JavaScript-Funktion für Excel implementieren und wie Sie diese in Excel integrieren.
Microsoft stellt zu diesem Thema ein englischsprachiges Tutorial auf der Website für Office-Entwickler sowie ein Beispiel mit einigen JavaScript-Funktionen auf GitHub zur Verfügung. Zum Erstellen einer oder auch mehrerer eigener JavaScript-Funktionen in Excel 2016 Insider sind im Grunde genommen nur vier Schritte erforderlich:
- Herunterladen der drei Dateien customfunctions.html, customfunctions.js und manifest.xml von der zuvor genannten GitHub-Seite.
- Anpassen der HTML- und JavaScript-Dateien sowie Hochladen auf einem Server, der das sichere Protokoll https unterstützt.
- Anpassen des Manifests zur Referenzierung der HTML- und JavaScript-Dateien auf dem Server.
- Sideload des Manifests in Excel 2016 zur Integration der JavaScript-Funktionen in Excel.
Für das nachfolgende Beispiel habe ich die drei Dateien zunächst in language.html, language.js und language.xml umbenannt. Und, als Beispiel habe ich mir überlegt, die Excel-Funktion INFO so zu erweitern, dass ich mir über die neue Funktion INFO.LANGUAGE die in Excel aktuell verwendete Anzeigesprache anzeigen lassen kann. Wohl wissend aber, dass INFO.LANGUAGE in keiner der für Excel verfügbaren Sprachpakete vorkommt. Dazu aber gleich mehr.
Aktive Anzeigesprache mit JavaScript in Excel abfragen
Im Office JavaScript API liefert die Eigenschaft displayLanguage die von der Anwendung verwendete Anzeigesprache in Form eines Sprachcodes zurück, z.B. Beispiel de-DE für Deutsch oder en-US für Englisch. Der Zugriff auf die Eigenschaft erfolgt über Office.context, weshalb eine recht einfache JavaScript-Funktion wie folgt aussieht, wo ich noch zusätzlich den Rückgabewert in Kleinschreibung umgewandelt habe:
function GetLanguageValue () {
return Office.context.displayLanguage.toLowerCase();
}
HTML-Datei anpassen
Das Anpassen der HTML-Datei ist recht einfach. Es muss nur die Referenzierung auf die JavaScript-Datei angepasst werden, hier dann auf language.js.
JavaScript-Beispieldatei reduzieren und verstehen
Die von Microsoft gelieferte Beispieldatei ist etwas umfangreicher und beinhaltet Beispiele für mehrere JavaScript-Funktionen. Die Beispieldatei lässt sich dennoch prima als Ausgangsbasis für eigene Experimente nutzen, was wohl auch sicherlich Einsteigern etwas entgegenkommt.
Somit wäre ein erster Schritt zur Erstellung der Funktion INFO.LANGUAGE das Reduzieren des Codes in der Beispieldatei. Entfernen Sie alles, was sich in der Beispieldatei auf Temperature bezieht sowie den Code, der dem Code von Excel.run folgt. Der verbleibende Code in der Beispieldatei sieht dann so aus, wie hier unten zu sehen.
Es sind somit nur die Definition eines Präfixes, die JavaScript-Funktion add42, eine Parametrisierung von CONTOSO.ADD42 und der Code zur Registrierung der benutzerdefinierten Excel-Funktionen übriggeblieben. Schauen wir uns das im Detail etwas genauer an.
Zunächst wird in dem Code ein Präfix für die neuen Excel-Funktionen festgelegt. Ein Präfix, hier beispielhaft CONTOSO, ist zwingend erforderlich, was dann auch heißt, dass alle Ihre Funktionen die Form PRÄFIX.FUNKTIONSNAME annehmen werden. Es lässt sich als Präfix ein beliebiger Text verwenden und sogar auch ein bestehender Funktionsname wählen. Und, es lassen sich natürlich auch mehrere Präfixe festlegen, wodurch Sie z.B. Gruppen bilden könnten.
Es folgt im Code die Definition der JavaScript-Funktion add42, die die zwei Übergabeparameter a und b addiert und nochmal um 42 erhöht. Wissen schon, 42 ist ja die Antwort auf alles.
Anschließend folgt die Definition und Parametrisierung der benutzerdefinierten Funktion. Als erstes wird der Funktionsname definiert, hier ADD42. In Excel erscheint dann CONTOSO.ADD42. Die Referenzierung auf den Namen der JavaScript-Funktion add42 erfolgt über die Angabe zu call und zwar ohne die Angabe von Parametern und Klammern. Über die Angaben zu description und helpUrl legen Sie eine Beschreibung und Online-Hilfe-URL zu Ihrer Funktion fest. In der aktuellen Preview werden diese beiden Angaben allerdings noch nicht verwendet.
Die Angaben zu result legen fest, welchen Datentyp Ihre Funktion zurückgeben soll und ob es sich um einen einzelnen Wert oder eine Matrix handelt.
Die Auflistung Excel.CustomFunctionValueType kennt zurzeit die Datentypen number (Zahl), string (Text), boolean (Logisch), ISODate (Datum) und Invalid (Fehler). Und die Auflistung Excel.CustomFunctionDimensionality kennt die beiden Typen scalar (Wert) und matrix (Matrix).
In ähnlicher Form werden die einzelnen Parameter für die Excel-Funktion definiert, wobei deren Anzahl der Anzahl der Parameter in der aufgerufenen JavaScript-Funktion entsprechen sollte. Falls Ihre Funktion keine Parameter aufweist, müssen Sie dennoch die Angabe parameters aufführen und dabei den Inhalt zwischen den eckigen Klammern weglassen.
Die letzten Angaben zu options sind Parameter, die in der Preview zwar aufgeführt, aber nicht aktiv sind. Sie brauchen dort nichts ändern.
Anmerkungen zur Namensgebung von benutzerdefinierten JS-Funktionen
Beachten bei der Wahl des Präfixes und bei der Namensgebung für Ihre Funktionen, dass diese keinen Namen einer in Excel bereits bestehenden Funktion annehmen. Das würde dazu führen, dass Excel beide Funktion anzeigt, aber bei der Verwendung die interne Funktion priorisiert. Zudem würde ein User, der vielleicht nicht die exakten Parameter der integrierten Excel-Funktion kennt, nicht erkennen, dass eine benutzerdefinierte Funktion angesprochen würde. Dies gilt insbesondere auch sprachübergreifend. Wenn Sie also z.B. eine verbesserte Funktion der deutschen Funktion GAMMA.VERT entwickeln und Ihre Funktion GAMMA.DIST nennen würden, bestünde in einem englischen Excel ein Problem. Denn in einem englischen Excel gibt es bereits die Funktion GAMMA.DIST (als Übersetzung von GAMMA.VERT) und somit würde Ihre Version mit der integrierten Version konkurrieren.
Implementieren der benutzerdefinieren Excel-Funktion INFO.LANGUAGE
Nachdem die Beispieldatei insoweit reduziert wurde, dass nur eine Funktion übrigbleibt, sind nur noch wenige Anpassungen vorzunehmen, um die eigene benutzerdefinierte Funktion INFO.LANGUAGE zu implementieren. Die JavaScript-Funktion GetLanguageValue soll hierbei die aktive Anzeigesprache als Text zurückgeben. Der Code sieht dann wie nachfolgend abgebildet aus.
Die Beschreibung wurde zudem variabel gestaltet. Statt also den Beschreibungstext statisch zur Angabe description hinzuzufügen, erfolgt ein Aufruf der Funktion GetLanguageDescription. Das zeigt, dass es auch möglich ist, je nach aktiver Sprache einen anderen Beschreibungstext anzuzeigen.
Jetzt könnte man auf die Idee kommen, auch den Funktionsnamen LANGUAGE sprachabhängig zu gestalten. Zum Beispiel so, dass in einem deutschen Excel INFO.SPRACHE erscheint und in einem englischen Excel dann INFO.LANGUAGE.
Tatsächlich lässt sich der Funktionsname auch per Code dynamisch generieren, indem statt ["Language"] z.B. [GetLanguageFunction()] verwendet wird, wobei dann GetLanguageFunction eine JavaScript-Funktion ist. Jedoch erkennt Excel nicht, dass es sich letztlich um dieselbe Funktion handelt, wenn man in Excel die Sprache wechselt. Insofern sind zurzeit sprachabhängige, unterschiedliche Namen für dieselbe benutzerdefinierte Funktion nicht realisierbar.
Server einrichten und Daten hochladen
Nach den erfolgten Anpassungen bzw. Entwicklung eigener Funktionen, heißt es nun die HTML-Datei und die JavaScript-Datei auf einen Server hochzuladen, der das Protokoll https unterstützt.
In meinem Fall steht mir beispielsweise die Website zum Excel-Translator zur Verfügung, wo ich die beiden Dateien auf eine Subdomain hochladen könnte. Da ich aber auch ein Konto bei Microsoft Azure besitze und ich mir dort kostenlos eine WebApp inklusive https einrichten kann, hatte ich die beiden Dateien dorthin hochgeladen wie in dem Screenshot im nächsten Abschnitt zu sehen.
Falls Sie keinen Server haben, der https kann, empfehle ich Ihnen, aus einem meiner vorherigen Artikel zu Script Lab den Abschnitt zur Erstellung einer sicheren Website mit Microsoft Azure zu lesen, wo detaillierter beschrieben wird, wie Sie dies bewerkstelligen können.
Manifest anpassen und Sideload durchführen
Letzter Schritt ist nun die Anpassung des Manifests und der Sideload auf Ihrem lokalen Rechner. Öffnen Sie dazu die XML-Datei zum Manifest mit einem Editor und passen Sie Pfade zum Server an. Die Stellen sind in der Beispieldatei von Microsoft durch Insert-Url-Here gekennzeichnet. Im nachfolgenden Screenshot sind diese Stellen rot unterstrichen.
Ein ganz wichtiger Aspekt ist, auch die GUID anzupassen. Generieren Sie sich z.B. über den Online-GUID-Generator eine neue GUID und ersetzen Sie die GUID in der XML-Datei.
Der Sideload eines Excel-Add-Ins besteht darin, einmalig eine Ordnerfreigabe auf einem Ihrer lokalen Laufwerke einzurichten und als Add-In-Katalog in Excel einzubinden. Eine detaillierte Anleitung dazu habe ich in meinem Artikel Sideload von Excel JavaScript-Add-Ins veröffentlicht.
Schließlich verbleibt nur noch das Laden des Add-Ins in Excel. Wechseln Sie dazu im Menüband zu dem Reiter Einfügen und rufen Sie den Dialog zu den Office-Add-Ins auf. Wechseln Sie dort zum Reiter Freigegebene Ordner und fügen Sie Ihr Add-In hinzu.
Beachten Sie, dass Sie gegebenenfalls das Add-In neu laden müssen, wenn Sie Änderungen am JavaScript-Code vornehmen.
Fazit
JavaScript-Add-Ins eröffnen bzw. werden zukünftig vielfältige Anwendungsmöglichkeiten eröffnen. Ein Vorteil ist, dass diese Add-Ins auch auf anderen Plattformen als Windows laufen werden, wie z.B. Excel Online. Nachteil ist sicherlich, dass eine Internetverbindung bestehen muss, um z.B. Werte zu aktualisieren. Momentan handelt sich noch um eine Preview, man kann gespannt sein, was in den nächsten Wochen und Monaten noch kommt.
Happy Coding :-)
- Benutzerdefinierte JavaScript-Funktionen, Englisch
- Beispiel zu benutzerdefinierte JavaScript-Funktionen, Englisch
- Office Insider (Fast) Programm, Deutsch
- Office Insider Updateverlauf, Deutsch
- Online GUID Generator, Englisch
- Erstellung einer sicheren Website mit Microsoft Azure
- Sideload von Excel JavaScript-Add-Ins, Deutsch
- Prototyping von Microsoft Office JavaScript Add-Ins mit Script Lab, Deutsch
