Search code examples
google-apps-scriptgoogle-appsgoogle-sheets-api

Pulling data from a Google spreadsheet from inside doGet()

Since SpreadsheetApp.openById("sheetid") is some what deprecated or no longer works https://code.google.com/p/google-apps-script-issues/issues/detail?id=5174. What is alternative method to get pull a spreadsheet data into a doGet() method. I have been trying to follow this tutorial https://www.youtube.com/watch?v=3deomYqHKgA

Solution

  • You can also use the JavaScript onload function to insteadly retrieve spreadsheetdata. Try it like this index.html:

    <!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
  <script>
    function onLoadFunction() 
    {
      google.script.run.withSuccessHandler(onSuccessData).getData();
    } 
    window.onload = onLoadFunction;
     
    function onSuccessData(rawdata) 
    {
      var div = document.getElementById('result');
      div.innerHTML += '<BR>' + rawdata; 
    }
</script>
<div id="result"><b>result:</b><br></div>
  </body>
</html>

    and Code.gs:

    var SHEET_ID = 'YOUR_SHEET_ID';

function getData() {
  var sheet   = SpreadsheetApp.openById(SHEET_ID);
  var dataRange = sheet.getDataRange();
  var values = dataRange.getValues();
  return values;
}

function doGet() {
  return HtmlService.createHtmlOutputFromFile('index')
      .setSandboxMode(HtmlService.SandboxMode.IFRAME);
}

    The function works and no mention of being deprecated: https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#openbyidid