Sanctions Check with Google Sheets and Zapier
Following our run-through with Automate.io we had some people ask for a Zapier solution … so here it is.
Just to reiterate the ways can perform a check including:
- A single search via the web interface
- A bulk search of up to 100 entrants via the web interface
- 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.
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.
3c) Once you’ve authenticated your sheets account select your Spreadsheet, select the Worksheet and the Trigger Column.
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
4c) Enter the url. For this demo I’m using the staging API url https://staging.sanctionscheck.co/api/v1/searches
4d) Select the Payload Type as JSON.
4e) In the Data section enter term and insert the Full Name.
4f) In the text field next to the Headers input enter your API Token from your SanctionsCheck.io account here and enter the value with the word Bearer
Bearer [your API token here]
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
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.
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 SanctionsCheck.io
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.