API Connector Documentation

Print

Import Google SERP Data to Google Sheets

Last Updated OnJanuary 3, 2024
byAna
< Knowledge Base

In this guide, we’ll walk through how to use Google's Programmable Search Engine API (previously known as the Custom Search API) to import search engine result page (SERP) data to Google Sheets, using the API Connector add-on for Sheets.

This free API is primarily designed for creating applications that retrieve and display search results programmatically, like a search box widget that you place on your own website. However, it also lets you query Google's search engine check the search result rankings for any keyword, using Google's own search engine results page data.

Contents

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

Part 1: Create a Programmable Search Engine

  1. While logged into your Google account, navigate to https://programmablesearchengine.google.com/controlpanel/all, and click Add.
    programmablesearch-add
  2. You'll be prompted to configure your custom search engine. Give your Search Engine a name, and choose to search the entire web (unless you specifically want to limit your results to a subset of pages). Click Create.
    programmablesearch-create
  3. Your search engine has been created. Click Customize.
    programmablesearch-customize
  4. Scroll down the page and optionally adjust any settings. Note the search engine ID as we'll need that soon.
    programmablesearch-searchengineid
  5. That's it, your Google Programmable Search engine is ready.

Part 2: Get your Custom Search API Key

Accessing Google's Custom Search JSON API requires the use of an API key.

  1. Navigate to the Custom Search JSON API page and click Get a Key.
    google-search-api-img1
  2. Choose an existing project, or create a new one, and click Next.google-search-api-img2
  3. Note and copy your API key, you'll need this along with your custom search engine ID.
    programmablesearch-key

Part 3: Pull Google SERP Data into Sheets

For our first API request, we'll pull in the top 10 page titles, URLs, and snippets for a keyword.

  1. Open up Google Sheets and click Extensions > API Connector > Open > Create request.
  2. In the request form enter the following. Substitute in your own key, search engine ID, and keyword in the key, cx, and q parameters, respectively.
    • Application: Custom
    • Method: GET
    • Request URL: https://www.googleapis.com/customsearch/v1?key=AIzaSy11111111&cx=e74099405af6745a5&q=island vacations&fields=queries,items(title,link,snippet)
  3. Create a new tab and click Set current to use that tab as your data destination.
  4. Under Output options, set the Report style to 'compact'.
  5. Name your request and click Run. A moment later you’ll see a list of 10 search results populate your sheet.
    programmablesearch-results
  6. Fetch more records, or further customize your request by including any of the parameters available in the documentation. You can filter the response data by site, region, language, and so on.

Part 4: Handle Pagination

By default, the Custom Search API will return only 10 results. To get more, use the start and num parameters as described in their documentation.

You can run through these request URLs manually, or use API Connector's pagination handling function to loop through these pages automatically, like this:

  • Pagination type: offset-limit
  • Offset parameter: start
  • Limit parameter: num
  • Limit value: 10
  • Run until: choose when the request should stop running
    programmablesearch-pagination

This API will not return more than 100 total records, regardless of the actual number of search results.

Part 5: API Documentation

Official API documentation: https://developers.google.com/custom-search/v1/reference/rest/v1/cse/list

29 thoughts on “Import Google SERP Data to Google Sheets”

    • Hi Arnaud, this is a web development question so the answer completely depends on what you're trying to accomplish and the technology behind your website. Generally you'd write some code to parse the JSON response so it can be displayed on your site. Or, if you're looking for a no-code solution, you can look for a CMS plugin (here's one for WordPress: https://wordpress.org/plugins/json-content-importer/), or use an app like http://sheet2site.com/ to turn your spreadsheet into a website.

      Reply

      • Hi Ana. Thanks for your answer ! 😉
        I looked into the WP Jason importer but unfortunately I'm not techie enough to implement it till the end... I am actually trying to display Google Custom Search Results only (Say article is speaking about "Paris restaurants close to Eiffel Tower", I want to display results from predefined search terms such as "Paris+restaurants+Eiffel+Tower" as a side information based on the blog article) on a WP site to give readers further details on a subject.

    • Hey Hassan, do you mean can you get search results for the whole web via a search box? Sure, in that case you'd configure your search engine as described, then click the 'Add it to your site' Get Code button to get the code that creates a search box on your site.

      Reply

  5. Hello Ana,
    Such an important article especially for developers. But I want to pay attention that the result of Google CSE is not the same actual Google web search. At this point, we as Aves API, offer the most powerful and affordable SERP API in the market.

    Best Regards,
    Murat Akboğa / Founder, AvesAPI

    Reply

    • Unfortunately the Custom Search JSON API only lets you specify one search term at a time. So you'll either need to run separate requests for each search query you're interested in (you can use Append mode to print them into the same sheet), or use API Connector's (paid) multi-query feature which lets you stack URLs and run them as a block.

      Reply

  10. Hi Ana,
    Would you mind answering a couple of questions about the Api connector:
    1. Can use this Api connector on a mobile, as I do not have a computer at the moment,
    2. If yes to 1, how would I would I be able to make a search request to add the the following headers: Date, Title, Description, Url, Size 1000, Sort Asc, and import data under these headings. I would like to get data on shared google or excel stock spreadsheets.
    3. Regarding free Api option, if I do one search request for 1000 search results, is that considered as one request for the months, in regards to billing.

    Reply

    • 1. Can use this Api connector on a mobile, as I do not have a computer at the moment,
      The mobile version of Google Sheets supports extensions for Android, but not iOS (info)
      2. If yes to 1, how would I would I be able to make a search request to add the the following headers: Date, Title, Description, Url, Size 1000, Sort Asc, and import data under these headings.
      I'm not sure what should go under Size 1000 and Sort Asc, but for Date you can add a timestamp, and for Title, Description, and URL you can use a URL like this: https://www.googleapis.com/customsearch/v1?key=1234&cx=123:123
      &q=pizza slice&fields=queries,items(title,link,snippet)       (use the field editor to label the fields exactly as you want them)
      3. Regarding free Api option, if I do one search request for 1000 search results, is that considered as one request for the months, in regards to billing.
      That would be considered one request. Requests are counted by the number of times the API is called, so it doesn't matter how many records are returned. Hope that helps, let me know if I can clarify anything else.

      Reply

  11. Hi Ana,
    I hope you and your family had an enjoyable start to 2023.
    Thank you for the quick reply. I am unable to see the add-on when I select add-ons from the sheet. However when I go to the Play Store it indicates that is already installed. Maybe it installed the pc version ? Can you please send me the link to download the android verskon

    Reply

    • Thank you, Ron, you too 🙂 There's no device-specific link for API Connector, so you should already have access if you installed it previously. Can you please check this link for how to open add-ons in the Android version of Google Sheets?

      Reply

  12. Hi Ana!
    What if I want to fetch Top100 search results for certain keyword and import into GSheets only search results from certain domains (like of me and my direct competitors) with corresponding ranking place?

    Reply

    • Hey Evgeniy! You can filter via the API request URL by using the siteSearch parameter, e.g. &siteSearch=yoursite.com. That's convenient for making dynamic changes but the siteSearch parameter only lets you specify a single domain at a time. So the other way to include more is to specify the domains you want to search in your custom search engine configuration. Under "What to search" you can enter the sites you want to include.

      Reply

  15. Hi Ana, Thanks for the excellent tutorial. I have a quick question: Is there a way to get data from the Google "People Also Ask" section for a given keyword?

    Reply

    • Good question, but as far as I can see, this data is not available from Google's Search API, and this Q&A from their community seems to confirm it. If you're looking for this data, I think you'll need to use one of the paid search data services; they have access to a wider range of data since they scrape data themselves from Google's search results rather than relying on the official API.

      Reply

Leave a Comment

Jump To