Scrape Google Maps Business Reviews with SerpApi's Google Sheets Extension [No Code]

Introduction

This tutorial will teach you how to use SerpApi's Google Maps Reviews API for getting user reviews for a business on Google Maps using the SerpApi Google Sheets Extension.

If you would like, you can find the entire google sheet we will be creating here, and follow along to dive deeper into the details.

Getting started

To begin extracting business reviews data, first, create a free account on serpapi.com. You'll receive one hundred free search credits each month to explore the API.

Next, install the SerpApi extension in Google Sheets. Simply go to Google Sheets, navigate to Extensions -> Add-ons -> Get add-ons, and search for 'SerpApi - Search Engine Results and Ranks'.

After you install the add-on, you will be asked to connect your SerpApi account with it. You can get your API key here: https://serpapi.com/manage-api-key.

After adding that, you can start exploring SerpApi Google Sheets add-on capabilities.

Google Maps Reviews API Details and Parameters

To get the reviews for a business listing, we will be using Google Maps Reviews API. I recommend heading to our playground, where you can test all the parameters and see what works best for you.

Parameters 

engine: Set parameter to google_maps_reviews to use the Google Maps Reviews API engine.

data_id: Parameter defines the Google Maps data ID. Find the data ID by using our Google Maps API [More on how to get this below].

api_key: Parameter defines the SerpApi private key to use.

sort_by: Parameter is used for sorting and refining results. 

Available options:

• qualityScore - the most relevant reviews (default).
• newestFirst - the most recent reviews.
• ratingHigh - the highest rating reviews.
• ratingLow - the lowest rating reviews.

topic_id: Parameter defines the ID of the topic you want to use for filtering reviews. You can access IDs inside our structured JSON response [More on how to get this below].

There are a couple more parameters that can be used. You can check them all in our documentation, but we will focus on the essential ones.

Expected Output

The API gives results in JSON format. The output includes JSON structured data for place information, topics, and reviews results.

A search status is accessible through search_metadata.status.

It flows this way: Processing -> Success || Error

If a search has failed, the error will contain an error message. search_metadata.id is the search ID inside SerpApi.

Basics of SerpApi's Google Sheets Extension

You will notice that the API request is composed of two separate elements - the query and the selector.

You can think of Query as the parameters that make the search page and Selector as the location in the JSON file where to find the bit of data you are looking for.

For the rest of the review results, you will change 0 in the Selector part to 1, 2, and so on. 

An alternate way to build the query is by heading to our playground, and searching with the relevant parameter values (for example, if you wish to sort or filter by topic), and then using the 'Export to Code' option as demonstrated below:

If you decide to go with this method, please note that the selector part of the SERPAPI_RESULT function will not be generated correctly, so you may need to change that according to the data you want to get in a particular cell in the Google Sheet.

Extract Business Reviews in Google Sheets

For this example, we will focus on extracting reviews for LifeTime Health Club in Austin, TX. 

First - we need the data_id in order to get the reviews for a given business. So if we want to get all the reviews for the LifeTime Health Club in Austin, TX, we first need to use the Google Maps API and search for LifeTime Health Club. Let's do this in our playground.

Scroll down in the JSON output and find the relevant result, then get the data_id for it.

Now we can go to Google Maps Reviews API in the playground, and search using the data_id:

This should give you a good visualization of the fields that are present in the response.

Second - you must decide what parts of the JSON response you need, and create a table with a header for each element. In this example, I am going to extract two parts of the result:

1. Metadata about the place

This includes: title, address, rating, reviews, type

2. Reviews Data

This includes: Index, iso_date_of_last_edit, User.Name, Rating, Snippet, Review_id, Likes, Link, Response, Response.Snippet, Response.iso_date

The Index variable helps in referencing each result in our function. The other columns are the different parts of each review we wish to obtain.

I will elaborate more below on how to retrieve reviews after sorting and filtering them in different ways from our API.

Extract the Metadata

