Reading Google Sheets Data using JavaScript and Google Sheets API Tutorial

Submitted by oretnom23 on Thursday, November 24, 2022 - 14:31.

In this tutorial, we will tackle about Reading Google Spreadsheets Data using JavaScript and Google Sheets API. The tutorial aims to provide the IT/CS students and new programmers a reference for learning to integrate their web applications with Google APIs specifically using Google Sheets and JavaScript. Here, a step-by-step tutorial with snippets is provided and a sample web application source code file that demonstrates the said integration is also provided and free to download.

What is Google Sheets API?

The What is Google Sheets API is a RESTful API implemented by Google to allow developers to integrate Google Sheets into their web application projects. Developers use this API to create, read, and modify spreadsheets using their applications.

How to use Google Sheet API?

Here are the following steps to setup Google Sheets API in Google to use it for your web applications

Browse the Google Developers Console page in your preferred browser.
Create a new project in Google Developers Console and fill in the required fields.
After the successful creation of the Project, select your created project and go to its Dashboard.
On the search box located at the top center of the screen, enter "Google Sheets API" find the Google Sheets API on the suggestion box and click it.
Enable the Google Sheets API
On the side navigation menu, click the APIs & Services menu and create an OAuth consent screen. Kindly fill in the required fields on the form like the images shown below.
On the Credentials page, create an API Key
On the Credentials page, create an OAuth Client ID. Kindly fill in the required fields on the form.

How to use Read Google Sheet Data using JavaScript and Google API?

In order to read the Google Sheet Data using Google API and JavaScript, we will use the generated Client ID and API Key.

Here are the simple snippets of reading Google Sheets data using JavaScript and Google Sheets API.

Interface

The below snippet is the script for the web application's main page. It contains the Authorization/Sign-in and Sign-out Button for reading the spreadsheets data.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Google Sheets API | JavaScript</title>
    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.2.0/css/all.min.css" integrity="sha512-xh6O/CkQoPOWDdYTDqeRdPCVd1SpvCA9XXcUnZS2FmJNp1coAFzvtCN9BmamE+4aHK8yyUHUSCcJHgXloTyT2A==" crossorigin="anonymous" referrerpolicy="no-referrer" />
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" integrity="sha384-Zenh87qX5JnK2Jl0vWa8Ck2rdkQ2Bzep5IDxbcnCeuOxjzrPF/et3URy9Bv1WTRi" crossorigin="anonymous">
    <script src="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.2.0/js/all.min.js" integrity="sha512-naukR7I+Nk6gp7p5TMA4ycgfxaZBJ7MO5iC3Fp6ySQyKFHOGfpkSZkYVWV5R7u7cfAicxanwYQ5D1e17EfJcMA==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
    <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" integrity="sha384-OERcA2EqjJCMA+/3y+gxIOqMEjwtxJY7qPCqsdltbNJuaOe923+mo//f6V8Qbsw3" crossorigin="anonymous"></script>
    <style>
        html, body{
            min-height:100%;
            width:100%;
        }
        .table-holder{
            width:100%;
            max-height:65vh;
            overflow:auto;
        }
        .table>thead{
            position:sticky;
            top:0;
            background:white;
            z-index: 1;
        }
    </style>
</head>
<body>
    <nav class="navbar navbar-expand-lg navbar-dark bg-primary bg-gradient">
        <div class="container">
            <a class="navbar-brand" href="./">Google Sheets API</a>
            <div>
                <a href="https://sourcecodester.com" class="text-light fw-bolder h6 text-decoration-none" target="_blank">SourceCodester</a>
            </div>
        </div>
    </nav>
    <div class="container-fluid px-5 my-3" id="SampleApp">
        <div class="col-lg-6 col-md-10 col-sm-12 mx-auto">
            <h3 class="text-center"><b>Reading Google Sheets using Google Sheets API and JavaScript</b></h3>
            <div class="d-flex w-100 justify-content-center">
                <hr class="w-50">
            </div>
            <div class="d-flex w-100 justify-content-between mb-3">
                <div class="col-lg-3 col-md-4 col-sm-6">
                    <button id="signin_btn" class="btn btn-sm btn-block w-100 btn-primary rounded-pill" type="button" onclick="GAuth()">Sign In</button>
                </div>
                <div class="col-lg-3 col-md-4 col-sm-6">
                    <button id="signout_btn" class="btn btn-sm btn-block w-100 btn-danger rounded-pill" type="button" onclick="GAuthSignout()">Sign Out</button>
                </div>
            </div>
            <div class="card rounded-0 shadow" id="dataSheetCard">
                <div class="card-header rounded-0">
                    <div class="d-flex w-100 justify-content-between">
                        <div class="col-auto flex-shrink-1 flex-grow-1">
                            <div class="card-title">Sample Data Sheet Data</div>
                        </div>
                        <div class="col-auto flex-shrink-1">
                            <button class="btn btn-primary btn-sm rounded-0" type="button" onclick="ReloadDataSheet()">Reload Data</button>
                        </div>
                    </div>
                </div>
                <div class="card-body rounded-0">
                    <div class="container-fluid">
                        <div class="table-holder">
                            <table class="table table-stripped table-bordered table-hover" id="contentTable"></table>
                        </div>
                        <div class="alert alert-danger" style="display:none" id="err_msg"></div>
                    </div>
                </div>
            </div>
        </div>
    </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="GSILoaded()"></script>
    <script src="app.js"></script>
</body>
</html>

Note: Please make sure that the following external scripts are loaded on the interface script.

<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="GSILoaded()"></script>

