Google Sheets JavaScript API Spreadsheet Tutorial
by Vincy. Last modified on October 11th, 2022.
Google Sheets API provides services of to read and write a Google spreadsheet document.
This tutorial is for reading data from Google sheets and displaying them in the UI with JavaScript. Only JavaScript is used without any plugins or dependencies.
Steps to access Google Sheets
It requires the following steps to achieve this.
- Get OAuth Credentials and API keys and configure them into an application.
- Authenticate and Authorise the app to allow accessing Google sheets.
- Read spreadsheet data and store it in an array.
- Parse response data and display them on the UI.
Steps 1 and 2 are common for all Google JavaScript API services. When uploading files to Google Drive via JavaScript API, we have seen it.
We have also seen how to upload to Google Drive using PHP. It doesn’t need API Key. Instead, it does token-based authentication to get API access.
Step 1: Get OAuth Credentials and API keys and configure them into an application
In this step, it needs to create a developer’s web client app to get the client id and the API keys. For that, it requires the following setting should be enabled with the developer’s dashboard.
- Login to the Google Developers console and create a web client.
- Enable Google Sheets API from the gallery of Google APIs.
- Configure OAuth content screen to set the app details
- Click OAuth credentials and get the app client id and secret key.
- Set the scope on which the program going to access the spreadsheet.
- Get the API key to authenticate and authorize the app to access the Google Spreadsheet API service.
Note: The secret key will be used for server-side implementation, but not in this JavaScript example.
Required scope to access the spreadsheet data
The following scopes should be selected to read the Google Spreadsheets via a program.
- …auth/spreadsheets – to read, edit, create and delete Spreadsheets.
- …auth/spreadsheets.readonly – to read Spreadsheets.
- …auth/drive – to read, edit, create and delete Drive files.
- …auth/drive.readonly – to read Drive files
- …auth/drive.file – to read, edit, create and delete a specific Drive files belongs to the app gets authorized.
Step 2: Authenticate and Authorise the app to allow accessing Google sheets
Authorization is the process of the client signing into the Google API to access its services.
On clicking an “Authorize” button, it calls authorizeGoogleAccess() function created for this example.
This function shows a content screen for the end user to allow access. Then, it receives the access token in a callback handler defined in this function.
Step 3: Read spreadsheet data and store it in an array
Once access is permitted, the callback will invoke the script to access an existing Google spreadsheet.
The listMajors() function specifies a particular spreadsheet id to be accessed. This function uses JavaScript gapi instance to get the spreadsheet data.
Step 4: Parse response data and display them on the UI
After getting the response data from the API endpoint, this script parses the resultant object array.
It prepares the output HTML with the spreadsheet data and displays them to the target element.
If anything strange with the response, it shows the “No records found” message in the browser.
A complete code: Accessing Google Spreadsheets via JavaScript
The following script contains the HTML to show either “Authorize” or the two “Refresh” and “Signout” buttons. Those buttons’ display mode is based on the state of authorization to access the Google Spreadsheet API.
The example code includes the JavaScript library to make use of the required Google API services.
The JavaScript has the configuration to pin the API key and OAuth client id in a right place. This configuration is used to proceed with the steps 2, 3 and 4 we have seen above.
<!DOCTYPE html>
<html>
<head>
<title>Google Sheets JavaScript API Spreadsheet Tutorial</title>
<link rel='stylesheet' href='style.css' type='text/css' />
<link rel='stylesheet' href='form.css' type='text/css' />
</head>
<body>
<div class="phppot-container">
<h1>Google Sheets JavaScript API Spreadsheet Tutorial</h1>
<p>This tutorial is to help you learn on how to read Google
Sheets (spreadsheet) using JavaScript Google API.</p>
<button id="authorize_btn" onclick="authorizeGoogleAccess()">Authorize
Google Sheets Access</button>
<button id="signout_btn" onclick="signoutGoogle()">Sign
Out</button>
<pre id="content"></pre>
</div>
<script async defer src="https://apis.google.com/js/api.js"
onload="gapiLoaded()"></script>
<script async defer src="https://accounts.google.com/gsi/client"
onload="gisLoaded()"></script>
</body>
</html>
// You should set your Google client ID and Google API key
const GOOGLE_CLIENT_ID = '';
const GOOGLE_API_KEY = '';
//
const DISCOVERY_DOC = 'https://sheets.googleapis.com/$discovery/rest?version=v4';
// Authorization scope should be declared for spreadsheet handing
// multiple scope can he included separated by space
const SCOPES = 'https://www.googleapis.com/auth/spreadsheets.readonly';
let tokenClient;
let gapiInited = false;
let gisInited = false;
document.getElementById('authorize_btn').style.visibility = 'hidden';
document.getElementById('signout_btn').style.visibility = 'hidden';
/**
* Callback after api.js is loaded.
*/
function gapiLoaded() {
gapi.load('client', intializeGapiClient);
}
/**
* Callback after the Google API client is loaded. Loads the
* discovery doc to initialize the API.
*/
async function intializeGapiClient() {
await gapi.client.init({
apiKey: GOOGLE_API_KEY,
discoveryDocs: [DISCOVERY_DOC],
});
gapiInited = true;
maybeEnableButtons();
}
/**
* Callback after Google Identity Services are loaded.
*/
function gisLoaded() {
tokenClient = google.accounts.oauth2.initTokenClient({
client_id: GOOGLE_CLIENT_ID,
scope: SCOPES,
callback: '', // defined later
});
gisInited = true;
maybeEnableButtons();
}
/**
* Enables user interaction after all libraries are loaded.
*/
function maybeEnableButtons() {
if (gapiInited && gisInited) {
document.getElementById('authorize_btn').style.visibility = 'visible';
}
}
/**
* Sign in the user upon button click.
*/
function authorizeGoogleAccess() {
tokenClient.callback = async (resp) => {
if (resp.error !== undefined) {
throw (resp);
}
document.getElementById('signout_btn').style.visibility = 'visible';
document.getElementById('authorize_btn').innerText = 'Refresh';
await listMajors();
};
if (gapi.client.getToken() === null) {
// Prompt the user to select a Google Account and ask for consent to share their data
// when establishing a new session.
tokenClient.requestAccessToken({ prompt: 'consent' });
} else {
// Skip display of account chooser and consent dialog for an existing session.
tokenClient.requestAccessToken({ prompt: '' });
}
}
/**
* Sign out the user upon button click.
*/
function signoutGoogle() {
const token = gapi.client.getToken();
if (token !== null) {
google.accounts.oauth2.revoke(token.access_token);
gapi.client.setToken('');
document.getElementById('content').innerText = '';
document.getElementById('authorize_btn').innerText = 'Authorize';
document.getElementById('signout_btn').style.visibility = 'hidden';
}
}
/**
* Print the names and majors of students in a sample spreadsheet:
* https://docs.google.com/spreadsheets/d/1aSSi9jk2gBEHXOZNg7AV7bJj0muFNyPLYwh2GXThvas/edit
*/
async function listMajors() {
let response;
try {
// Fetch first 10 files
response = await gapi.client.sheets.spreadsheets.values.get({
spreadsheetId: '',
range: 'Sheet1!A2:D',
});
} catch (err) {
document.getElementById('content').innerText = err.message;
return;
}
const range = response.result;
if (!range || !range.values || range.values.length == 0) {
document.getElementById('content').innerText = 'No values found.';
return;
}
const output = range.values.reduce(
(str, row) => `${str}${row[0]}, ${row[2]}\n`,
'Birds, Insects:\n');
document.getElementById('content').innerText = output;
}
Source and output of this example
The spreadsheet shown in this screenshot is the source of this program to access its data.
The JavaScript example reads the spreadsheet and displays the Birds and the Insects column data in the UI.
Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.
Hi, I'm Vincy. I help build websites, grow businesses, big and small. If that is what you want, contact me.
I'm currently available for freelance work.