I have a Google form where I’m attempting to run a charity raffle. One of the questions is a multiple choice: 1 Raffle Ticket – $20 and the other option is: 3 Raffle Tickets – $50. If someone selects “3 Raffle Tickets” and submits the form, I need 3 corresponding entries in my Google Sheet for the number of tickets they purchased.

I tried using AI to create the Apps Script, but have been unsuccessful. Here is what I currently have:

function onFormSubmit(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  var values = sheet.getRange(lastRow, 1, 1, sheet.getLastColumn()).getValues()[0];
  
  var ticketChoice = values[4]; // Assuming ticket choice is in column E
  
  if (ticketChoice.includes("3 Raffle Tickets")) {
    // Add two more rows for 3 Raffle Tickets
    for (var i = 0; i < 2; i++) {
      sheet.appendRow(values);
    }
  }
  // Do nothing for 1 Raffle Ticket as it's already added by form submission
}

I also added a Trigger for this Apps Script:

• Choose which function to run: onFormSubmit
• Choose which deployment should run: Head
• Select event source: From spreadsheet
• Select event type: On form submit

In testing the form, here is what’s happening. When I select the “3 Raffle Tickets” option, it appears to work:
Test with 3 Raffle Tickets option

However, in my next test I select the “1 Raffle Ticket” option, and here’s what happens:
Test with 1 Raffle Ticket option

As you can see, the original entries for 3 tickets is there. However, when I submitted the 1 Rafflet Ticket option, it was added, but 2 other entries were added for the original 3 Ticket entry. Any help in resolving this would be greatly appreciated so we can launch this charity raffle on time. Thank you!

I’m not a scripting expert, so I tried using Perplexity AI to get a working script (see above), but it’s been unsuccessful.

I’m expecting that when someone buys a “3 Raffle Tickets” option, 3 entries are added to the Google Sheet and when someone buys a “1 Raffle Ticket” option, 1 entry is added to the sheet. People can have multiple entries.

UPDATE: I have the script working now and wanted to share it so anyone could benefit from it.

function onFormSubmit(e) {
  try {
    var sheet = e.range.getSheet();
    var ticketColumnIndex = 5;  // Column E = 5
    
    var ticketOption = e.values[ticketColumnIndex - 1].trim();
    Logger.log("Value retrieved from column: " + ticketOption);
    
    var numTickets = parseInt(ticketOption.match(/d+/)[0], 10);
    Logger.log("Number of tickets: " + numTickets);
    
    var lastRow = sheet.getLastRow();
    var rowData = e.values;
    
    // Always add the original entry to the end
    sheet.getRange(lastRow + 1, 1, 1, rowData.length).setValues([rowData]);
    
    // Add additional entries for multiple tickets
    if (numTickets > 1) {
      for (var i = 1; i < numTickets; i++) {
        lastRow = sheet.getLastRow();
        sheet.getRange(lastRow + 1, 1, 1, rowData.length).setValues([rowData]);
      }
      Logger.log("Entry successfully duplicated " + (numTickets - 1) + " times and grouped at the end.");
    } else {
      Logger.log("Single ticket entry added at the end.");
    }
    
    // Remove the original entry added by the form
    sheet.deleteRow(e.range.getRow());
    
    // Remove any rows with "No Match" in column A
    var dataRange = sheet.getDataRange();
    var values = dataRange.getValues();
    for (var i = values.length - 1; i >= 0; i--) {
      if (values[i][0].toString().startsWith("No Match")) {
        sheet.deleteRow(i + 1);
      }
    }
    
  } catch (error) {
    Logger.log("Error during form submission processing: " + error);
  }
}