Need help with dynamic CC addresses in email script
I’m working on an automated email system for a catering business with multiple branches. We have a Google Form that feeds data into a Google Sheet, and there’s a script that sends emails when new entries are added.
Right now the script works fine for sending emails to a fixed list of recipients. However, I want to add a CC field that pulls the email address from a specific column in the same row of data. Each catering order is for a different location, so the CC email should be different for each new form submission.
The current setup sends emails to 4 static recipients, but I need the CC to be dynamic based on the location column in the spreadsheet. The script reads data by referencing row numbers even though the data is organized in columns.
I tried adding a CC parameter similar to how recipients are handled, but that only works for static addresses. I need it to read from the sheet data for each new row that gets created.
Has anyone dealt with similar email automation where CC recipients need to be pulled from spreadsheet cells rather than hardcoded?
hey, i totally get you! for dynamic CCs, try using sheet.getRange(row, ccColNum).getValue() for that cc email. also, don’t forget to validate them before sending to avoid any crashes! good luck!
I’ve dealt with this exact same automation issue in event management. The trick is mapping your column reference correctly when you’re processing row data. First, figure out which column has your location emails - something like var locationEmailCol = 5 for column E. When processing new form submissions, grab that cell value with sheet.getRange(currentRow, locationEmailCol).getValue(). Don’t forget to trim whitespace and verify the cell actually has an email before adding it to your CC parameter. Here’s a gotcha I hit - empty cells return null values and break the email function. Always validate your CC field exists and looks like a real email address before sending.
I did something similar for our company’s automated invoicing system. Just grab the CC email from your data row the same way you’re already pulling other fields. When processing each row, store the CC email in a variable like var ccEmail = data[ccColumnIndex] where ccColumnIndex matches your location email column. Then pass that variable to your GmailApp.sendEmail function using the cc parameter. Don’t forget to validate the CC field has a real email address first - you’ll get errors otherwise. Also think about what happens if the cell’s empty. You might want a default CC or just skip it.