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.
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
Google Spreadsheets has a limit of 400,000 cells per sheet which allows 10,810 rows for this add-on. Reaching this limitation will cause the add-on to stop syncing audit data.
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
- Open this link
- Click “+free” to install the add-on
- 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
- From your spreadsheet click “Add-ons” –> then click “SafetyCulture” from the drop-down menu –> then click “Sync” from the submenu
- 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.
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.
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:
|Item Type||String||Item Type as selected in the template editor. This can be a
|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
|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 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
|Audit Score Percentage||Number||The percentage score of this audit. Calculated as
|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.
|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/|
- 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.
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:
- If you are new to Google Sheets, this guide may be helpful: https://gsuite.google.com/learning-center/products/sheets/
- Supported browsers: https://support.google.com/docs/answer/2375082?co=GENIE.Platform%3DDesktop&hl=en