The app.js script that is loaded with the main page interface is the file that contains the JavaScript codes for integrating the Google Sheet with the application.

JavaScript

Here's the snippet that is written in the app.js. The following code contains the scripts for initiating the Google Authentication/Consent and Google API.

// App Credentials
const client_id = '{client-id}'; // Replace the vakue with your Google API Client ID
const api_key = '{api-key}'; // Replace the vakue with your Google API key
 
// Discovery doc URL for APIs used by the quickstart
const DISCOVERY_DOC = 'https://sheets.googleapis.com/$discovery/rest?version=v4';
 
// Authorization scopes required by the API; multiple scopes can be
// included, separated by spaces.
const SCOPES = 'https://www.googleapis.com/auth/spreadsheets.readonly';
 
let ClientToken;
let GAPI_init = false;
let GIS_init = false;
 
const dataSheetCard = document.getElementById('dataSheetCard')
const signin_btn = document.getElementById('signin_btn')
const signout_btn = document.getElementById('signout_btn')
 
signin_btn.style.display = 'none'
signout_btn.style.display = 'none'
dataSheetCard.style.display = 'none'
 
/**
* Callback after the API client is loaded. Loads the
* discovery doc to initialize the API.
*/
async function intializeGapiClient() {
    await gapi.client.init({
        apiKey: api_key,
        discoveryDocs: [DISCOVERY_DOC],
    });
    GAPI_init = true;
    enableButtons();
}
 
/**
    * Enables user interaction after all libraries are loaded.
    */
function enableButtons() {
    if (GAPI_init && GIS_init) {
        signin_btn.style.display = 'block'
    }
}
 
function GAPILoaded() {
    gapi.load('client', intializeGapiClient);
}
 
 
/**
    * Callback after Google Identity Services are loaded.
    */
function GSILoaded() {
    ClientToken = google.accounts.oauth2.initTokenClient({
        client_id: client_id,
        scope: SCOPES,
        callback: '', // defined later
    });
    GIS_init = true;
    enableButtons();
}
 
 
 
/**
    *  Sign in the user upon button click.
    */
function GAuth() {
    ClientToken.callback = async (resp) => {
        if (resp.error !== undefined) {
            throw (resp);
        }
        signout_btn.style.display = 'block';
        signin_btn.innerText = 'Refresh Authorization';
        dataSheetCard.style.display = 'block'
        // await listMajors();
        await displaySheetData();
    };
 
    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.
        ClientToken.requestAccessToken({ prompt: 'consent' });
    } else {
        // Skip display of account chooser and consent dialog for an existing session.
        ClientToken.requestAccessToken({ prompt: '' });
    }
}
 
 
/**
    * Display some content of spreedsheet
    * https://docs.google.com/spreadsheets/d/1E3674wpJdfrCS1uEu7sHu3ALHciDnsBFQlEdx-XdknU/edit
    */
async function displaySheetData() {
    let response;
    try {
        // Fetch first 10 files
        response = await gapi.client.sheets.spreadsheets.values.get({
            spreadsheetId: '1E3674wpJdfrCS1uEu7sHu3ALHciDnsBFQlEdx-XdknU',
            range: 'Employee Leave Credits!A1:E',
        });
    } catch (err) {
        console.log(err)
        document.getElementById('err_msg').innerText = err.message;
        document.getElementById('err_msg').style.display = 'block'
        return;
    }
    const range = response.result;
    if (!range || !range.values || range.values.length == 0) {
        document.getElementById('err_msg').innerText = 'No values found.';
        document.getElementById('err_msg').style.display = 'block'
        return;
    }
 
    document.getElementById('contentTable').innerHTML = '';
    var tr = document.createElement('tr')
    /**
        * Table Header
        */
    var thead = document.createElement('thead')
 
    if (!!range.values[0]) {
        var head_tr = tr.cloneNode(true)
        Object.keys(range.values[0]).map(k => {
            var column = range.values[0][k]
            head_tr.innerHTML += `<th>${column}</th>`;
        })
        thead.appendChild(head_tr)
        document.getElementById('contentTable').appendChild(thead)
    }
 
    /**
        * Table body
        */
    var tbody = document.createElement('tbody')
 
    Object.keys(range.values).map(k => {
        if (k != 0) {
            var body_tr = tr.cloneNode(true)
            var rows = range.values[k]
            rows.map(column => {
                body_tr.innerHTML += `<td>${column}</td>`;
            })
            tbody.appendChild(body_tr)
        }
    })
    document.getElementById('contentTable').appendChild(tbody)
 
}
 
 
/**
    * Reload Data Sheed
    */
 
function ReloadDataSheet(){
    displaySheetData();
}
 
 
/**
    *  Sign out the user upon button click.
    */
function GAuthSignout() {
    token = gapi.client.getToken();
    if (token !== null) {
        google.accounts.oauth2.revoke(token.access_token);
        gapi.client.setToken('');
        signin_btn.innerText = 'Sign In';
        signout_btn.style.display = 'none';
        dataSheetCard.style.display = 'none'
    }
}

Make sure to change the client_id and api_key constants value with your created Google API credentials.

Snapshots

Here are the snapshots of the result of the snippets given above.

Getting the user's consent

Displaying the Google Sheets Data into HTML Table

The Source of Google Sheet Data

That's the end of this tutorial. I have also provided the source code file that I created for this tutorial. You can download it by clicking the Download Button below this article.

I hope this Reading Google Sheets Data using JavaScript and Google Sheets API Tutorial helps you with what you are looking for and that you'll find this useful for your current and future projects.

Explore more on this website for more Tutorials and Free Source Codes.

Happy Coding :)

Add new comment

600 views