1

I created a web form using Google Apps Script, where form visitors would see result.html after data submission. However, the data may be submitted multiple times if visitors reload the result.html by pressing F5, Ctrl + R, ignoring the alert of resubmission. The same concern has already been posted here, and I tried implementing one of the solutions for that, but in vain.

I have now 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

The solution I have tried is adding the following JavaScript code result.html. I also add that to JavaScript.html so that the code is to be executed in index.html, too.

<script>
    if ( window.history.replaceState ) {
        window.history.replaceState( null, null, window.location.href );
    }
</script>

However, the resubmission still occurs when I reload the result.html even after I added that code to both result.html and index.html. What am I missing?

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>

      <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);
  }

  if ( window.history.replaceState ) {
        window.history.replaceState( null, null, window.location.href );
  }
</script>

result.html

<!DOCTYPE html>
<html lang="ja">
  <head>
    <meta charset="UTF-8">
    <base />
    <title>Thank you for your order!</title>
    <!-- <?!= include('css'); ?> -->
  </head>
  <script>
    if ( window.history.replaceState ) {
      window.history.replaceState( null, null, window.location.href );
    }
  </script>
  <body>
    <p>
      Don't forget what you've ordered!
    </p>
  </body>
</html>

code.gs

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

function doGet(){
  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 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))

  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;
}
Carlos Luis Rivera
  • 3,108
  • 18
  • 45
  • 1
    From your question, I proposed a simple workaround as an answer. If I misunderstood your current issue and that was not useful for your situation, I apologize. – Tanaike May 22 '22 at 08:10

1 Answers1

2

In your situation, how about checking the submit using PropertiesService? When your script is modified, it becomes as follows.

Modified script:

In this modification, 2 functions of doGet and doPost of code.gs are modified.

doGet

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

doPost

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();
}
  • When you access to your Web Apps, sample is stored by setProperty("key", "sample") in doGet(). And, when the HTML form is submitted, the PropertiesService is checked in doPost(e). When sample is existing, the data is put, and the PropertiesService is cleared. By this, even when the submitted page is reopened, the PropertiesService is not existing. By this, the resubmitted can be avoided.

Reference:

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • Thank you, Tanaike san, you saved me again! Your code perfectly works! One follow-up question: Do I have to remove `if ( window.history.replaceState ) { window.history.replaceState( null, null, window.location.href ); }` from `` in `JavaScript.html` and `result.html` or should I keep that code in those files? – Carlos Luis Rivera May 22 '22 at 08:44
  • 1
    @Carlos Luis Rivera Thank you for replying. I think that in this workaround, `if ( window.history.replaceState ) { window.history.replaceState( null, null, window.location.href ); }` is not required to be used. – Tanaike May 22 '22 at 08:47
  • Thank you again, @Tanaike san, for your reply, too. Then, I will delete that code from my project. One more follow-up question: Using `PropertiesService`, is it also possible to prevent the return from the submission page to the input page by pressing browser's "go back" button or `Alt` + `<-`? – Carlos Luis Rivera May 22 '22 at 09:04
  • 1
    @Carlos Luis Rivera About your 3rd question, I would like to support you. But, I have to apologize for my poor English skill. Unfortunately, I cannot understand your 3rd question. So, in this case, I would like to recommend posting it as a new question by including more information. By this, it will help users think of the solution. If you can cooperate to resolve your 3rd question, I'm glad. Can you cooperate to do it? And, I deeply apologize that I cannot resolve your various questions soon. This is due to my poor skill. I deeply apologize for this again. – Tanaike May 22 '22 at 09:08
  • Please do not apologise so much, @Tanaike san... It is me who should apologise for repeatedly asking ambiguous questions. I was wondering whether I could prevent visitors who completed the submission once from resubmitting the form again by going back to the form from the browsing history and re-fill the form. However, I came to think that such an implementation might not be so helpful. I would like to thank you again for your advice. – Carlos Luis Rivera May 22 '22 at 09:29
  • 1
    @Carlos Luis Rivera Thank you for replying. Unfortunately, I cannot still understand most questions because of my English skill. So, I always think that I want to study more to correctly understand various questions and situations. I would like to study more and be able to think of the solutions and workarounds for various situations. – Tanaike May 22 '22 at 11:45