I use the following onSubmit(e) script running in a Google Sheet to access key-pair values from a Google Form submission. They are then put in an HTML list and emailed. So far so good. Every time the form is submitted, I receive an email notification with the questions and responses listed on rows.
Problem: the order of the pairs is random, and changes with each submission. I want it to be in the same order as the questions on the form, top to bottom.
Here's the basic working script (minus my details):
function onFormSubmit(e) {
var values = e.namedValues;
var sendFrom = '';
var ccEmail = '';
var subject = '';
var htmlBody = '<ul>';
for (Key in values) {
var label = Key;
var data = values[Key];
htmlBody += '<li>' + label + ": " + data + '</li>';
};
htmlBody += '</ul>';
MailApp.sendEmail(sendFrom, subject, htmlBody, {
htmlBody: htmlBody,
cc: ccEmail,
name: 'Automated Email',
noReply: true
});
}
Here's the actual question order in the form (first 5 questions):
And here's an example notification email, with the questions in random order:
I'm guessing that the namedValues object is created asynchronously or something? Is there anyway to lock it down to match the order on the form, or access the order of questions on the form first and then map to that? Obviously don't want to be hardcoding the keys here, one edit on the form and it's broken...
Bit beyond my level of scripting I'm afraid. Any help appreciated!
This question was posted two years by someone else, but there are no answers on that post.
Use the e.values
array instead of the e.namedValues
object. In the array, values appear in the same order as they are stored in the spreadsheet. To get the field names, use SpreadsheetApp.getActive().getRange('Form Responses 1!A1:1').getValues()[0]
.
See the event objects help page.