To get the different parts of this metadata about the place, we will use the SERPAPI_RESULT function in Google Sheets along with the engine and data_id parameters. This information about a place is part of the place_info part of the JSON response. We will need to query for each item (title, address, etc.) individually.

Our query for title for will look something like this:

=SERPAPI_RESULT("engine=google_maps_reviews" & "&data_id=0x8644cd7fdc1bf17b:0x1eb1b9a3fe5dab90", "place_info.title")

You can see that some variables can be replaced with the data from the Google Sheet cells, which will be used repeatedly (example: data_id and the parameter name). Below you can see the updated function for the title.

=SERPAPI_RESULT("engine=google_maps_reviews" & "&data_id=" & B2, "place_info." & A5)

You can adjust the selector part of the function to get the other metadata information.

For example, for extracting the overall rating, you can use place_info.rating as the selector.

Get The Most Recent Reviews

You can sort reviews by navigating to the reviews section on the Google Maps for LifeTime Health Club and clicking on "Sort By". This will display the available options to sort by.

Corresponding options for the sort_by parameter in our Google Maps Reviews API are the following:

• qualityScore - the most relevant reviews (default).
• newestFirst - the most recent reviews.
• ratingHigh - the highest rating reviews.
• ratingLow - the lowest rating reviews.

To get the most recent reviews, we need to use the sort_by parameter with value newestFirst.

We have already set up our columns: Index, iso_date_of_last_edit, User.Name, Rating, Snippet, Review_id, Likes, Link, Response, Response.Snippet, Response.iso_date.

Our query for iso_date_of_last_edit for the first review will look something like this:

=SERPAPI_RESULT("engine=google_maps_reviews" & "&data_id=0x8644cd7fdc1bf17b:0x1eb1b9a3fe5dab90" & "&sort_by=newestFirst", "reviews.0.iso_date_of_last_edit")

You can see that some variables can be replaced with the data from the Google Sheet cells, which will be used repeatedly (example: data_id, Index, parameter names). Below you can see the updated function for the iso_date_of_last_edit for the first review:

=SERPAPI_RESULT("engine=google_maps_reviews" & "&data_id=" & B2 & "&sort_by=" & B13, "reviews." & A16 & "." & LOWER(B15))

The columns Index, iso_date_of_last_edit, User.Name, Rating, Review_id, Likes, and Link are available for all reviews, so the method above works. However, not all reviews may have a snippet (text along with the rating), or a response from the owner. In that case, if you try to get those fields with the function above, it may throw this error because the field does not exist in the JSON response:

For columns, Snippet, Response, Response.Snippet, Response.iso_date, we should add a conditional statement to look for this error and populate the cell in the table appropriately with a relevant message such as "Field does not exist".

For extracting the snippet, this looks like:

=IF(
    IFERROR(
        SEARCH(
            "Oops field is not found", 
            SERPAPI_RESULT(
                "engine=google_maps_reviews" & "&data_id=0x8644cd7fdc1bf17b:0x1eb1b9a3fe5dab90" & "&sort_by=newestFirst", "reviews.0.snippet"
            )
        ), 
        0
    ) = 1, 
    "No review provided, only rating given by the reviewer", 
    SERPAPI_RESULT(
        "engine=google_maps_reviews" & "&data_id=0x8644cd7fdc1bf17b:0x1eb1b9a3fe5dab90" & "&sort_by=newestFirst", "reviews.0.snippet"
    )
)

What does this do?

• This function checks if the review snippet field is missing or not found by searching for the error message "Oops field is not found".
• If the error message is found, it means only a rating (and no text review) was provided, so the function returns "No review provided, only rating given by the reviewer".
• If the error message is not found, it means the desired review field exists, so the function returns the actual review data fetched by the SERPAPI_RESULT function.

Let's dive deeper into how this works.

• SEARCH Function
  • The SEARCH function looks for the substring "Oops field is not found" within the result returned by SERPAPI_RESULT.
  • This substring is returned by the API when a specific field (like the snippet) is missing or not found.
