Search code examples
google-apps-scriptgoogle-sheets

Google Apps Script and HTML Service; how do I bring in Spreadsheet values to display on my .html page?


Using Google Apps Script and HTML Service how do I bring in specific cells of data into my index.html page?

Basically my 'getdata' function does get me spreadsheet data but my 'getdata2' needs a little help. I am hoping someone might have and idea that might get me going.

I have some code that I think will help give you an idea of what I am hoping for.

index.html follows

 <H1>Hope to get data from a spreadsheet</H1>
<div id="myDataDiv">
<? var data = getData(); ?>
<?= data       ?>

</div>
<Hr>
<div id="myDataDiv2">
<? var data2 = getData2(); ?>
<?= data2.mySingleValue       ?><br />
<?= data2.mySingleValue2       ?>
</div>

code.gs follows

var submissioSSKey = '1Jyc2XmgQ7ffuRDpieT_DyJIUWVWi-9UUn2vAQqr_zJY';//this is an altered key
function doGet() {
      return HtmlService
      .createTemplateFromFile('index')
      .evaluate()
      .setSandboxMode(HtmlService.SandboxMode.IFRAME);
}

function getData() {
  return SpreadsheetApp
 .openById(submissioSSKey) 
  .getActiveSheet().getRange("B3").getValue(); 
}

function getData2() {
var ss = SpreadsheetApp.openById(submissioSSKey); 
var mySingleValue = ss.getSheetByName('Sheet1').getRange('B3').getValue(); //hoping for value 1 
var mySingleValue2 = ss.getSheetByName('Sheet1').getRange('B4').getValue(); //hoping for value 2

}

Solution

  • Your function getData2() is not returning any value, so nothing is passed to the data2 variable in your HTML scriptlet.

    Try this:

    function getData2() {
     var ss = SpreadsheetApp.openById(submissioSSKey);
     var myValues = {}; 
     myValues["mySingleValue"] = ss.getSheetByName('Sheet1').getRange('B3').getValue(); //hoping for value 1 
     myValues["mySingleValue2"] = ss.getSheetByName('Sheet1').getRange('B4').getValue(); //hoping for value 2
     return myValues;
    };