Google Sheets API v4 Integration Guide

Google web services have turn out to be a necessary a part of many projects’ infrastructure, an important integration element. We will now not imagine online services without them. Meanwhile, Google developers are working on expanding the capabilities of their services, developing recent APIs, and increasing the safety of our data. Normally, updates are released easily for users and don’t require any changes in your side. But not this time with the brand new Google Sheets API.

Preface: Progress Is a Pain

In 2021, Google introduced version 4 of its Sheets API, which is incompatible with the previous one. This affected data security and privacy. Sheets API v3 support was prolonged until August 2021 to supply developers with more time to migrate to the brand new API version. Because the end of support for the v3 API, many JavaScript developers have faced migration issues. And although Google provided an in depth migration guide, because it normally happens, several crucial details are missing from it.

As a support engineer at AnyChart, I actually have received and proceed to cope with quite a few requests for help from our JS charting library users who suddenly faced issues with feeding visualizations with data from their Google spreadsheets. It shows the issue has been and stays really topical. So I made a decision to make a fast Google Sheets API v4 integration guide for anyone else on the market.

This text showcases a basic approach to accessing a spreadsheet document on Google Sheets and loading the information from it as apparently probably the most common use case.

Accessing Google Spreadsheets from JavaScript

To access a Google Sheets spreadsheet from the JavaScript code, you would like google-api-javascript-client and Sheets API, in addition to a configured Google project and a document itself.

Let me walk you thru all this step-by-step.

Configuration on Google Side

1) Create a project

Go to the Google Cloud Platform:

Create a recent project:

2) Enable API

Go to “Enable APIS and services”:

Type “google sheets” within the search field to search out the API:

Select “Google Sheets API”:

Enable the Google Sheets API:

3) Credentials

Go to the “Credentials” tab:

Click “Create credentials” and choose “API key”:

Note: Copy and store the API key. You have to it within the JavaScript code later ({GOOGLE_API_KEY} within the JS code).

c) Click “Restrict key”:

Note: Keep your API keys secure during each storage and transmission. Best practices for this are well covered by Google in this text. All of the code snippets below are simplified for demonstration purposes and don’t describe security points.

d) Within the “Restrict key” dropdown menu, locate the “Google Sheets API” item:

e) Select it, click “OK” and “SAVE”:

4) Create a document

Create a Google Sheets document the way in which you normally do and fill it with some data. Set a reputation for the sheet along with your data or copy the default one — it would be required later within the JS code ({SHEET_NAME}).

Enable access to the document via a link. You possibly can do it by clicking on the “Share” button and selecting “Anyone with the link”. (The “Viewer” access is enough.)

Copy the ID of the document. It will possibly be present in the document’s URL, between the “/spreadsheets/d/” and “/edit” parts. This ID will likely be required later within the JS code ({SPREADSHEET_ID}).

All of the vital settings on the Google side are accomplished. Let’s move on to an application.

Accessing Google Spreadsheet Data from JavaScript Applications

Now, I’ll explain methods to create a straightforward JavaScript application that fetches the information from the spreadsheet and shows it to users. To attach the app to the Sheets API, I’ll use the Google API Client Library for JavaScript (aka gapi), which is well described in its GitHub repository.

1) Making a basic JavaScript application

Initially, include the gapi library in your page using the direct link.

Add the

tag to the HTML code and apply the CSS code you want for the table and its future content.

Within the JavaScript code, create a function that will likely be used for fetching the information.

const start = () => {};

Inside that function, initialize the gapi client along with your Google API key created earlier.

