Skiply Timesheet for Google Sheets: Implementation Guide

Follow this guide to set up a QR code clocking system for field workers with Ubiqod (Skiply) and Google Sheets.

If you follow all the steps, you will have, at the end of this guide, a complete software to remotely record the passage of personnel on site.

Ubiqod offers 100 free credits per month, which corresponds to 3 sites. Beyond that, the cost varies between 0.9 and 3 EUR/month/site (unlimited number of passages), depending on the quantities purchased.

The application we are going to configure also works with Smilio clock in/out devices and Skiply secure QR codes.

You can test the final result by scanning the QR codes contained in the Google Sheets Timesheet App pages.

Before you start

This guide is accessible to anyone who knows how to use a computer. However, we recommend that you set aside an hour, in a quiet place, to take the time to follow the different steps.

To be able to follow this guide, you must first meet 2 conditions:

  1. Have a Google account. If you use Gmail, you already have one. Otherwise, it’s here, and it’s free: https://support.google.com/accounts/answer/27441?hl=en
  2. Have an Ubiqod account. You can create a free account here: https://admin.ubiqod.com/#/register. Don’t forget to click on the link you will receive an email to validate your account (check your spam if you don’t find it).

Are you ready? Here we go.

Configuration of the Timesheet App

Copy the app to your Google space

Sign-in to your Google Account.

Access the App template by clicking here: https://docs.google.com/spreadsheets/d/1QRUd0VB1d17PybGwnq_DKZKM0JiPQffMDucAIXPN_WM/edit#gid=0

In the “File” menu, choose “Make a copy”, as here:

template_Google_Sheets

In the dialog box that opens, give your copy a name (remember to give it a name that is easy to find), select a location and click OK. The app is now in your home!

Activate and authorize the app

By duplicating the previous table, the system has also duplicated the App it contains. We will now allow this App to modify the data of the table.

To do this, go to the “Skiply Timesheet” menu (may appear after a few seconds when first used):

Choose “Set permissions”.

Click on “Continue” in the “Give required permissions” dialog box.

Choose the Google account to use to authorize the App:

choose_account_Gsheets

Then choose “Authorize”:

authorize-Google-Sheets

In the Skiply Timesheet menu, choose “Add automations”.

You should then see this message:

ready message

The works on the Google Sheets side is finished! Congratulations!

Now we have to configure Ubiqod to make the link between the QR codes, your sites and the application.

Ubiqod Configuration

Log in to your Ubiqod account here: https://admin.ubiqod.com/#/login

Create the link with Google Sheets App

We will create a group linked to the Google Sheets App. go to the “Dispatch Group” menu, give the group an explicit title, then click on the “New” button:

create group Ubiqod

In the next screen, click on the “Connect” button on the Google Sheet line.

Follow the instructions to authorize Ubiqod to access Google Sheets (be sure to choose the Google account on which you have copied the App).

Click on “Connect”.

In the next screen, you can give a name to this connection. You must also copy the url of the Google Sheets in the field “Google Spreadsheet URL”.

url

Once the URL is entered, click on “Retrieve spreadsheet data”, then select the Logs table (sheet):

Click on “Next”.

The mapping of the fields must be done as follows:

map fields

To understand the meaning of the different fields, you can consult this page.

Click on “Finish”.

Create a site

Ubiqod integrates a site management, which allows  you to locate customer sites and to associate the coordinates of the manager of this customer and the contact on site.

In the left menu, go to “Sites” / “New”.

Create a first site, using for example the address of the place where you are:

create a site

Enter the postal address, then click on “Geocode it” to translate it into GPS coordinates automatically. Check if the location is correct by clicking on “Check on Google Maps”.

Click on “Create” when you are done.

Create an interface/action list

The interface is an action list that allows you to configure the buttons that stakeholders will see when they scan the code. This interface can be used by several QR codes.

To create this interface, go to the menu “Settings” / “Action List” / “New”.

