CODEWITHSUNDEEP
htmlgoogle-apps-script
AKD
AKD

Reputation: 21

Submit form results to google script?

I'm trying to submit the input results of an html form in a container-bound sidebar and copy them to a google sheet. I reviewed a few code examples from this site as well as others (https://www.packtpub.com/mapt/book/web_development/9781785882517/4/ch04lvl1sec48/submitting-form-using-google-script-api-method) but, perhaps because I'm a complete newbie at html/javascript/gas, found them confusing and difficult to follow such that the code I wrote isn't working and I can't figure out why.

In short, I'd like to pass the value entered in 'formcommenttext' input text box to my Google Apps Script function called 'postCommentToSheet', where I'm hoping I can get the value entered by the user and (eventually) write it to a google sheet.

Any suggestions?

Here's my html code:

  <!-- input control to add comment -->
  <form id="addCommentForm">
  <fieldset>
  <legend>Add your comment:</legend>
  <br>
  <input type="text" name="formcommenttext" value=""><br>
  <input type="submit" value="Add" onclick="google.script.run.postCommentToSheet(this.parentNode);">
  <input type="reset" value="Cancel">
  </fieldset>
  </form>

I have also include the following code snippet into the script portion of my html form (as suggested elsewhere) though i'm unclear what this does and how to edit it to fit my needs.

  function postData(form){
    google.script.run
      .withSuccessHandler(callback)
      .withFailureHandler(callback)
      .postFormDataToSheet(form);
  }

Here's my .gs code:

function postCommentToSheet(formObject) {
  //read formcommenttext from add comment form: 
  //https://www.packtpub.com/mapt/book/web_development/9781785882517/4/ch04lvl1sec48/submitting-form-using-google-script-api-method

  var result = GLOBAL_UI.alert(formObject.formcommenttext.value)

  //get comments data sheet
  var ss = GLOBAL_DataFile.getSheetByName("Comments");

  ss.appendRow([formObject.formcommenttext]);

}

Upvotes: 2

Views: 2569

Answers (2)

Diego
Diego

Reputation: 9581

You don't currently need the postData() function, but you could call it instead of google.script.run.postCommentToSheet() in case you wanted to do some processing before calling adding the comment to the sheet.

For example, you could run validations to prevent the user from, say, submitting a blank comment. I've included this exact functionality in my example as a demonstration.

Also included is a custom menu so that the sidebar can be opened from within the sheet and the CSS package for add-ons.

enter image description here

Index.html:

<!DOCTYPE html>
<html>

<head>
  <base target="_top">
  <!-- (OPTIONAL) Make your sidebar look consistent with Google Sheets with the below CSS https://developers.google.com/apps-script/add-ons/css -->
  <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
</head>

<body>
  <!-- input control to add comment -->
  <form id="addCommentForm" onsubmit=checkData(this)>
    <fieldset>
      <legend>Add your comment:</legend>
      <br>
      <input type="text" name="formcommenttext" value="">
      <br>
      <input type="submit" value="Add">
      <input type="reset" value="Cancel">
    </fieldset>
  </form>

  <script>
    function checkData(form) {
      if (form.formcommenttext.value != "") {
        google.script.run.postCommentToSheet(form);
      }
    }
  </script>
</body>

</html>

Code.gs:

// Create a custom menu to open your sidebar
function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [];
  menuEntries.push({name: "Open Sidebar", functionName: "openSidebar"});
  ss.addMenu("My Custom Menu", menuEntries);
 }

// Open the sidebar
function openSidebar() {
  var sidebar =  HtmlService.createHtmlOutputFromFile("Index");
  SpreadsheetApp.getUi().showSidebar(sidebar);
}

function postCommentToSheet(formObject) {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Comments"); // Almost ALWAYS better to explicitly define which sheet to write to
  sheet.appendRow([formObject.formcommenttext]);
}

Upvotes: 0

Serge insas
Serge insas

Reputation: 46812

here is how it works :

  <form id="addCommentForm">
  <fieldset>
  <legend>Add your comment:</legend>
  <br>
  <input type="text" name="formcommenttext" value=""><br>
  <input type="submit" value="Add" onclick="google.script.run.postCommentToSheet(this.form);">
  <input type="reset" value="Cancel">
  </fieldset>
  </form>

and the .gs part :

function sidebar(){
  var sb =  HtmlService
    .createHtmlOutputFromFile('index');
  SpreadsheetApp.getUi().showSidebar(sb);
}

function postCommentToSheet(form){
  Logger.log(JSON.stringify(form));
  var ss = SpreadsheetApp.getActiveSheet();
  ss.appendRow([form.formcommenttext]);
}

I added a log to check the content of the form answer and simplified a bit to make it functional in this small example.

Upvotes: 2

Related Questions