SearchKit & Excel / Google Sheets

Wir laden SearchKit Suchergebnisse über die API in Excel oder Google Sheets. Die Datenvisualisierung und weitere Auswertung machen wir dort.

🧹 daten-organisieren: CiviCRM Datenbank; Power Query bzw. Google Apps Script zur Ansprache der CiviCRM API
🔢 daten-auswerten: SearchKit
📊 daten-visualisieren: Excel bzw. Google Sheets

SearchKit, Excel und Power Query

Voraussetzungen

• CiviCRM API Schlüssel, um auf die folgenden Endpunkte der CiviCRM API eurer Instanz zuzugreifen:
  • ajax/api4/SavedSearch/get
  • ajax/api4/SearchDisplay/download
• Lizenz für Microsoft Excel Desktop Version¹, optional für Excel für das Web

Anleitung

A. Setup

1. Ladet die test-excel.xlsx Datei aus dem GitHub Repository herunter. Dazu oben rechts auf die drei Punkte und dann auf "Download" bzw. "Herunterladen" klicken.

2. Öffnet die Datei in der Desktop Version von Microsoft Excel.

3. Aktiviert die externen Datenverbindungen, indem ihr auf "Inhalt aktivieren" klickt.

4. Öffnet den Power Query Editor über den Ribbon Daten -> Daten abfragen (Power Query) -> Power Query-Editor starten.

5. In der Seitenleiste links wählt den Eintrag CiviToken. Gebt euren API Schlüssel für die CiviCRM API ein. Klicke auf "Übernehmen".

B. Eigene SearchKit-Suchen herunterladen

Im Power Query Editor ist die Funktion getSavedSearch (dt. "bekomme gespeicherte Suche") hinterlegt. Mit dieser Funktion könnt ihr eigene SearchKit Suchen abfragen.

1. In der linken Seitenleiste -> fx getSavedSearch

2. in das Feld "SearchName" den Namen eurer SearchKit Suche eingeben. Wichtig: Leerzeichen müsst ihr mit Unterstrichen ersetzen. z.B. wird "geschlecht statistik" zu "geschlecht_statistik" oder "Zuwendungen nach Zahlungsmethode" zu "Zuwendungen_nach_Zahlungsmethode". auf "Aufrufen" klicken.

3. dies legt einen neuen Query in der Seitenleiste an und führt ihn direkt aus. Ihr könnt dem Query mit Rechtsklick einen aussagekräftigeren Namen geben.

4. Wenn ihr ganz normal auf den Query in der Seitenleiste klickt, solltet ihr das Ergebnis eurer SearchKit Suche als Tabelle sehen.

5. Sehr ihr anstatt dessen eine Warnung oder einen Fehler, ist etwas schief gelaufen. Überprüft, ob ihr die richtigen Zugriffsrechte habt und ob ihr den API Schlüssel bei Setup richtig hinterlegt habt.

6. Um in Excel gut weiterarbeiten zu können, müsst ihr noch sicherstellen, dass die Daten den richtigen Typ haben. Das ist v.a. wichtig für Zahlenwerte wie Anzahl, Mittelwert, usw. Hierzu in der Vorschautabelle im Power Query Editor Rechtsklick auf den Spaltennamen -> Änderungstyp. Dann den gewünschten Datentyp auswählen.

7. Schließt den Power Query Editor mit dem Button Schließen und Laden. Dies legt ein neues Tabellenblatt für die neuen Queries an und fügt die Ergebnistabellen dort ein.
8. Ihr könnt jetzt die Tabelle markieren mit den Daten weiterarbeiten: z.B. ein Diagramm (Einfügen -> Diagramm auswählen) oder eine Pivot-Tabelle (Einfügen -> PivotTable) einfügen.

Tipp: auf Windows könnt ihr noch genauer kontrollieren, wie und wo ihr eure Power Queries verwenden wollt. Auf Youtube gibt es viele Videos zu Power Query, die mit der Windows Version arbeiten und euch zeigen, was ihr noch so machen könnt.

C. Power Queries aktualisieren und bearbeiten

Über Ribbon Daten -> Alle Aktualisieren könnt ihr eure Power-Query-Abfragen aktualisieren. Verbundene Diagramme, Pivot-Tabellen und sonstige Auswertungen aktualisieren sich automatisch.

