Skiply Timesheet add-on for Google Sheets: Setup 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. Don’t forget to look at the Known Limitations before proceeding.

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.

Configure the Skiply Timesheet Add-on for Google Sheets

Install the App

  • Create a blank Google spreadsheet
  • In the Extensions menu, select Add-ons, then Get add-ons
  • Search for “Skiply Timesheet”
  • Click on the App
  • If your company is using Google Workspace, select Admin install (only available if you are admin) or Individual Install. Contact your Google Workspace administrator if you can not install the App
  • If your are an individual user, select Install

After that, you should see a new item in the Extensions menu, called Skiply Timesheet:

Skiply timesheet menu

If you don’t see it, relaod the page and wait for a couple of seconds.

Check and setup your timezone and langage

Before you launch the initial setup, set your timezone (File / Settings menu of Google Sheets) :

Setup time zone in Google Sheets

Tips: if you change the timezone afterwards, you will have to build a new integration in Ubiqod.

Authorize the app

  • In the Extensions menu, go to Skiply Timesheet, and select Create Sheets (initial Setup).
  • The first time you launch the App, a consent screen will ask you to authorize the App. You need to authorize the App in order to use it.

Create the timesheet

  • If you were asked to authorize the App, you have to go again in the Extensions menu, select Skiply Timesheet, then Create Sheets (initial Setup)
  • A dialog box will ask you to enter your date format preference:

timesheet start menu

  • The system will create your sheets and setup the triggers. Wait until the success message before taking new actions.

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 read 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, setup code 1 for the “Arrive” label (used to clock in), then code 2 for the “Depart” label (used to clock out). These two actions must be created for the App to work properly.

If you want to change the labels or code number, update the “Settings” tab of the Google Sheets accordingly (B4 and B5). Otherwise, it will be considered as a service request.

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. Geolocation, when activated, is made with user consent, and only at the time of form submission
  2. It does not use cookies on the user’s device
  3. It does not require the installation of an App on user’s device

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 Skiply Timesheet Add-on 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.

Known limitations

When new data is added, Google Sheets performs a calculation to update the timesheet. This calculation may take several seconds to complete. Therefore, if several clockings arrive at the same time within 3 seconds of each other, some may not be processed. However, they will still be visible in the “Logs” tab.

If you use the system for many sites, we recommend that you use several tables (for example one per customer).