Building a Shopify store locator with Google Spreadsheets

For customers it is not always easy to work with content management systems to administrate their webshops. And in many cases, they do not use it as their basic way of creating and saving their content. More than once, we have had customers who had difficulties to enter their data in their CMS. Customers are mostly feeling more comfortable with working with systems that they are already used to.

It is not possible to save store locator data directly into Shopify. For that you will need an external database or app. So in this case, we're using Google Spreadsheets to save this data.

Google Sheets API

Google has an API available for almost everything, and spreadsheets are no exception. Their API allows the creation of spreadsheets, reading and writing cell values, updating formatting, and managing connected sheets. This can be done by generating an API key in the API console. Find more information on this here: https://support.google.com/googleapi/answer/6158862?hl=en.

Once you've created your API key with the correct permissions, you are ready to start building the code to retrieve data from a Google Spreadsheet. Down below I will explain in different steps how this can be done.

Setting up a document for use with the API

For this example I have made a test spreadsheet with a few columns and rows. First, you need to know the URL of your spreadsheet to obtain the ID. In my case, the URL to the document is https://docs.google.com/spreadsheets/d/14n417NUoor4rm5B6GNDXOeAgceM4r1ITPCGS-A6WxrE. The last part of the URL is your sheet ID. Copy this to your clipboard.

To be able to access the document through the API, make sure that the visibility for the document has been set to 'public'. If this is not the case this may result in an access denied error when using the API.

Calling the API and retrieving data

The next step is to construct an API URL to retrieve data from your spreadsheet. The URL format is

https://sheets.googleapis.com/v4/spreadsheets/<spreadsheetID>/values/<sheetName>?key=<apiKey>.

Replace <spreadsheetID> with the one on your clipboard, <sheetName> with the actual sheet name, and the <apiKey> with the one you generated.

Upon entering the URL in your browser, you should see something like this:

{
  "range": "Blad1!A1:Z1000",
  "majorDimension": "ROWS",
  "values": [
    [
      "col A row 1",
      "col B row 1",
      "col C row 1"
    ],
    [
      "col A row 2",
      "col B row 2",
      "col C row 2"
    ],
    [
      "col A row 3",
      "col B row 3",
      "col C row 3"
    ]
  ]
}

Writing code to retrieve data

Retrieving the data shouldn't be very difficult anymore from this point. With javascript for example, you can simply use the 'fetch' function:

fetch('https://sheets.googleapis.com/v4/spreadsheets/<sheetID>/values/<sheetName>?key=<apiKey>', {
    method: 'get'
}).then(res => res.json())
  .then((response) => {
    console.log(response.values);
  });

The final result

This is the full example of a functioning store locator script that loads the locations from a Google Spreadsheet document:

https://github.com/madebymouses/shopify-storelocator/blob/main/page.storelocator.liquid

A successful implementation

We recently applied our expertise for one of our clients, seamlessly integrating a practical solution. Our client maintains a spreadsheet containing their available stores, their addresses and their coordinates. We are using this sheet to develop a functional store locator with the Google Maps API. You can see the result here: https://kooduu.com/pages/store-locator.