Sanctions Check with Google Sheets and Zapier

Following our run-through with we had some people ask for a Zapier solution … so here it is.

Just to reiterate the ways can perform a check including:

  1. A single search via the web interface
  2. A bulk search of up to 100 entrants via the web interface
  3. A direct API call using through our documented API

And now we’ve developed a new way for you to perform a sanctions check through Google Sheets and Zapier. The first time I set this up it took me about 45mins to get rolling end-to-end.

How to get started

Step 1: set up your Google Sheet

Whether you’re writing from a form or using Google Sheets as a backup data source you need a sheet you can use with user access.

To get your test rolling you can use this sample sheet I created. Just copy it to your own account.

Here’s what my sample sheet looks like with a sample row

Step 2: create an account at Zapier

We’ve been early adopters of Zapier at Insured by Us and Agile Underwriting.

Step 3: create a zap

3a) In the top right-hand corner of your account click on the Create Zap and then select Google Sheets.
3b) Connect to your Google Sheets Account.

Select Google Sheets and then New or Updated Spreadsheet Row

3c) Once you’ve authenticated your sheets account select your Spreadsheet, select the Worksheet and the Trigger Column.

Selecting the Google Sheet you want to work with

Step 4: connect to the API via the webhook

We’re going to connect to the API using the following options:

4a) Add a Webook
4b) Select POST data

Selecting the Webhook and Post data

4c) Enter the url. For this demo I’m using the staging API url
4d) Select the Payload Type as JSON.
4e) In the Data section enter term and insert the Full Name.

Customising the request

4f) In the text field next to the Headers input enter your API Token from your account here and enter the value with the word Bearer

Bearer [your API token here]

Adding your API Token

Step 5: update the Google Sheet with the API response

Now we’re going to update the Google Sheet with the API response. We do this by:

5a) Adding another step to the Zap and selecting Google Sheets.
5b) Select the Google Sheet you’ve been using
5c) The select the Full Name row to select the row you want to update

Selecting the sheet

5d) In the Results Count add the API response for the Result Count
5e) In the Sanctions Check URL add the API response for the URL
5f) In the Results add the API response for the full Results

Step 7: update our sheet with the API response

Now we’re going to update our Google Sheet with the results from the API.

a) Click add and select Google Sheets
b) Select Update Row (Recommended)
c) Select Row Number from the Row Number text field.

Sending API data back to the Google Sheet

Step 8: save your Zap and turn it on

You can view my Zap here ›

Step 9: check the sheet

The sheet should have updated column values like the image below.

Step 10: check interface on

The web interface should also show the API results.

Now you’re ready to run as many sanctions checks as you like via Google Sheets.