API - Google Sheets – KPA Flex

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

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

STEP 1) GET A TOKEN

Login to your KPA Flex account
Go to Control Panel > API
Generate an API token (it will look like kpaehs_live_2347324)
That token lets you download your data through the KPA Flex 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.