How to access Google Form key-pair values with the same order as the questions in the form-CodePudding

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):

Class
Teacher
Student ID
Given Name
Family Name etc.

And here's an example notification email, with the questions in random order:

This student is causing concern in the following area/s:: lack of progress, not completing class work/refusal to participate, Camera is often off
Given Name: John
Class : Level ABC
Student ID: 1234
Teacher: JF
Teacher comment: ...
teacher action to date (N/A if student is absent): ...
result of action: ...
Timestamp: 9/14/2021 9:06:58
Family Name: SMITH
Preferred Name: Johnny

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.

CodePudding user response：

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.

CodePudding user response：

Thanks to doubleunary for the solution. Here's the working code:

function onFormSubmit(e) {
//access the values only in original order
  var values = e.values;
  var vlen = values.length;
//access the headers from the response sheet (i.e. questions)
  var fields = SpreadsheetApp.getActive().getRange('sheetName!A2:K2').getValues()[0];
  var i;
  var htmlBody = '<ul>';
//loop through headers   values to build HTML list
  for (i=0;i<vlen;i  ) {
    
    var label = fields[i];
    var data = values[i];
    htmlBody  = '<li>'   label   ": "   data   '</li>';
    
  };
  
  htmlBody  = '</ul>';
  var recipientsTO =  "someoneelse1@xyz" "," "someoneelse2@xyz";
  
  MailApp.sendEmail({
    to:"me@xyz",
    bcc:recipientsTO,
    subject: "Language Division RIC",
    htmlBody: htmlBody
  })
}