Cube + NodeRed + Google Sheets = Zeiterfassung Pro
Im PanOffice (Pandemie Office trifft’s eher als Home-Office) möchte man seine Arbeitszeit im Auge behalten und muss auf verschiedene Situationen “schnell” reagieren. So war es nun der Wunsch von mir die Arbeitszeit zu erfassen – ohne ständig auf die Uhr zu schauen und Zeiten zu schreiben. Vorhandene Lösungen waren mir dann aber zu teuer 😉
Ich will also die aktuelle Aufgabe auf dem Würfel oben sehen und wenn sich was ändert den Würfel einfach auf die Aufgabe drehen. Ich habe da jetzt z. B. Freizeit, Arbeit, Meeting und Pause aktuell.
Was der Würfel leider nicht erkennt ist es, wenn man ihn aufnimmt und auf eine andere Fläche stellt. Der Lagesensor reagiert dann nicht – man muss ihn richtig von A nach B kippen. Man kann den Würfel aber auf den Tisch stellen und schieben oder 2x klopfen. 🙂
Den Cube von Xiaomi habe ich bei EBay für 12,08€ gekauft – dauert nur rund 4 Wochen.
Den Cube habe ich über DeConz (ZigBee USB Stik) als Schalter eingebunden:
Dazu muss man nur den Deckel mit dem mitgelieferten Metallteil öffnen – das geht etwas schwer aber durchhalten – es klappt 🙂 Dann einfach den Link Button drücken und etwas Geduld haben.
Ganz wichtig zu wissen ist jetzt, dass DeConz das Gerät 2x publiziert. Im Node Red kann man die leider nicht unterscheiden:
Hier kann man nun leider nur probieren was man für Daten bekommt. Es gibt immer das “buttonevent” und die gesture. Ein Gerät liefert Daten zur Lage und was mit dem Würfel gemacht wird und das zweite Gerät liefert Daten wenn das Gerät gedreht wird.
Würfelfelder
Darstellung im Web Phoscon
zur Lage
Es gibt folgende Gesten:
- 1 wedeln
- 2 in die Luft geworfen ==> button event 7008
- 3 kippen
- 4 Update: Kippen um 180°
- 5 status stehen/ schieben
- 6 klopfen 2x
Das Buttonevent ist immer eine 4-stellige Zahl – interessant ist aber nur die 1. und 4. Stelle. so kommt z.B. die Zahl 1002 mit geste 3 => der Würfel zeigt nun die Fläche 1 und vorher war die Fläche 2 aktiv.
Ein Sonderfall ist die Fläche 7. Schüttelt man den Würfel kommt 7007. Wirft man den Würfel in die Luft kommt 7008. Klopft man den Würfel 2x auf den Tisch (nicht in der Hand halten!!) kommt die aktuelle Fläche 2x also z.b. 3003 oder 4004.
Nach dem Anschalten kommt 7000 (Ruhemodus).
Schiebt man den Würfel horizontal kommt zur Geste 5 das buttonevent 5000 oder z.B. 1000. Hier wird also die aktuelle Fläche (1. Stelle) geliefert und die vorherige Fläche ist 0.
Zur Drehung
wenn der Würfel auf dem Tisch liegt kann man ihn links und rechts drehen (z.b. für Helligkeit oder Lautstärke). Als Buttonevent erhält man die Angabe wie stark der Würfel gedreht wurde in Grad aber mit Faktor 100. Wenn der Wert 4324 kommt, bedeutet dies der Würfel wurde um 43,24° nach rechts gedreht. Ein negativer Wert bedeutet nach links.
Zusätzlich zeigt die Geste 7 (rechts) und 8(links) an in welche Richtung gedreht wurde.
Node Red
Im Node Red habe ich nun also 4 DeConz Adapter die mir von jedem Zauberwürfel den buttonevent und gesture liefert. Die Drehung nutze ich aktuell noch nicht.
Aus der Lage extrahiere ich durch simple Mathematik die aktuelle Fläche und die vorherige Fläche (letztes nutze ich auch noch nicht). Der Fläche habe ich für mich Aufgaben zugeordnet und bis auf die 7 nutze ich alle.
Node Red selbst schreibt über den node-red-contrib-google-sheets Adapter die Daten zu Google Tabellen. Das Einrichten ist etwas “tricky” aber in der README erklärt. Was nicht drinnen steht, ist dass man in dem Projekt noch die API für Sheets (Tabellen) aktivieren muss. Das kann man aber nachträglich noch machen und die Debug Console zeigt hilfreiche Fehlermeldungen.
Die Payload Merge Funktion hier sammelt die Payload Objekte und gibt sie als vereintes Objekt nach einer Zeit X weiter. Also 1. Objekt lautet { aktuell: 1, vorheriges: 2} und das 2. Objekt {geste: 3} dann kommt am raus: { aktuell: 1, vorheriges: 2, geste: 3}. Node Red Code hier: payload merge.
Ich verwende in dem Adapter dann die “Append Row” Funktion um eine neue Zeile anzufügen. Für mich reicht hier die aktuelle Fläche und die Uhrzeit mit sekunden. Folgt ein weiteres Event, weiß ich ja wie lange die andere Fläche aktiv war.
Da beim einfügen der Zeile wird nicht einfach die letzte Zeile beschrieben, sondern wirklich eine Zeile eingefügt. Ich kann somit in der Tabelle keine Formeln in die z.B. 3. oder 4. Spalte setzen. Node Red muss die mit einfügen. Einerseits möchte ich aber das Ende in einer Zeile stehen haben und 2. die Dauer daraus berechnen. Für weitere Berechnungen benötige ich noch das Datum ohne Uhrzeit:
Dafür benötigt man noch 3 Formeln. Da wir nicht wissen in welcher Zeile wir sind benötigt man die Funktion INDIREKT mit RC-Notation.
Bevor man die Daten nun also an Excel senden kann muss man ein 2-Dimensionales Array erschaffen. Eine Liste von Zeilen also. Jedes Element ist eine Spalte. Formeln können direkt übergeben werden.
Hier wird nun jeweils das Start-Datum des Nächsten Events als Ende genommen. Wenn das da ist wird die Differenz berechnet und das Datum gebildet.
In meinem Beispiel lass ich das Tablett noch die aktuelle Tätigkeit sprechen mittels Text-To-Speech was der Fully Kiosk Browser super per API kann.
Noch als Tipp: Nach dem Append to Row liefert der GSheet Adapter noch Informationen wo er die Zeile eingefügt hat. Damit kann man dann noch mehr machen 🙂
Den Node Red Code gibt es hier Zeiterfassung Node Red
Google Skills
Jetzt muss man das ganze ja noch in Tabelle / Excel auswerten was man da an Daten hat.
Zuerst erstellen wir eine Liste von den Tagen die erfasst wurden. Mit der Funktion UNIQUE bekomme ich alle Elemente und SORT zeigt mir diese in der Spalte noch an
=SORT(UNIQUE(Rohdaten!E2:E40035);1;TRUE)
Zu dem Datum kann ich nun die Arbeitszeiten raussuchen mit einer SUMIFS Funktion. Da die nur UND Verknüpfungen kennen muss man jedes Event einzeln zählen. Daher hier mehrere SUMIFS addiert.
=IF(B2<>""; SUMIFS(Rohdaten!$D$2:$D$10033;Rohdaten!$E$2:$E$10033;B2;Rohdaten!$B$2:$B$10033;5)+SUMIFS(Rohdaten!$D$2:$D$10033;Rohdaten!$E$2:$E$10033;B2;Rohdaten!$B$2:$B$10033;6);"")
Alternativ kann man hier auch die Pausen berechnen – also das jeweilige Event.
Ich berechne aus dem Datum dann noch die Kalenderwoche und beginne das Spiel dann noch einmal und zähle zu der Woche (vorher ja zum Datum) die Stunden und kann so die Wochenarbeitszeit berechnen.
Die Funktion INDIREKT kennt Excel nun nicht. Ich stelle aber trotzdem mal hier den Export der Sheets Datei bereit. Da kann man die Formeln mal sehen und vielleicht auch wieder reimportieren nach Excel.
