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 ;)

Timesheet example: Example of timesheet

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: Create a Dispatch group

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:

Trigger

  • 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 Select Ubiqod trigger

Step 1 - Format the date

Select the "Formatter by Zapier" action to extract the date from the timestamp sent by Ubiqod. Select date formatter by zapier

Select the output format and timezone desired: Set up date formatter by zapier

Step 2 - Lookup for a row

Select the Google Sheets action and the event "Lookup Spreadsheet Row". Select GSheets action

Configure it as follow: Set up GSheets lookup action

Select the option "Create Google Sheets Spreadsheet Row if it doesn't exist yet?" and configure the following fields: Set up GSheets raw creation

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: Select Only If continue action

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". Select GSheets update action

Setup the following fields: Set up GSheets udpate action

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 Set up GSheets duration formula

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 an Action List
  • 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.

Troubleshooting

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.