Build Your Own Search Engine Results Pages (SERP) Dashboard in Sheets

This project will introduce how to build a SERP dashboard in Google Sheets, so you can quickly check on the search result rankings of any keyword, filtered for numerous different parameters. The final result will look like this, with a keyword input tool and a Run button on the right:

If you just want to get the dashboard, feel free to skip to the final section for the download, otherwise read on for how to build it yourself. The advantage of doing it yourself is that you’ll understand how to customize the SERP tool just the way you like it. This article is long only because it walks through each step in detail; the process isn’t difficult.

Contents

• Ingredients
• Step 1: Create a Custom Search Engine
• Step 2: Modify the Search Engine to Search the Web
• Step 3: Get your API Key
• Step 4: Build a Dynamic Search URL
• Step 5: Set up the Request in API Connector
• Step 6: Complete the SERP Dashboard
• Download the SERP Dashboard for Sheets

Ingredients

1. An instance of Google Sheets. A link to Sheets probably isn’t necessary, but regardless here it is.
2. A Search Results API as our source for search data. We’ll be using Google’s own Custom Search JSON API.
3. An installation of API Connector. This will connect Google’s Search API to Google Sheets.

Cost: FREE

Step 1: Create a Custom Search Engine

While logged into your Google account, navigate to https://cse.google.com/cse/all, and click ‘Add’.

You’ll be prompted to configure your custom search engine. Choose a URL (any URL), and give your search engine a name. For this example, I’ve entered ‘www.demo.com’ and named it ‘Demo’, but you can name it anything. Click the Create button.

Now click into the Control Panel to configure your custom search engine.

Step 2: Extend the Search Engine to Search the Web

You will see a long list of settings related to your new custom search engine. For our purposes you only need to pay attention to the following:

1) Scroll down the page until you see your search engine ID. Keep this handy as we’ll need it shortly:

2) Delete your dummy URL, and toggle on the ‘Search the entire web’ option.

That’s it, move on to the next step!

Step 3: Get your API Key

Accessing Google’s Custom Search JSON API requires the use of an API key. To get this key, navigate to the Custom Search JSON API page and click Get a Key.

Choose an existing project, or create a new one, and click Next.

Note and copy your API key, you’ll need this soon as well.

Step 4: Build a Dynamic Search URL

Google’s documentation describes how to create a URL that returns results from their API. The key query string holds the API key, the cx query string holds the search engine ID, the q query string holds your search query, and the fields query string lets us limit the response to just the fields we want. In this case it will be each search result’s ranking, title, link, snippet, and image.

• API root: https://www.googleapis.com
• Endpoint: /customsearch/v1
• Query strings: ?key={API key}&cx={custom search engine ID}&q={search query}&fields={fields of interest}
  Query strings example: ?key=AIzaSy…&cx=016496445636816056556:qvzl4ib4xtb
  &q=pizza slice&fields=queries,items(title,link,snippet,pagemap(cse_thumbnail))

Putting it together, we get the full API Request URL:

https://www.googleapis.com/customsearch/v1?key=AIzaSy...&cx=016496445636816056556:qvzl4ib4xtb
&q=pizza slice&fields=queries,items(title,link,snippet,pagemap(cse_thumbnail))

Since we want this whole sheet to be dynamic, let’s reference cells in our request instead of hard coding them into the URL. We’ll create a big yellow block at the top in cell G2, as an input box for our search terms. Over on the left, in cells C4:C5, let’s also add input boxes for the API key (‘key’) and search engine ID (‘cx’). Fill those values in now. Below that, we’ll also leave slots for optionally entering advanced settings.

Now we can treat our cells as input variables, using them to easily swap out different keywords and setup IDs.

https://www.googleapis.com/customsearch/v1?key=+++Summary!C4+++&cx=+++Summary!C5+++&q=+++Summary!G2+++&fields=queries,items(title,link,snippet,pagemap(cse_thumbnail))

Step 5: Set up the request in API Connector

We next need to add this request into the API Connector sidebar.

1. Click Add-ons > API Connector > Create New API Request
2. In the Create Request interface, enter the Request URL we just created.
   
3. We don’t need any headers for Google’s custom search API. Just leave that section blank.
4. Create a new tab. We’ll call it ‘Data’. While still in that tab, click ‘Set’ to use that tab as your data destination.
5. Under Output options, let’s choose some options to make the output more convenient for our dashboard. We’ll choose “compact” report style, so that each search result gets printed on separate rows. We’ll also choose to add in a timestamp so we can check when our data has last updated.
6. (Optional) Still under Output options, we’ll set up pagination. Google’s search API only returns 10 results at a time so we want to automatically loop through and append 3 sets of results together, for a total of 30 records.
   
   Please note that pagination is a paid feature of API Connector. If you’re on the free plan you can either leave as is and get the top 10 results, or get multiple sets of data by running requests separately like this (you can group them together at the end with a QUERY() function):

   page 1 https://www.googleapis.com/customsearch/v1?key=...&start=1&num=10

   page 2 https://www.googleapis.com/customsearch/v1?key=...&start=11&num=10

   page 3 https://www.googleapis.com/customsearch/v1?key=...&start=21&num=10

7. Name and save your request. We'll call it 'SERP'.

Step 6: Complete the SERP Dashboard

1. Making sure you've already entered in your values for search engine ID and API key, enter a search term and Click Run on your saved SERP request. You should see your Data sheet populate with results for that term.
2. Now back in your Summary sheet, set up formulas to pull in just the data you want. We'll get the following:
   • Total Results:

     cell G4 =iferror(HLOOKUP("queries » request » totalResults",Data!1:2,2,false),"")

   • Item Title:

     cell F5 =QUERY(Data!$1:$1000,"SELECT "&SUBSTITUTE(ADDRESS(1,MATCH("items » title",Data!$1:$1,0),4),1,""),1)

   • Item Link:

     cell G5 =QUERY(Data!$1:$1000,"SELECT "&SUBSTITUTE(ADDRESS(1,MATCH("items » link",Data!$1:$1,0),4),1,""),1)

   • Item Snippet:

     cell H5 =QUERY(Data!$1:$1000,"SELECT "&SUBSTITUTE(ADDRESS(1,MATCH("items » snippet",Data!$1:$1,0),4),1,""),1)

   • Item Image:

     cell I5 =QUERY(Data!$1:$1000,"SELECT "&SUBSTITUTE(ADDRESS(1,MATCH("items » pagemap » cse_thumbnail » src",Data!$1:$1,0),4),1,""),1)

   • Image Array:

     cell J5 =arrayformula(image(I6:I))

   Your Summary sheet will now be a dynamic SERP report, with nicely formatted results from Google's Custom Search API.
   
   Enter different search terms into the search entry cell at G2 and click Run to refresh your results.

   Optional: Add additional advanced search parameters to your request URL by referencing the cell at the end of your request URL in the API Connector sidebar, like this, like this: &c2coff=+++Summary!C6+++

   Download SERP dashboard for Sheets

   You can grab the template at this link: SERP Dashboard Template.
   Notes:
   *it's read-only, so click File > Make a Copy before using
   *Make sure to set up an API request (Add-ons > API Connector > Create New API Request) with the following URL before running (you'll need to choose Compact report style and add in your API key and search engine ID as described above):

   https://www.googleapis.com/customsearch/v1?key=+++Summary!C4+++&cx=+++Summary!C5+++&q=+++Summary!G2+++&fields=queries,items(title,link,snippet,pagemap(cse_thumbnail))