Skip to main content

Scripting extension to send SendGrid email not resolving fields

J
Forum|alt.badge.img+11

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

ScottWorld
Forum|alt.badge.img+33
  • ScottWorld
  • Brainy
  • 8805 replies
  • August 8, 2023

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

J
1 person likes this
  • J
    Jean_Thoensen
S
Forum|alt.badge.img+19
  • Sho
  • Inspiring
  • 560 replies
  • August 9, 2023

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

J
1 person likes this
  • J
    Jean_Thoensen
J
Forum|alt.badge.img+11
  • Jean_Thoensen
  • Author
  • Known Participant
  • 20 replies
  • August 9, 2023
ScottWorld wrote:

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


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.

ScottWorld
1 person likes this
  • ScottWorld
    ScottWorld
J
Forum|alt.badge.img+11
  • Jean_Thoensen
  • Author
  • Known Participant
  • 20 replies
  • August 9, 2023
Sho wrote:

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


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)
}
ScottWorld
Forum|alt.badge.img+33
  • ScottWorld
  • Brainy
  • 8805 replies
  • August 9, 2023
Jean_Thoensen wrote:

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.


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. 

S
Forum|alt.badge.img+19
  • Sho
  • Inspiring
  • 560 replies
  • August 9, 2023

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.

J
1 person likes this
  • J
    Jean_Thoensen
J
Forum|alt.badge.img+11
  • Jean_Thoensen
  • Author
  • Known Participant
  • 20 replies
  • August 9, 2023
Sho wrote:

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!

J
Forum|alt.badge.img+11
  • Jean_Thoensen
  • Author
  • Known Participant
  • 20 replies
  • August 9, 2023
ScottWorld wrote:

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


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

ScottWorld
1 person likes this
  • ScottWorld
    ScottWorld

Reply


Most helpful members this week

Terms & Conditions