BLOG

Introducing our Google Cloud API Connector

August 24, 2022
   
Solution
Posted by Jeremy Posner

Intro

Anyone who uses the Google Cloud platform will be familiar with Google’s huge range of API’s. There are API’s for all of their cloud products, ranging from Google Analytics, Google Ads, Google Search, to Google Maps, Natural Languge, and Cloud Logging. Here’s their API Explorer Page, with 245 different APIs at the time of writing.

This blog is about our new Google Cloud API connector – we will show how to set this up, and how to leverage this to build data products using RAW. Remember also we can use these APIs and combine them with any other data from other APIs, files or databases – giving the ability to create very powerful and insightful Data Products in a short time frame and little code.

So, let’s go….


OAuth2.0

Google has many ways to authenticate and each API may support different (and often multiple) mechanisms. Some of their APIs still support API keys, but Google is moving towards the OAuth2.0 mechanism. For more information see the Google Cloud API authentication page.

If you need help with setting up Google Cloud for Service Account & Key, you can also see our step-by-step instructions.

Connecting RAW to Google Cloud APIs

For this example we will use our RAW App and select a Repository for the Credential. Click on the Credentials tab for your chosen Repository, and select “Google” Credential below:

Credentials for a RAW Repository

On the dialogue box, we will upload the JSON file from our Google Account set up. We will need to select a Scope. See a list of Google OAuth2 Scopes here. The example below shows Google Search Console and a “webmasters.readonly” scope :

Adding a Google Credential

Now the Credential has been created, we can run some code….


Testing the Google Credential

Open Visual Studio Code, remember we need the Visual Studio Code Extension loaded, and a logged in account to RAW too. For more details go to our guide.

Enter the following code, changing details for your URL as necessary. In this example we are simply executing the API index:inspect, it’s a HTTP POST method, and takes a HTTP Body with some JSON and a credential which we named above. We could in fact use any endpoints with access now from the credential – Search Console has a few to choose from.

READ_JSON("https://searchconsole.googleapis.com/v1/urlInspection/index:inspect", 
  http_method:="POST",
      http_body_string := """{
        "inspectionUrl": "https://raw-labs.com/",
        "siteUrl": "https://raw-labs.com/",
        "languageCode": "en-US"
      }""",
 http_auth_cred_name:="RAW_Google_SearchConsole")

Execute the query, and it should return JSON data structure below. The structure itself is inferred and can be seen in the bottom-left pane (‘RAW QUERY PROPERTIES’)

RAW extension, in visual studio code – executing Google API

Creating a new API

Now we have connectivity tested we can create new API endpoints very easily. Here’s one we created earlier, this allows Search statistics from Search Console to be reported very easily, even into an Excel Spreadsheet.

Below is some code that allows us to query our Google Search Console data, using the query endpoint:

// import the query library function which replicates the GSC 'query'
FROM `github://raw-labs/lib/1/public/google.com/searchConsole.rql`
  IMPORT query;

select 
  cfirst(keys) as search_term, 
  clast(keys) as country,
  clicks, 
  impressions, 
  ctr, 
  position
from
query(
  http_auth_cred_name:="RAW_Google_SearchConsole", 
  url:="https://raw-labs.com/",
  startDate:="2022-08-01", 
  endDate:="2022-08-22",
  dimensions:=["query","country"]
  ).rows

The code above will take the output of the Google endpoint, which is a nested JSON structure like this:

{
  "rows": [
    {
      "keys": [
        "raw labs",
        "usa"
      ],
      "clicks": 1938,
      "impressions": 8772,
      "ctr": 0.22093023255813954,
      "position": 2.383720930232558
    },
    {
      "keys": [
        "raw labs",
        "grc"
      ],
      "clicks": 1734,
      "impressions": 2652,
      "ctr": 0.6538461538461539,
      "position": 1
    },

…. and flatten it into a tabular structure. Flattening the data makes it easy for an Excel or BI analytics end user.

tabular output for Google Search Console query

Lastly, we can configure this query and turn it into a new API endpoint simply. Just turn the query above into a function, and add a simple YAML file. You can see these files on GitHub here with other SEO-related APIs.

Securing the endpoint

We don’t want this endpoint as Public endpoint, meaning anyone can access our data. We do, however want to share it securely with others. RAW supports API keys currently for this purpose, with OAuth2 coming soon.

Now we can permission people to access this data and can share easily too with our invitation mechanism – so we could share outside of our organisation too without them needing access to our precious Google account, needing to know how to use Search Console, or its APIs.

Go to our permissions page inside the RAW App and add a Key. We will call it simply gsc-key, and it uses the gsc scope in the YAML file above:

adding a key for accessing the endpoint securely

Now we have created the API key, we can copy it on the API keys page below:

… and then use that API Key in any invocation, e.g. CURL, Python, or in our Excel spreadsheet that we automatically generate for this endpoint.

Executing the Secure Endpoint inside Excel

RAW generates a spreadsheet with the API invocation inside whenever we create an endpoint. You can read more about that here.

This API invocation, because it’s protected with an API key, requires the API key from above to be entered into the ‘Settings’ tab of the spreadsheet (cell C5).

Enter the API key into the cell C5 above in the Settings tab.

Finally, over on the ‘Data’ tab, enter some values for the Parameters in column C – in this case they are required: the credential name, URL, and two dates. Click ‘Get Data’ and the result from our API is now in Excel:

spreadsheet executes API

Wrap Up

That’s it for this blog post. What did you think? Is there anything in the Google Cloud API space or the SEO area you would like to see in RAW? Feel free to contact us if you have ideas, or share on the community

Jeremy Posner, VP Product & Solutions, RAW Labs.


Want to learn more?

Learn More

VIDEOS

Product Walkthrough

Walkthrough of Major Features, using a worked example to show both Data Consumer and Data Producer perspectives. Code accompanying this demo can be found on GitHub. You can also See Our Demo Catalog Here, where you can register for your...

Blog

Powerful new SEO & SEM Data Products with RAW

Create powerful SEO/SEM data products with RAW. This example compares historical search volumes for two keywords / brands over a time period for trend analysis.

Blog

Defining and Running Tests for API endpoints

We show how easy it is to create an API test suite using just YAML for your RAW APIs inside VS Code using our RAW Extension: Define tests, execute them and fix your code.

Blog

New Features in the RAW Visual Studio Code Extension

New features in our RAW Visual Studio Code Extension to enable our developer community to build great APIs to expose and share data.