How do I fetch data from Google Sheets?

Sharperlight Query Builder is not only capable of delivering data from a traditional database, but also accessing data from file systems, documents and more – including API endpoints such as the Google Sheets API.

This article will explore the use of Sharperlight Query Builder to retrieve data from a Google Sheets document via the ‘spreadsheets.values.get’ method of the Google Sheets API.
Broadly, the steps for doing so are:

• Configure the Google Cloud Platform and enable the Google Sheets API
• Generate and retrieve the API key
• Allow your sheet to be accessible via API key and retrieve the Spreadsheet ID and Range parameter
• Build and format the API request in Query Builder

Pre-requisites

In order to successfully access data via the Google Sheets API, your organisation must appropriately configure the Google Cloud Platform via the Developer Console.
Within the Developer Console, the Google Sheets API must be enabled so that the endpoints can be queried. To enable the API, or to check if it is currently enabled, simply search for the Google Sheets API in the search bar at the top of the console, and browse to the API management page.

Once you have located the Google Sheets API Management page, click the ‘Enable’ button to allow the endpoint to be queried. Once you have done so, a green tick should indicate that it is successfully enabled.

Security & Authentication

There are two acceptable forms of authenticating a Google Sheets API Request.
The simplest form of authentication requires the use of an API key. Once an API key has been generated, this can be used to authenticate any request that it has been allowed access to – however it does have some shortcomings with access to private documents and must be managed correctly to prevent security issues.
The second form of authentication is using OAuth 2.0. This allows for user-level security and permissions to be applied, allowing users to query their own spreadsheets, or spreadsheets they have been given access to. While this is a more secure, flexible and powerful option – it can be tricker to configure depending on your organisation and it’s administrative needs.

How do I get the credentials I need?

As configuring and navigating user flow and consent with OAuth 2.0 is quite complex and out of scope for this document, we will explore the usage of an API key to allow read-access to a Google sheet.

Due to API keys lacking any ‘user’ identification to assign a particular sheet to, Google Sheets must be enabled for open sharing via link in order to be accessed by an API key. Depending on the sensitivity of the data, this may be a security concern.

Once you have retrieved your API Key and configured its scope and restrictions where necessary, copy it and keep it saved for later.

Building an API request in Query Builder

Once we have enabled the Sheets API and generated an API key we can then start building our API request.

Sharperlight’s Query Builder is typically used to provide access to data from products via ‘Datamodels’. The datamodel, or Product that you are querying is selected in the Query Builder interface.
The ‘System’ product contains many helpful utilities and query tools, such as Service diagnostics, usage reports, and in this instance, we can use it to query the Google Sheets API.

The System product’s ‘tables’ represent the different query tools, administrative and diagnostic queries we can perform. For API requests, we are looking for the ‘table’ entitled “Web Page Table, CSV, JSON, XML or OData”.

To query the API endpoint, we can build our request URL using the Google Sheets API specifications. We will use the ‘spreadsheet.values.get‘ method, as this method will allow us to read the data from our spreadsheet directly. The request syntax for this method is as follows:

https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}?key={apiKey}

The spreadsheetId, range, and apiKey values are all variables that will change depending on which Spreadsheet, tab (range) and API key we are using – so to make this query more reusable we can use “Prompt Only Filters”.

First, let’s change our URL slightly to allow Query Builder to be able to replace our variables with the “Prompt Only Filter” values. Filter references in Sharperlight are similar to the ones used above, but are prefixed with an @ symbol, e.g. {@Filter_Name}.
By placing an @ at the front of the variable name, but inside the brace, they will reference filter parameters.

https://sheets.googleapis.com/v4/spreadsheets/{@spreadsheetId}/values/{@range}?key={@apiKey}

The full API specification can be found at Sheets API  |  Google Developers

Next, we will need to copy our URL into the Query Builder “URL Link” property.

This will result in a warning that we have not yet created the filter variables (spreadsheetId, range and apiKey), so let’s handle that now.

You can create additional ‘variables’ using Prompt Only Filters. Right click anywhere in the filters section (the top pane of Query Builder), and navigate the drop-down command list to find Other > Text. Add the name of the field you want to create (e.g. “spreadsheetId”) when prompted, and press OK.

Lastly, drag the new filter to the top of the filters pane, above “URL Link”. Repeat this step with ‘range’ and ‘apiKey’ so that we have all three required variables, as below.

Care should be taken to ensure the attribute names (highlighted in green above) match the required variable name from our URL. If they do not, this can be changed in the options menu, by right clicking on each filter value and selecting options from the dropdown menu.
You may also wish to set the filters to “single value only” as the additional ‘cell’ is not needed for these values.

Now that we have our three variables set up, we need to input the values. You should already have the API Key saved from earlier – if not, return to the “Security and Authentication” section for details.

Open the Google Sheet you would like to share.
The ‘range‘ value will be the name of the tab you wish to read (e.g. Sheet1) – in Excel this would be referred to as the worksheet.

The “Spreadsheet ID” can be retrieved from the URL bar. This is the long string of characters following “/d/”

To allow the API key access to your sheet, and click the green share button in the top-right corner. In the pop-up window, click “Change to anyone with the link”.

Now that we have all the required details, we can input the values into Query Builder and make a request.

We have successfully queried the API at this point – and some minor formatting is required to make the dataset readable.
Due to the nested structure of the ‘JSON’ format returned by the API, we will need to tell Query Builder to ‘start in’ the values field. Either drag and drop ‘values’ from the selection tree on to the ‘Start Position’ filter cell, or simply type ‘values’ into the filter cell.

Once we have ‘expanded’ the values field and it is no longer nested, we can select our columns and optionally the line id, and preview the formatted output of the API request.

And finally, we have our full dataset. This query can be used to drive output for any Sharperlight query, including within Publisher or Excel. When refreshed, it will request updated information from the Google Sheets API.

There are additional options for formatting and adjusting the output of the API request – for more information refer to the article Querying JSON.

Can I pull data from Google Sheets into Excel?

How do I pull data from Google Sheets to a website?