admin管理员组文章数量:1289986
Is there a way to get cell values of a public google spread sheet ?
GET
returns 403.
I also sent 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/google.rpc.ErrorInfo",
"reason": "API_KEY_HTTP_REFERRER_BLOCKED",
"domain": "googleapis",
"metadata": {
"consumer": "projects/666",
"service": "sheets.googleapis"
}
}
]
}
}
I am trying to access a public sheet's data directly from client-side JavaScript.
No round-trips to the server. I remember this was possible some 10 years ago but am unable to locate the docs.
Is there a way to get cell values of a public google spread sheet ?
GET https://sheets.googleapis./v4/spreadsheets/1vW01Y46DcpCC7aKLIUwV_W4RXLbeukVwF-G9AA7P7R0/values/A1A4?key=abcdef
returns 403.
I also sent 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./google.rpc.ErrorInfo",
"reason": "API_KEY_HTTP_REFERRER_BLOCKED",
"domain": "googleapis.",
"metadata": {
"consumer": "projects/666",
"service": "sheets.googleapis."
}
}
]
}
}
I am trying to access a public sheet's data directly from client-side JavaScript.
No round-trips to the server. I remember this was possible some 10 years ago but am unable to locate the docs.
Share Improve this question edited Jan 29, 2022 at 4:33 anjanesh asked Jan 29, 2022 at 4:10 anjaneshanjanesh 4,2619 gold badges52 silver badges74 bronze badges 2- The sheet ID you have provided is not available. – Mike Steelson Commented Jan 29, 2022 at 9:05
- I deleted after I got it working with JavaScript key and sheetID. – anjanesh Commented Jan 29, 2022 at 18:49
4 Answers
Reset to default 7You can access the public spreadsheet by json endpoint
var id = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
var gid = '1111111111111';
var url = 'https://docs.google./spreadsheets/d/'+id+'/gviz/tq?tqx=out:json&tq&gid='+gid;
Take a slice
data.substring(47).slice(0, -2)
and parse the json
direct link
https://docs.google./spreadsheets/d/1n-rjSYb63Z2jySS3-M0BQ78vu8DTPOjG-SZM4i8IxXI/gviz/tq?tqx=out:json&tq&gid=0
example by gas
function getEndpointJson(){
var id = '1n-rjSYb63Z2jySS3-M0BQ78vu8DTPOjG-SZM4i8IxXI';
var gid = '0';
var txt = UrlFetchApp.fetch(`https://docs.google./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(colonne => row.push(colonne.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)
}
example by html page
For instance on html page (you have to store it in outside server)
<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./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(colonne => table += '<th>' + colonne.label + '</th>')
table += '</tr>'
json.table.rows.forEach(ligne => {
table += '<tr>'
ligne.c.forEach(cellule => {
try{var valeur = cellule.f ? cellule.f : cellule.v}
catch(e){var valeur = ''}
table += '<td>' + valeur + '</td>'
}
)
table += '</tr>'
}
)
table += '</table>'
return table
}
</script>
</body></html>
The sheet ID you have provided is wrong.
Consider using this library.
https://github./fureweb-/public-google-sheets-parser
You can use it like this way:
const PublicGoogleSheetsParser = require('public-google-sheets-parser')
const spreadsheetId = '10WDbAPAY7Xl5DT36VuMheTPTTpqx9x0C5sDCnh4BGps'
// 1. You can pass spreadsheetId when instantiating the parser:
const parser = new PublicGoogleSheetsParser(spreadsheetId)
parser.parse().then((items) => {
// items should be [{"a":1,"b":2,"c":3},{"a":4,"b":5,"c":6},{"a":7,"b":8,"c":9}]
})
Based on some brief research, there are available JS libraries that let you access GSheets data, but Google requires an API key:
Requests to the Google Sheets API for public data must be acpanied by an identifier, which can be an API key or an access token.
Here's an example library:
gsheets - Get public Google Sheets as plain JavaScript/JSON.
Answer is to remove the restrictions in Google Cloud Console
本文标签： Accessing a public google sheet39s data directly from clientside JavaScriptStack Overflow
版权声明：本文标题：Accessing a public google sheet's data directly from client-side JavaScript - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1741441961a2378973.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论