Integrating KPA EHS Reports Into Google Sheets

Companies often wish to import KPA EHS data into their own dashboards or databases. This is made possible through the KPA EHS API.

In this example, we’ll share how to automatically sync KPA EHS data into a Google Sheets document.

STEP 1) GET A TOKEN

  • Login to your KPA EHS account
  • Go to Control Panel > API 
  • Generate an API token (it will look like iscout_live_2347324)
  • That token lets you download your data through the KPA EHS API (so keep it private)

STEP 2) WHICH FORM DO YOU WANT?

  • Now go to your Forms and figure out which form you want
  • Click on “Fill Out” and you’ll see the form id in the url
  • For this example, “Near Miss Report” is form 1234

STEP 3) CREATE THE URL

Now use the token and form id to create a url: (learn more)

https://api.kpaehs.com/v1/responses.flat?%7B%22token%22%3A%22YOURTOKEN%22%2C%22pretty%22%3Atrue%2C%22form_id%22%3AYOURFORMID%2C%22limit%22%3A1000%2C%22format%22%3A%22csv%22%7D

So in this example, we’ll add in our token and form id to get:

https://api.kpaehs.com/v1/responses.flat?%7B%22token%22%3A%22 iscout_live_2347324%22%2C%22pretty%22%3Atrue%2C%22form_id%22%3A1024%2C%22limit%22%3A1000%2C%22format%22%3A%22csv%22%7D

STEP 4) TRY IT

Open that URL in a browser tab. It should download a spreadsheet of your 1000 most recent responses for that form.

STEP 5) ADD IT TO GOOGLE SHEETS

Now create a new Google Sheets document and highlight the top-left cell.

Paste in this formula: 

=importData(“https://api.kpaehs.com/v1/responses.flat?%7B%22token%22%3A%22iscout_live_273642364….%22%2C%22pretty%22%3Atrue%2C%22form_id%22%3A155844%2C%22limit%22%3A1000%2C%22format%22%3A%22csv%22%7D”)

This formula imports the CSV data from your URL. The spreadsheet should now have a bunch of data.

STEP 6) KEEP IT UPDATED

Now you’ve imported data for right now, but what if new form responses come in?

In Google Sheets, go to File > Spreadsheet Settings > Calculation > Recalculation

That setting lets you choose how often the data will refresh.

KPA EHS in 60 Seconds

Watch this quick overview of the KPA EHS system.

10 Minute Walk-through

Watch this walk-through of the KPA EHS system.