Writing HTML form radio and checkbox button selection to Google Sheets

Question

I created an HTML form which successfully submits data to Google Sheets via Google Apps Script.

The problem is that I can’t see which radio or checkbox button is checked. The only thing I get is in which fieldset the buttons are checked. Only on label is written, as you can see below.

How can I write each button selection to the appropriate column in the sheet, so I could for example see foo, bar, baz in the foobar column, and quux, quuz in the xyzzy column?

The code for an HTML form:

const scriptURL =
  "https://script.google.com/macros/s/AKfycbzz-KveHder1A3CX8GcqZI6GR2MQj66PDRWNKoatIET_LXNqQs/exec"
const form = document.forms[0]

form.addEventListener("submit", e => {
  e.preventDefault()
  fetch(scriptURL, {
      method: "POST",
      body: new FormData(form)
    })
    .then(response => console.log("Success!", response))
    .catch(error => console.error("Error!", error.message))
})

<form>
  <fieldset>
    <legend>Select Foobar</legend>
    <label><input type="checkbox" name="foobar" id="foo">Foo</label>
    <label><input type="checkbox" name="foobar" id="bar">Bar</label>
    <label><input type="checkbox" name="foobar" id="baz">Baz</label>
  </fieldset>
  <fieldset>
    <legend>Choose Xyzzy</legend>
    <label><input type="radio" name="xyzzy" id="quux">Quux</label>
    <label><input type="radio" name="xyzzy" id="quuz">Quuz</label>
  </fieldset>
  <fieldset>
    <legend>Enter Personal Details</legend>
    <input type="text" placeholder="John Doe" name="name"><br>
    <input type="email" placeholder="[email protected]" name="email"><br>
    <textarea placeholder="Lorem ipsum dolor sit amet…" name="description"></textarea>
  </fieldset>
  <input type="submit" value="Submit">
</form>

The sheet that the above form submits to:

https://docs.google.com/spreadsheets/d/10VHS6bozcdNFYcRskkoONMT8Rt-2CwJ_LJGQWdkTJq4/

The code for the Google Apps Script I’m using:

var sheetName = "Sheet1"
var scriptProp = PropertiesService.getScriptProperties()

function intialSetup() {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  scriptProp.setProperty("key", activeSpreadsheet.getId())
}

function doPost(e) {
  var lock = LockService.getScriptLock()
  lock.tryLock(10000)

  try {
    var doc = SpreadsheetApp.openById(scriptProp.getProperty("key"))
    var sheet = doc.getSheetByName(sheetName)

    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
    var nextRow = sheet.getLastRow() + 1

    var newRow = headers.map(function(header) {
      return header === "timestamp" ? new Date() : e.parameter[header]
    })

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])

    return ContentService.createTextOutput(
      JSON.stringify({ result: "success", row: nextRow })
    ).setMimeType(ContentService.MimeType.JSON)
  } catch (e) {
    return ContentService.createTextOutput(
      JSON.stringify({ result: "error", error: e })
    ).setMimeType(ContentService.MimeType.JSON)
  } finally {
    lock.releaseLock()
  }
}

Tanaike · Accepted Answer · 2019-09-24 12:42:21Z

2

About radio buttons and checkboxes on HTML, you want to identify the checked contents.

If my understanding is correct, how about this modification? Please think of this as just one of several answers.

Modification points:

For the tags of radio button and checkboxes, the attribute of value is added. The value is the same with the text.
Values are retrieved from the property of parameters of the event object of doPost(e).

Modified script:

Please modify your script as follows.

HTML:

From:

<fieldset>
  <legend>Select Foobar</legend>
  <label><input type="checkbox" name="foobar" id="foo">Foo</label>
  <label><input type="checkbox" name="foobar" id="bar">Bar</label>
  <label><input type="checkbox" name="foobar" id="baz">Baz</label>
</fieldset>
<fieldset>
  <legend>Choose Xyzzy</legend>
  <label><input type="radio" name="xyzzy" id="quux">Quux</label>
  <label><input type="radio" name="xyzzy" id="quuz">Quuz</label>
</fieldset>

To:

<fieldset>
  <legend>Select Foobar</legend>
  <label><input type="checkbox" name="foobar" id="foo" value="Foo">Foo</label>
  <label><input type="checkbox" name="foobar" id="bar" value="Bar">Bar</label>
  <label><input type="checkbox" name="foobar" id="baz" value="Baz">Baz</label>
</fieldset>
<fieldset>
  <legend>Choose Xyzzy</legend>
  <label><input type="radio" name="xyzzy" id="quux" value="Quux">Quux</label>
  <label><input type="radio" name="xyzzy" id="quuz" value="Quuz">Quuz</label>
</fieldset>

Google Apps Script:

From:

var newRow = headers.map(function(header) {
  return header === "timestamp" ? new Date() : e.parameter[header]
})

To:

var newRow = headers.map(function(header) {
  return header === "timestamp" ? new Date() : e.parameters[header].join(",");
})

Result:

When above modification points are reflected to your script, the values of the column "foobar" and "xyzzy" become the values of radio button and checkboxes.
- For example, the checkboxes of "Foo" and "Bar" are checked and the radio button of "Quuz" is checked, the values of he column "foobar" and "xyzzy" are "Foo,Baz" and "Quuz", respectively.

If I misunderstood your question and this was not the direction you want, I apologize.

answered Sep 24, 2019 at 12:42

Tanaike

203k12 gold badges126 silver badges222 bronze badges

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

4 Comments

Tzar Over a year ago

Thank you very much! Do you think this is the most elegant way of doing it? If not, could you send me in the better direction?

Tanaike Over a year ago

@Tzar Thank you for replying. I'm glad your issue was resolved. I'm not sure whether this is "the most elegant way". But I think that this way is more suitable for your situation. I would like to comment about the reason I think like this as follows.

Tanaike Over a year ago

@Tzar Although I think that also there is the method for modifying the attribute of name for each tag, in this case, e.parameter[header] is required to be used. If all radio buttons and checkboxes are independent, this way is suitable. But, in your case, you want to identify 2 radio buttons as one group, and want to identify 3 checkboxes as one group. In this case, I thought that to use the attribute of value and e.parameters[header] is more suitable. If my comment was not useful, I apologize.

Tzar Over a year ago

Thanks again for providing very clear answers. You are very kind. Arigato!

Collectives™ on Stack Overflow

Writing HTML form radio and checkbox button selection to Google Sheets

1 Answer 1

Modification points:

Modified script:

HTML:

Google Apps Script:

Result:

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modification points:

Modified script:

HTML:

Google Apps Script:

Result:

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related