Can you write a Google Apps Script that can duplicate a row the number of times a comma appears in a column? I have a Google Form that's for scheduling appointments for blower door testing and sometimes there are multiple addresses for one appointment. However, I need each address to be its own entry with the same base info(first name, last name, company name, date requested, etc..)? This is for a Google Form linked to a Google sheet with responses.

1. onSubmit:

Name Company Date Requested Address John Doe XYZ 05/30/2022 Lot 1, Lot 2, Lot 3

2. What the script needs to do automatically for all new responses :

Name Company Date Requested Address John Doe XYZ 05/30/2022 Lot 1 John Doe XYZ 05/30/2022 Lot 2 John Doe XYZ 05/30/2022 Lot 3

3. What I tried: functions/formulas, macro reader, power tools...none of these accomplished automation...I used TRANSPOSE(SPLIT(H:H,",")...recorded a macro (trash), and power tools just wanted money..

CodePudding user response：

Try:

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

}

With this, you have some play on how you would like to structure your entries; as of right now, for each location listed in the H column, it will add a new row on your specified sheet in the format you've given in your example.