JSON is a standard protocol for sending data between applications and it can be used to access your store locator's Search and Click data inside a Google Sheets automatically. This allows you to automate the process of analysing your store locator performance and generate your own custom business analytics.
Here's how to import JSON data into a Google Sheet using a free, open source script available on Github.
Adding the free ImportJSON Script to your Google Sheet
1. Open a Google Sheet
Open a new or existing Google Sheet.
- Tip: You can go to sheets.new in your browser if you want to create a new Google Sheet
2. Open Script Editor
In your Google sheet, in the menu at the top, click Extensions > Apps Script
3. In the script editor, remove the placeholder content
If there is any placeholder code typed into the script editor, you can remove it.
4. Copy and paste an open-source script
Go to: https://gist.github.com/paulgambill/cacd19da95a1421d3164
Copy all of the code for “import_json_appsscript.js” and paste the code into the script editor of your Google Sheet.
This is a free, open-source script someone created to import JSON into a Google Sheet.
Copy the import json script
Paste the import json script
There’s also other free importjson scripts available online if you do a search for them.
5. Save and name the script
After you copy and paste all of the script into the script editor, click the save button and name your project.
Click the save button
Name your project
You can name the project anything you want, but naming it something like “ImportJSON” will make it easy to remember.
6. Go back to your Google Sheet
After saving your project in the script editor, you can now close the script editor tab in your browser and go back to your Google Sheet.
Now, in any cell of your Google Sheet, start typing “=import” (without the quotation marks) and you will see “ImportJSON” pop-up as an option. This is the script you saved in the script editor.
Click on the “ImportJSON” option.
Import JSON formula
7. Add the JSON API URL for your Search data
After opening your Import JSON formula, you’ll need to add your JSON API URL. Here is an example of the URL:
You will need to enter the following values:
type
Can be either searches for search records of clicks for click records.
uid
This is your unique store locator account uid. You can find this here: https://www.storelocatorwidgets.com/admin/Details under 'Store Locator API key'.
startstamp & endstamp
This is the UNIX timestamp of the start time and end time of the data that you want to retrieve. You can convert Google Sheet dates to UNIX timestamps using this function:
=INT((A1-("1/1/1970"-"1/1/1900"+2))*86400).
format
Set this to JSON to retrieve data in the JSON format (required for this tutorial).
Here's an example of the overall function call:
=ImportJSON("https://www.storelocatorwidgets.com/admin/reports?type=searches&uid=YOU_UID_HERE&startstamp=1648785600&endstamp=1651031999&format=json")
Once this is done, you should see JSON data being pulled automatically into your Google Sheet as shown below:
