Home > Software engineering >  How to make submission function work in a web form that is created using Google Apps Script and embe
How to make submission function work in a web form that is created using Google Apps Script and embe

Time:05-29

I want to embed a web created using Google Apps Script to a Google Site. However, data submission button in the form comes to be defunct, when the form is embedded in a Google Site like this.

In the web form, form visitors input data to the form produced by index.html and see result.html after data submission. There is an internal link in index.html to connect a header and its related contents. The form app successfully works when it is not embedded in any other site. See the form app and you will find the data submission button works fine.

Does anybody tell me what I am missing?

MWE

I have four files in the same project of Google Apps Script:

  1. index.html that produces the form
  2. JavaScript.html that defines functions used in index.html
  3. result.html that is presented after the form submission
  4. code.gs that shows the form by doGet(), and processes the submitted data and presents result.html by doPost(). include() defined in this file enables to input JavaScript.html into index.html

index.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <!-- <?!= include("css"); ?> -->
  </head>

  <body onl oad="addOptions()">   <!--Execute addOptions function immediately after a page has been loaded-->
    <form  action="<?!= getScriptUrl(); ?>" method="post" onSubmit="document.getElementById('submit').disabled=true;">
      <div>
        <h1 id="Question">
          Choose either cheesecake or chocolate cake.
        </h1>
          <select id="dropdownList" name="cake" > 
          </select>
      </div>
      <p>
        <div style="width:100px;height:500px;border:1px solid #000;">
          Blank box to scroll down
        </div>
      </p>
      <p>
        Please do not forget what you've answered in the <a href="#Question" target="_self">question<a>
      </p>
      <div >
        <input type="submit" name="" value="Submit">
      </div>
    </form>
  </body>
  <?!= include('JavaScript') ?>
</html>

JavaScript.html

<script>
  function addOptions() {
    /*This will call server-side Apps Script function getAvailableExps and if it is successful, 
    it will pass the return value to function addListValues which will add options to the drop down menu*/
    google.script.run
      .withFailureHandler(onFailure)
      .withSuccessHandler(addListValues)
      .getAvailableExps();
  }

  function addListValues(values) { 
    //Add options to drop down menu using the values of parameter 'values'.     
    for (var i = 0; i < values.length; i  ) {
      var option = document.createElement("option");
      option.text = values[i][0];
      option.value = values[i][0];
      var select = document.getElementById("dropdownList");
      select.appendChild(option);
    }
  }

  function onFailure(err) {
    alert('Error: '   err.message);
  }
</script>

result.html

<!DOCTYPE html>
<html lang="ja">
  <head>
    <meta charset="UTF-8">
    <base />
    <title>Thank you for your order!</title>
    <!-- <?!= include('css'); ?> -->
  </head>
  <body>
    <p>
      Don't forget what you've ordered!
    </p>
  </body>
</html>

code.gs

var sheetID = "............................................";
var inventory_sheet = "Inventory";

function doGet(){
  PropertiesService.getScriptProperties().setProperty("key", "sample");
  return HtmlService.createTemplateFromFile("index").evaluate();
}

function include(filename){
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

function getScriptUrl() {
  var url = ScriptApp.getService().getUrl();
  Logger.log(url);
  return url;
}

function doPost(e){
  var p = PropertiesService.getScriptProperties();

  if (p.getProperty("key") == "sample") {

    var ss = SpreadsheetApp.openById(sheetID);
    var sh = ss.getSheets()[0];
    sh.appendRow([String(e.parameters.cake)]);

    //update Inventory
    var inventory = ss.getSheetByName(inventory_sheet);
    var row = inventory.createTextFinder(e.parameters.cake).findNext().getRow();
    var range = inventory.getRange(row, 2);
    var data = range.getValue();
    range.setValue(parseInt(data - 1))

    p.deleteProperty("key");
  }

  return HtmlService.createTemplateFromFile("result").evaluate(); 
  
}

function getAvailableExps(){
  var inventory = SpreadsheetApp.openById(sheetID).getSheetByName(inventory_sheet);
  var data =  inventory.getRange(2, 1, 2, 2).getValues();
  var filtered = data.filter(arr =>  arr[1] > 0 || arr[1] != ''); //remove exp to array if quantity is 0 or empty
  return filtered;
}

CodePudding user response:

Issue and workaround:

I think that in your situation, your goal is difficult to be directly achieved using your showing script. The reason for this has already been mentioned in Gustavo's comment.

When I saw your comment, it seems that you are required to run the Web Apps on the Google side.

In this case, I thought that a workaround might be required to be used. In this answer, in order to achieve your goal, I would like to propose a workaround. The point of this workaround is as follows.

  • In your script, the value of <select id="dropdownList" name="cake" ></select> is sent to doPost using action="<?!= getScriptUrl(); ?>" method="post" of the form.
  • In this workaround, the value is sent to Google Apps Script using google.script.run. And, after the value was completely submitted, the HTML body is overwritten by result.html.

When this point is reflected in your script, it becomes as follows.

Modified script:

index.html

Please modify index.html as follows.

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <!-- <?!= include("css"); ?> -->
  </head>
  <body id="body" onl oad="addOptions()">
    <form id="form">
      <div>
        <h1 id="Question">
          Choose either cheesecake or chocolate cake.
        </h1>
          <select id="dropdownList" name="cake" > 
          </select>
      </div>
      <p>
        <div style="width:100px;height:500px;border:1px solid #000;">
          Blank box to scroll down
        </div>
      </p>
      <p>
        Please do not forget what you've answered in the <a href="#Question" target="_self">question<a>
      </p>
      <div >
        <input type="submit" name="" value="Submit" onclick="sample(this);return false;">
      </div>
    </form>
  </body>
  <?!= include('JavaScript') ?>
</html>

JavaScript.html

Please add the following function to JavaScript.html.

function sample(e) {
  const f = document.getElementById("form");
  const obj = { parameters: [...f].reduce((o, g) => (o[g.name] = [g.value], o), {}) };
  google.script.run
    .withSuccessHandler((res) => {
      document.getElementById("body").innerHTML = res;
    })
    .sample(obj);
}
  • In this sample script, in order to directly use your doPost, the value of obj is prepared. Please be careful about this.

Code.gs: Google Apps Script side

Please add the following function to Code.gs. This function use your doPost.

function sample(e) {
  return doPost(e).getContent();
}

Testing:

When this modification is reflected in your script and your Web Apps is embedded to a Google site, when the submit button is clicked, the value of cake is sent to the Google Apps Script side and result.html is displayed. I thought that this situation might be your expected result.

Note:

  • This modification is a simple modification for explaining the workaround. So, please modify this for your actual situation.

  • When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful this.

  • You can see the detail of this in the report of "Redeploying Web Apps without Changing URL of Web Apps for new IDE".

  • About the internal link of the Web Apps on Google site, it seems that when the while page of Web Apps is embedded and the scrollbar is not shown, the internal link doesn't work. When the scrollbar of the frame is shown, the link works. In this case, it seems that the internal link cannot be worked using both HTML and Javascript. And, I cannot confirm the error message.

  • Related