gapi.client.init({
‘apiKey’: ‘{GOOGLE_API_KEY}’,
‘discoveryDocs’: [“https://sheets.googleapis.com/$discovery/rest?version=v4”],
})

Then execute a request to get values via the gapi client. Within the request, you must provide the spreadsheet ID and the range of cells where the information you ought to access is situated.

.then(() => {
return gapi.client.sheets.spreadsheets.values.get({
spreadsheetId: ‘{SPREADSHEET_ID}’,
range: ‘{SHEET_NAME}!{DATA_RANGE}’,
})
})

If all settings are correct, the resolved promise returns a response with the fetched data. Now you’ll be able to get the information from the response and populate the HTML table using a straightforward JS script.

.then((response) => {

const loadedData = response.result.values;

const table = document.getElementsByTagName(‘table’)[0];

const columnHeaders = document.createElement(‘tr’);
columnHeaders.innerHTML = `<th>${loadedData[0][0]}th>
<th>${loadedData[0][1]}th>`;
table.appendChild(columnHeaders);

for (let i = 1; i < loadedData.length; i++) {
const tableRow = document.createElement(‘tr’);
tableRow.innerHTML = `<td>${loadedData[i][0]}td>
<td>${loadedData[i][1]}td>`;
table.appendChild(tableRow);
}
}).catch((err) => {
console.log(err.error.message);
});

To execute the code, call the load() function from the gapi library and pass the function created above as an argument.

gapi.load(‘client’, start);

The resulting application looks like below. You might be welcome to ascertain out the complete code template of this HTML table with data from Google Sheets on JSFiddle. To get your individual thing like this working, just replace {GOOGLE_API_KEY}, {SPREADSHEET_ID}, {SHEET_NAME}, and {DATA_RANGE} along with your own information (and don’t keep the braces).

2) Tinkering output — show the information as a chart

In real-world applications, easy HTML tables are often not enough; we would like to visualise and analyze the information. Let me show you methods to create a dashboard that increases the readability of the information and brings us closer to the real-world use case. After I am on duty and asked for assistance with Google Sheets API integration, it is definitely the primary example I share, and mainly, almost all the time the last because it’s very illustrative and no further assistance is needed.

So, let’s use the AnyChart JS library for data visualization. It includes column charts and pie charts, which can be enough for this straightforward dashboard.

Before anything, add AnyChart’s base JS module to HTML:

Also, add

tags for dashboard containers and apply an appropriate ID for every:

Many of the JavaScript code stays absolutely the identical. I’ll just rework the code that handles the Sheets API response.

So, keep the primary a part of the JS code unchanged:

const start = () => {

gapi.client.init({
‘apiKey’: ‘{GOOGLE_API_KEY}’,
‘discoveryDocs’: [“https://sheets.googleapis.com/$discovery/rest?version=v4”],
}).then(() => {
return gapi.client.sheets.spreadsheets.values.get({
spreadsheetId: ‘{SPREADSHEET_ID}’,
range: ‘{SHEET_NAME}!{DATA_RANGE}’,
})
}).then((response) => {

In the response handler, parse the information to compose a structure compatible with the AnyChart API:

const loadedData = response.result.values;
const parsedData = {
‘header’: loadedData.shift(),
‘rows’: loadedData,
};

Now we’ve got every thing we’d like to create and configure charts for the dashboard:

const columnChart = anychart.column();

columnChart.data(parsedData);

columnChart.title(‘Sales volume by manager’);
columnChart.xAxis().title(‘Manager’);
columnChart.yAxis().title(‘Sales volume, $’);

columnChart.container(‘container1’).draw();

const pieChart = anychart.pie(parsedData);
pieChart.title(‘Sales volume distribution within the department’);
pieChart.legend().itemsLayout(‘vertical’).position(‘right’);
pieChart.container(‘container2’).draw();

Then goes the identical ending part as with the HTML table — let’s recollect it just in case:

}).catch((err) => {
console.log(err.error.message);
});
};

gapi.load(‘client’, start);

Below is what the resulting dashboard looks like. You possibly can try the complete template code of this dashboard visualizing data from Google Sheets using the v4 API on JSFiddle. To get your individual project like this, simply put your individual information instead of {GOOGLE_API_KEY}, {SPREADSHEET_ID}, {SHEET_NAME}, and {DATA_RANGE} (and don’t keep the braces).

Epilogue and Links

I hope this text will likely be helpful to anyone who decides to construct an app that uses data from Google Sheets and access it from JavaScript applications. If you may have any further questions, please be happy to get in contact with me and I will likely be completely happy to do my best to allow you to out.

In your convenience, here is a listing of all useful links from this text, in a single place:

Prerequisites

Integration examples