Apps Script to Send Email with Values Based on Number Value

[Frayda Zirkind]

Hi, 

Sample spreadsheet with my data: https://docs.google.com/spreadsheets/d/1ipGzVMPetlTVCFWQKbg3Lsmw8458UTIxEr9s3mgpEDU/edit

I am trying to make a Google Apps Script that does the following:

When the value in cell K3 is less than or equal to 2, search through the whole sheet to find false (unchecked) check boxes and of those false check boxes, find the earliest date in the column preceding the check boxes. 

Then, I want an email to be sent to me with the following information: the date (of the preceding column of the false check boxes; e.g. 6/4/2026), the text in Column B of the row that has that earliest date (e.g. Microsoft), and the URL of the hyperlink that's in that cell in Column B (e.g. microsoft.com). 

I've tried to make a Script to do this but have been getting stuck. Any help would be greatly appreciated. 

Thank you in advance!

[Michael O'Shaughnessy]

Well I took a stab at this!!

I wrote some scripts (with Gemini's help) that you may find useful.  Here is a "force copy" link to the spreadsheet:

https://docs.google.com/spreadsheets/d/1N__gUZKA_4n0EN0PyApSjYvcmnoCq4eKJM_hYl3OoO8/copy

It has scripts accessible from a menu that you can experiment with.  It also has an onEdit trigger for emailing, but I set it to use K3.  YOU may have to install the trigger.  If you run into issues let me know and I can help you set it up.

At least this will give you something to work with!

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/98b6bdc7-00b9-45c5-80d8-b1cb33949d13n%40googlegroups.com.

[Frayda Zirkind]

This script is awesome! Thank you!!

I adapted the message (body) of the email notification to what I would like the email to say, and I am wondering if there is any way to include the URL of the hyperlink in the cell that ${result.name} returns. In other words, when I test the email notification in the menu bundle that you made, the email says that Facebook is the first one unchecked and the date unchecked by Facebook (5/17/2026). Is there any way for the email to also include facebook.com, which is the URL of the hyperlink of the cell that says Facebook?

[Michael O'Shaughnessy]

Well of course there is!!  😁

Now to do so we have to switch from a "plain text" email to an HTML email.  Not to worry, it is pretty straightforward.

So I updated the original sheet I shared to include 2 more scripts:

findEarliestUncheckedDateWurl()

This is just like the other one, but it now includes getting the company URL.

testEmailNotificationWurl()

This is also just like the other email test BUT it sends an HTML message.

I suggest getting another copy of my updated sheet and reviewing the changes.  So you don't have to search, here is the link:

https://docs.google.com/spreadsheets/d/1N__gUZKA_4n0EN0PyApSjYvcmnoCq4eKJM_hYl3OoO8/copy

You just might want to rename this copy with URL in the title somewhere.   

Let me know if you have any questions or run into any issues.  I would be more than happy to help.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/2fe78c59-ea3b-423e-8a1c-5406b82d25e4n%40googlegroups.com.

[Frayda Zirkind]

Thank you again!!

I combined the onEditScript with the testEmailWurl and installed a trigger for every morning at midnight so that every day, when K3 updates, it checks if K3 has a "2" value and if so, send me an email. 

I'm very grateful for all your help!

[Michael O'Shaughnessy]

It was my pleasure!!

I enjoyed working through this!

Let me know if you need anything else!

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/f7b14b5a-6571-4dcf-91ff-d28cc50a8bb2n%40googlegroups.com.

[Kildere S Irineu]

Vou separar em duas partes: primeiro o diagnóstico do problema, depois um comentário pronto para você postar na discussão somando uma melhoria técnica.

Sim, o problema já foi solucionado pelo Michael: ele adicionou a versão com URL (findEarliestUncheckedDateWurl) e mudou o envio para HTML (testEmailNotificationWurl). A ideia correta é usar RichTextValue para ler o link da célula em B e enviar com htmlBody. A documentação do serviço Sheets confirma o uso de RichTextValue/estilos de texto, e o MailApp permite enviar email com htmlBody.

Comentário sugerido para somar:

Great solution, Michael. One small improvement I’d suggest is making the URL extraction handle both cases: when the whole cell has one hyperlink and when only part of the cell text is linked.

For example, after getting the RichTextValue from column B, you can try:

const richText = sheet.getRange(row, 2).getRichTextValue();
let url = richText ? richText.getLinkUrl() : '';

if (!url && richText) {
const runs = richText.getRuns();
const linkedRun = runs.find(run => run.getLinkUrl());
url = linkedRun ? linkedRun.getLinkUrl() : '';
}

This makes the script a bit more robust if the company name cell contains partial rich-text links. Also, since th