Give your list a name, then enter the interface buttons:

create a code

First, reserve code 1 for the “Arrive” label, then code 2 for the “Depart” label. These two actions must be created for the App to work properly. You can change the labels later, using the “Settings” tab of the Google Sheets table.

You can also create other custom actions, such as a customer callback request (code 3 / label: Call back customer).

Clock-in/out will go to the “Timesheet” tab of the Google Sheets App, requests will go to the “Request” tab.

Create a QR code

We have finished all the preparatory work. From now on, it will be very easy to add new sites and/or QR codes.

In the “Trackers” / “Custom Qods” menu, press “New”.

Give your QR code a name (for example: lobby), choose a site, and then set up the interface as follows (select the action list and group you created earlier):

create a qr code

Click on “Next”.

In the next screen, check the box “Ask user to enter a PIN code” (optional, you can also work in anonymous mode).

We are not going to activate the GPS position at this time. You can do it later.

Saving this Qod as a template will allow you to go faster when creating your future QR codes (optional).

Test with QR codes

In the list of custom qods, you have a column “QR code”. Click on the code icon to display the QR code to put on site (you can copy/paste it to print it, or use the mass printing by selecting the QR codes you want to print in the list).

Scan the code, click on “Arrival”, enter a PIN code. You should see this in the corresponding Google Sheets:

data-gsheets

Test with the connected buttons simulator

The Timesheet application is compatible with Skiply connected buttons.

Buttons 1 and 2 are reserved for arrival and departure. The other buttons can be used to dial a code.

For example, the sequence: 1333 means: Arrival, with identification code 333.

In the “IoT device” menu, edit the simulation button. Assign to it the group you created in the previous steps. Check “Force code mode”.

Start the simulator by clicking on the orange button on the bottom line (play).

smilio A device

Dial for example: 1333.

If you order Smilio connected buttons, they will behave exactly the same in the real world.

To go further

Assign an employee to a code

There are several ways to assign a PIN to an employee:

  1. Use the “Employees” tab in the Google Sheets table
  2. Use a “PIN code list” in the Ubiqod interface

The first option applies to all QR codes, while the second allows you to assign different lists of codes depending on the site/QR code.

Validation of the GPS position (geofencing)

In order to use the user’s position verification function, the “GPS position” option must be activated in the QR code configuration. The user will then have to authorize access to his GPS when using the App.

In the Logs tab, you will see the user’s position at the time of the scan. The “Validation” column will be set to 1 if the user’s GPS position is within the perimeter of the site where the QR code is located.

If these columns are empty despite the activation of the GPS function for this QR code, it means that the user has refused to share his location.

Using Ubiqod Key

If you need to prove the passage of staff, but you don’t want or can’t use geolocation, you can use Ubiqod Key. This is a rotating QR code, so it cannot be copied for reuse.

Mass printing of QR codes

The Ubiqod platform includes a mass printing function. To print one or more QR codes, select the Qods to print, then click on “Export to print”.

Configuration options in Google Sheets

The “Setting” tab of Google Sheets contains elements to modify the behavior of the App. It is reserved for advanced users.

Share data with a client

The Google Sheets QR code check-in app benefits from all the features offered by Google. In particular, it allows you to give a client read access with the “Share” button, located at the top right of the interface.

Respect for individual liberties

Even with geofencing enabled, Ubiqod remains one of the most privacy-friendly systems available:

  1. It does not track users. It geolocates them, when the geofencing option is activated, and with their consent, only when they press
  2. It does not use cookies
  3. It does not require the installation of an App

Precautions for use

Do not change the name of the labs or the order of colums, otherwise the application will not function properly.

Guarantees and updates

The Google Sheets QR code and IoT check-in application works as described in this guide at the time of writing. Its operation depends on the Google Platform, with which we have no connection. This application is provided without any guarantee of operation. Support services are offered by Skiply, which can also implement specific functionalities for use cases.