Javascript

Fetch Google Spread Sheet Data Using JavaScript

How to fetch the data from the google sheets using the google visualization API and using javascript.

Google sheets help us easily create spreadsheet documents using our google account. It also provides google visualization API and helps us retrieve the data.

In this article, we will learn how to fetch google spreadsheet data using plain javascript.

Table of Contents

Create a Google doc spread sheet

Login to google docs and create a new spreadsheet. Name the spreadsheet as user-data and add the content as shown below.

google sread sheet

Click on the Share button to get the link to the sheet.

google sheet share link

Click on the Copy Link button to copy the link. We can also change the permission by clicking on the Change to anyone with the link option as shown below.

google sheet java script retrieve

The copied link looks similar to the one given below. This link contains the unique string after /d/ which is a unique identifier of the google sheet.

https://docs.google.com/spreadsheets/d/1dzx8QSiBQFcUOQTPa_uI64OCGQqESWtrq6EwqOVngZw/edit?usp=sharing

Retrieve data from google sheets

We will create a simple HTML table to display the data retrieved from the Google Sheets.

We will use javascript to fetch the data from google sheets, process the data, and display it as an HTML table.

Create a HTML page

Create an HTML page with the name index.html and add the below content.

<html>
<head>
    <style>
        table {
          font-family: Arial, Helvetica, sans-serif;
          border-collapse: collapse;
          width: 100%;
        }
        
        table td, table th {
          border: 1px solid #ddd;
          padding: 8px;
        }
        table tr:nth-child(even){background-color: #f2f2f2;}
        table tr:hover {background-color: #ddd;}
        
        table th {
          padding-top: 12px;
          padding-bottom: 12px;
          text-align: left;
          background-color: #ff7b00;
          color: white;
        }
        </style>
</head>

<body>
    <h1 style="text-align: center;">User Data</h1>
    <table class="output"></table>
    <script src="spreadsheet.js"></script>
</body>

</html>

The HTML page contains some CSS styling for the table element. The CSS styling gives our page a nice look.

We also have an <h1> HTML element, the <table> element inside the body of the page.

We are also importing the javascript file with the name spreadsheet.js. We will create this in the next section.

Fetching the google sheet data using javascript

Create a javascript file with the name spreadsheet.js in the current directory.

Add the required google sheets related properties as shown below.

const sheetId = '1dzx8QSiBQFcUOQTPa_uI64OCGQqESWtrq6EwqOVngZw';
const base = `https://docs.google.com/spreadsheets/d/${sheetId}/gviz/tq?`;
const sheetName = 'user-data';
const query = encodeURIComponent('Select *')
const url = `${base}&sheet=${sheetName}&tq=${query}`

Here, the sheetId variable is the unique string that we can extract from the google sheets link. We are dynamically creating the URL to make it easier to change the sheet id.

The base variable forms the base URI of the google sheets resource. We are passing the sheetId dynamically and using the Google Visualisation API(gviz) to query to fetch the data.

Advertisements

The sheetName variable specifies the spreadsheet name, and the query variable holds the google visualization API query. we can fetch the complete sheet data using the query Select *

Finally, we are constructing the URL that fetches the google sheets data.

Add the data array variable that holds the retrieved data. Also, create a ‘DOM content Loaded’ listener and specify the function to be executed.

const data = []
document.addEventListener('DOMContentLoaded', init)

Add the init() function that loads the google sheets data on page load.

function init() {
    fetch(url)
        .then(res => res.text())
        .then(rep => {
            //Remove additional text and extract only JSON:
            const jsonData = JSON.parse(rep.substring(47).slice(0, -2));
            console.log(jsonData)

            const colz = [];
            const tr = document.createElement('tr');
            //Extract column labels
            jsonData.table.cols.forEach((heading) => {
                if (heading.label) {
                    let column = heading.label;
                    colz.push(column);
                    const th = document.createElement('th');
                    th.innerText = column;
                    tr.appendChild(th);
                }
            })
            output.appendChild(tr);

            //extract row data:
            jsonData.table.rows.forEach((rowData) => {
                const row = {};
                colz.forEach((ele, ind) => {
                    row[ele] = (rowData.c[ind] != null) ? rowData.c[ind].v : '';
                })
                data.push(row);
            })
            processRows(data);
        })
}

We are using the fetch() function of javascript to invoke the google visualization API.

The callback method reads the response text with looks similar to the below.

Then, we remove the characters at the beginning and end of the response text with the javascript functions substring() and slice().

const jsonData = JSON.parse(rep.substring(47).slice(0, -2));

We then created a colz array, that holds the available column headers. Also, we are dynamically creating the HTML table headers. This results in the below output.

We are also extracting the row data and then calling the processRows() function to append the table rows.

Create a processRows() function as shown below.

function processRows(json) {
    json.forEach((row) => {

        const tr = document.createElement('tr');
        const keys = Object.keys(row);
    
        keys.forEach((key) => {
            const td = document.createElement('td');
            td.textContent = row[key];
            tr.appendChild(td);
        })
        output.appendChild(tr);
    })
}

Here, for each row object from google sheets response JSON, a new <tr> element is created. Then we add the <td> element with the response row value.

The complete javascript code is given below.

const sheetId = '1dzx8QSiBQFcUOQTPa_uI64OCGQqESWtrq6EwqOVngZw';
const base = `https://docs.google.com/spreadsheets/d/${sheetId}/gviz/tq?`;
const sheetName = 'user-data';
const query = encodeURIComponent('Select *')
const url = `${base}&sheet=${sheetName}&tq=${query}`

const data = []
document.addEventListener('DOMContentLoaded', init)

const output = document.querySelector('.output')

function init() {
    fetch(url)
        .then(res => res.text())
        .then(rep => {
            //Remove additional text and extract only JSON:
            const jsonData = JSON.parse(rep.substring(47).slice(0, -2));
            console.log(rep)

            const colz = [];
            const tr = document.createElement('tr');
            //Extract column labels
            jsonData.table.cols.forEach((heading) => {
                if (heading.label) {
                    let column = heading.label;
                    colz.push(column);
                    const th = document.createElement('th');
                    th.innerText = column;
                    tr.appendChild(th);
                }
            })
            output.appendChild(tr);

            //extract row data:
            jsonData.table.rows.forEach((rowData) => {
                const row = {};
                colz.forEach((ele, ind) => {
                    row[ele] = (rowData.c[ind] != null) ? rowData.c[ind].v : '';
                })
                data.push(row);
            })
            processRows(data);
        })
}
 
function processRows(json) {
    json.forEach((row) => {

        const tr = document.createElement('tr');
        const keys = Object.keys(row);
    
        keys.forEach((key) => {
            const td = document.createElement('td');
            td.textContent = row[key];
            tr.appendChild(td);
        })
        output.appendChild(tr);
    })
}

Open the index.html HTML file on a browser. We should be able to retrieve and display the data as shown below.

Conclusion

In this article, we learned how easily we can fetch the Google sheets data with the help of plain java script.

Leave a Reply