Falls ihr die Funktion bearbeiten möchtet, könnt ihr dies über Rechtsklick auf getSavedSearch -> Erweiterter Editor tun. Den Code für die Funktion sowie einen Power Query, der euch eine Liste aller SearchKit Suchen zurückgibt, haben wir auch im GitHub Repository hinterlegt.

Fazit

Die Verwendung von Microsoft Excel und PowerQuery ist vor allem sinnvoll, wenn ihr ...

• a) ... sowieso schon Daten in Excel verwaltet und ihr diese mit Daten aus eurem CiviCRM kombinieren wollt
• b) ... eine unkomplizierte, interne Lösung sucht, um einfache Grafiken und "Dashboards" zu erstellen

Da es sich bei Microsoft um einen US-amerikanischen "Big-Tech" Anbieter handelt, solltet ihr auf Datenschutz und auch -sicherheit achten, vorzugsweise nur aggregierte Daten importieren und generell überlegen, welche Art von Daten ihr "in die Hände von Google" geben wollt, selbst wenn sie nicht mehr unter die DSGVO fallen.

SearchKit, Google Sheets und Google Apps Script

Voraussetzungen

• CiviCRM API Schlüssel, um auf die folgenden Endpunkte der CiviCRM API eurer Instanz zuzugreifen:
  • ajax/api4/SavedSearch/get
  • ajax/api4/SearchDisplay/download
• Google Konto²

Anleitung

A. Setup

Schneller Setup

1. Erstellt eine Kopie dieses Google Sheets: Datei -> Kopie erstellen.
2. Ihr bekommt eine Warnung, dass eine Apps Script-Datei ebenfalls kopiert wird. Ihr könnt den Code erst überprüfen, bevor ihr eine Kopie erstellt.
3. Öffnet das erstellte Google Sheet und öffnet die Google Apps Script Konsole über Erweiterungen -> Apps Script. Ein neues Fenster öffnet sich mit der Datei runAllSearches.gs geöffnet.

Manueller Setup

1. Erstellt ein leeres Google Sheet und gebt ihm einen sinnvollen Namen (z.B. "CiviCRM API Test")
2. Öffnet Google Apps Script (Erweiterungen -> Apps Script). Es öffnet sich ein Editor.
3. Benennt die Datei Code.gs um in runAllSearches (drei Punkte rechts neben Namen -> Umbenennen) und drückt Enter.
4. Löscht den Beispielcode und kopiert den Code der Datei runAllSearches.js in das Editorfenster. Speichert mithilfe von STRG/CMD+S oder dem Diskettensymbol.
5. Erstellt eine neue Script-Datei im Apps Script Editor: + Zeichen bei Datei -> Script

5. Löscht den Beispielcode und kopiert den Code der Datei fetchSavedSearch.js in das Editorfenster. Speichert mithilfe von STRG/CMD+S oder dem Diskettensymbol.

B. Apps Script ausführen & automatisieren

Manuelle Ausführung

Um den Code laufen zu lassen, drückt ihr auf den Button "Ausführen" (en: "Run") (nicht: "Bereitstellen"!), wenn ihr runAllSearches.gs geöffnet habt. Wenn ihr zum ersten Mal den Code ausführt, werdet ihr dazu aufgefordert, der App Berechtigungen zu geben (mehr dazu im aufklappbaren Infokasten).

Im Ausführungsprotokoll (en: Execution Log) könnt ihr nachvollziehen, ob die Ausführung funktioniert.

Automatisierte Ausführung

Damit ihr nicht jedes Mal auf "Ausführen" klicken müsst, könnt ihr die Ausführung von runAllSearches.gs auch mithilfe von sogenannten Triggern automatisieren. Trigger lösen die Ausführung des Skripts aus. Es gibt verschiedene Trigger (z.B. beim Öffnen des Google Sheets etc.), eine Übersicht findet ihr hier.

Die genaue Wahl des Triggers hängt von eurer Anwendung ab. Wir empfehlen:

• Zeitgesteuert: Aktualisiert die Daten und damit verknüpften Visualisierungen regelmäßig. Den genauen Rhythmus (stündlich/täglich/wöchentlich) legt ihr fest. Eine populäre Wahl ist einmal pro Tag mit Ausführung in der Nacht (siehe Screenshot).
• beim Öffnen des Google Sheets: Garantiert, dass sich eure Daten und damit verknüpfte Visualisierungen bei jedem Öffnen des Sheets aktualisieren.