• IFERROR Function
  • IFERROR handles any potential errors that might occur in the SEARCH function. If SEARCH fails (e.g., because the field is missing), it returns 0 instead of an error.
  • If SEARCH finds "Oops field is not found" in the API response, it will return the position of the substring (which is 1 since it's at the beginning). If it doesn't find the substring, SEARCH returns an error, and IFERROR then returns 0.
• IF Statement
  • The IF function checks whether the IFERROR(SEARCH(...)) result equals 1.
    • If true (meaning the "Oops field is not found" message was detected), it returns the string "No review provided, only rating given by the reviewer".
    • If false (meaning the search didn't find the snippet), it runs SERPAPI_RESULT to fetch the actual data from the API and returns that result.

Similarly, we can form a function to check if there is an owner's response snippet and get that field in the Google Maps Reviews data:

=IF(
    IFERROR(
        SEARCH(
            "Oops field is not found", 
            SERPAPI_RESULT(
                "engine=google_maps_reviews" & "&data_id=0x8644cd7fdc1bf17b:0x1eb1b9a3fe5dab90" & "&sort_by=newestFirst", "reviews.0.response.snippet"
            )
        ), 
        0
    ) = 1, 
    "No response from owner", 
    SERPAPI_RESULT(
        "engine=google_maps_reviews" & "&data_id=0x8644cd7fdc1bf17b:0x1eb1b9a3fe5dab90" & "&sort_by=newestFirst", "reviews.0.response.snippet"
    )
)

• It uses SEARCH to look for the phrase "Oops field is not found" in the data fetched by SERPAPI_RESULT.
• If the error message is found (meaning the owner's response snippet is missing), it returns "No response from owner".
• If the message isn’t found, it returns the owner’s response snippet fetched by the API.

Following the example above for other columns, you now have a complete list of extracted parameters for the most recent review for a business. To retrieve results for following reviews, copy the function and paste it into the rows below the first one and increment the index.

Get Reviews Filtered By Topic

You can filter reviews by topic in the reviews section on the Google Maps page for LifeTime Health Club by clicking on one of the topic options present there.

To get the most recent reviews, we need to use the topic_id parameter.

First, use the playground to make a query to the Google Maps Reviews API for the place, and find a topic_id for the topic you are interested in getting reviews for.

The topic_id will look something like this: /m/0f3yyn (for Amenities)

We can create a new table for collecting reviews for a particular topic. We will use the same columns as before: Index, iso_date_of_last_edit, User.Name, Rating, Snippet, Review_id, Likes, Link, Response, Response.Snippet, Response.iso_date.

For example, our query for iso_date_of_last_edit for the first review will look something like this:

=SERPAPI_RESULT("engine=google_maps_reviews" & "&data_id=0x8644cd7fdc1bf17b:0x1eb1b9a3fe5dab90" & "&topic_id=/m/0f3yyn", "reviews.0.iso_date_of_last_edit")

We can use the same kind of queries as in the earlier example, and just add "&topic_id=<insert_topic_id>" to filter by topic, and remove the sorting parameter if it is not needed.

Following the example above for other columns, you now have a complete list of extracted parameters for the most recent review for a business. To retrieve results for following reviews, copy the function and paste it into the rows below the first one and increment the index.

Conclusion

We've covered how to scrape business reviews with Google Maps Reviews API in a no-code way using the SerpApi Google Sheet Extension.

You can find the full Google Sheets example I used for this blog post in this Google Sheet.

I hope you found this tutorial helpful and straightforward. If you have any questions, don't hesitate to reach out to me at sonika@serpapi.com.

Relevant Links

Documentation

• Google Sheet with Complete Example
• Google Maps API
• Google Maps Reviews API

Related Posts

• Getting Google Maps Business Listing with SerpApi Google Sheets Extension
• Scraping Google Maps with SerpApi - an Overview of the Google Maps API
• No-Code Google Maps API Reviews Scraping with SerpApi Video
• Scraping Google Maps Place Reviews Results using SerpApi