accessing google spreadsheet data from web app

Question

I am using google app script and I want to read some spreadsheet values but it doesn't work.

<html lang="en">
 <head>
   <meta charset="UTF-8">
   <meta name="viewport" content="width=device-width, initial-scale=1.0">
   <meta http-equiv="X-UA-Compatible" content="ie=edge">
 </head>
 <body>
   <h1>My Google Map</h1>
   <div id="map"></div>
    <script>
     function initMap(){
       var options = {
         zoom:13,
         center:{lat:45.538681, lng:10.221905}
       }
  var map = new google.maps.Map(document.getElementById('map'), options);
  //!!!BUG: just doesn't work!!!
  var sheet = SpreadsheetApp.openById("1Ogqb5kgaY9frtzk5_uu3y_Kwr_9ma5OfjghqDwetrCM").getSheetByName("Palestre");
  //!!!BUG: just doesn't work!!!
  //var lRow = sheet.getLastRow();
  //var lCol = sheet.getLastColumn();
  //var db = sheet.getRange(3, 1, lRow-2, lCol).getValues();

The description and code aren't complete. Please add a [mcve]. — Rubén, Dec 28 '17 at 02:26

score 0 · Accepted Answer · answered Dec 28 '17 at 02:49

Google Apps Script runs only server-side. Putting var sheet = SpreadsheetApp... in client-side JavaScript is not going to work because the browser executing JavaScript has no idea what SpreadsheetApp is. You can call Apps Script from webpages, but it takes work. There are two cases.

HTML page is served by HtmlService from Apps Script

For HTML pages served by Google Apps Script HTML service, Google provides

Class google.script.run (Client-side API)
google.script.run is an asynchronous client-side JavaScript API available in HTML-service pages that can call server-side Apps Script functions.

The HTML page can be

<html>
  <head>
    <script>
      var data = google.script.run.accessSpreadsheet();
      // do something to display data 
    </script>
  </head>
  <body>
  </body>
</html>

The above client-side script calls your Apps Script function, which looks like this:

function accessSpreadsheet() { 
  var sheet = SpreadsheetApp....
  // open sheet, get data 
  return data
}

Your own HTML pages

You could try to deploy Apps Script as a web app serving data, and have your client-side script send XHR request to the web app, getting the data as JSONP. See this answer.

score 0 · Answer 2 · answered Dec 28 '17 at 04:51

Alternatively if needed there is a way to get data from a spreadsheet without using GAS. Below is an example that uses jQuery and gets the sheet as JSON.

var firstSheet = () => {
  var spreadsheetID = "SPREADSHEET_ID";
  var url = "https://spreadsheets.google.com/feeds/list/" + spreadsheetID +"/1/public/values?alt=json";
  return new Promise((resolve,reject)=>{
    $.getJSON(url, (data)=>{
        let result = data.feed.entry
        resolve(result)
   });
  })
}

firstSheet().then(data => {
  console.log(data)
})

For this to work you will need to "publish" the sheet. File => Publish to the web... => Publish. Also the sheet will need to be set to anyone with the link can view.

accessing google spreadsheet data from web app

2 Answers2

HTML page is served by HtmlService from Apps Script

Your own HTML pages