Sanctions Check with Google Sheets and Automate.io

We’re working hard to make performing SanctionsChecks as frictionless as possible. There are a number of ways you 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 Automate.io. The first time I set this up it took me about 15mins 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 Automate.io

We use Automate.io for a number of services and it’s a robust and useful automation tool.

Step 3: create a bot

3a) In the top right-hand corner of your account click on the create a bot and then select Google Sheets.

3b) Once you’ve authenticated your sheets account select New Row (Recommended).

3c) Select your file and the active sheet where new rows will be populated.

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 API url. For this demo I’m using the staging API url https://staging.sanctionscheck.co/api/v1/searches
4d) Format the data with the following parameters

{
"term": "[insert full name using the + option]",
"strategy": "partial"
}

4e) In the Headers section enter

authorization

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: add our first formatter

Now we’re going to format the response from the API so we can update the Google Sheet with the response for audit purposes.

5a) Choose to add a Formatter
5b) Select Parse Data
5c) Select JSON from the Input Data Format
5d) In the Data field we’re going to be check the Body response.
5e) In the JSON/XML Path to be retrieved field add search.resultsCount this will format the number of results from the API response.

Step 6: add our second formatter

Now we’re going to add another formatter to grab the URL of the SanctionsCheck.co so we can write it to the Google Sheet.

6a) Choose to add a Formatter
6b) Select Parse Data
6c) Select JSON from the Input Data Format
6d) In the Data field we’re going to be check the Body response.
6e) In the JSON/XML Path to be retrieved field add search.url

Step 7: update our sheet with the API response

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

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

7d) In the Results Count section add the Retrieved Value 2 from the first formatter we created.
7e) In the Sanctions Check URL section add the Retrieved Value 3 from the first formatter we created.
7f) In the Full API response section add the Body from the API response.

Step 8: save your bot and turn it on

Now we’re ready to save your bot and turn it on. From there you can choose I’m done and check for sample content and the bot will run for the first time.

Sometimes you may need to add a new row of data for the bot to detect a new row.

If everything runs smoothly you should be able to check the Google Sheet.

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.