CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsweb-applicationsclient-server
user1019450
user1019450

Reputation: 41

Google Sheets WebApp returning value as undefined

I have no problem sending data to google sheets through my webapp but getting data from google sheets to my webapp is eluding me. Maybe there's something wrong with my code. Maybe someone can help me fix my problem.

My first goal is to load a value from a certain cell into a textbox on my html page.

My second goal is to have that textbox refresh every 10 seconds to see if there is new data and update the data if necessary. It's a bit like a chat box.

My HTML code:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>

    <table>
    <tr>
          <td align="center" colspan="3">Message from recorder</td>
    </tr>
    <tr>
          <th colspan="3"><input type="text" id="rmessage"></th>
    <tr>
    </table>

<?!= include("judge1-js"); ?>
  </body>
</html>

Next, here is judge-1.js:

<script>

document.getElementById("rmessage").addEventListener("mousedown",doRMessage);
    
function doRMessage(){
    var rmessage = google.script.run.j1RMessage();
    document.getElementById("rmessage").value = rmessage;
}
    
</script>

And finally, here's my Google Script file funcs.gs:

var url = "https://docs.google.com/spreadsheets/d/1U0vO4AWFAXvsCW6zlF5nkekSKb2WJ80JarZUEO4ljK8/edit#gid=0";
var ss = SpreadsheetApp.openByUrl(url);
var ws = ss.getSheetByName("Sheet1");


function j1RMessage() {
  var rmessage = ws.getRange("B16").getValue();
  return rmessage;
}

Can anyone see why when i click on the input box i get "undefined" returned? When I run the script in google's script editor in the logger ( Logger.log(rmessage); ) it returns to correct cell's value.

Once i actually get this working can someone please help me to make it refresh every 10 seconds? Thanks!

Upvotes: 1

Views: 532

Answers (2)

user1019450
user1019450

Reputation: 41

I found a solution. It turns out that to get data from the spreadsheet and display it on an html page you need to convery the data to html first is the Code.gs page.

Code.gs:

function doGet(e){
   Route.path("judge1",loadJudge1);
             
   if(Route[e.parameters.v]) {
     return Route[e.parameters.v]();
   }  else {
     return render("home"); 
     } 
}    

function render(file,argsObject){
      var tmp = HtmlService.createTemplateFromFile(file);
      if(argsObject){
        var keys = Object.keys(argsObject);
        keys.forEach(function(key){
          tmp[key] = argsObject[key];
        });
      }//END IF
      return tmp.evaluate();
    }
        function loadJudge1(){
          var ss = SpreadsheetApp.openByUrl(url);
          var ws = ss.getSheetByName("Sheet1");
          var rmessage = ws.getRange("B16").getValue();
          return render("judge1",{rmessage: rmessage});
        }

Then in my judge1.html:

<div id=auto><p type="text" id="rmessage"><?= rmessage ?></p></div>

Now for my autorefresh problem. I think I'll create another post for that.

Upvotes: 2

Tanaike
Tanaike

Reputation: 201388

Unfortunately, google.script.run.j1RMessage() doesn't return the value from j1RMessage(). I think that this is the reason of your issue. In order to retrieve the value from j1RMessage(), I would like to propose the following modification.

From:

function doRMessage(){
    var rmessage = google.script.run.j1RMessage();
    document.getElementById("rmessage").value = rmessage;
}

To:

function doRMessage(){
  var rmessage = google.script.run.withSuccessHandler(rmessage => {
    document.getElementById("rmessage").value = rmessage;
  }).j1RMessage();
}

Reference:

Upvotes: 1

Related Questions