Google Apps Script: eigenes Add-on für Google Sheets bauen

Ein Add-on für Google Sheets entsteht mit Google Apps Script: JavaScript, das auf Googles Servern läuft und über eingebaute Dienste wie SpreadsheetApp auf Tabellen zugreift. Sie öffnen in Google Sheets unter „Erweiterungen“ den Apps-Script-Editor, schreiben eine Funktion onOpen, die ein Menü anlegt, und zeigen bei Bedarf eine Seitenleiste mit HTML an. Ohne Installation, ohne eigenen Server.

Zwei Arten von Add-ons

Google unterscheidet zwei Add-on-Typen, die beide mit Apps Script gebaut werden:

Für eine Erweiterung, die gezielt in Google Sheets arbeitet und eigene Formeln oder eine frei gestaltete Seitenleiste braucht, ist das Editor-Add-on der richtige Typ. Darum geht es in diesem Artikel.

Schritt 1: Skript anlegen

Öffnen Sie eine Tabelle und wählen Sie „Erweiterungen“ und dann „Apps Script“. Es öffnet sich ein Editor mit einer Datei Code.gs. Das Projekt ist zunächst an diese Tabelle gebunden, ideal zum Entwickeln und Testen.

Schritt 2: Menü anlegen

function onOpen(e) {
  SpreadsheetApp.getUi()
    .createAddonMenu()
    .addItem("Leere Zeilen markieren", "leereZeilenMarkieren")
    .addItem("Seitenleiste öffnen", "seitenleisteZeigen")
    .addToUi();
}

function onInstall(e) {
  onOpen(e);
}

function leereZeilenMarkieren() {
  const blatt = SpreadsheetApp.getActiveSheet();
  const werte = blatt.getDataRange().getValues();
  werte.forEach((zeile, i) => {
    if (zeile.every((zelle) => zelle === "")) {
      blatt.getRange(i + 1, 1, 1, zeile.length).setBackground("#FFF4CC");
    }
  });
}

onOpen ist ein einfacher Trigger: Apps Script ruft die Funktion automatisch auf, wenn jemand die Tabelle öffnet. createAddonMenu() legt den Eintrag unter dem Menü „Erweiterungen“ an, wie es für Add-ons vorgesehen ist. onInstall sorgt dafür, dass das Menü direkt nach der Installation erscheint und nicht erst nach dem nächsten Öffnen.

Wichtig für Add-ons: In onOpen sind die Rechte eingeschränkt, solange das Add-on in einem Dokument noch nicht genutzt wurde. Legen Sie dort nur Menüs an und greifen Sie nicht auf Daten oder externe Dienste zu.

Schritt 3: Seitenleiste mit HTML

Legen Sie im Editor eine HTML-Datei Seitenleiste.html an:

<!DOCTYPE html>
<html>
  <body>
    <p>Spalte zum Prüfen (z. B. B):</p>
    <input id="spalte" value="B">
    <button onclick="pruefen()">Duplikate zählen</button>
    <p id="ergebnis"></p>
    <script>
      function pruefen() {
        const spalte = document.getElementById("spalte").value;
        google.script.run
          .withSuccessHandler((anzahl) => {
            document.getElementById("ergebnis").textContent = anzahl + " Duplikate gefunden";
          })
          .duplikateZaehlen(spalte);
      }
    </script>
  </body>
</html>

Und die passenden Funktionen in Code.gs:

function seitenleisteZeigen() {
  const html = HtmlService.createHtmlOutputFromFile("Seitenleiste").setTitle("Prüfwerkzeug");
  SpreadsheetApp.getUi().showSidebar(html);
}

function duplikateZaehlen(spalte) {
  const blatt = SpreadsheetApp.getActiveSheet();
  const werte = blatt.getRange(spalte + ":" + spalte).getValues().flat().filter(String);
  return werte.length - new Set(werte).size;
}

Die Seitenleiste läuft im Browser, der Code in Code.gs auf Googles Servern. google.script.run verbindet beide: Es ruft eine Server-Funktion auf und liefert das Ergebnis asynchron an einen Handler zurück. Prüfen Sie Eingaben aus der Seitenleiste immer auf dem Server, denn der Browser-Teil lässt sich verändern.

Schritt 4: Eigene Formeln

/**
 * Entfernt Leerzeichen am Anfang und Ende und wandelt in Kleinbuchstaben um.
 * @param {string} text Der Eingabetext
 * @return Der bereinigte Text
 * @customfunction
 */
function BEREINIGEN(text) {
  if (Array.isArray(text)) {
    return text.map((zeile) => zeile.map((z) => String(z).trim().toLowerCase()));
  }
  return String(text).trim().toLowerCase();
}

In einer Zelle steht danach =BEREINIGEN(A2). Der JSDoc-Kommentar sorgt dafür, dass Sheets die Funktion in der Autovervollständigung anzeigt. Die Prüfung auf ein Array erlaubt Aufrufe wie =BEREINIGEN(A2:A100), was deutlich schneller ist als hundert einzelne Aufrufe. Benutzerdefinierte Funktionen haben eine kurze maximale Laufzeit und dürfen keine Dienste verwenden, die eine Autorisierung erfordern.

