Retrieving information from a public Google spreadsheet using client-side JavaScript

Question

Retrieving information from a public Google spreadsheet using client-side JavaScript

How can I retrieve cell values from a public Google spreadsheet?

GET https://sheets.googleapis.com/v4/spreadsheets/1vW01Y46DcpCC7aKLIUwV_W4RXLbeukVwF-G9AA7P7R0/values/A1A4?key=abcdef

The above request returns a 403 error.

I also included the Referrer in Postman :

Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.128 Safari/537.36

{
    "error": {
        "code": 403,
        "message": "Requests from referer Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.128 Safari/537.36 are blocked.",
        "status": "PERMISSION_DENIED",
        "details": [
            {
                "@type": "type.googleapis.com/google.rpc.ErrorInfo",
                "reason": "API_KEY_HTTP_REFERRER_BLOCKED",
                "domain": "googleapis.com",
                "metadata": {
                    "consumer": "projects/666",
                    "service": "sheets.googleapis.com"
                }
            }
        ]
    }
}

I am attempting to access data directly from a public sheet using client-side JavaScript.

No need for server round-trips. I vaguely remember this being possible around 10 years ago but I can't find the documentation now.

javascript google-sheets google-sheets-api

Answer 1

Answer №1

Gain access to the public spreadsheet through a JSON endpoint

var id = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
var gid = '1111111111111';
var url = 'https://docs.google.com/spreadsheets/d/'+id+'/gviz/tq?tqx=out:json&tq&gid='+gid;

Grab a portion of the data

data.substring(47).slice(0, -2)

Then, decode the JSON information

Shortcut link

https://docs.google.com/spreadsheets/d/1n-rjSYb63Z2jySS3-M0BQ78vu8DTPOjG-SZM4i8IxXI/gviz/tq?tqx=out:json&tq&gid=0

Sample using Google Apps Script

function getEndpointJson(){
  var id = '1n-rjSYb63Z2jySS3-M0BQ78vu8DTPOjG-SZM4i8IxXI';
  var gid = '0';
  var txt = UrlFetchApp.fetch(`https://docs.google.com/spreadsheets/d/${id}/gviz/tq?tqx=out:json&tq&gid=${gid}`).getContentText();
  var jsonString = txt.match(/(?<="table":).*(?=}\);)/g)[0]
  var json = JSON.parse(jsonString)
  var table = []
  var row = []
  json.cols.forEach(col => row.push(col.label))
  table.push(row)
  json.rows.forEach(r => {
    var row = []
    r.c.forEach(cel => {
        try{var value = cel.f ? cel.f : cel.v}
        catch(e){var value = ''}
        row.push(value)
      }
    )
    table.push(row)
    }
  )
  return (table)
}

Demonstration on an HTML page

For example, on an HTML page hosted externally

<html>
<title>Google Sheets JSON Endpoint V4</title>
<author>Mike Steelson</author>
<style>
table {border-collapse: collapse;}
th,td{border: 1px solid black;}
</style>
<body>
<div id="json">json here</div>
<script>
var id = '1n-rjSYb63Z2jySS3-M0BQ78vu8DTPOjG-SZM4i8IxXI';
var gid = '0';
var url = 'https://docs.google.com/spreadsheets/d/'+id+'/gviz/tq?tqx=out:json&tq&gid='+gid;
fetch(url)
  .then(response => response.text())
  .then(data => document.getElementById("json").innerHTML=myItems(data.substring(47).slice(0, -2))  
  );
function myItems(jsonString){
  var json = JSON.parse(jsonString);
  var table = '<table><tr>'
  json.table.cols.forEach(col => table += '<th>' + col.label + '</th>')
  table += '</tr>'
  json.table.rows.forEach(row => {
    table += '<tr>'
    row.c.forEach(cell => {
        try{var value = cell.f ? cell.f : cell.v}
        catch(e){var value = ''}
        table += '<td>' + value + '</td>'
      }
    )
    table += '</tr>'
    }
  )
  table += '</table>'
  return table
}           
</script>
</body></html>

The sheet ID provided is incorrect.

Answer 2

Gain access to the public spreadsheet through a JSON endpoint

var id = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
var gid = '1111111111111';
var url = 'https://docs.google.com/spreadsheets/d/'+id+'/gviz/tq?tqx=out:json&tq&gid='+gid;

Grab a portion of the data

data.substring(47).slice(0, -2)

Then, decode the JSON information

Shortcut link

https://docs.google.com/spreadsheets/d/1n-rjSYb63Z2jySS3-M0BQ78vu8DTPOjG-SZM4i8IxXI/gviz/tq?tqx=out:json&tq&gid=0

Sample using Google Apps Script

function getEndpointJson(){
  var id = '1n-rjSYb63Z2jySS3-M0BQ78vu8DTPOjG-SZM4i8IxXI';
  var gid = '0';
  var txt = UrlFetchApp.fetch(`https://docs.google.com/spreadsheets/d/${id}/gviz/tq?tqx=out:json&tq&gid=${gid}`).getContentText();
  var jsonString = txt.match(/(?<="table":).*(?=}\);)/g)[0]
  var json = JSON.parse(jsonString)
  var table = []
  var row = []
  json.cols.forEach(col => row.push(col.label))
  table.push(row)
  json.rows.forEach(r => {
    var row = []
    r.c.forEach(cel => {
        try{var value = cel.f ? cel.f : cel.v}
        catch(e){var value = ''}
        row.push(value)
      }
    )
    table.push(row)
    }
  )
  return (table)
}

