I'm trying to use Google Sheets as a data source for a dynamic web application, without needing the user to be logged in client-side. I'm using Bang's answer from this post but still need to be logged in to Google myself in order for access to the spreadsheet to be provided. Am I doing anything wrong or is this still expected behaviour? Is there any other way I can enable the website application to access the spreadsheet without user login required? I've created a 'service user' with the aim of trying to achieve this but not sure if that's right (plus I can't seem to work out how to incorporate the service user authentication into the below code. Any assistance would be hugely appreciated, thank you.

    <pre id="content" style="white-space: pre-wrap;"></pre>

    <script type="text/javascript">
      // Client ID and API key from the Developer Console
      var CLIENT_ID = 'xxxxxx';
      var API_KEY = 'xxxxxx';

      // Array of API discovery doc URLs for APIs used by the quickstart
      var DISCOVERY_DOCS = ["https://sheets.googleapis.com/$discovery/rest?version=v4"];

      // Authorization scopes required by the API; multiple scopes can be
      // included, separated by spaces.
      var SCOPES = "https://www.googleapis.com/auth/spreadsheets.readonly";

      /**
       *  On load, called to load the auth2 library and API client library.
       */
      function handleClientLoad() {
        gapi.load('client', initClient);
      }

      /**
       *  Initializes the API client library and sets up sign-in state
       *  listeners.
       */
      function initClient() {
        gapi.client.init({
          apiKey: API_KEY,
          clientId: CLIENT_ID,
          discoveryDocs: DISCOVERY_DOCS,
          scope: SCOPES
        }).then(function () {
          listData();
        }, function(error) {
          appendPre(JSON.stringify(error, null, 2));
        });
      }

      /**
       * Append a pre element to the body containing the given message
       * as its text node. Used to display the results of the API call.
       *
       * @param {string} message Text to be placed in pre element.
       */
      function appendPre(message) {
        var pre = document.getElementById('content');
        var textContent = document.createTextNode(message   '\n');
        pre.appendChild(textContent);
      }

      /**
       * Print data from spreadsheet
       * https://docs.google.com/spreadsheets/d/xxxxxx/edit
       */
      function listData() {
        gapi.client.sheets.spreadsheets.values.get({
          spreadsheetId: 'xxxxxx',
          range: 'Sheet2!B2:E'
        }).then(function(response) {
          var range = response.result;
          if (range.values.length > 0) {
            
            
            appendPre('Account Name, Account ID, Rent:');
            for (i = 0; i < range.values.length; i  ) {
              var row = range.values[i];
              
              if (row[1] == 'xxxxxx') {
                console.log(row[0]);
                 appendPre(row[0]   ', '   row[1]   ', '   row[2]);
              }
            
            }
          } else {
            appendPre('No data found.');
          }
        }, function(response) {
          appendPre('Error: '   response.result.error.message);
        });
      }

    </script>

    <script async defer src="https://apis.google.com/js/api.js"
      onl oad="this.onload=function(){};handleClientLoad()"
      onreadystatechange="if (this.readyState === 'complete') this.onload()">
    </script>

CodePudding user response：

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:

1. 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
}

2. 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
}

3. 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