Accessing a public google sheet's data directly from client-side JavaScript
You can access the public spreadsheet by 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;
Take a slice
data.substring(47).slice(0, -2)
and parse the json
direct link
https://docs.google.com/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.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(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.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(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.
Get data from Google Sheets without Sheets API
I believe your goal as follows.
- You want to retrieve the values from Google Spreadsheet without using Sheets API.
- I understood that in this case, you want to retrieve the values without using the access token and API key.
- You want to achieve this using Javascript.
Issue and workaround:
When Google Spreadsheet is used with Sheets API, the access token and the API key are required to be used. When you want to retrieve the values from Google Spreadsheet without using the access token and the API key, in this answer, I would like to propose the following workaround using Web Apps created by Google Apps Script. The flow of this workaround is as follows.
- Request to Web Apps from Javascript.
- At Web Apps, the values are returned from Google Spreadsheet.
- In this workaround, the Google Spreadsheet is not required to be shared publicly.
- Retrieve the values from Web Apps at Javascript.
Usage:
Please do the following flow.
1. Create new project of Google Apps Script.
Sample script of Web Apps is a Google Apps Script. So please create a project of Google Apps Script.
If you want to directly create it, please access to https://script.new/. In this case, if you are not logged in Google, the log in screen is opened. So please log in to Google. By this, the script editor of Google Apps Script is opened.
2. Prepare script.
Please copy and paste the following script (Google Apps Script) to the script editor. This script is for the Web Apps.
function doGet(e) {
const id = e.parameter.spreadsheetId;
const sheetName = e.parameter.sheetName;
const sheet = SpreadsheetApp.openById(id).getSheetByName(sheetName);
const values = sheet.getDataRange().getValues();
return ContentService.createTextOutput(JSON.stringify({values: values})).setMimeType(ContentService.MimeType.JSON);
}
3. Deploy Web Apps.
- On the script editor, Open a dialog box by "Publish" -> "Deploy as web app".
- Select "Me" for "Execute the app as:".
- By this, the script is run as the owner.
- Select "Anyone, even anonymous" for "Who has access to the app:".
- In this case, no access token is required to be request. I think that I recommend this setting for your goal.
- Of course, you can also use the access token. At that time, please set this to "Anyone". And please include the scope of
https://www.googleapis.com/auth/drive.readonly
andhttps://www.googleapis.com/auth/drive
to the access token. These scopes are required to access to Web Apps.
- Click "Deploy" button as new "Project version".
- Automatically open a dialog box of "Authorization required".
- Click "Review Permissions".
- Select own account.
- Click "Advanced" at "This app isn't verified".
- Click "Go to ### project name ###(unsafe)"
- Click "Allow" button.
- Click "OK".
- Copy the URL of Web Apps. It's like
https://script.google.com/macros/s/###/exec
.- When you modified the Google Apps Script, please redeploy as new version. By this, the modified script is reflected to Web Apps. Please be careful this.
4. Run the function using Web Apps.
You can retrieve the values from Google Spreadsheet using the following script.
const baseUrl = "https://script.google.com/macros/s/###/exec"; // Please set your Web Apps URL.
const para = {
spreadsheetId: "###", // Please set your Google Spreadsheet ID.
sheetName: "Sheet1" // Please set the sheet name you want to retrieve the values.
};
const q = new URLSearchParams(para);
const url = baseUrl + "?" + q;
fetch(url)
.then(res => res.json())
.then(res => {
const values = res.values;
console.log(values);
});
If you want to test the above Web Apps using a curl command, you can also use the following curl command.
$ curl -L "https://script.google.com/macros/s/###/exec?spreadsheetId=###&sheetName=Sheet1"
Note:
- When you modified the script of Web Apps, please redeploy the Web Apps as new version. By this, the latest script is reflected to the Web Apps. Please be careful this.
- In my environment, recently, I have confirmed that when the values are retrieved from the publicly shared Spreadsheet and the published Spreadsheet to Web using Javascript, the error related to CORS occurs. I'm worry that such issue might also occur at your environment. So in order to achieve your goal, I proposed to use Web Apps. In this case, I have already confirmed that no error occurs.
References:
- Web Apps
- Taking advantage of Web Apps with Google Apps Script
How get data directly from a google spreadsheet?
Is possible, some example can be the code of James Moore http://blog.restphone.com/2011/05/very-simple-google-spreadsheet-code.html
Remember you need to add manually in spreadsheet "File->Publish to the Web"
package com.banshee;
import java.io.IOException;
import java.net.URL;
import com.google.gdata.client.spreadsheet.SpreadsheetService;
import com.google.gdata.data.spreadsheet.CustomElementCollection;
import com.google.gdata.data.spreadsheet.ListEntry;
import com.google.gdata.data.spreadsheet.ListFeed;
import com.google.gdata.util.ServiceException;
public class SpreadsheetSucker {
public static void main(String[] args) {
SpreadsheetService service = new SpreadsheetService("com.banshee");
try {
// Notice that the url ends
// with default/public/values.
// That wasn't obvious (at least to me)
// from the documentation.
String urlString = "https://spreadsheets.google.com/feeds/list/0AsaDhyyXNaFSdDJ2VUxtVGVWN1Yza1loU1RPVVU3OFE/default/public/values";
// turn the string into a URL
URL url = new URL(urlString);
// You could substitute a cell feed here in place of
// the list feed
ListFeed feed = service.getFeed(url, ListFeed.class);
for (ListEntry entry : feed.getEntries()) {
CustomElementCollection elements = entry.getCustomElements();
String name = elements.getValue("name");
System.out.println(name);
String number = elements.getValue("Number");
System.out.println(number);
}
} catch (IOException e) {
e.printStackTrace();
} catch (ServiceException e) {
e.printStackTrace();
}
}
}
How to read data using the spreadsheets API without using oauth2
If you try to access a Spreadsheet that is not public, you will have to use OAuth 2.0. Since it is not a public resource, you have to use the credentials of an account that has access to this resource.
In the example you provided, they are accessing a public resource, so the API key is enough. That's not your case.
Reference:
- Using OAuth 2.0 to Access Google APIs
Related Topics
How to Extract a String from Double Quotes
How to Read Xml File from Url Using PHP
How to Find Annotations in a PHP5 Object
How to Retrieve the Visitor's Isp Through PHP
Mysql_Connect VS MySQL_Pconnect
Prevent Session Expired in PHP Session for Inactive User
Unpacking an Array of Arguments in PHP
How to Get the Value from Object(Stdclass)
Verify Valid Date Using PHP's Datetime Class
Unexpected 'Use' (T_Use) When Trying to Use Composer
How to Fix Warning from Date() in PHP"
Laravel Polymorphic Relations Has Many Through
Why Is Type Hinting Necessary in PHP
Accessing a Variable Defined in a Parent Function
How to Eliminate PHP5 Strict Standards Errors
PHP Pdo. Error Number '00000' When Query Is Correct