I want to send emails via SendGrid. this is to be done through an automation baed on a trigger of when a record enters a view, like this:
The email text contains many personalisations, pulled from the database. Some of these fields are lookup fields from other databases. Below are all the personalised fields:
And here is the script:
// Get your SendGrid API key (store securely)
const SENDGRID_API_KEY = "-KEYisHERE-";
// Get the record that triggered the automation
let record = input.config();
// Extract values from arrays BEFORE building email
let firstName = Array.isArray(record.firstName) ? record.firstName[0] : record.firstName;
let projectLeadName = Array.isArray(record.projectLeadName) ? record.projectLeadName[0] : record.projectLeadName;
let task = Array.isArray(record.task) ? record.task[0] : record.task;
// Debug - verify values
// console.log("Project Lead Name:", projectLeadName);
// console.log("Task:", task);
// FOR TESTING
// console.log("All record data:", record);
// console.log("Type:", typeof record.projectLeadName[0]);
// console.log("Type:", typeof record.task);
// console.log(record.projectLeadName[0])
// Prepare the email
let emailData = {
personalizations: [{
to: [{ email: Array.isArray(record.email) ? record.email[0] : record.email}],
subject: `[ID: ${record.applicationID}] Future Impact Group: Invite to Submit Project Assessment`
}],
from: {
email: "luke@futureimpact.group",
name: "Luke from FIG"
},
reply_to: {
email: "info@futureimpact.group",
name: "Future Impact Group"
},
content: [{
type: "text/html",
value:
`<p>Hi ${record.firstName}!</p>
<p>Thanks for your interest in the Winter 2025 FIG Fellowship. Now it's time to answer a specific question about the project you applied for!</p>
<p>In this email you'll find:</p>
<ul>
<li>project description</li>
<li>a description of the task you should complete</li>
<li>your application ID, <strong>specific to this project</strong></li>
<li>a link to a form where you can submit your answer.</li>
</ul>
<p>If you registered interest to participate in more than one project you'll receive a separate email for each project, <strong>each with a different task and application ID.</strong></p>
<p>❗️ Please <strong>submit all your answers by midnight on Oct 19, Anywhere on Earth</strong> to allow the FIG team and the project lead enough time to evaluate it.</p>
<br>
<h3>DESCRIPTION</h3>
<p>As a reminder, here's the description of project you applied for: ${record.projectTitle} with ${record.projectLeadName}.</p>
<br>
<h3>TASK</h3>
<p>The task we have for you is below.</p>
<p>${record.task}</p>
<p>Answering should take you ~30 minutes. You can do so directly in the form, or write elsewhere and copy-paste into the form.</p>
<br>
<h3>APPLICATION ID</h3>
<p>Lastly—and <strong>very importantly</strong>!—your application ID for this project is below. You'll be asked to write it in the submission form.</p>
<h2>${record.applicationID}</h2>
<h3>Submit your answer ➡️ <a href="https://airtable.com/appvWAOurdQ3Y6kFm/pagqERyUofyUmvcbt/form">here</a>!</h3>
<p>Thanks again for taking the time to apply and best of luck!</p>
<p>Luke Dawes<br>
`
}]
};
// Send via SendGrid API
let response = await fetch("https://api.sendgrid.com/v3/mail/send", {
method: "POST",
headers: {
"Authorization": `Bearer ${SENDGRID_API_KEY}`,
"Content-Type": "application/json"
},
body: JSON.stringify(emailData)
});
if (response.status === 202) {
console.log("✅ Email sent successfully!");
} else {
console.log("❌ Error:", response.status, await response.text());
}When I test the email in the SG scripting window it works well. By this I mean:
- the email is delivered, AND
- the text shows all the personalised values.
However, when the automation is turned on it changes. The emails get delivered, but not all the personalised values appear in the email text. Namely `projectLeadName` and `task` do not appear.
Claude tells me that Airtable automatically flattens some values in testing, a process that’s not repeated in deployment. If true, this is frustrating.
I’d appreciate suggestions on:
- how this script can be changed such that al values appear in the email;
- what can/should I be checking to look for the cause of this error (assume we can’t rely on test outputs of running this script)
If you have any questions that would help you answer the above, let me know.
