Use Apps Script in Google Sheets to Extract Search Results Data

Google Apps Script is a scripting language, based on JavaScript, that allows users to extend the functionality of Google Sheets using a small amount of code. Using Apps script to make sense of search results can help gather and analyze data useful for SEO, product analysis, preparing reports, travel planning, and many other use cases.

To scrape search results, we can use SerpApi. SerpApi helps with scraping public data not only from Google but from all other top search engines, as well as Home Depot and Walmart product catalogs. Combining Google Apps Scripts functionality with rich search engine data from SerpApi can help unlock a world of possibilities for extracting, organizing, and analyzing search engine results directly within your Google Sheet.

This blog post will guide you through the process of using Google Apps Scripts within Google Sheets to extract specific data points from SerpApi's JSON responses.

Why Use SerpApi To Scrape Search Results

SerpApi manages the intricacies of scraping and returns structured JSON results, which allows you to save time and effort. We take care of proxies and any CAPTCHAs that might be encountered, so that you don't have to worry about your searches being blocked.

We also do all the work to maintain our parsers. This is important, as Google and other search engines are constantly experimenting with new layouts, new elements, and other changes. By taking care of this for you on our side, we eliminate a lot of time and complexity from your workflow.

You can check the full list of our APIs here. If you want to get a sense of what SerpApi can accomplish, I would suggest checking out our Playground, where you can try out our APIs and experiment with many of the search engines and parameters we support.

SerpApi's Google Sheets Add-on

SerpApi Google Sheets Extension is a Google Workspace Add-on that allows you to easily extract search engine results data directly into your Google Sheet without needing to write any code.

Key Features and Capabilities:

• Extracts data from various search engines: It supports scraping data from search engines like Google, Bing, and Baidu.
• Allows for data extraction from various elements: Retrieves data from organic results, local results, knowledge graphs, and more.
• Offers useful functions: Includes functions SERPAPI_RESULT() , SERPAPI_RANK, etc. We will use the SERPAPI_RESULT() function to extract data with specific queries and selectors in this blog post.

To read more about how the add-on works and how to use it in your google sheet, I suggest reading this blog post: Connect SERP API with Google Sheets (No Code).

We'll be using this to scrape data from search engines in Google Sheets.

Using Apps Script along with SerpApi's Google Sheets Add-on

A custom Apps Script function can help with extracting particular fields from JSON in Google Sheets, and laying them out in a readable report format (e.g., one row per week). This is particularly useful because native Google Sheets formulas can’t easily parse deeply nested JSON arrays.

The most important thing it allows for is flexible data parsing. You can:

• Extract only the fields you care about (e.g., only titles, links, prices) from all the results at once.
• Reshape JSON results to fit your sheet's format.
• Filter, sort, and process before displaying on the sheet
• Automate reporting using data from SerpApi

Using this along with SerpApi's Google sheet Add-on makes it easy to use the JSON response from SerpApi, pick and process various fields we care about extracting, and show those on our Google sheet.

How To Add A Google Apps Script Custom Function

1. Open your Google Sheet → Extensions → Apps Script.
2. Create a new function and add the code depending on what you want to do with the data. You write your code in the script editor, using JavaScript syntax and Apps Script APIs. Example Usage:

function FunctionName(jsonString) {
  // Add function steps
}

3. Save the script, and then use the function in your sheet, just like a formula.

Since the aim is to use the Apps Script function with the response from SerpApi's function, we will mention the SERPAPI_RESULT function within the Apps Script function we created in step 2. In this way, the element you select from the SERPAPI_RESULT function (in the <SELECTOR> part below) will be accessible to the Apps script function to use.

Here is the example usage in a cell:

=FunctionName(SERPAPI_RESULT("<PARAMETERS>", "<SELECTOR>"))

Example Use Cases

We'll use SerpApi Google Sheets Add-on for quick data extractions without any coding. Apps Script provides granular control over processing data from these API calls and we'll use that in conjunction with the add-on to create dynamic workflows.

To begin, if you haven't already, please install the SerpApi Google Sheets Add-on using this guide.

Let's discuss some common use cases where Apps Script can help you extract useful data from our API responses.

Scrape AI overview snippets in a single cell for any query

Example Usage: You want to get the snippets from a list of text blocks in the JSON response

The SERPAPI_RESULT function in our Google Sheet Add-on can provide the AI overview response, and you can select the text_blocks field from it to get a list of all the text parts in the AI overview.

Here's what the function usage would look like:

=SERPAPI_RESULT("engine=google&q=drop shipping&google_domain=google.com", "ai_overview.text_blocks")

Here's the function you can add to Apps Script to extract all the snippets from the text blocks:

function EXTRACT_SNIPPETS(jsonString) {
  const blocks = JSON.parse(jsonString);
  const snippets = blocks.flatMap(block => {
    if (block.snippet) return [block.snippet];
    if (block.list) return block.list.map(item => item.snippet);
    return [];
  });
  return snippets.join('\n'); 
}

Save the script, and then use the function in your sheet, just like a formula. Since we are passing the SerpApi response to this function, we will need to put the SerpApi formula for getting text blocks from AI overviews inside the EXTRACT_SNIPPETS() function call.

Example Usage: Paste this into a cell on your Google sheet -

=EXTRACT_SNIPPETS(SERPAPI_RESULT("engine=google&q=drop shipping&google_domain=google.com", "ai_overview.text_blocks"))

What the response looks like:

Create a report using Google Trends Data

Example Usage: You want to get a report out of the Google Trends data from the JSON response

The SERPAPI_RESULT function in our Google Sheet Add-on can provide the Google Trends response, and you can select the field timeline_data field from it to get a list of all the datapoints for the graph.

Here's what the function usage would look like:

=SERPAPI_RESULT("engine=google_trends&q=Coffee&data_type=TIMESERIES", "interest_over_time.timeline_data")

Here's the function you'd add to Apps Script to create a report from the data points:

function CREATE_TRENDS_REPORT(jsonString) {
  try {
    const data = JSON.parse(jsonString);
    const rows = [["Date", "Value"]]; // header row

    data.forEach(item => {
      const date = item.date || "";
      const value = item.values?.[0]?.extracted_value ?? "";
      rows.push([date, value]);
    });

    return rows;
  } catch (e) {
    return [["Error parsing JSON:", e.message]];
  }
  
}

Since we are passing the SerpApi response to this function, we will need to put the SerpApi formula for getting Interest over time trends data inside the CREATE_TRENDS_REPORT() function call.

Example Usage: Paste this into a cell on your Google sheet -

=CREATE_TRENDS_REPORT(SERPAPI_RESULT("engine=google_trends&q=Coffee&data_type=TIMESERIES", "interest_over_time.timeline_data"))

What the response looks like:

Get all competing offers for a product from the Google Product API

Example Usage: You want to get all the sellers and their offers for a product on Google Shopping from our Google Product API JSON response

The SERPAPI_RESULT function in our Google Sheet Add-on can provide the Google Product API response, and you can select the online_sellers field from it to get a list of all the sellers and prices.

Here's what the function usage would look like:

=SERPAPI_RESULT("engine=google_product&google_domain=google.com&product_id=4887235756540435899&gl=us&hl=en", "sellers_results.online_sellers")

Here's the function you'd add to Apps Script to create a report with the seller and price information:

function EXTRACT_ALL_OFFERS(jsonString) {
  try {
    const data = JSON.parse(jsonString);
    const rows = [["Seller Name", "Total Price"]]; // header row

    data.forEach(item => {
      const name = item.name || "";
      const price = item.total_price || "";
      rows.push([name, price]);
    });

    return rows;
  } catch (e) {
    return [["Error parsing JSON:", e.message]];
  }
  
}

Since we are passing the SerpApi response to this function, we will need to put the SerpApi formula for getting product seller data inside the EXTRACT_ALL_OFFERS() function call.

Example Usage: Let's consider getting this data for product_id=4887235756540435899 from Google Product API. To do so, paste this into a cell on your Google sheet -

=EXTRACT_ALL_OFFERS(SERPAPI_RESULT("engine=google_product&google_domain=google.com&product_id=4887235756540435899&gl=us&hl=en", "sellers_results.online_sellers"))

What the response looks like:

Try It Out Yourself

I've created a Google sheet with all the examples I listed above in case you want to try them out yourself and edit the scripts to see what else you can accomplish.

You can find it here:

apps_script_blog_post_example

Google Docs

Conclusion

By combining search results data from SerpApi and using the automation capabilities of Google Apps Script, we have transformed Google sheets into a dynamic tool for search data analysis.

I hope you found this tutorial helpful to understand how to use Apps Script in Google Sheets to extract search results data. If you have any questions, don't hesitate to reach out to me at sonika@serpapi.com.

Relevant Links

• Playground
• API Documentation page
• Plans and Pricing page
• Google Sheets Extension

Related Posts

Connect SERP API with Google Sheets (No Code)

Here is how to connect SERP API (SerpApi) to Google Sheets using a no-code solution

SerpApiHilman Ramadhan

Scrape Google Lens with Google Sheets

How to scrape Google Lens search results into a Google Sheet with the SerpApi Google Sheets Extension

SerpApiRyan Schafer

Connecting Google Sheets and SerpApi on Make.com

Getting started with a simple no-code Google Sheets and SerpApi integration on Make.com

SerpApiAlex Barron