Google Sheets API v4 Integration Guide

Google Web Services has become an essential part of the infrastructure of many projects, an important integration element. We can no longer imagine online services without them. In the meantime, Google developers are working on expanding the capabilities of their services, developing new APIs, and enhancing the security of our data. Usually, updates are released smoothly to the users and no changes are required on your side. But not this time with the new Google Sheets API.

Preface: Progress is a Pain

In 2021, Google introduced its version 4 Sheets API, which is inconsistent with the previous one. This affected data security and privacy. Sheets API v3 support was Extended till August 2021 To provide more time for developers to migrate to the new API version. Since the end of support for the v3 API, many JavaScript developers have faced migration issues. And although Google provided A Detailed Migration GuideAs is usually the case, it is missing several important details.

As a Support Engineer at AnyChart, I have received and continue to deal with numerous requests for help from users of our JS charting library who suddenly encountered a problem feeding visualizations with data from their Google Spreadsheets. This shows that the problem has indeed been and remains topical. So I decided to create a quick Google Sheets API v4 integration guide for anyone else.

This article shows a basic approach to accessing a spreadsheet document on Google Sheets and loading data from it, apparently as the most common use case.

Accessing Google Spreadsheets from JavaScript

To access a Google Sheets spreadsheet from JavaScript code, you need google-api-javascript-client And Sheets APIAs well as a configured Google Project and a document itself.

Let me walk you through all this step by step.

Configuration on the Google Side

1) Create a project

  1. go to Google Cloud Platform,
  1. Create a new project:
1656995060image Google Sheets API v4 Integration Guide

2) Enable API

  1. Go to “Enable APIs and Services”:
enable api screen
  1. Type “google sheets” in the search field to find the API:
Google Sheets Search Screen
  1. Select “Google Sheets API”:
1656995066image Google Sheets API v4 Integration Guide
  1. Enable Google Sheets API:
Enable Sheet API Screen

3) credential

  1. Go to the “Credentials” tab:
credential screen
  1. Click “Create Credentials” and select “API Key”:
create credential screen

Note: Copy and store the API key. You’ll need it later in the JavaScript code (GOOGLE_API_KEY js code).

c) Click on “Restricted Keys”:

restrict key screen

Comment: Protect your API keys during both storage and transmission, Best practices for this are well covered by Google this article, All the code snippets below have been simplified for demonstration purposes and do not describe the security aspects.

d) In the “Restricted Keys” dropdown menu, find the item “Google Sheets API”:

Restrict Key Google Sheets API Screen

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

ok screen

4) Create a document

  1. Create a Google Sheets document the way you usually do and fill it with some data. Set a name for the sheet with your data or copy the default one – this will be needed later in the JS code (SHEET_NAME,
create sheet screen
  1. Enable access to the document through a link. You can do this by clicking the “Share” button and selecting “Anyone with the link.” (“Audience” reach is sufficient.)
share sheet screen
  1. Copy the ID of the document. It can be found in the URL of the document, between the “/Spreadsheet/D/” and “/Edit” parts. This ID will be needed later in the JS code (SPREADSHEET_ID,
copy id from url bar

All the necessary settings have been completed by Google. Let’s move on to an application.

Accessing Google Spreadsheet Data from Javascript Applications

Now, I will explain how to create a simple JavaScript application that receives data from a spreadsheet and shows it to users. To connect the app to the Sheets API, I would use the Google API Client Library for JavaScript (aka Gappy), which is well described in its GitHub repository,

1) Creating a basic JavaScript application

First, include the gappy library in your page using direct link,

add

Tag the HTML code and apply the CSS code of your choice to the table and its future content.

In the JavaScript code, create a function that will be used to fetch the data.

const start = () => ;

Inside that function, initialize the gappy client 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 the request to get the value through gappy client. In the request, you must provide the spreadsheet ID and the range of cells where the data you want to access is located.

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

If all the settings are correct, the resolved promise responds with the data received. Now you can get the data from the response and populate the HTML table using a simple 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 code, call Burden() Take function from gappy library and pass as argument to the function created above.

gapi.load('client', start);

The resulting application looks like below. You are welcome to view the full code template of this HTML table with data from Google Sheets JSFiddle, To make your own thing work like this, just change GOOGLE_API_KEY, SPREADSHEET_ID, SHEET_NAMEAnd data range with your information (and don’t put braces).

view code

2) tinkering output – show data as a chart

In real-world applications, simple HTML tables are usually not enough; We want to visualize and analyze data. I’ll show you how to build a dashboard that increases the readability of data and brings us closer to a real-world use case. When I’m on duty and ask for help with Google Sheets API integration, it’s actually the first example I share, and basically, almost always the last because it’s so illustrative and No further help is needed.

So, let’s use AnyChart JS Library for data visualization. is included in column chart And Documentary filmWhich will be enough for this simple dashboard.

Before anything else, add AnyChart’s base js module For HTML:

<script src="https://cdn.anychart.com/releases/8.11.0/js/anychart-base.min.js">script>

In addition, add

Tags for the dashboard container and apply the appropriate ID to each:

<div id="container1">div>
<div id="container2">div>

Most of the JavaScript code remains exactly the same. I’ll just iterate over the code that handles the Sheets API response.

So, keep the first piece of 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 data 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 everything we need 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 in the department');
    pieChart.legend().itemsLayout('vertical').position('right');
    pieChart.container('container2').draw();

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

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


gapi.load('client', start);

Below is what the resulting dashboard looks like. You can see the complete template code of this dashboard visualizing data from Google Sheets using v4 API JSFiddle, To get your project like this, just put your information in the place of GOOGLE_API_KEY, SPREADSHEET_ID, SHEET_NAMEAnd data range (and don’t put braces).

dashboard screen

II hope this article is helpful to all those who decide to build an app that uses data from Google Sheets and access it from a JavaScript application. If you have any further questions, please feel free to contact me and I will be happy to do my best to help you.

For your convenience, this article lists all the useful links in one place:

prerequisites

integration example

Leave a Reply

×
×

Cart