Using select multiple to send multiple values to google sheets using google apps script

0
votes

I'm trying to use multiple selector to post data to google sheet using google apps script as follows:

Code.gs:

function doGet() {
return HtmlService.createTemplateFromFile('Form.html').evaluate()
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

function doPost (e) {
var lock = LockService.getScriptLock();
lock.tryLock(10 * 1000);
  
try {
var doc = SpreadsheetApp.getActiveSpreadsheet();
var sheet = doc.getSheetByName("Sheet1");
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 HtmlService.createTemplateFromFile('Confirmation.html').evaluate()
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);

}

catch (e) {
return ContentService
.createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
.setMimeType(ContentService.MimeType.JSON)
}

finally { lock.releaseLock() }

}

Form.html:

<!DOCTYPE html>
<html lang="en">

<body>

<form name="myForm" action="https://script.google.com/macros/s/AKfycbwamMCkOCAmwrRDNj8cTqgp3cevm8B9niyrlYa5dnbxP2H3q0o/exec" target="_self" method="POST">

<div>
<select name="M Values" multiple required>
<option value="" disabled selected>Select Value(s)</option>
<option value="M1">M1</option>
<option value="M2">M2</option>
<option value="M3">M3</option>
<option value="M4">M4</option>
<option value="M5">M5</option>
<option value="M6">M6</option>
</select>
</div>
<button type="submit">Send</button>

</form>

</body>
</html>

The problem is when a user chooses many options for example M1, M3, and M5 and click the submit button, I only receive M1 in the sheet instead of receiving all the selected values like that M1, M3, M5

Sheet: https://docs.google.com/spreadsheets/d/1FdOH2zU_ZnxB2a7RA_QfO3HdPTRhVhSHo5988a8Au9s/edit?usp=sharing

The sheet is editable so please feel free to use. Please any help is very appreciated. Thanks in advance

1
javascriptgoogle-apps-scriptgoogle-sheets
About I tried to use e.postData.contents but it gives me the error: TypeError: Cannot read property 'postData' of undefined, in this case, e is undefined. So I'm worry that you might directly run the function of doPost. How about this? If my guess is not correct, can you provide the detail flow for replicating your issue of TypeError: Cannot read property 'postData' of undefined? By this, I would like to confirm it. - Tanaike
"So I'm worry that you might directly run the function of doPost." Yes I do, why wouldn't you recommend that? "can you provide the detail flow for replicating your issue" I just added the logger as indicated in the example code but I think I didn't understand the documentation in how to post an array of values in a single cell - MBay
Thank you for replying. From "So I'm worry that you might directly run the function of doPost." Yes I do, why wouldn't you recommend that?, the reason of your issue could be found. In your script, it seems that Web Apps is used. So please run the script with the Web Apps. developers.google.com/apps-script/guides/web - Tanaike
Thank you for your interest and effort. I red the documentation and I have no problem in deploying the web app and receiving the inputs in the sheet. The problem is when I select many options from the multiple selector for example M2, M4, and M6 I only receive M2 in the sheet instead of receiving the full array like that M2, M4, M6 - MBay
You can try from here script.google.com/macros/s/… and see the results in Sheet1 in the spreadsheet shared in the question - MBay

1 Answers

2
votes

I believe your goal as follows.

  • You want to put all values of multi selected values to the cell.

I think that the reason of your new issue is due to e.parameter[header]. In this case, e.parameter is only one value in the selected values. So please modify as follows.

From:

return header === 'Timestamp' ? new Date() : e.parameter[header]

To:

return header === 'Timestamp' ? new Date() : e.parameters[header].join(",");

Note:

  • When you modified the script of Web Apps, please redeploy the Web Apps as new version. By this, the latest script is reflected to the Web Apps. Please be careful this.

Reference: