Create a time tracking Zap with QR codes
Learn how to create a Zapier App with Qods and Google Sheet to track working times on the field..
Let's track arrival and departure times with QR codes
You manage a home service business and want to daily log working time of workers. You monthly compute times to invoice your customers.
What we suggest is to provide a QR code to each customer and to request workers to scan it at arrival and departure:
- Every time a worker arrives at the customer, he scans the QR code and enters his personal PIN code to identify.
- When he leaves, he scans the QR code once again to notify his departure time.
As we want to avoid cheating, we implement the geofencing feature to check if the worker is really at customer when scanning.
It may also be interesting to ask the worker how he feels, so in the tutorial we configure the Qod to enable the worker to select a smiley depending on its mood.
Before you start: requirements
Before starting, you need to:
- Create a free Ubiqod account
- Validate your email and connect to your Ubiqod account
- Ensure you have a paid Zapier account thaht allows "Multi-step Zaps" (Zapier Free plan do not)
- Ensure you have a Google account to use Google Sheets
Prepare your Google Sheet
Connect to your Google account and create a new spreadsheet.
Name it "Time tracking" and configure the following labels in the first row:
- A: Timestamp
- B: ID
- C: Date
- D: Site
- E: Employee
- F: Arrival
- G: A-mood
- H: A-geofencing
- I: Departure
- J: D-mood
- K: D-geofencing
- L: Duration
You can configure Conditional Formattings on geofencing cells to set the color depending on the worker was on site or not (geofencing value = 1 if he is on site, or else 0).
Do the same on column G & J to set colors depending on the mood ;)
Setup your Dispatch Group
Connect to the Ubiqod platform and select the "Dipatch Groups" menu.
Create a new group, name it "Time tracking with Zapier" for example. You can specify further details in the description field.
Click on "+ New", then on the "Integrations" icons:
Select the Zapier "Connect" button: your are redirected to Zapier to create your Zap.
To continue, we assume that you are familiar with Zap creation. If not, please read this tutorial before pursuing.
Make a Zap
The Zap is made of a Trigger and 4 Actions:
- Look for and select the Ubiqod integration
- Choose your account and set up the trigger by selecting the previously created Disaptch Group.
- Test the trigger & continue
Step 1 - Format the date
Select the "Formatter by Zapier" action to extract the date from the timestamp sent by Ubiqod.
Select the output format and timezone desired:
Step 2 - Lookup for a row
Select the Google Sheets action and the event "Lookup Spreadsheet Row".
Configure it as follow:
Select the option "Create Google Sheets Spreadsheet Row if it doesn't exist yet?" and configure the following fields:
Step 3 - Test arrival or departure
Select the Filter by Zapier action to check if a new raw was created or if it already existed:
Step 4 - Update the row
In case a row existed (i.e the scans corresponds to a departure), we want to update the departure time.
Select the Google Sheets action and the event "Update Spreadsheet Row".
Setup the following fields:
For the duration field, enter the formula that enables to substract column F value to column I value:
- Don't forget to start the formula with "="
- Use dynamic fields from step 2 to setup the raw number
THe Zap configuration is achieved, do not forget to turn on your Zap before continuing.
Configure a Qod
Connect to the Ubiqod plateform to configure a QR code:
- Create a Site
- Create an Action List to define moods
Create a Custom Qod with following parameters:
- Label: enter the name of the customer for example
- Site: select the Site corresponding to the customer location
- Dipatch group: select the previously created group named "Time tracking"
Setup Qod Action
Choose "Select an option in a list" and the Action List "Daily mood".
Setup Qod Conditions
First check the GPS option is checked (if you want to use geofencing). Then, check the option to enter a PIN code (no need to select a list).
Display and print the QR code
Once your Qod is created, you can display the QR code by clicking on the QR code icon in the Custom Qod list. Copy and paste the picture to print it!
Test your Zap
- Scan the QR code once, select your mood and enter a PIN code (3457 for example): a new row is added in the GSheet with current date & time
- Scan the QR code twice, select a mood and enter a different PIN code: a new raw is added
- Scan once again, select your mood and enter the PIN code first entered (3457 in our example): the first raw is updated with the departure time and the duration is computed.
If data are not dispatched in your Google Sheet, chek if your Zap is turned on in Zapier.
Date & time are not displayed in the correct timezone or format: edit your Zap and modify step 2 to select requested formats.