Raffle Ticket Form

[Folami Akinrimisi]

Hi All, 

I am working on developing an electronic raffle ticket form. I have worked on it but keep getting an error when I run it. Is there anything I am getting wrong?

function onEdit(e) {

  var sheetName = "Electronic Raffle Ticket";

  var sheet = e.source.getSheetByName(sheetName);

  var range = e.range;

  // Columns (1-based, because range.getColumn() is 1-based)

  var verifiedCol = 11;             // K

  var confirmedTicketsCol = 12;     // L

  var codesSentCol = 13;            // M

  var emailCol = 10;                // J

  var uniqueCodesCol = 14;          // N

  // Only run if editing in correct sheet and correct column

  if (sheet.getName() === sheetName && range.getColumn() === verifiedCol) {

    var row = range.getRow();

    var verified = sheet.getRange(row, verifiedCol).getValue();

    var confirmedTickets = sheet.getRange(row, confirmedTicketsCol).getValue();

    var codesSent = sheet.getRange(row, codesSentCol).getValue();

    var email = sheet.getRange(row, emailCol).getValue();

    Logger.log("Row: " + row + ", Verified: " + verified + ", Tickets: " + confirmedTickets + ", CodesSent: " + codesSent + ", Email: " + email);

    if (verified == "Yes" && codesSent != "Yes" && confirmedTickets > 0 && email) {

      var codes = [];

      for (var j = 0; j < confirmedTickets; j++) {

        codes.push(generateCode());

      }

      // Write codes into Unique Codes column

      sheet.getRange(row, uniqueCodesCol).setValue(codes.join(", "));

      // Send email to user

      MailApp.sendEmail({

        to: email,

        subject: "Your Ticket Codes",

        body: "Thank you! Here are your ticket codes:\n\n" + codes.join("\n")

      });

      // Mark as sent

      sheet.getRange(row, codesSentCol).setValue("Yes");

      Logger.log("Codes sent: " + codes.join(", "));

    }

  }

}

// Helper to generate a 6-character unique code

function generateCode() {

  return Math.random().toString(36).substring(2, 8).toUpperCase();

}

[AliceKeeler Keeler]

I plugged this into Google Gemini code Gem

Let's get this script working for you! This Google Apps Script is designed to automate sending raffle codes when a row is marked as "Verified". Here's a breakdown of common issues and how to troubleshoot them, along with some improvements.

---

Understanding the Script's Purpose

This script runs automatically when a cell is edited in your Google Sheet. Specifically, it checks if:

1. The edit is made on the "Electronic Raffle Ticket" sheet.

2. The edit is in Column K (the "Verified" column).

If these conditions are met, it then checks if:

• The "Verified" column (K) for that row is "Yes".

• The "Codes Sent" column (M) is not "Yes".

• The "Confirmed Tickets" column (L) has a value greater than 0.

• The "Email" column (J) has an email address.

If all these conditions are true, the script will:

1. Generate a number of unique codes based on the "Confirmed Tickets" value.

2. Write these codes into the "Unique Codes" column (N) of that row.

3. Send an email with these codes to the email address in Column J.

4. Mark the "Codes Sent" column (M) as "Yes" to prevent duplicate emails.

---

Common Issues and Troubleshooting Steps

Here are the most common reasons why your onEdit function might not be working as expected, along with how to fix them:

1. Authorization Issues 🔒

The most frequent problem with Google Apps Script, especially when sending emails or accessing external services, is lack of authorization. When you run a script that performs actions like sending emails for the first time, Google requires you to grant it permission.

