When using spreadsheet with app script, html script parameters cannot pass to app script

0
votes

I tried to use app script to let people to fill google spreadsheet with an html page. But after I enter data on html page, the data did not show up on my target spreadsheet.

There is no problem when I execute function on code.gs. The log show up normally. And there is no problem when I use alert to test script on html. But while I tried to pass parameter from html to code.gs through js. It not work.

code.gs:

function doGet(event) {
  // Logger.log(event);
  return HtmlService.createHtmlOutputFromFile("page");
}

function GG(name, email, link) {    
  var url = "spreadsheet url";
  var ss = SpreadsheetApp.openByUrl(url);
  var ws = ss.getSheetByName("Data");

  ws.appendRow([new Date(), name, email, link]);
  Logger.log("GG function is work")
}

function WTF() {
  Logger.log("WTF !!!");
}

page.html:

<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <!-- Latest compiled and minified CSS -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
    <!-- jQuery library -->
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.0/jquery.min.js"></script>
    <!-- Popper JS -->
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js"></script>
    <!-- Latest compiled JavaScript -->
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"></script>
    <base target="_top">
  </head>
  <body>
    <div class="container">
      <form>
        <div class="form-group">
          <label for="email">Name:</label>
          <input type="text" class="form-control" id="username">
        </div>

        <br>
        <div class="form-group">
          <label for="email">Email:</label>
          <input type="text" class="form-control" id="email">
        </div>
        <div class="form-group">
          <label for="link">Link:</label>
          <input type="url" class="form-control" id="link">
        </div>
        <button class="btn btn-primary" id="submit">Submit</button>
      </form>
    </div>

    <script>
      document.getElementById("submit").addEventListener("click",fn);

      function lol() {
        google.script.run.WTF();
      }

      function fn() {
        var uname = document.getElementById("username").value;
        var email = document.getElementById("email").value;
        var link = document.getElementById("link").value;

        google.script.run.GG(uname,email,link);
      }

    </script>
  </body>
</html>

It works fine when I change document.getElementById("submit").addEventListener("click",fn); to document.getElementById("submit").addEventListener("click",lol);

The log shows up as expected, but GG not work. The sheet did not add any data as expected unless I execute it through "Run > Run function > GG" on app script page, it will show "undefined" on each cell for data.

2
javascripthtmlgoogle-apps-script
Although I'm not sure about your whole script, when I saw your script, I couldn't find resaon of document.getElementById("resaon").value. When fn() is run under this condition, document.getElementById("resaon") becomes null, and an error occurs. By this, google.script.run.GG(uname,reason,link) doesn't work. For example, as a test case, how about modifying to document.getElementById("email").value? I thought that this issue might be have been modified in your actual script. So I posted this as a comment. If I misunderstood your question, I apologize. - Tanaike
Thank you for replying. I'm glad your issue was resolved. When you modified the script of Web Apps and you don't use the developer mode, please deploy Web Apps as new version. By this, the latest script is reflected to Web Apps. Please be careful this. - Tanaike
haha, The issue is still exist. That one just a typo I forget to change, the original code was using "reason" instead of "email", I just think email is easier for people to understand so I use email in stackoverflow to explain the issue. But thanks anyway. - Chionn K
Thank you for replying. I apologize I misunderstood your situation. When I saw your updated script, I thought that the script works. So I thought that your issue was resolved. When GG() is run by google.script.run.GG(uname,reason,link), if url and Data of the sheet name are correct, the values of new Date(), name, email, link are appended to the sheet. How about this? If it doesn't work, can I ask you about the settings of Web Apps you deployed? - Tanaike
Maybe it is the problem. I am new to app script, I did not touch anything about setting. I just download app script and start a new sheet. Than using the code above. What setting? Where to configure? Thanks - Chionn K

2 Answers

0
votes

I've had a deeper look at your code and I think you are missing the way a form like this works as a web application. Your GG function is trying to do the same thing as submitting your form using a POST method and implementing a doPost method in your code.gs.

If you write your form with method="POST" action="[your script url]" and you add a name attribute to each control:

  <form method="post" action="[your script url]">
    <div class="form-group">
      <label for="email">Name:</label>
      <input type="text" class="form-control" id="username" name="username">
    </div>
    <div class="form-group">
      <label for="email">Email:</label>
      <input type="text" class="form-control" id="email" name="email">
    </div>
    <div class="form-group">
      <label for="link">Link:</label>
      <input type="url" class="form-control" id="link" name="link">
    </div>
    <button class="btn btn-primary" id="submit">Submit</button>
  </form>

The doPost() method in the code.gs could be as follows:

function doPost(e) {

  var name = e.parameter.username;
  var email = e.parameter.email;
  var link = e.parameter.link;

  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");  

  ws.appendRow([new Date(), name, email, link]);
  Logger.log("Event logged" + name + " " + email + " " + link);

  return HtmlService.createHtmlOutput("<h1>Thanks for submitting your information</h1>");
}

This will append a row to the Data sheet and log a message that you can view from the script editor and is more in keeping with the way that this sort of Spreadsheet backed web application should work.

0
votes

Updated:

Your submit event handler is clashing with the default browser action for submitting a form so google.script.run.fn() is called, but the browser also moves to another page before you can see the results. If you add a event parameter to your fn and call e.preventDefault() you will at least get an opportunity to see the effect of your call to google.script.run.fn().

  function fn(e) {
    e.preventDefault();

    var uname = document.getElementById("username").value;
    var email = document.getElementById("email").value;
    var link = document.getElementById("link").value;

    google.script.run
       .withSuccessHandler(function(){
         alert("success");
       })
       .withFailureHandler(function(e){
         alert("error:" + e);
       })
       .GG(uname,email,link);
  }

You should now see an error message, which will tell you that you don't have permission to open a spreadsheet.

Your GG() function doesn't need to reopen the Spreadsheet - it is already associated with your script:

function GG(name, email, link) {    
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  ws.appendRow([new Date(), name, email, link]);
  Logger.log("GG function is working:" + new Date());
}