Get Variable Value from inside code.gs Google App Script to Javascript

user_17406616 0
Viewed 583

I have a simple webapp that show a button and when a user clicked, it will open a new window that shows a website. What I want to do is when a user clicked the button, it will get the value from my google sheet and open a new window based on that value. Then, the webapp will update the value in the google sheet with a new value.

For example: if the value in my google sheet is "Google", it will open a window to "www.google.com" and then update the google sheet value to "other website".

enter image description here

I have succesfully made the function for updating the value on google sheet whenever a user clicked the button but I fail in getting the value from the code.gs/google sheet to my Javascript. Please help.

here is my code.gs:

var url = "url"; //mygooglesheet url
var web = "";

function doGet(e) {
  let tmp = HtmlService.createTemplateFromFile("index");
  return tmp.evaluate();

}

function setWebsite () {

  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("website");
  web = ws.getRange(1,1,1,1).getValue();
  
  if (web === "Google") {
    ws.getRange(1,1,1,1).setValue("Youtube");
  } 
    else if (web === "Youtube") {
    ws.getRange(1,1,1,1).setValue("Facebook");
  }
    else {
    ws.getRange(1,1,1,1).setValue("Google");
  } 
}

function getWebsite() {  
  
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("website");
  var web = ws.getRange(1,1,1,1).getValue();
    return web;
    }

my index.html:

<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <h1>Please Click Below</h1>
    <!-- <h2><?=web?>:</h2> -->
    <button id = "btn" type="submit" >Open Window</button>
              
  <script>
  document.getElementById("btn").addEventListener("click", doStuff);
  var web = "";
  
  function doStuff() {

  google.script.run.getWebsite(); //dont know for sure if this is needed or not
  google.script.run.setWebsite();
  web = <?=web?>; //dont know for sure if this is needed or not
  if (web === "Google") {
      window.open("https://www.google.com/");
    } 
      else if (web === "Youtube") {
      window.open("https://www.youtube.com/");
    }
     else {
      window.open("https://www.facebook.com/");
    } 
  }
</script>

  </body>
</html>

Right now, my webapp only open a new window to "facebook.com" and update to the next value in the code. I tried "google.script.run.withSuccessHandler(onSuccess).getWebsite()" but not successful to get the variable value from the code.gs, please help.

Thank you.

google-apps-script html javascript
2 Answers

In html, in script section, write

<? var web = getWebsite(); ?>

and erase google.script.run.getWebsite();

user_150193800

Issue:

You want to open a new tab with the URL returned by getWebsite() when the button is clicked.

Solution:

In order to handle data returned by a server-side function called by google.script.run, use the success handler. The function called by this handler will be passed the value returned by the server-side function (getWebsite()) as a parameter.

Code sample:

<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <h1>Please Click Below</h1>
    <button id = "btn" type="submit" >Open Window</button>
    <script>
      document.getElementById("btn").addEventListener("click", doStuff); 
   
      function doStuff() {
        google.script.run.withSuccessHandler(openWebsite).getWebsite();
      }

      function openWebsite(web) {
        if (web === "Google") {
          window.open("https://www.google.com/");
        } else if (web === "Youtube") {
          window.open("https://www.youtube.com/");
        } else {
          window.open("https://www.facebook.com/");
        }
      }
    </script>
  </body>
</html>

Note:

If the data returned by getWebsite() is supposed to remain static between the moment in which the page first loads and when the button is clicked, you could also use the approach mentioned by Mike Steelson, using template scriplets.

user_106120110
Related