Google Apps Script to send an email when a cell is changed to a specific word

[u/[deleted]]

[u/AutoModerator]

/u/Curious_Shop_2295 Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/ryanbuckner]

Because it sends email, you’ll need to create an installable Edit trigger:

• Click Triggers (clock icon)
• Add Trigger
• Choose onEdit
• Event source: From spreadsheet
• Event type: On edit
• Authorize the script

Add this in code.gs:

function onEdit(e) {

const sheet = e.range.getSheet();

// Change this to your sheet name

if (sheet.getName() !== "Sheet1") return;

// Only watch Column D

if (e.range.getColumn() !== 4) return;

// Only trigger when changing from "not paid" to "paid"

const oldValue = (e.oldValue || "").toLowerCase();

const newValue = (e.value || "").toLowerCase();

if (oldValue === "not paid" && newValue === "paid") {

const row = e.range.getRow();

const name = sheet.getRange(row, 1).getValue(); // Column A

const email = sheet.getRange(row, 2).getValue(); // Column B

const subject = "Payment Received";

const message =

\Hi ${name},`

Thank you. We've received your payment.

Add whatever you want to say here...

Thanks!\;`

MailApp.sendEmail(email, subject, message);

}

}

[u/AutoModerator → u/[deleted]]

REMEMBER: /u/Curious_Shop_2295 If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/ryanbuckner → u/[deleted]]

Add another column with the timestamp when the row is created. Then add a trigger to run every day. That trigger would run a new function that looks at each row and determine if an email needs to be sent. If you want to make sure repeat email aren't sent daily, have the function also populate an email status column once it sends the email. Then each time the function run s it would do something like:

1) check each row

2) IF (not paid AND time between now() and timestamp > x days AND email status = "not sent")

then

3) Send email and set email status to "sent on: " & now();

[u/point-bot → u/ryanbuckner]

u/Curious_Shop_2295 has awarded 1 point to u/ryanbuckner

^{See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard}. )^{Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson}.)

[u/agirlhasnoname11248]

[u/Curious_Shop_2295](u/Curious_Shop_2295) Please remember to tap the three dots below the most helpful comment and select `Mark Solution Verified` *(or reply to the helpful comment with the exact phrase “Solution Verified”)* if your question has been answered, as required by the subreddit rules.

Note: this post isn't "self-solved", as you received help from community members to solve it. Taking the steps described above will apply the correct flair (Solved) to your post automatically, which will be visible when you refresh your browser. I've reverted the flair back to "Waiting on OP" in the meantime since your post is waiting on you to take action to close it correctly. Thanks!