Select Section...

Overview

This article shows how to install and use the iAuditor Google Sheets add-on. It will also outline the format used by the add-on.

Definitions

Spreadsheet: refers to a Google Spreadsheet. To create a new Google Spreadsheet, sign into your Google Account, and click here
Sheet: refers to a Google Sheet which is a single tab within a Google Spreadsheet. See here

Installation

  1. Open this link
  2. Click “+free” to install the add-on
  3. Accept the terms of service by clicking “Allow”

Once the add-on is installed it will be available from any Google Spreadsheet that you own or have permission to edit.

If you don’t have the link provided above, you can search for the add-on instead:
1. Open any Google Spreadsheet
2. Click “Add-ons” in the menu
3. Click “Get Add-ons….”
4. Search for “iAuditor”
5. Select the add-on named “iAuditor SafetyCulture Integrations” and then follow steps 1-3 above

How to Use the add-on

  1. From your spreadsheet click “Add-ons” –> then click “SafetyCulture” from the drop-down menu –> then click “Sync” from the submenu
  2. If this is the first sync, you will be prompted to provide an API token. Here is a guide on how to generate an API token. Once a valid API token is provided audits will start syncing to the spreadsheet.

The initial sync will export your most recent four audits completed. After that, the spreadsheet will automatic sync audits each hour. You do not need to have the spreadsheet open for this to occur.

Settings

You can update or revoke access to your API token from settings. To access settings, click “Add-ons” –> then click “SafetyCulture” from the drop-down menu –> then click “Settings” from the submenu.
From settings you can:
1. Update API Token: This will prompt you to provide a new API token. See how to generate an API token
2. Delete API Token (Stop sync): This will delete your API token, and stop the hourly sync.

Format

Audits are grouped by template. This means that all audits built off a particular template are exported to the same sheet. A new sheet is created for each new template that is discovered.

Within a given sheet, each audit item is exported to a single row. Each row represents a single item. The number of rows a single audit takes up depends on how many items the audit contains. As audits are exported, the new rows are appended to the bottom of the appropriate sheet. As more audits are exported for a particular template, the sheet will grow vertically.

Each column holds a specific piece of information. The column headers are as follows:

Column Header

Item Type

Description

Item Type String Item Type as selected in the template editor. This can be a question, signature, location, etc.
Label String The prompt written in the template editor. For a question item, this column contains the question itself.
Response String / Number / Boolean The item response selected in the audit.
Comment String The comment associated with this item, if a comment was added in the audit.
Media Hypertext Reference String If this item contains an image, this column holds the HREF to access the media file. For more information, see https://developer.safetyculture.io/#get-media
Latitude Number If this item contains location data, this column holds the latitude of the location
Longitude Number If this item contains location data, this column holds the longitude of the location
Item Score Number The score of this item
Item Max Score Number The maximum possible score associated with this item
Item Score Percentage Number The percentage score of this item. Calculated as Item Score / Item Max Score
Mandatory Boolean True if this item is set to mandatory in the template. If not, this column is False. For more information about mandatory fields, see https://support.safetyculture.com/templates/mandatory-items/
Failed Response Boolean Treu if the response selected for this item is selected as a failed response in the template. For more information, see https://support.safetyculture.com/analytics/failed-responses/
Inactive Boolean True if the item is made inactive by a smart field. For more information about smart fields, see https://support.safetyculture.com/templates/smart-fields/
Item ID String Unique UUID of this item. This can be used to identify like items over different audits, even if the item label changes over time.
Response ID String Unique UUID of the response selected. This can be used to identify like responses over different audits, even if the response text changes over time.
Parent ID String Unique item ID of the item this item is nested under in the template editor.
Audit Owner String The full name of the user that created this audit
Audit Author String The full name of the user that last edited this audit
Audit Name String The name of the Audit. This is the response to the Audit Title field
Audit Score Number The score of this audit. This is the sum of the audits item scores
Audit Max Score Number The maximum possible score of this audit. This is the sum of the audits Item Max Score values
Audit Score Percentage Number The percentage score of this audit. Calculated as Audit Score / Audit Max Score
Audit Duration Number (seconds) The number of seconds this audit took to conduct. This is measured when the Audit is conducted on a device using the iAuditor application
Date Started String The Coordinated Universal Time (UTC) this audit was started. Format is YYYY/MM/DD HH:MM:SS
Date Completed String The Coordinated Universal Time (UTC) this audit was completed. Format is YYYY/MM/DD HH:MM:SS
Date Modified String The Coordinated Universal Time (UTC) this audit was modified. Format is YYYY/MM/DD HH:MM:SS
Audit ID String The unique UUID of this audit.
Template ID String The unique UUID of the template this audit is built on
Template Name String The name of the template this audit is built on
Template Author String The full name of the author of the template this audit was built on
Item Category String The label of the Section or Category this item is nested under in the template.
Document Number Number The Document number of this audit. The document number is a header field. For more information about header fields, see https://support.safetyculture.com/templates/header-fields/
Conducted On String The date and time this audit was conducted. Conducted On in Coordinated Universal Time (UTC). This is a header field. For more information about header fields, see https://support.safetyculture.com/templates/header-fields/
Prepared By String Response to the “Prepared By” header field. For more information about header fields, see https://support.safetyculture.com/templates/header-fields/
Location String Respnse to the “Location” header field. For more information about header fields, see https://support.safetyculture.com/templates/header-fields/
Personnel String Response to the “Personnel” header field. For more information about header fields, see https://support.safetyculture.com/templates/header-fields/
Client Site String Response to the “Client/Site” header field. For more information about header fields, see https://support.safetyculture.com/templates/header-fields/

Troubleshooting

  • My API token is being rejected

Please refer to this guide for creating a valid API token: https://support.safetyculture.com/integrations/how-to-get-an-api-token/. The token should be 64 characters and can include ‘a’ to ‘z’ and 1 to 9.

  • Some audits are not being exported

Only audits that have been shared to the account whose API token is being used will be exported. If you cannot see the audit from that account within the website then it will not export. Also, only completed audits will be exported. In order to complete an audit, the audit conductor must click ‘complete audit’ at the end of the audit process.

  • The hourly sync stopped

If an error occurs when accessing the API the hourly sync will be stopped. You can see when this occurred within the sheet named “diagnostics”. You can start the hourly sync again by clicking sync and providing your API token.

Limitations

The iAuditor add-on is limited in scope.

  • Users cannot restrict the CSV export to specific item types. All items are exported for each audit.
  • Users cannot restrict which columns are used.
  • Users cannot restrict which audits export using a template filter. In bulk export mode, all audits are exported.
  • Cannot limit the export to selected templates
  • If you update an Audit that has already been exported, it will be appended to the spreadsheet a second time

Advanced Use

Explore the Data

Once you have some data exported, try clicking “Explore” in the bottom right corner of the spreadsheet. This will auto-create graphs of your data, and allow you to ask questions about your data.
Try asking for …

  • What is the average Audit Score by Audit ID?
  • How many unique Template Author’s are there?
  • Chart of Response
  • Chart of average audit score by date completed

Map the Data

Get the add-on ‘Mapping Sheets to create a visual map of your audit locations.

Do more with the data

Connect your Google Sheets to other applications using Zapier: https://zapier.com/zapbook/google-sheets/

Create Custom Charts:

Histogram breakdown of audit scores:

Average audit score by team manager:

Resources