Building a Shopify store locator with Google Spreadsheets
Published on November 8th, 2023
Using your spreadsheet data directly on your Shopify store
![](http://a.storyblok.com/f/163869/1326x858/6a9dd7b0d6/screenshot-storelocator.png)
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); });
Using the Google Maps API
Now it's time to load the Google Maps API on your page.
First you need to create credentials. You can find more information about this at the following link: https://developers.google.com/maps/documentation/javascript/get-api-key
Loading the Google Maps API is fairly simple. You can copy and paste the code below in your page. Make sure to put your API key inside the URL.
<script src="https://unpkg.com/@googlemaps/markerclusterer/dist/index.min.js"></script> <script src="https://maps.googleapis.com/maps/api/js?key=<ENTER YOUR API KEY>&callback=initMap&libraries=geometry,places&v=weekly" async></script>
As you can see, in addition to the Google Maps script, we also load a markerclusterer plugin. We use this to automatically cluster markers so the map is more 'readable', especially when zoomed out.
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.