Berechtigungen bewusst einschränken

Apps Script ermittelt die benötigten Rechte (OAuth-Scopes) automatisch aus dem Code. Das ist bequem, führt aber oft zu zu weit gefassten Rechten, etwa Zugriff auf alle Tabellen statt nur auf die aktuelle. Zwei Werkzeuge helfen:

• Annotation @OnlyCurrentDoc: Ein Kommentar mit dieser Annotation am Anfang einer Skriptdatei beschränkt den Zugriff auf das Dokument, in dem das Add-on gerade läuft.
• Scopes im Manifest: In den Projekteinstellungen lässt sich die Manifestdatei appsscript.json anzeigen. Dort tragen Sie unter oauthScopes ausdrücklich nur die nötigen Bereiche ein.

{
  "timeZone": "Europe/Berlin",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets.currentonly",
    "https://www.googleapis.com/auth/script.container.ui"
  ]
}

Weniger Rechte bedeuten einen harmloseren Zustimmungsdialog, mehr Vertrauen bei Nutzerinnen und Nutzern und eine einfachere Prüfung bei der Veröffentlichung. Warum zu breite Berechtigungen bei Erweiterungen generell ein Risiko sind, erklärt der Bereich Erweiterungen sicher nutzen.

Einstellungen speichern und externe Dienste abrufen

Ein Add-on braucht oft dauerhafte Einstellungen. Dafür bietet Apps Script den PropertiesService mit drei Bereichen:

function spalteSpeichern(spalte) {
  PropertiesService.getDocumentProperties().setProperty("pruefspalte", spalte);
}

Externe Schnittstellen erreichen Sie mit UrlFetchApp.fetch(). Dafür fordert das Skript einen zusätzlichen Scope für externe Anfragen an, und für veröffentlichte Add-ons müssen die angefragten Adressen ausdrücklich erlaubt werden. API-Schlüssel gehören nicht in den Quellcode, sondern bestenfalls in die Script Properties oder hinter ein eigenes Backend.

Trigger und Grenzen

Neben onOpen gibt es weitere einfache Trigger wie onEdit, der bei jeder Bearbeitung läuft. Sie arbeiten ohne Rückfrage, dürfen aber keine autorisierungspflichtigen Dienste nutzen. Für mehr Möglichkeiten gibt es installierbare Trigger, die zeitgesteuert oder bei Ereignissen mit den Rechten der Person laufen, die sie eingerichtet hat.

Apps Script setzt Kontingente und Laufzeitgrenzen. Ein einzelner Aufruf darf nur wenige Minuten dauern, und viele Dienste haben Tageslimits. Für große Tabellen gilt dasselbe Prinzip wie bei Office.js: Daten einmal mit getValues() lesen, im Speicher verarbeiten und einmal mit setValues() zurückschreiben, statt Zelle für Zelle zu arbeiten.

Testen

Im gebundenen Skript testen Sie Menüs und Seitenleisten direkt, indem Sie die Tabelle neu laden. Funktionen lassen sich im Editor auch einzeln ausführen; Ausgaben mit console.log erscheinen im Ausführungsprotokoll. Um das Verhalten als installiertes Add-on in anderen Tabellen zu prüfen, legen Sie im Editor über „Bereitstellen“ eine Testbereitstellung an. Testen Sie dabei auch den Fall, dass das Add-on in einer Tabelle zum ersten Mal geöffnet wird.

Wer lieber lokal arbeitet, nutzt das Kommandozeilenwerkzeug clasp, um Projekte zu synchronisieren und mit Git zu versionieren.

Veröffentlichen

Für die Veröffentlichung im Google Workspace Marketplace sind mehrere Schritte nötig:

1. Eigenes Google-Cloud-Projekt: Das Skript wird mit einem Standard-Cloud-Projekt verknüpft statt mit dem automatisch angelegten.
2. OAuth-Zustimmungsbildschirm: Name, Logo, Datenschutzerklärung und Kontakt hinterlegen.
3. Bereitstellung als Add-on: eine versionierte Bereitstellung erzeugen.
4. Marketplace SDK: im Cloud-Projekt aktivieren, App-Konfiguration und Store-Eintrag ausfüllen.
5. Prüfung: Je nach angeforderten Scopes durchläuft die App eine OAuth-Überprüfung, bei sensiblen Bereichen auch eine aufwendigere Sicherheitsprüfung.

Für den Einsatz in der eigenen Organisation lässt sich das Add-on privat veröffentlichen, sodass nur Konten der eigenen Domain es sehen. Allgemeine Hinweise zu Stores und Prüfungen bündelt der Bereich Add-ons veröffentlichen. Den Vergleich zur Microsoft-Welt zeigt der Artikel Excel-Add-in mit Office.js entwickeln.