1

First, shoutout to NEWAZA for writing this amazing code!

Secondly, please refer to the StackOverflow link/question.

Google Apps Script to automatically duplicate a row the number of times a comma appears in a column keeping basic info?

 function onSubmit(e) {
  Logger.log(JSON.stringify(e.values))
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(`onSubmit`)
  const [timestamp,emailAddress,companyName,_,phoneNumber,firstName,lastName,locations,city,state,zipCode,dateRequested,...rest] = e.values;
  locations.split(`,`).forEach(i => sheet.appendRow([firstName, lastName, i.trim(), city, state, zipCode, dateRequested]))
}

Finally, I am getting an error in the code. Also, if I wanted to add new columns or questions to the Google form would that be as simple as just add the header's text with the other constants in the script?

JM Gelilio
  • 3,482
  • 1
  • 11
  • 23
Kevin Nichols
  • 25
  • 1
  • 7
  • You cannot run the code from the script editor. It requires the trigger from the onFormSubmit in order to populate the event object – Cooper May 26 '22 at 20:51
  • You can add new columns or questions to the new form with minor modifications to the code. Provide a specific request and we can assist you – Cooper May 26 '22 at 20:59

1 Answers1

2

Generally, the error you're receiving indicates there was no e or event object passed to the function. My guess is you tried to run this function outside of a form response, which is required by design. (The function utilizes values from a form response, no form response - no values).

The best upkeep for this code in regards to adding/deleting questions will be to make sure each constant/variable corresponds to the correct value in the form response.

A handy trick to see each index, question and answer in a form response is to add the following snippet into the top of your onSubmit() function:

function onSubmit(e) {

  Object.entries(e.namedValues).forEach(([key, value], index) => {
    Logger.log(`[${index}] "${key}" : "${value}"`) 
  })

  // Rest of code goes here.

}

After submitting a response, you can check the Execution Logs and view how your constants/variables should align to.

Essentially, your constants/variables must be equal to the response answers.

Hope this helps! If I left anything out, or you need further explanation, just let me know.

NEWAZA
  • 1,536
  • 2
  • 4
  • 19