javascripthtmlformsgoogle-apps-scriptgoogle-sites

How to make submission function work in a web form that is created using Google Apps Script and embedded in a Google Site?

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 onload="addOptions()">   <!--Execute addOptions function immediately after a page has been loaded-->
    <form class="" 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" class="form-control"> 
          </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 class="form-submit">
        <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;
}
Solution

  • 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.

    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" onload="addOptions()">
    <form id="form">
      <div>
        <h1 id="Question">
          Choose either cheesecake or chocolate cake.
        </h1>
          <select id="dropdownList" name="cake" class="form-control"> 
          </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 class="form-submit">
        <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);
}

    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: