CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-api
Anthony Bahl
Anthony Bahl

Reputation: 25

Google Sheets - Passing form data

I'm using google sheets and I'm creating a document that will pull through employees that are out of the office. I have a menu option to add new employee data, and it opens the sidebar where I have an HTML form (Image of my project). It then posts the data on the next available line.

Issue: The checkboxes keep pulling through as undefined, and I can't figure out how to retrieve their true/false value.

Here is my html:

<!DOCTYPE html>
<html>

<head>
  <base target="_top">
  <script>
    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 submitForm(formObject) {
      google.script.run
        .submitAddEmployee(formObject);
    }
  </script>
</head>

<body>
  <form id="AddEmployee" onsubmit="submitForm(this)">
    Employee Name:<br />
    <input type="text" name="employeename" />
    <hr /><br /> Check boxes that Employee Works:<br />
    <input type="checkbox" name="Workdays" value="sunday"> Sunday<br />
    <input type="checkbox" name="Workdays" value="monday" /> Monday<br />
    <input type="checkbox" name="Workdays" value="tuesday" /> Tuesday<br />
    <input type="checkbox" name="Workdays" value="wednesday" /> Wednesday<br />
    <input type="checkbox" name="Workdays" value="thursday" /> Thursday<br />
    <input type="checkbox" name="Workdays" value="friday" /> Friday<br />
    <input type="checkbox" name="Workdays" value="saturday" /> Saturday
    <hr /><br />
    <input type="checkbox" name="ooo" value="isOOO" />OOO Employee?

    <br /><br /><input type="submit" value="Submit" />
  </form>
  <div id="output"></div>
</body>

</html>

Here is my script:

function submitAddEmployee(object) {
  var emName = object.employeename;
  var sunday = object.sunday;
  var monday = object.monday;
  var tuesday = object.tuesday;
  var wednesday = object.wednesday;
  var thursday = object.thursday;
  var friday = object.friday;
  var saturday = object.saturday;
  var isOOO = object.isOOO;

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  if (sheet != null) {
    var range = sheet.getDataRange();
    var nextRow = range.getLastRow() + 1;
    sheet.getRange(nextRow, 1).setValue(emName);
    sheet.getRange(nextRow, 2).setValue(sunday);
    sheet.getRange(nextRow, 3).setValue(monday);
    sheet.getRange(nextRow, 4).setValue(tuesday);
    sheet.getRange(nextRow, 5).setValue(wednesday);
    sheet.getRange(nextRow, 6).setValue(thursday);
    sheet.getRange(nextRow, 7).setValue(friday);
    sheet.getRange(nextRow, 8).setValue(saturday);
    sheet.getRange(nextRow, 9).setValue(isOOO);
    sheet.getRange(nextRow, 10).setValue("False");
  } else {
    Browser.msgBox('Sheet "Tasks" was not found.');
  }
}

Any help you can give me would be appreciated.

Thank you!

Upvotes: 1

Views: 213

Answers (1)

Cameron Roberts
Cameron Roberts

Reputation: 7367

The submitted checkbox values are accessed by the field Name rather than the Value.

So in your case, you have a series of fields with the same name, "Workdays", which would be accessed as object.Workdays. The simplest fix is to rename your checkboxes to match your Apps Script:

<input type="checkbox" name="sunday" value="sunday"> Sunday<br />

etc.

Upvotes: 1

Related Questions