Getting Google Maps Business Listing with SerpApi Google Sheets extension

Introduction

This tutorial will teach you how to use SerpApi's Google Maps API for Business Listing using the Google Sheets extension SerpApi - Search Engine Results and Ranks.

Getting started

Let's start with the basics. To start your Business extraction, you first need to register a free account on SerpApi.com. You will get one hundred free monthly search credits to explore the API.

The second thing you need is to get your extension installed in Google Sheets. The process is straightforward. Head to the Google Sheets Extensions -> Add-ons -> Get add-ons and search for SerpApi - Search Engine Results and Ranks.

After you install the extension, you will be asked to connect your SerpApi account with it. After that, you can start exploring SerpApi Google Sheets extension capabilities.

Google Maps API

To get the Business Listing, we will be using Google Maps API. I recommend heading to our Playground, where you can test all the parameters and see what works best for you.

There are a couple of parameters that will be required for our searches. Those are q, which defines the query of your search, ll - your GPS coordinates, and start for pagination. There are a couple more that can be used. You can check them all in our documentation, but we will focus on the essential ones.

Getting your first Google Sheets query

The easiest way to get your first Google Sheets query is to head over to our Playground, fill out your parameters, and use the Export To Code option from the top right corner.

After you copy the formula to your Google Sheets and analyze it, you will notice that it's composed of two separate elements - query and selector. You can refer to the below screenshot for advice:

While your query is defined by you in the Playground, the selector needs to be adjusted according to your requirements. Otherwise, you might encounter the error below:

Oops field is not found: suggestions

To avoid this, head back to the Playground and check your JSON response. You will better understand what selectors can be used for your query. For example, if you want to get the title of your first result, the selector needs to be rewritten as below:

local_results.0.title

Similarly, for the rest of the result's titles, you will change 0 to 1, 2, and so on. There are some nested responses like the gps_coordinates:

Simply add another key to your selector like the ones below:

local_results.0.gps_coordinates.latitude
local_results.0.gps_coordinates.longitude

You should now know how to operate with Google Sheets formulas using the SerpApi extension. Let's try to automate the process to extract more results from your query.

Extracting Business Listing in Google Sheets

We will focus on extracting restaurant information in New York. You can find the complete Google Sheets example here for future reference.

First, you must decide what parts of the JSON response you want, and create a table with a header for each element. I decided to go with the title, GPS coordinates, rating, number of reviews, restaurant type, and address.

The second thing you want to do is to set up your variables. In my case, it's Query, Start, which defines the intervals for your pagination, Latitude, Longitude, and Zoom.

Lastly, we need to focus on getting the proper indexes for the query and page numbers for each row. Since we will not do it manually, we can define page numbers using the built-in Google Sheets CEILING and ROW functions. You can use formulas similar to the ones below and save them as additional columns:

=CEILING((ROW() - ROW($H$3)) / ($C$2))

=ROW() - (H4 - 1) * ($C$2) - ROW($G$3) - 1

You now have the base for your Google Maps Business Listing. We can focus on changing your query to make it more automated. Let's see once again the formula we started with.

=SERPAPI_RESULT("q=Coffee&engine=google_maps&start=0&ll=@40.7455096,-74.0083012,14z", "suggestions.0.value")

You can see that some variables can be replaced with the Data from the Google Sheet cells we prepared beforehand. Below you can see the updated formula for the title column. You can adjust all the other columns of your listing accordingly.

=SERPAPI_RESULT("q=" & $A$2 & "&engine=google_maps" & "&start=" & $C$2 * ($H4 - 1) & "&ll=" & $E$2 & "," & $F$2 & "," & $G$2, "local_results." & $I4 & "." & LOWER(A$3))

Following the example above for other columns, you now have a full list of extracted parameters for the first Business from your listing. You must copy the formula and paste it into the rows below the first to get all the results for multiple pages.

Conclusion

The above example shows you an easy way to integrate your SerpApi Google Maps API for Business Listing in Google Sheets. You can now adjust your parameters according to your needs without changing your formulas. They will automatically adjust to the information you provide.

Below you can find the full Google Sheets example I used for this blog post, as well as other useful Google Sheets-related materials:

Google Maps API Business Listing - Google Sheets
Scrape Google search results to Google Sheets - SerpApi
How to scrape Google results into a spreadsheet: SerpApi Google Sheets plugin

Join us on Twitter | YouTube