Demonstration on an HTML page

For example, on an HTML page hosted externally

<html>
<title>Google Sheets JSON Endpoint V4</title>
<author>Mike Steelson</author>
<style>
table {border-collapse: collapse;}
th,td{border: 1px solid black;}
</style>
<body>
<div id="json">json here</div>
<script>
var id = '1n-rjSYb63Z2jySS3-M0BQ78vu8DTPOjG-SZM4i8IxXI';
var gid = '0';
var url = 'https://docs.google.com/spreadsheets/d/'+id+'/gviz/tq?tqx=out:json&tq&gid='+gid;
fetch(url)
  .then(response => response.text())
  .then(data => document.getElementById("json").innerHTML=myItems(data.substring(47).slice(0, -2))  
  );
function myItems(jsonString){
  var json = JSON.parse(jsonString);
  var table = '<table><tr>'
  json.table.cols.forEach(col => table += '<th>' + col.label + '</th>')
  table += '</tr>'
  json.table.rows.forEach(row => {
    table += '<tr>'
    row.c.forEach(cell => {
        try{var value = cell.f ? cell.f : cell.v}
        catch(e){var value = ''}
        table += '<td>' + value + '</td>'
      }
    )
    table += '</tr>'
    }
  )
  table += '</table>'
  return table
}           
</script>
</body></html>

The sheet ID provided is incorrect.

Answer 3

Answer №2

After conducting a quick review, it appears that there are readily available JS libraries that allow access to GSheets data, however, Google mandates the use of API keys:

When accessing public data through the Google Sheets API, an identifier such as an API key or access token is required.

One example of such a library is:

gsheets - A tool to retrieve public Google Sheets information in plain JavaScript/JSON format.

Answer 4

After conducting a quick review, it appears that there are readily available JS libraries that allow access to GSheets data, however, Google mandates the use of API keys:

When accessing public data through the Google Sheets API, an identifier such as an API key or access token is required.

One example of such a library is:

gsheets - A tool to retrieve public Google Sheets information in plain JavaScript/JSON format.

Answer 5

Answer №3

The solution is to eliminate the limitations in Google Cloud Console

https://i.sstatic.net/dmYO2.png

Answer 6

The solution is to eliminate the limitations in Google Cloud Console

https://i.sstatic.net/dmYO2.png

Answer 7

Answer №4

Here's a helpful library you may want to consider.

Check out this library for public Google Sheets parsing

You can utilize it in the following manner:

const PublicGoogleSheetsParser = require('public-google-sheets-parser')

const spreadsheetId = '10WDbAPAY7Xl5DT36VuMheTPTTpqx9x0C5sDCnh4BGps'

// 1. Instantiating the parser with the spreadsheetId:
const parser = new PublicGoogleSheetsParser(spreadsheetId)
parser.parse().then((items) => {
  // items will contain data objects like: [{"a":1,"b":2,"c":3},{"a":4,"b":5,"c":6},{"a":7,"b":8,"c":9}]
})

Answer 8

Here's a helpful library you may want to consider.

Check out this library for public Google Sheets parsing

You can utilize it in the following manner:

const PublicGoogleSheetsParser = require('public-google-sheets-parser')

const spreadsheetId = '10WDbAPAY7Xl5DT36VuMheTPTTpqx9x0C5sDCnh4BGps'

// 1. Instantiating the parser with the spreadsheetId:
const parser = new PublicGoogleSheetsParser(spreadsheetId)
parser.parse().then((items) => {
  // items will contain data objects like: [{"a":1,"b":2,"c":3},{"a":4,"b":5,"c":6},{"a":7,"b":8,"c":9}]
})

Retrieving information from a public Google spreadsheet using client-side JavaScript

Answer №1

Shortcut link

Sample using Google Apps Script

Demonstration on an HTML page

Answer №2

Answer №3

Answer №4

Similar questions

Locate the index position of an element in one array based on a corresponding element in a

Pass a JavaScript variable to a PHP script using AJAX when a button is clicked, with a dynamically set href attribute

The async/await syntax can be finicky at times and may not always

Steps to sending a parameter to an AngularJS $http success callback

Allow editing for only a specific part of a text box

What is the best way to execute JavaScript on the main MVC page when an AJAX request in a partial view has finished?

What is the best way to populate a remote html page in real-time according to user input?

Steps for embedding JavaScript code within HTML tags using a JavaScript file

Is there a way for me to connect the ajax data to Vue components?

I am in search of a specialized 3D camera with a unique effect

Ways to verify if JSON response is null using jquery

Angular debounce on checkboxes allows you to prevent multiple rapid changes from

Utilizing HTML5 canvas to extract pixel data from an image loaded from an external source

Converting a React element using JSON.stringify results in transforming it into a JavaScript object

Identifying line breaks caused by browsers or CSS forced line breaks

Determine whether any property within the object is currently null

Toggle visibility with jQuery's Show & Hide feature

In AngularJS, showcasing five elements at a time from an array containing 'n' items before looping back to the beginning

Jquery: Pressing Enter will cause the input field to lose

AngularJS Large file size