Submit form results to google script?

Question

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

}

Serge insas · Accepted Answer · 2018-04-08 09:27:23Z

2

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.

answered Apr 8, 2018 at 9:27

Serge insas

46.9k7 gold badges113 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Diego · Accepted Answer · 2018-04-08 12:12:45Z

0

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.

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

edited Apr 8, 2018 at 12:12

answered Apr 8, 2018 at 9:43

Diego

9,5812 gold badges24 silver badges35 bronze badges

4 Comments

Serge insas Over a year ago

The doGet function will create a standalone webapp, not a sidebar UI.

Diego Over a year ago

Thanks, forgot it was a sidebar.

AKD Over a year ago

Thank you so much, Diego. This makes total sense and your code sample worked like a charm.

AKD Over a year ago

I also appreciate you pointing me to the google css script resources.

Collectives™ on Stack Overflow

Submit form results to google script?

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related