Uren en kilometerregistratie m.b.v. IFTTT en Google Drive

Het bijhouden en registreren van gewerkte uren en gereden (zakelijke) kilometers kan een tijdrovend en vervelende klus zijn. Ik heb geprobeerd dit te automatiseren en dit is mij gelukt door gebruik te maken van IFTTT. Met behulp van IFTTT (IF This Then That) is het mogelijk om verschillende IoT diensten aan elkaar te koppelen.

Ik heb gebruik gemaakt van de “Location” service van IFTTT in combinatie met de “Google Drive” service van IFTTT door elke keer als ik in de buurt van de klantlocatie komt een regel weg te schrijven in een Google Sheets bestand. Doordat ik de inhoud van de regel vol heb gestopt met functies is het me gelukt om hier bruikbare informatie van te maken.

In dit artikel leg ik stap voor stap uit hoe je dit ook zelf kan opzetten en gebruiken.

Opzetten

  1. Creëer een Google Account (als je die nog niet hebt).
  2. Ga naar Google Drive en maak een nieuwe folder met naam “IFTTT”.
    • Selecteer “Mijn Drive” en dan “Nieuwe map”
    • Type “IFTTT” en druk op de knop “Maken”.
    • De map “IFTTT” is gemaakt.
  3. Ga naar https://drive.google.com/open?id=1khwozeFnoPr9SKcyZtoTG3xlUlLJJB9EZH3pdxbEimY en download het reeds door mij voorbereide testbestand en upload deze in de “IFTTT” folder in je Google Drive.
  4. Creëer een IFTTT account (als je die nog niet hebt). Dit kan met je Google en/of Facebook account of een e-mailadres/wachtwoord combinatie. Ik raad aan je Google account te gebruiken (die je al had of net hebt aangemaakt).
  5. Ga naar https://ifttt.com/create om een nieuwe applet aan te maken.
  6. Druk op de witte + in het zwarte vierkant om een service te selecteren.
  7. Type “location” en de Location service blijft als enige over. Klik op het blauwe vierkant.
  8. Kies een trigger. Kies “You enter an area” zodat we een applet kunnen maken voor het arriveren bij de klant.
  9. Zoek de locatie die bij jouw klant hoort en zorg ervoor dat de radius niet te groot is. Het is een beetje passen en meten maar het is zeker goed te doen.
  10. Als je de radius om het adres correct hebt ingesteld, druk je op de knop “Create trigger”.
  11. Druk nu op de tweede witte + in het zwarte vierkant om de actie te definiëren.
  12. Kies een actie service. Type “sheet” in de zoekbalk waardoor alleen “Google sheets” over blijft. Druk op het donkerblauwe vierkant.
  13. Kies vervolgens een actie voor “Google sheets”. Selecteer “Add row to spreadsheet”.
  14. Als laatste gaan we de actie velden configureren. Het gaat hier om 3 velden, namelijk “Spreadsheet name”, “Formatted row” en “Drive folder path”.

    • Spreadsheet name: vul hier letterlijk “UrenKilometerRegistratie” in tenzij je het voorbeeld bestand een andere naam hebt gegeven.
    • Formatted row: in dit veld staat de informatie die we toevoegen aan de Google Spreadsheet. We vullen 10 velden (kolommen) in, in het bestand “UrenKilometerRegistratie”. Elk veld wordt gescheiden door “|||”.Kopieer de volledige schuingedrukte tekst hieronder en plak deze in het veld “Formatted row”.

      In ||| {{OccurredAt}}||| TEST ||| 100 ||| =WEEKNUM(indirect(“F”&ROW());1) ||| =1*(left(right(indirect(“B”&ROW());19);2)&”-“&vlookup(left(indirect(“B”&ROW());3);Maanden!A:B;2;0)&”-“&$K$1) ||| =if(indirect(“A”&ROW())=”In”;”Starttijd”;”Eindtijd”) ||| =CONCATENATE(indirect(“F”&ROW());indirect(“G”&ROW())) ||| =right(indirect(“B”&ROW());7) ||| =MONTH(indirect(“F”&ROW()))

      • “In/Out”. Gaan we het gebied in of out. In dit voorbeeld gaan we erin: “In”.
      • “Datum en tijd”. {{OccurredAt}} wordt door IFTTT omgezet naar een datum en tijd in het formaat “January 03, 2020 at 08:09AM”
      • “Locatie”. De locatie van de klant. In dit voorbeeld gebruiken we als locatie “TEST”.
      • “Aantal KM”. Het aantal kilometers van huis naar de klant. In dit voorbeeld gebruiken we “100”.
      • “Week”. De formule om het weeknummer te bepalen van de activiteit.
        • =WEEKNUM(indirect(“F”&ROW());1)
      • “Datum”. Hier zetten we een excel formule die er voor zorgt dat de datum in kolom 2 wordt omgezet naar een excel datum waarmee we kunnen rekenen.
        • =1*(left(right(indirect(“B”&ROW());19);2)&”-“&vlookup(left(indirect(“B”&ROW());3);Maanden!A:B;2;0)&”-“&$K$1)
      • “Start of eind”. Met dit veld bepalen we of de activiteit een starttijd is of een eindtijd van de dagactiviteit. Dit hebben we nodig om het totaal aantal uren van die dag te kunnen bepalen. Zie tabblad “Uren 2020”.
        • =if(indirect(“A”&ROW())=”In”;”Starttijd”;”Eindtijd”)
      • “Combi”. Met dit veld combineren we het datumveld en de start of eindveld. OOk deze wordt gebruikt bij te bepalen van het totaal gewerkte uren. Zie tabblad “Uren 2020”.
        • =CONCATENATE(indirect(“F”&ROW());indirect(“G”&ROW()))
      • “Tijd”. Met deze formule bepalen we het tijdstip waarop de activiteit heeft plaatsgevonden.
        • =right(indirect(“B”&ROW());7) 
      • “Maand”. Met deze formule bepalen we de maand waarin de activiteit heeft plaatsgevonden.
        • =MONTH(indirect(“F”&ROW()))
    • Drive folder path: de folder waarin het bestand “UrenKilometerRegistratie” staat. Wijzig dit veld naar “IFTTT”.
  15. Druk op de knop “Create action”.
  16. Als laatste kan je je applet een eigen naam geven. Maak hier bijvoorbeeld van “Starten bij Klant X”. Druk vervolgens op de zwarte knop “Finish”.
  17. De applet is nu verbonden
  18. Herhaal stap 5 tot en met 17 voor het einde van de dag waarbij je dus uit een gebied vertrekt.
    • Bij stap 8 selecteer je “You exit an area”.
    • Bij stap 9 zorg je ervoor dat je hetzelfde gebied gebruikt als bij de andere.
    • Bij stap 14 wijzig je de eerste 2 letters van “In” naar “Out”. De rest laat je hetzelfde of de terugweg moet meer of minder kilometers hebben dan de heenreis. Dan pas je dat veld ook aan.
  19. Download vervolgens de IFTTT app op je telefoon en log in met je IFTTT account. Deze heb je nodig om de “IF” te kunnen triggeren: aankomen of weggaan van de klantlocatie.

Je hebt nu twee actieve applets die je fysiek kan gaan testen door daadwerkelijk met je auto of fiets naar de ingestelde locatie te rijden en vervolgens te controleren of er een record is toegevoegd aan het Google Sheets bestand.

Gebruiken voor 1 klant

Het bestand “UrenKilometerRegistratie” heeft 5 tabbladen, namelijk:

  1. “2020”
  2. “Maanden”
  3. “Overzicht 2020”
  4. “Pivot 2020”
  5. “Uren 2020”

Tabblad 2020

Tabblad “2020” ziet er na 1 dag (arriveren bij de klant en wegrijden bij de klant) als volgt uit:

Ik heb het bestand zo gemaakt dat ik hem elk jaar opnieuw kan gebruiken door de tabbladen te kopiëren en te hernoemen naar het nieuwe jaar. In veld K1 staat ook de waarde “2020” die in de functie in kolom F (Datum) wordt gebruikt. Ik had hem natuurlijk ook uit kolom B kunnen halen d.m.v. de RIGHT en LEFT functies maar dit was makkelijker. Nu is ook zichtbaar wat er met de formules in de kolommen E tot en met J is gebeurd. Er staan nu normale leesbare waarden.

Dit tabblad wordt dus continu gevuld met regels door het gebruik van de IFTTT app. Hier kunnen natuurlijk ook handmatig regels aan worden toegevoegd of uit worden verwijderd. Ook kunnen velden worden aangepast als er bijvoorbeeld meer kilometers zijn gereden dan normaal op die dag.

Het is belangrijk dat het tabblad “2020” altijd het eerste (dus meest linker) tabblad is, want de records worden vanuit IFTTT weggeschreven in het eerste tabblad van het bestand.

Tabblad Maanden

Het tabblad “Maanden” is heel simpel in opzet. De maanden met maandnummers worden weergegeven en per maand wordt het aantal gereden kilometers opgeteld uit tabblad “2020”. In kolom D wordt dit aantal vermenigvuldigd met 19 cent per kilometer zodat dit bedrag gebruikt kan worden voor de kilometervergoeding die ik per maand aan mezelf uit keer. De maanden in kolom A moeten 3 letters lang zijn, want die wordt gebruikt om de datum op tabblad “2020” te bepalen in kolom F.

Tabblad Pivot 2020

In tabblad “Pivot 2020” wordt een draaitabel gemaakt over de tabel in tabblad “2020” waarmee een basis wordt gelegd voor de rest van de tabbladen. Er staat een filter op locatie “TEST” zodat als er meerdere klanten zijn waar je heen rijdt je meerdere tabbladen voor de uren kan maken of als er ritten tussen zitten die niet direct van toepassing zijn voor de desbetreffende klant dat die er uit worden gefilterd.

Tabblad Uren 2020

In het tabblad “Uren 2020” staat per dag een regel waarin de starttijd en de eindtijd van die dag wordt opgehaald ALS er data beschikbaar is voor die dag. In kolom E wordt dan het totaal aantal aanwezige uren bepaald door de starttijd van de eindtijd af te halen en hier dan weer een getal met decimalen van te maken. De starttijden en eindtijden worden uit tabblad “Pivot 2020” gehaald door middel van verticaal zoeken.

Tabblad Overzicht 2020

In het tabblad “Overzicht 2020” staat per week het aantal gewerkte uren en het aantal gereden kilometers die ik gebruik als basis voor mijn facturen. Op deze manier heb ik altijd snel mijn facturen klaar. Ik maak mijn facturen trouwens in het boekhoudpakket van Jortt.nl waar ik erg tevreden over ben.

Gebruiken voor meerdere klanten tegelijk

Als je meerdere klanten tegelijkertijd hebt dan heb je ook meerdere locaties waar je naartoe gaat en weg gaat. Hiervoor zal je dan ook 2 nieuwe IFTTT applets moeten definiëren (met dus een ander aantal kilometers en een andere klantnaam) en zal je de Google Sheet zo moeten aanpassen dat elke klant een eigen Pivot tabblad krijgt en een eigen Uren tabblad krijgt. Als je hier niet uit komt, mail mij dan op berry@vanderrijk.nl en ik help je graag.