• How to fix:

  1. Open your Google Sheet.

  2. Go to Extensions > Apps Script.

  3. In the Apps Script editor, find any function (you can just select onEdit from the dropdown, though you can't run it directly) and click the "Run" button (looks like a play icon ▶️).

  4. A pop-up will appear asking you to "Review permissions". Click on it.

  5. Select your Google account.

  6. You'll likely see a warning that "Google hasn't verified this app." This is normal for personal scripts. Click on "Go to [Project Name] (unsafe)" at the bottom.

  7. Finally, click "Allow" to grant the necessary permissions.

  Once authorized, the onEdit function should run automatically when triggered by an edit.

2. Incorrect Trigger Type ⚡

The onEdit(e) function is a special simple trigger that runs automatically when a user manually changes a value in a spreadsheet. However, simple triggers have limitations (e.g., they cannot send emails directly without prior authorization or cannot run if the edit was made by another script).

• Consider an Installable Trigger: If the simple onEdit trigger isn't consistently working (e.g., you're sharing the sheet, or other scripts are making changes), you might need an installable onEdit trigger. This type of trigger runs under your authorization and has fewer restrictions.

  1. In the Apps Script editor, on the left sidebar, click on "Triggers" (the clock icon ⏰).

  2. Click "+ Add Trigger" in the bottom right.

  3. For "Choose which function to run", select onEdit.

  4. For "Choose event source", select "From spreadsheet".

  5. For "Choose event type", select "On edit".

  6. Click "Save". You will likely need to re-authorize the script as described in point 1.

3. Case Sensitivity and Typos ✍️

Google Apps Script is case-sensitive, and even small typos can prevent your script from working.

• sheetName: Ensure the sheetName variable ("Electronic Raffle Ticket") exactly matches the name of your sheet in Google Sheets, including spaces and capitalization.

• Column Headers/Values: Double-check that the values you're comparing (e.g., "Yes") exactly match what's in your sheet. For instance, if your sheet has "yes" (lowercase) but your script checks for "Yes" (uppercase), it won't match.

4. Debugging with Logger.log() 🐞

Your script already uses Logger.log(), which is excellent for debugging!

• How to use: After making an edit in your sheet that should trigger the script:

  1. Go to Extensions > Apps Script.

  2. In the Apps Script editor, click on "Executions" (the list icon 📋) on the left sidebar.

  3. You'll see a list of script runs. Click on the most recent onEdit execution.

  4. The "Log" section will show you the output of your Logger.log() statements. This will tell you if the script is running, what values it's reading (e.g., Verified, Tickets, CodesSent, Email), and which if conditions are being met or skipped.

  The log Logger.log("Row: " + row + ", Verified: " + verified + ", Tickets: " + confirmedTickets + ", CodesSent: " + codesSent + ", Email: " + email); is particularly useful. Check the values displayed here to ensure they are what you expect.

5. Spreadsheet Structure 📊

Confirm that your columns are correctly set up as expected by the script:

• Column K: "Verified" (where you'll be typing "Yes")

• Column L: "Confirmed Tickets" (should contain a number like 1, 2, 3...)

• Column M: "Codes Sent" (will be marked "Yes" by the script)

• Column J: "Email"

• Column N: "Unique Codes" (where generated codes will appear)

---

Code Improvements and Best Practices

Here are a few suggestions to make your script more robust and readable:

JavaScript

function onEdit(e) { // Define sheet and column names for clarity and easier modification var sheetName = "Electronic Raffle Ticket"; var sheet = e.source.getSheetByName(sheetName); // Exit if the edited sheet is not the target sheet if (!sheet || sheet.getName() !== sheetName) { return; } var range = e.range; var editedColumn = range.getColumn(); var editedRow = range.getRow(); // Define column indices using a more readable approach (0-based for arrays, 1-based for getColumn()) // It's often clearer to use constants for column numbers const VERIFIED_COL = 11; // K const CONFIRMED_TICKETS_COL = 12; // L const CODES_SENT_COL = 13; // M const EMAIL_COL = 10; // J const UNIQUE_CODES_COL = 14; // N // Only proceed if the edited column is the 'Verified' column if (editedColumn === VERIFIED_COL) { // Get all relevant values from the edited row to minimize calls to `getRange().getValue()` var rowValues = sheet.getRange(editedRow, 1, 1, sheet.getLastColumn()).getValues()[0]; // Access values using their column index (adjusting for 0-based array index) var verified = rowValues[VERIFIED_COL - 1]; var confirmedTickets = rowValues[CONFIRMED_TICKETS_COL - 1]; var codesSent = rowValues[CODES_SENT_COL - 1]; var email = rowValues[EMAIL_COL - 1]; Logger.log("Row: " + editedRow + ", Verified: " + verified + ", Tickets: " + confirmedTickets + ", CodesSent: " + codesSent + ", Email: " + email); // Check conditions using strict equality (===) where appropriate if (verified === "Yes" && codesSent !== "Yes" && confirmedTickets > 0 && email) { var codes = []; for (var j = 0; j < confirmedTickets; j++) { codes.push(generateCode()); } // Prepare an array of values to write to avoid multiple `setValue` calls var valuesToWrite = []; valuesToWrite[UNIQUE_CODES_COL - 1] = codes.join(", "); valuesToWrite[CODES_SENT_COL - 1] = "Yes"; // Write codes and mark as sent in a single operation for efficiency // Determine the start column and number of columns to write var startCol = Math.min(UNIQUE_CODES_COL, CODES_SENT_COL); var endCol = Math.max(UNIQUE_CODES_COL, CODES_SENT_COL); var numCols = endCol - startCol + 1; // Create a 2D array for `setValues` var updateRangeValues = []; var rowToUpdate = []; for(let i = startCol; i <= endCol; i++) { if (i === UNIQUE_CODES_COL) { rowToUpdate.push(codes.join(", ")); } else if (i === CODES_SENT_COL) { rowToUpdate.push("Yes"); } else { rowToUpdate.push(rowValues[i - 1]); // Keep existing value if not being updated } } updateRangeValues.push(rowToUpdate); sheet.getRange(editedRow, startCol, 1, numCols).setValues(updateRangeValues); // Send email to user try { MailApp.sendEmail({ to: email, subject: "Your Raffle Ticket Codes", // Slightly more descriptive subject body: "Hello,\n\nThank you for your participation! Here are your unique raffle ticket codes:\n\n" + codes.join("\n") + "\n\nGood luck!" }); Logger.log("Email sent to: " + email + " with codes: " + codes.join(", ")); } catch (error) { Logger.log("Error sending email to " + email + ": " + error.toString()); // Optionally, you could set a specific cell to "Email Failed" if needed } } } } // Helper to generate a 6-character unique code function generateCode() { // Using a more robust method to ensure uniqueness (though still not guaranteed across massive scale) // For truly unique codes, you might want to check against existing codes in the sheet. return Math.random().toString(36).substring(2, 8).toUpperCase(); }

---

Key Improvements in the Refactored Code:

• Early Exit: The script now checks the sheet name at the very beginning and uses return; if it's not the target sheet. This makes the code more efficient by avoiding unnecessary operations.

• Constants for Columns: Using const for column numbers (VERIFIED_COL, etc.) makes the code more readable and easier to maintain.

• Batch Reading: sheet.getRange(editedRow, 1, 1, sheet.getLastColumn()).getValues()[0]; reads the entire row's data in one go, then you access specific values from that array. This is much more efficient than calling sheet.getRange().getValue() multiple times, reducing the number of calls to the Google Sheets API.

• Batch Writing (setValues): Instead of setValue twice, the improved code attempts to update both the UNIQUE_CODES_COL and CODES_SENT_COL in a single setValues call. This is a significant performance improvement. It involves figuring out the range that encompasses both columns and constructing a 2D array for setValues.

• Error Handling for Email: A try...catch block around MailApp.sendEmail will help you identify if email sending fails and why, logging the error.

• Strict Equality (===): Using === instead of == is generally a good practice in JavaScript as it checks both value and type, preventing unexpected type coercion issues.

Try implementing these suggestions, and use the Executions log in Apps Script to pinpoint exactly where the script might be stopping or behaving unexpectedly. Let me know what you find in the logs!

[Cayden Boahene]

Hello,

Thanks for the breakdown—super helpful! Just one quick thing: could you clarify who all the people mentioned in the message are? I want to make sure I fully understand the context before moving forward.

Appreciate your help!

Best, 

Cayden

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/934c4cf5-092f-4f65-8320-42b7cfbc9235n%40googlegroups.com.

[Robyn E.]

Is this something sent by Cory?

Sent with Spark

[Folami Akinrimisi]

Thanks all. I have been able to resolve this, and it works perfectly.