API Excel Zugriff mit Windows (via Daten abrufen)

Prinzipiell gibt es für Windows Benutzer zwei Varianten um per Excel auf die HQ-API zuzugreifen:

  1. Zugriff über die Excel Funktion Daten abrufen
  2. Zugriff per Excel Add-In

Um die Daten direkt über Excel abzurufen musst du auf den Reiter "Daten" - "Daten abrufen" - "Aus anderen Quellen" - "Leere Abfrage" gehen.

Screen_1.png

Nun erscheint ein Fenster auf dem zu auf "Erweiterter Editor" klickst.

Screen_2.png

In nächsten Fenster muss die folgende Abfragen eingegeben werden (Einfach per Copy & Paste einfügen):

let 
    BaseUrl         = "https://api.hqlabs.de/v1/<<<HIER OBJEKT EINFÜGEN>>>?",
    Token           = "<<<HIER API TOKEN EINFÜGEN>>>",
    EntitiesPerPage = 1000,
 
    GetJson = (Url) =>
        let Options = [Headers=[ #"Authorization" = "Bearer " & Token ]],
            RawData = Web.Contents(Url, Options),
            Json    = Json.Document(RawData)
        in  Json,
 
    GetEntityCount = () =>
        let Url   = BaseUrl & "$count=true&$top=0",
            Json  = GetJson(Url),
            Count = Json[#"@odata.count"]
        in  Count,
 
    GetPage = (Index) =>
        let Skip  = "$skip=" & Text.From(Index * EntitiesPerPage),
            Top   = "$top=" & Text.From(EntitiesPerPage),
            Url   = BaseUrl & Skip & "&" & Top,
            Json  = GetJson(Url),
            Value = Json[#"value"]
        in  Value,
 
    EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
    PageCount   = Number.RoundUp(EntityCount / EntitiesPerPage),
    PageIndices = { 0 .. PageCount - 1 },
    Pages       = List.Transform(PageIndices, each GetPage(_)),
    Entities    = List.Union(Pages),
    Table       = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    Table

 

Nun sind noch 2 kleine Anpassungen notwendig. Erstens, musst du dich authentifizieren. Dies passiert über den “API Token”, den du dir direkt aus dem HQ holen kannst. Unter Admin/ API Clients ist dieser Token zu finden, der nun in unser Skript statt <<<HIER API TOKEN EINFÜGEN>>> einsetzen, eingefügt wird.

Nun musst du der API noch sagen, welche Objekte eigentlich abgerufen werden sollen (z.B. Leads, Unternehmen, Projekte usw.). Das gewünschte Objekt fügst du dann in den Code anstelle von <<<HIER OBJEKT EINFÜGEN>>> ein. Die Liste aller verfügbaren Objekte findest du unter https://developer.helloHQ.io 

Screen_3.png

Wenn Du nun auf "Fertig" klickst musst Du noch die Spalten erweitern.

Screen_4.png

Anschließend kann die Abfrage benannt werden und per "Schließen & laden" die Daten in die Excel übernommen werden.

Screen_5.png

Über den Reiter "Daten" - "Alle aktualisieren" können ab jetzt immer die aktuellen Daten aus dem HQ abgerufen werden.

Screen_6.png

Nun kannst Du mit den Daten in Excel weiterarbeiten und z.B. Pivot-Tabellen und Grafiken zur Auswertung erstellen. Natürlich kannst du hierfür auch mehrere Abfragen pro Excel hinzufügen, z.B. um Projekte und Unternehmen zu verbinden.

ACHTUNG: Wenn du eine ältere Version von Excel als 2016 verwendest musst du das kostenlose Excel Add-In PowerQuery von Microsoft installieren. 

API Excel Zugriff mit Windows (via Excel Add-In)

Prinzipiell gibt es für Windows Benutzer zwei Varianten um per Excel auf die HQ-API zuzugreifen:

  1. Zugriff per Excel Add-In
  2. Zugriff über die Excel Funktion Daten abrufen

ACHTUNG: Das Add-In funktioniert erst ab der Excel Version 2016 (oder höher)

Um Daten aus dem HQ via API abzurufen, kannst Du dir HQ als Excel Add-In installieren. Dazu muss zunächst die folgende Datei heruntergeladen werden:

hq-addin-manifest.xml

Diese muss auf dem PC in einem Ordner abgelegt werden, in welchem die Datei dauerhaft gespeichert werden kann (Achtung: Der Name des Ordners darf keine Leerzeichen enthalten). Anschließend muss die Datei per Rechtsklick / "Zugriff gewähren auf" / "Bestimmte Personen" an den Benutzer des PCs freigegeben werden.

Screenshot_1.png

Nun wird ein Link angezeigt, der per Rechtsklick kopiert werden muss. 

Screenshot_2.png

Als nächstes muss dieser Link in Excel hinterlegt werden. Dazu geht man in Excel auf "Datei" - "Optionen" - "Trust Center" - "Einstellungen für das Trust Center". 

Screenshot_3.png

Im neuen Fenster muss die Kategorie "Kataloge vertrauenswürdiger Add-Ins" ausgewählt werden und die URL unter "Katalog-URL" eingegeben werden (Achtung: Alle "/" müssen durch "\" ausgetauscht werden), sowie alle Inhalte nach dem Ordnernamen bzw. vor dem doppelten "\\" müssen entfernt werden (Siehe Screenshot). Klicke nun auf "Katalog hinzufügen" und wähle "Im Menü anzeigen". Nun auf "ok" klicken und Excel neu starten. 

Screenshot_4.png

Das Add-In kann nun in Excel über "Einfügen" "Meine Add-Ins" geladen werden.

Screenshot_5.png

Klicke nun auf "Geteilter Ordner", wähle die Option "HQ-API-ACCESS" aus und klicke auf "Hinzufügen".

Screenshot_6.png

Nun steht die HQ API per Button in Excel zur Verfügung.

Screenshot_7.png

Nun muss nur der API Token (zu finden im HQ unter Admin / API-Client) eingegeben und auf "Tabelle hinzufügen" geklickt werden. Nun werden die Daten aus der ausgewählten Tabelle in die Excel geladen. Über den Button "Daten aktualisieren" können immer die neusten Daten aus dem HQ abgerufen werden.

Screenshot_8.png

 

ACHTUNG: Damit die Datei auch mit größeren Datenmengen umgehen kann, muss die "Berechnungsoption" (in Excel unter Formeln) auf "manuell" gesetzt werden. Das Plugin führt diese Berechnung automatisch am Ende durch.

Screen.png

API Excel Zugriff mit MacOS (via Excel Add-In)

ACHTUNG: Das Add-In funktioniert erst ab der Excel Version 2016 (oder höher)

In Microsoft Excel für MacOS kannst du ebenfalls die HQ-API einbinden. Folge dafür einfach folgenden Schritten.

1. Installer herunterladen: https://hqlabs.zendesk.com/hc/article_attachments/360000272080/HQ_API_Excel_Plugin.zip

2. ZIP entpacken und Datei ausführen

(WICHTIG: Rechtsklick auf die .mpkg-Datei und dann "Öffnen" auswählen)

Screen_Shot_2018-08-27_at_18.13.07.png

3. Anweisungen im Installer folgen und installieren

Screen_Shot_2018-08-27_at_18.14.02.png

4. Microsoft Excel starten, zum Reiter "Einfügen" wechseln und bei "Meine Add-ins" auf den kleinen Pfeil klicken und das HQ-API-Add-in starten

Screen_Shot_2018-08-27_at_18.36.38.png

5. Auf API Access laden klicken und einen API-Token hinterlegen. API-Token erhältst du direkt aus deinem HQ unter Admin / API Clients.

Screen_Shot_2018-08-27_at_18.38.47.png

6. Um eine Entität wie Projekte, Unternehmen, Benutzer usw. aus dem HQ zu laden, wählst du zunächst die gewünschte Tabelle aus und klickst anschließend auf "Tabelle einfügen".

Excel fügt anschließend die Tabelle als neues Tabellenblatt ein, das in gewohnter Weise verwendet kann.

Screen_Shot_2018-08-27_at_18.40.41.png 

Um Daten zu aktualisieren reicht eine Kick auf "Daten aktualisieren".

Welche Entitäten aus der API geladen werden können, kannst du auch unter developer.helloHQ.io herausfinden.

ACHTUNG: Damit die Datei auch mit größeren Datenmengen umgehen kann, muss die "Berechnungsoption" (in Excel unter Formeln) auf "manuell gesetzt werden. Das Plugin führt diese Berechnung automatisch am Ende durch.

image.png

API Excel Zugriff - Daten aktualisieren (via Excel Add-In)

Die Abfrage der aktuellen Daten aus dem HQ ist sehr einfach. Dazu muss einfach das Plugin aktiviert werden und auf den Button "Daten aktualisieren" gerückt werden. 

Screen_1.jpg

Sind die Daten dann nicht aktuell muss ggf. die Berechnung manuell angestoßen werden (dies sollte eigentlich durch das Plugin passieren). Dazu einfach auf "Formeln" und "Neu berechnen" (oder F9) klicken. 

Screen_3.jpg

Wenn mit einer Pivot gearbeitet wird muss die Pivot selbst auch aktualisiert werden. Dazu einfach in die Pivot Tabelle klicken, auf "Analysieren" gehen und "Aktualisieren" klicken.

Screen_2.jpg

 

Daten aus dem HQ mit Power BI auswerten

Mit dem HQ haben unsere Kunden den gesamten Prozess ihrer Agentur an einer zentralen Stelle im Griff. Natürlich bietet diese Integration grenzenlose Möglichkeiten für gutes Controlling und Auswertungen aller Art. Mit unseren integrierten Controlling-Modulen bietet das HQ bereits eine Vielzahl unterschiedlicher Berichts- und Auswertungsmöglichkeiten “Out-of-the-Box”. Die konkreten Anforderungen an das Unternehmenscontrolling unserer Kunden sind aber so vielfältig, wie es die Agenturlandschaft ist. Natürlich können wir nicht jede noch so ausgefallene Berichtsidee im HQ umsetzen – aber wir können die passenden Daten bereit stellen.

Mit Power BI bietet Microsoft seit einiger Zeit ein sehr mächtiges – und kostenfreies – Controllingwerkzeug an, das Daten aus den unterschiedlichsten Quellen aggregieren und aufbereiten kann. Aufgrund seiner Nähe zu Excel sind die zu verwendenden DAX-Formeln (DAX von Data-Analytics-Expressions, nicht von Deutscher Aktien Index) vielen Office-Nutzern außerdem schon bekannt.

Einer der wichtigsten Schritte bei der Erstellung von Berichten in Power BI ist das richtige Anbinden der Datenquelle. Klassiker wie Excel-Tabellen oder SQL-Datenbanken sind kein Problem, bei komplexen Datenstrukturen wie sie dem HQ zugrunde liegen, ist etwas mehr Denkarbeit notwendig. Hier kommt unsere offene REST-API (Daten- und Programmierschnittstelle) zur Hilfe.

Wir verwenden den verbreiteten OData Standard zur Übertragung von Daten aus und in das HQ. Dieser Standard wird auch von Power BI unterstützt und erleichtert uns das Leben. Los geht es also:

Als erstes öffnen wir Power BI und beginnen, indem wir über die Schaltfläche “Daten abrufen” eine neue Datenquelle anlegen.

ezgif-5-85a204e84d.jpg

Wir wählen “Sonstige” –> “Leere Abfrage” aus der langen Liste der Verfügbaren Datenquellen aus.

Screen2.jpg

Ein leeres Abfragefenster wird geöffnet. Um uns mit der HQ-API zu verbinden, öffnen wir den erweiterten Editor.

Screen3.jpg

Nun folgt der etwas technischere Teil unserer Übung. Unser Entwickler Mark hat Gott sei Dank etwas Vorarbeit für uns geleistet und das technische Prinzip zur Ansprache der HQ-API aus Power BI für uns geknackt. Die technischen Details hat er für Interessierte hier zusammen gefasst. Wir werden es an dieser Stelle etwas vereinfachen.

Der folgende Code ruft Daten aus dem HQ ab. Um dies in Power BI zu verwenden, kopieren wir einfach den vollständigen Code in unser leeres “erweiterter Editor” Fenster in Power BI.

let 
    BaseUrl         = "https://api.hqlabs.de/v1/<<<HIER OBJEKT EINFÜGEN>>>?",
    Token           = "<<<HIER API TOKEN EINFÜGEN>>>",
    EntitiesPerPage = 1000,
 
    GetJson = (Url) =>
        let Options = [Headers=[ #"Authorization" = "Bearer " & Token ]],
            RawData = Web.Contents(Url, Options),
            Json    = Json.Document(RawData)
        in  Json,
 
    GetEntityCount = () =>
        let Url   = BaseUrl & "$count=true&$top=0",
            Json  = GetJson(Url),
            Count = Json[#"@odata.count"]
        in  Count,
 
    GetPage = (Index) =>
        let Skip  = "$skip=" & Text.From(Index * EntitiesPerPage),
            Top   = "$top=" & Text.From(EntitiesPerPage),
            Url   = BaseUrl & Skip & "&" & Top,
            Json  = GetJson(Url),
            Value = Json[#"value"]
        in  Value,
 
    EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
    PageCount   = Number.RoundUp(EntityCount / EntitiesPerPage),
    PageIndices = { 0 .. PageCount - 1 },
    Pages       = List.Transform(PageIndices, each GetPage(_)),
    Entities    = List.Union(Pages),
    Table       = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    Table

WICHTIG: Bei gewissen Entitäten, wie zum Beispiel Rechnungen oder Eingangsrechnungen, muss für den Wert 'EntitiesPerPage' statt 1000 eine 100 eingetragen werden, damit alle Daten angezeigt werden.

Nun sind noch 2 kleine Anpassungen notwendig. Erstens, müssen wir uns authentifizieren. Dies passiert über den “API Token”, den wir uns direkt aus unserem HQ holen. Unter Admin/ API Clients ist dieser Token zu finden, den wir nun in unser Skript statt <<<HIER API TOKEN EINFÜGEN>>> einsetzen.

Screen4.jpg

Nun müssen wir der API noch sagen, welche Objekte wir eigentlich abrufen wollen (z.B. Leads, Unternehmen, Projekte usw.). Das gewünschte Objekt fügen wir dann in unseren Code statt <<<HIER OBJEKT EINFÜGEN>>> ein. Die Liste aller verfügbaren Objekte finden wir auch unter developer.helloHQ.io , hier ein Auszug:

Screen5.jpg

Für unser Beispiel rufen wir als erstes alle Leads aus unserem Testsystem ab. nachdem wir das Code-Fenster geschlossen haben, erscheint als erstes folgende Fehlermeldung:

Screen6.jpg

Nach einem Klick auf “Anmeldeinformationen bearbeiten” wählen wir im folgenden Fenster die Option “Anonym” und bestätigen mit “Verbinden”.

Screen7.jpg

Jetzt werden die ersten Daten aus dem HQ abgerufen. Das folgende Fenster sieht allerdings noch nicht besonders spektakulär aus, so in etwa wie der folgende Screenshot. Wir müssen nun noch die richtigen Spalten unserer Daten einblenden. Das funktioniert mit einem Klick auf den kleinen “Expand” Button wie auf dem Screenshot gezeigt.

Screen8.jpg

Es öffnet sich ein kleines Fenster, in dem bereits die einzelnen Informationen unseres Datensatzes angezeigt werden. Theoretisch könnten wir nun auswählen, welche Spalten wir wirklich benötigen – wir können aber auch einfach alle aktiviert lassen.

Wichtig:
Das Häkchen “Ursprüngliche Spaltennamen…” unbedingt deaktivieren.

Screen9.jpg

Jetzt kommen wir der Sache schon sehr nahe, wir sehen schon die tatsächlichen Daten aus unserem HQ. Damit später z.B. ein Datum auch als solches erkannt wird und wir hübsche Zeitfilter etc. verwenden können, müssen wir die Spalten noch entsprechend markieren. Dazu einfach auf eine betroffene Spalte mit einer Datumsangabe oder z.B. einer Zahl klicken und auf “Datentyp erkennen” klicken. Nun wird automatisch der passende Datentyp zugewiesen.

Screen10.jpg

Zu guter Letzt geben wir unserer Abfrage noch einen passenden Namen (in diesem Fall “Leads”) und schließen den Editor.

Screen11.jpg

Das war der schwierige Teil! Jetzt stehen uns alle Daten unserer Abfrage in Power BI zur Verfügung. Per einfachem Drag & Drop bewegen wir die Spalten in unseren Bericht. Fertig ist das interaktive Dashboard. Ein Klick auf “Aktualisieren” holt die neuesten Daten aus dem HQ.

Screen12.jpg

Meistens wollen wir natürlich nicht “nur” Leads auswerten, sondern diese mit vielen anderen Informationen aus dem HQ verbinden. Das ist jetzt garkein Problem mehr – wir wiederholen einfach die oben gezeigten Schritte und rufen statt “Leads” jetzt eben noch “Unternehmen” oder “Projekte” oder irgendein anderes Objekt aus der API ab. Power BI bietet jede Menge Werkzeuge, um diese zu verknüpfen, Berechnungen anzustellen und auszuwerten. Inspiration bietet Microsoft direkt auf der Power-BI-Seite.

Natürlich könnt ihr euch wie immer gerne an uns wenden, wenn es um die Erstellung schöner Berichte geht – wir helfen mit Rat und Tat.

Custom Fields auswerten per API (via Daten abrufen)

Achtung: Diese Anleitung gilt nicht für den Fall, dass das Excel Plugin verwendet wird. Hier werden die Custom Fields direkt mit den Daten abgerufen. 

Um Custom Fields (oder andere Felder, die sich laut developer.hellohq.io expanden lassen) in deiner Abfrage auszuwerten, musst du zunächst deinen Befehl erweitern. 

Am Beispiel einer Auswertung der Projekte bedeutet dies: 

Der Befehl https://api.hqlabs.de/v1/Projects? 

wird um die Custom Fields auszuwerten zu 

https://api.hqlabs.de/v1/Projects?$expand=CustomFields& 

Möchtest du nicht Custom Fields sondern andere Daten ausklappen, ersetzt du "CustomFields" einfach durch das jeweilige Objekt. Als nächstes klickst du auf 'fertig' und kannst per Klick auf den Kopf  der entstehenden Spalte auswählen, welche Elemente dir angezeigt werden sollen. Den Marker bei "Ursprünglichen Spaltennamen als Präfix verwenden" solltest du an dieser Stelle nicht setzen. 

Nun erhältst du eine Liste, in der du die Spalte CustomFields siehst. Allerdings sind deren Werte als "List" angegeben. Nach einem Klick auf den Button oben rechts im Spaltenkopf wird dies zu "Record". Dann kannst du den Button erneut anklicken und im Pop-Up Value und Name selektieren. Hier selektierst du außerdem "Ursprünglichen Spaltennamen als  Präfix verwenden".

API_1.PNG

Allerdings stehen deine Datenfelder nun in der Spalte "CustomField.Name" untereinander. Um sie sinnvoll gegliedert anzuzeigen, markiere einmal die Spalte und klicke im Reiter "Transformieren" auf den Button "Spalte pivotieren". 

API_2_marker.PNG

Im darauffolgenden Pop-Up selektierst du nun CustomFields.Value und unter Erweiterte Optionen "nicht aggregieren". 

API_3.PNG

Nun sollten dir deine Custom Fields und deren Inhalte tabellarisch angezeigt werden.