How to Access Google Sheet Spreadsheets Only with JavaScript

How can I access Google Sheet spreadsheets only with Javascript?

I have created a simple javascript library that retrieves google spreadsheet data (if they are published) via the JSON api:

https://github.com/mikeymckay/google-spreadsheet-javascript

You can see it in action here:

http://mikeymckay.github.com/google-spreadsheet-javascript/sample.html

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.

How can I use data from a Google Sheet on a website without needing the user to be logged in?

So, your main goal is Use data from a Google Sheet on a website without needing the user to be logged in.

An easy approach to this problem is to deploy a web application within Apps Script that works as a kind of REST API.

The steps are as follows:

Create a function that collects the data from the Spreadsheets. I am using this Google Sheets as mock data.

`RestAPI.gs`

const collectData = () => {
  const ss_id = SPREADSHEET_ID
  const ss = SpreadsheetApp.openById(ss_id).getSheetByName('Class Data')
  const rangeData = ss.getRange('A2:F31').getValues()
  return rangeData
}

Deploy a Web App that serves that data as a JSON. I setted the parameter Who has access to Anyone (copy and save the deployment URL)

`RestAPI.gs`

const doGet = () => {
  const response =  ContentService.createTextOutput()
  response.setContent(JSON.stringify({"data":collectData()}))
  response.setMimeType(ContentService.MimeType.JSON)
  return response
}

Consume this "REST API" within your application. In my case I will create a new Google Apps Script for this purpose.

`Web.gs`

const doGet = () => HtmlService.createTemplateFromFile('index').evaluate()

const URL_TO_FETCH = URL_REST_API

const fetchData = () => UrlFetchApp.fetch(URL_TO_FETCH).getContentText()

`index.html`

<h1>RESULT</h1>
<div id="result"></div>
<script>
   const onSuccess = data => {
     const result = document.getElementById('result')
     const userArr = JSON.parse(data).data
     userArr.forEach(user=>{
       result.innerHTML += `<div>${user.join(" ")}</div>`
     })
   }
   google.script.run
     .withSuccessHandler(onSuccess)
     .fetchData()
</script>

This way you have a method that exposes the information of a Google Sheets without the need to implement a logging system.

Of course, this information is publicly available, and anyone with access to your REST API link could access it. Implementing an OAuth system would always be more secure.

Documentation:

SpreadSheetApp
Google Apps Script Web Apps
ContentService
google.script.run

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 and https://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".
1. Click "Review Permissions".
2. Select own account.
3. Click "Advanced" at "This app isn't verified".
4. Click "Go to ### project name ###(unsafe)"
5. 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

modify google spreadsheet using Ajax javascript only

From your showing script, I couldn't understand the following.

What method in Sheets API do you want to use?
Which do you want to put the value to the cell "A1" or "A2".
I couldn't understand whether your access token can be used for updating the Spreadsheet.

From this situation, I guessed your current issue and your goal as follows.

You want to put a value of "32" to a cell "A1" of the 1st sheet of the Spreadsheet.
- In this case, "Method: spreadsheets.values.update" is used.
Your access token can be used for updating the Spreadsheet.

In this case, how about the following sample script?

Sample script:

const auth = "Bearer ###"; // Please replace "###" with your access token.
const sheetId = "###"; // Please set your Spreadsheet ID.

$.ajax({
  type: 'put',
  headers: { Authorization: auth, 'content-type': 'application/json' },
  data: JSON.stringify({
    "values": [["32"]]
  }),
  url: 'https://sheets.googleapis.com/v4/spreadsheets/' + sheetId + '/values/A1?valueInputOption=RAW',
  success: function (r) {
    console.log(r)
  }, error: function (r) {
    console.log(r)
  }
});

When this script is run, "32" is put to a cell "A1" of the 1st sheet of the Spreadsheet.

Reference:

Method: spreadsheets.values.update

NodeJS accessing Google SpreadSheets

When I saw your showing script and your error message, I'm worried that the current version of google-spreadsheet you are using might be different from your showing script. So, in this answer, I would like to propose a sample script converted from your showing script using the latest version of google-spreadsheet.

When I check the latest version of google-spreadsheet, it seems that it's 3.2.0. So, first, please check the version of google-spreadsheet you are using. And, please install the latest one.

When your script is modified for using with the latest one (v3.2.0), it becomes as follows.

Modified script:

const { GoogleSpreadsheet } = require("google-spreadsheet");

const creds = require('./credentials.json');
const doc = new GoogleSpreadsheet('###'); // Please set your Spreadsheet ID.

const accessSheet = async () => {
  await doc.useServiceAccountAuth(creds);
  await doc.loadInfo();
  const worksheet = doc.sheetsByIndex[0];
  const rows = await worksheet.getRows();
  rows.forEach((row) => {
    console.log(row.Title);
  });
};
accessSheet();

Reference:

node-google-spreadsheet

How to Access Google Sheet Spreadsheets Only with JavaScript

How can I access Google Sheet spreadsheets only with Javascript?

Accessing a public google sheet's data directly from client-side JavaScript

direct link

example by gas

example by html page

How can I use data from a Google Sheet on a website without needing the user to be logged in?

`RestAPI.gs`

`RestAPI.gs`

`Web.gs`

`index.html`

Documentation:

Get data from Google Sheets without Sheets API

Issue and workaround:

Usage:

1. Create new project of Google Apps Script.

2. Prepare script.

3. Deploy Web Apps.

4. Run the function using Web Apps.

Note:

References:

modify google spreadsheet using Ajax javascript only

Sample script:

Reference:

NodeJS accessing Google SpreadSheets

Modified script:

Reference:

Related Topics

Leave a reply