Write data array from a google app script function to a HTML paragraph <p> tag

0
votes

I have this google test sheet with dummy data that I have created for testing purposes

https://docs.google.com/spreadsheets/d/1Hxjr1ai6KWWJyyQLPJ2GRWYgQftyBxJmiKEu25Co2aQ/edit#gid=0

Crypto Data Image

By using google app script I have created a google web app with a HTML drop down box (select in HTML) that gets data from the above google sheet.

https://script.google.com/macros/s/AKfycbzYDDLg6YDNLBo-3jKQpcedU9jLJbxbOUDMh5YayKtVaurN2COq/exec

The google web app successfully displays the users selection from the drop down box in a HTML paragraph tag but the web app fails to display the data array for the selected cryptocurrency. The error message is "undefined". I have just been doing this google app script stuff for a couple of weeks and I have a hard time understanding why the user selected array with data is not displayed on the HTML page.

My next step will be to try to plot the data the user selects from the HTML drop down box but before I can do that I need to visually confirm that the data is actually there.

If I select nano in the dropdown box this the data I get from the google apps script Logger.log:

[19-02-22 12:02:15:237 PST] The number of columns in sheet = 5

[19-02-22 12:02:15:238 PST] Crypto currencies in sheet = Bitcoin,Ethereum,Nano,Status,,,,,,,,,,,,,,,,,,,,

[19-02-22 12:02:15:241 PST] Dropdown box selection = Nano

[19-02-22 12:02:15:244 PST] Data to plot =0.9,0.8,0.7,0.8,

That tells me that the google app script is working but what is not working is the display of the data in the HTML paragraph tag on the website.

Code.gs

function getSelectList() 
{
  // puts crypto currency names in dropdown box on website
  var ss = SpreadsheetApp.openById("1Hxjr1ai6KWWJyyQLPJ2GRWYgQftyBxJmiKEu25Co2aQ");
  var sheet = ss.getSheetByName("Sheet1");
  var lastRow = sheet.getLastRow();
  var myRange = sheet.getRange("A2:A" + lastRow);
  var data = myRange.getValues();
  return data;
}

function Selection(crypto_name) 
{
var ss = SpreadsheetApp.openById("1Hxjr1ai6KWWJyyQLPJ2GRWYgQftyBxJmiKEu25Co2aQ");
var sheet = ss.getSheetByName("Sheet1");
var name = sheet.getRange('A2:A101').getValues();
var Nc = sheet.getLastColumn();   
Logger.log("The number of columns in sheet = " + Nc); 
Logger.log("Crypto currencies in sheet = " + [name]);  
Logger.log("Dropdown box selection = " + crypto_name); 

  for (i = 0; i < name.length; i++)
     {
      if(name[i]== crypto_name)
        { 
         var TS = sheet.getRange(i+2,2,1,Nc).getValues();
         Logger.log("Data to plot =" + [TS]); 
        }
     }

return TS ;  
}

 function doGet() 
 {
 return HtmlService.createHtmlOutputFromFile("Web");
 }

Web.html

<!DOCTYPE html>
<html>
<head>
</head>
<body>

<select id="MyBox" onchange="GetMyBox(this)">  </select>
<script>
(function() 
{
google.script.run.withSuccessHandler( function (selectList) 
{
var select = document.getElementById("MyBox");
for( var i=0; i<selectList.length; i++ ) 
{
var option = document.createElement("option");
option.text = selectList[i][0];
select.add(option);
}
}
).getSelectList();
}());

function GetMyBox(sel) 
{
var crypto_name = sel.value;  
document.getElementById("demo1").innerHTML = crypto_name;  
var data = google.script.run.Selection(crypto_name); 
document.getElementById("demo2").innerHTML = data; 
}        

</script>   

<p id="demo1"></p>   
<p id="demo2"></p>   

</body>
</html>
2
google-apps-scriptweb-applications
google.script.run.<any function name ever>() will always return undefined because it is an asynchronous function. Set a success handler if you care about the result from the function. (See your last HTML function, GetMyBox) – tehhowch

2 Answers

0
votes

This isn't intended to be a complete answer I just took your code and reformatted it a bit based upon what I think you're trying to accomplish. I didn't look at your code precisely but I'm pretty sure you still have some debugging to do.

In the html it looked like you want to use the JQuery ready function so I added some JQuery references to the head. If that wasn't your plan then you might want to change to window.onload.

html:

<!DOCTYPE html>
<html>
  <head>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
    <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
  </head>
  <body>

    <select id="MyBox" onchange="GetMyBox(this)">  </select>
  <script>
    $(function() {
      google.script.run
      .withSuccessHandler(selectList)//You can just put the name of the function in this way
      .getSelectList();
    });

    function GetMyBox(sel) {
      var crypto_name = sel.value;  
      google.script.run
      .withSuccessHandler(function(data){//you put an anonymous function this way
           document.getElementById("demo2").innerHTML = data; 
         }
      .Selection(crypto_name);
    }        

    function selectList(data){
    var select = document.getElementById("MyBox");
      for( var i=0; i<data.length; i++ ) {
        var option = document.createElement("option");
        option.text = data[i][0];
        select.add(option);
    }
</script>   

<p id="demo1"></p>   
<p id="demo2"></p>   

</body>
</html>

Google Script:

function getSelectList() {
  // puts crypto currency names in dropdown box on website
  var ss = SpreadsheetApp.openById("1Hxjr1ai6KWWJyyQLPJ2GRWYgQftyBxJmiKEu25Co2aQ");
  var sheet = ss.getSheetByName("Sheet1");
  var lastRow = sheet.getLastRow();
  var myRange = sheet.getRange("A2:A" + lastRow);
  var data = myRange.getValues();
  return data;//this is a 2d array it might be what you want for select options
}

function Selection(crypto_name) {
  var ss = SpreadsheetApp.openById("1Hxjr1ai6KWWJyyQLPJ2GRWYgQftyBxJmiKEu25Co2aQ");
  var sheet = ss.getSheetByName("Sheet1");
  var name = sheet.getRange('A2:A101').getValues();
  var Nc = sheet.getLastColumn();   
  Logger.log("The number of columns in sheet = " + Nc); 
  Logger.log("Crypto currencies in sheet = " + [name]);  
  Logger.log("Dropdown box selection = " + crypto_name); 
  for (i = 0; i < name.length; i++) {
    if(name[i]== crypto_name) { 
      var TS = sheet.getRange(i+2,2,1,Nc).getValues();
      Logger.log("Data to plot =" + [TS]); 
    }
  }
  return TS ; //this is a 2d array so it's not ready for html yet
}
0
votes

After spending some time on the problem I figured it out. I will therfore answer my own question since no one is willing to provide a good answer to the question. I think it is important that a working solution to the problem is clearly presented so that anyone in the furure can easily find a solution to a similar question. 

function GetMyBox(sel) 
{
var crypto_name = sel.value;  
document.getElementById("Tag1").innerHTML = crypto_name;  
google.script.run.withSuccessHandler(callback).Selection(crypto_name);  
}  

function callback(whatToWrite) 
{
document.getElementById("Tag2").innerHTML = whatToWrite;
}

</script>   

<p id="Tag1"></p>   
<p id="Tag2"></p>