Google Sheets Sidebar | HTML form, how do you parse the onsubmit formObject?

Issue

I’m creating a form in the sidebar to create new sheets based on a wide range of criteria. I’ve got about 27 inputs total. I have one text entry, and several groups of radio’s and checkboxes.

I’m stuck at just creating a new sheet where the name is the value of the text input.

Code.gs:

var ui = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet();

// Create a menu item to access the sidebar.
function onOpen(e) {
  ui.createMenu("Sidebar Test").addItem("Sidebar Test","makeSidebar").addToUi();
};
// Setup the sidebar
function makeSidebar(){
  var html = HtmlService.createHtmlOutputFromFile('Index').setTitle("Sidebar Test");
  ui.showSidebar(html);
};

// Creates the new sheet using the name provided in the form, and in index 4 using the MAINTemplate sheet as a template
function mkSheet(name) {
var templateSheet = ss.getSheetByName('MAINTemplate');
ss.insertSheet(name, 4, {template: templateSheet});
}

Index.html:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <script>
      // Prevent forms from submitting.
      function preventFormSubmit() {
        var forms = document.querySelectorAll('form');
        for (var i = 0; i < forms.length; i++) {
          forms[i].addEventListener('submit', function(event) {
            event.preventDefault();
          });
        }
      }
      window.addEventListener('load', preventFormSubmit);

      function btnMkSheet(formObject) {
        var formObject = formObject;
        var formData = new FormData(formObject);
        var formString = JSON.stringify(formObject);
        var sheetName = formObject.sheetName;
        console.log(formObject.sheetName);
        console.log(formData);
        console.log(formString);
        console.log(sheetName);
        google.script.run.mkSheet(sheetName);
      };

    </script>
  </head>
  <body>
    <button onclick="google.script.host.close()">Close</button>
    <h2>Create a new sheet</h2>
    <form id="myForm" onsubmit="btnMkSheet(this)">
      <label for="nameText">Sheet Name</label>
      <input id="nameText" name="sheetName" type="text" placeholder="QA WEB DEV 01-01-1985" />
      <p>i.e. "QA WEB DEV 01-01-1985"</p>
      <input type="submit" value="Create" />
    </form>
 </body>
</html>

When I put a name in the text input and click the create button. It doesn’t create a new sheet. instead it throws an error in the console.

And the four console logs are as follows.

<input id="nameText" name="sheetName" type="text" placeholder="QA WEB DEV 01-01-1985">
FormData {}
{"0":{},"1":{}}
<input id="nameText" name="sheetName" type="text" placeholder="QA WEB DEV 01-01-1985">

Uncaught InvalidArgumentError: Failed due to illegal value in property: 0

I could probably just get the value of the input by using a query selector. But I have a lot more to add to the form. And I would like to figure out the correct way to use the formObject.

Solution

In your script, how about the following modification? Please modify Index.html as follows.

From:

var sheetName = formObject.sheetName;

To:

var sheetName = formObject.sheetName.value;
  • formObject.sheetName is the object. So, please retrieve the value.

Answered By – Tanaike

Answer Checked By – Jay B. (AngularFixing Admin)

Leave a Reply

Your email address will not be published.