Help

Scripting extension to send SendGrid email not resolving fields

Topic Labels: Scripting
3335 8
cancel
Showing results for 
Search instead for 
Did you mean: 
Jean_Thoensen
6 - Interface Innovator
6 - Interface Innovator

Based on the wonderful scripting resources here, I wrote my first script with the Scripting extension to send an email via SendGrid with the user clicks a button field.

The email recipient is stored as a field in the People table in Airtable. The email body text is stored as a long text field in the Email Templates field.

The template contains several fields from the People table to communicate customized information to the recipient.. I surrounded the field names with double curly braces, but they are not being resolved. The sent email is just the raw text.

I think I'm missing something obvious...

The reason for doing this as a script instead of using the SendGrid extension is that the extension sends to all records in a view. I want to have a button on an interface that the user clicks as they go through their workflow. It's not something that can be automated. The button should send the customized email to just that record.

8 Replies 8

Congratulations on writing your first script! Sorry, I don't know much scripting so I can't help you there. Hopefully somebody else will chime in below to help you figure out your script.

Alternatively, I wanted to let you know that you can fully automate this with no scripting at all by using Make's SendGrid integrations. A user can click on a button in Airtable which triggers a custom webhook URL in Make, which then sends the email for only that one record.

There is a small learning curve with Make, which is why I created this basic navigation video to help. I also provide the links to a few other Make training resources there as well. For example, to instantly trigger your Make scenarios from Airtable with a script instead of a button, check out this thread.

p.s. If you have a budget for your project and you’d like to hire an expert Airtable consultant to help you with the Make side of things, please feel free to contact me through my website: Airtable consulting — ScottWorld

Sho
11 - Venus
11 - Venus

If you could just share the source around the fields and the email body, I may be able to figure something out.

I've watched a couple of videos about Webhooks and Make and things aren't clicking for me yet. We have to use SendGrid for sending emails because of IT restrictions.

Thanks for the offer, @Sho. I've pasted some of the code below. My script reads the email body text out of Airtable just fine. It's just that it doesn't populate the values of the fields from the People table. I suspect it may be because the email template is in a different table than the fields themselves, but I don't know how to solve that as a noob.

 

let sourceTable = base.getTable('People');

// Airtable table containing email templates
let emailTemplateTable = base.getTable('Email Templates');

// Find the UID of the email template you want to use and put it in this variable
let emailTemplateRecordID = 'rec0eEyUzNcGYvD9L'

// Find the email template based on its record ID
let queryResult = await emailTemplateTable.selectRecordAsync(emailTemplateRecordID);

// Find the text of the email template within the template record
let emailBodyText = queryResult.getCellValueAsString("Body Text");
console.log(emailBodyText);

let record = await input.recordAsync('Pick a record', sourceTable);
if (record) {
    // get the name, email and message values from the record
    let name = record.getCellValue('Name, First')
    let emailRecipient = record.getCellValue('Email, Primary')

    console.log(name, emailRecipient, emailBodyText)

    // now set some of the values in the data object with the record values
    let data = {
        'personalizations': [
            {
            'to': [
                {
                'email': emailRecipient
                }
            ]
            }
        ],
        'from': {
            'email': SendGridFrom
        },
        'subject': '👋 Hi ' + name,
        'content': [
            {
            'type': 'text/html',
            'value': emailBodyText
            }
        ]
    }

    let options = {
        method: 'POST',
        headers: {
            'Content-Type': 'application/json',
            'Authorization': 'Bearer ' + SendGrid_API    
        },
        body: JSON.stringify(data)
    }
 
    let response = await remoteFetchAsync(SendGrid_URL, options)
    console.log(response)
}

Hi @Jean_Thoensen, Sounds like @Sho will be able to help you out with your script. And if your IT department will let you use Make (Make has very strict security protocols), I can help you get it setup to send your emails through SendGrid. 

Sho
11 - Venus
11 - Venus

For example, if you want to display the name in the body text
Enter <Name> for the name part of "Body Text" in "Template".

And change the code like this

 

// Find the text of the email template within the template record
let emailTemplateBodyText = queryResult.getCellValueAsString("Body Text");

let record = await input.recordAsync('Pick a record', sourceTable);
if (record) {
    // get the name, email and message values from the record
    let name = record.getCellValue('Name, First')
    let emailRecipient = record.getCellValue('Email, Primary')
    let emailBodyText = emailTemplateBodyText.replace("<Name>",name)

 

Try this and see how it works

I have never used Sendgrid, so I do not know how to use Sendgrid's API.
It seems that there is a replacement function.

Thanks for the thoughts, @Sho. However, doing some sort of find/replace for every field in the email body is impractical and error prone. It defeats the purpose of having the template text stored in a table. I've seen screenshots of how this has been done but I'm obviously missing something!

Those partner videos are great, @ScottWorld! Thank you.