Die Einrichtung eines Triggers ist intuitiv über eine grafische Benutzeroberfläche im Google Apps Script Editor möglich. Eine kurze Anleitung findet ihr hier.

C. Eure eigenen Suchen abrufen

Um eure eigenen Suchen abzurufen, müsst ihr den Code im Google Apps Script Editor anpassen.

In der Datei runAllSearches.gs:

1. Fügt in Zeile 2 euren API-Schlüssel zwischen den "" ein.
2. Fügt in Zeile 10 den Namen eurer SearchKit Suche und den Namen des Tabellenblatts in den Funktionsaufruf von fetchSavedSearch jeweils zwischen den "" ein. Das Tabellenblatt muss noch nicht existieren, die Funktion erstellt es dann automatisch.
3. Optional: Wiederholt Schritt 1+2 in Zeile 14, wenn ihr zusätzlich Ergebnisse einer anderen Suche in ein weiteres Tabellenblatt importieren wollt. Falls nicht, löscht den Code in Zeile 14.
4. Optional: Wenn ihr noch mehr Suchen importieren wollt, könnt ihr den Code aus Zeile 10 so oft kopieren und anpassen (siehe Schritte 1+2), wie ihr wollt.

function runAllSearches() {
  var apiKey = "fügt euren API-SCHLÜSSEL hier ein"; // Replace with your actual API key within the "", e.g. "b2391932kss"

 // Dokumentation der fetchSavedSearch Funktion
 // fetchSavedSearch(searchName, sheetName, apiKey);
 // @param {string} searchName - The name of the saved search in CiviCRM.
 // @param {string} sheetName - The name of the sheet in the Google Spreadsheet where data should be written. Does not need to exist when executed
 // @param {string} apiKey - Your CiviCRM API key.

  fetchSavedSearch("fügt den Namen eurer SearchKit Suche hier ein", "fügt den Namen des Google Sheet Blattes hier ein", apiKey);
  //Beispiel: fetchSavedSearch("geschlecht_statistik", "Geschlechtsverteilung", apiKey);

  // ihr könnt noch mehr Suchen in anderen Blättern abrufen
  fetchSavedSearch("fügt den Namen eurer SearchKit Suche hier ein", "fügt den Namen des Google Sheet Blattes hier ein", apiKey);
  //Beispiel: fetchSavedSearch("Bundesland", "Bundeslandverteilung", apiKey);
}

Folgt der Anleitung in B. Apps Script ausführen, um euren angepassten Code auszuführen.

D. Visualisierung

Sobald die Daten in Google Sheets importiert sind, könnt ihr wie gewohnt die Visualisierungstools von Google Sheets verwenden, um Balken-, Tortendiagramme usw. zu erstellen. Auf YouTube und mit der Suchmaschine eurer Wahl findet ihr zahlreiche Ressourcen.

Die Visualisierungen könnt ihr im jeweiligen Tabellenblatt hinzufügen oder in einem eigenen "Dashboard"-Sheet arrangieren (z.B. hier).

Die interaktiven Datenvisualisierungen lassen sich einzeln veröffentlichen und in eine Website einbetten. Hier der Donut-Chart zur Geschlechtsverteilung, den ihr auch im Sheet "Dashboard" unseres Google Sheets findet:

Fazit

Die Verwendung von Google Sheets und Google Apps Script ist vor allem sinnvoll, wenn ihr ...

• a) ... sowieso schon Daten in Google Sheets verwaltet und ihr diese mit Daten aus eurem CiviCRM kombinieren wollt
• b) ... eine unkomplizierte, interne Lösung sucht, um einfache Grafiken und "Dashboards" zu erstellen
• c) ... einen "Zwischenspeicher" für andere Anwendungen benötigt, die Google Sheets als Datenquelle verwenden können

Da es sich bei Google um einen US-amerikanischen "Big-Tech" Anbieter handelt, solltet ihr auf Datenschutz und auch -sicherheit achten, vorzugsweise nur aggregierte Daten importieren und generell überlegen, welche Art von Daten ihr "in die Hände von Google" geben wollt, selbst wenn sie nicht mehr unter die DSGVO fallen.