How to set recipient name and email based on Google Sheets scenarios

[FMurad]

I'm looking to build a rather complex automation, and was wondering if it was possible.  I have a Google Sheets worksheet with some "scenarios."  It contains scenarios 1-20 under a Scenarios column.

Each scenario is determined by the availability, and category of different types of contact information (contact type, contact seniority, job title, interal/external, etc...)

There is then an Action for each scenario.  So for example, if Scenario 1, then Action is to email a specific contact.

If Scenario 2, then email different contact.

Let's say I'm reading a report, and the report mentions multiple contacts, their emails, company name and other information.  I will extract this information.  Then, emails should be sent to different recipients, based on which type of information is available (or not) in the report.

I want to automate this.  My thinking is that this can be done with custom properties and workflows, but I'm a little lost.  My guess is that somehow custom properties can be matched to columns in Google Sheets.

I hope this makes sense.  Basically I want to automatically set who will recieve a campaign based on pre-set parameters from a Google spreadsheet.

[h-recker]

I love this workflow, thanks for sharing! I imagine you have everything worked out now? Would love to hear what you landed on.

You're essentially building a conditional routing system for email outreach, based on a decision matrix in Google Sheets. 

Goal: Automatically send emails to specific recipients based on scenario logic defined in a Google Sheet, where each scenario depends on the presence or absence of certain contact info.

Step 1: Structure your Google Sheet properly

It sounds like you already have this. Just make sure it includes a scenario field, availability, contact type, job title, category, and email at a minimum, plus anything else you might have added to your rules engine.

Step 2: Create a Lookup System

You’ll need to match the current contact/report data against this sheet to determine which scenario applies. There are a few ways to do this:

• Use Google Sheets formulas (like VLOOKUP, FILTER, or INDEX/MATCH) to find the matching scenario and return the right email contact to send to.

• Or use Apps Script to automate this lookup if you’re doing it in bulk.

Step 3: Trigger the Email

Now you need a system that:

1. Reads the determined recipient from your lookup result

2. Sends the correct email

You’ve got a few options:

Option A: Google Sheets + Apps Script

• You can use Google Apps Script to read your report data, match it against the scenario table, and send emails via Gmail.

• This gives you full flexibility: dynamic recipients, conditional content, templates, and even time-based triggers.

Perfect if you’re comfortable with light scripting.

Option B: Google Sheets + Make (Integromat)

• Make allows you to watch for changes or new rows in Google Sheets

• Then use filters/routers to match the right scenario and send emails based on that logic

• It’s more visual, less code, and good for branching workflows

Option C: Google Sheets + Coefficient + HubSpot (if you're sending through HubSpot workflows)

If you’re already using HubSpot for email:

1. Use Coefficient from HubSpot's marketplace (a 2-way sync between HubSpot and Google Sheets / Excel) to bring the scenario output from Google Sheets into a custom property on the Contact record.

2. Have another custom property store the “email address to send to”, based on that scenario.

3. Create a HubSpot workflow that sends the email to the contact email stored in that custom field.

Here’s a walkthrough on syncing data like this from Sheets to HubSpot:

[kgeddon]

Setting recipient names and emails from Google Sheets can be streamlined with scripts. Using Apps Script to automate this process based on scenarios is super efficient.

[LautaroDelPlato · Accepted solution]

Hi @FMurad 

You can import your Excel document into HubSpot by following the import steps below and adding the actions in a multiple selection dropdown property within the tool.

This way you will then be able to filter the contacts you want to perform different actions on or include in a workflow.

Regards,

[FMurad]

Hi @LautaroDelPlato!

So, if I understand this correctly, you import the excel sheet, then map each "scenario" in the sheet to a multiple selection dropdown property?

I think what I'm struggling to visualize is how do I match the options in the dropdown property to the different parameters in the spreadsheet?

The flow:

1. Report is analyed, data is extracted
2. Extracted data is added to the spreadsheet with automation (Zapier maybe?)
3. Extracted data is analyzed and matched to a scenario.
4. Each scenario has an action, so if the extracted data is assigned "scenario 1" the action for that scenario is to only contact the brand contact from the report, and not the media contact.
5. Automatically set the recipient(s) name and email in Hubspot campaign based on the scenario that was assigned.  

I really hope all of this makes sense.  Thanks for your help!

[LautaroDelPlato]

Hi @FMurad !

I recommend having the data within HubSpot, as this simplifies management and process automation. If importing the data directly isn’t possible, you can explore options like HubSpot Marketplace integrations or developing a custom integration to transfer data from your spreadsheet to HubSpot.

Once the data is in HubSpot, you can use a multiple-selection property to assign more than one value to each record based on the defined scenarios. This allows you to set up workflows that trigger automatically depending on the assigned values. For example:

1. Multiple-Selection Property: Each "scenario" can be mapped as an option within the property. This lets you assign multiple scenarios to a contact, deal, or other HubSpot object based on the analyzed information.

2. Automated Workflow: Set up a workflow that triggers based on the selected values in the property. For instance, if a contact is assigned "Scenario 1," the workflow can:

   • Filter the recipients to include only the brand contact from the report and exclude the media contact.
   • Automatically set the recipient's name and email in the HubSpot campaign.

Regards!

[Akash_Vi_R]

Hey @FMurad - if you're looking at third-party tools to pull in data from Sheets to Hubspot (you should totally do this if the Sheets data is going to be dynamic), check out Superjoin.

Integration is pretty neat and there's 2-way data sync.

[DianaGomez]

Hi @FMurad,

Thanks for reaching out to the Community!

I’d like to invite our experts to join this conversation so they can share if they’ve done something similar and how they do, it to give you some inspiration.

Hi @Lucila-Andimol, @JuanGomez0818, @Gilempert, @LautaroDelPlato, would you happen to have any suggestions on how @FMurad could achieve their goal?

Thanks for sharing 🧡

Wishing everyone an excellent day!

Diana