Google Sheets - Send email based on cell data

0
votes

I'm new to script editing, so far have only made 2 custom scripts, and could use help with a 3rd much bigger one. The two I have are (not relevant to current post but just in case)

  1. When the top empty row has data in certain cells, create new row.
  2. At certain points/triggers it sorts a range of cells by date, so that its always a sorted list in dates descending order

Essentially, I have staff that inputs the information of customers who want to watch a YT video of mine that's on private (which link, what date, starting at what time, and their google account). From this list I copy the email and share the private video with their google account (I do this manually, and then take them off the shared list manually when the time comes as well).

I do this as soon as the email shows up on the sheet, but we email them the link at the agreed upon Date and Time. I was hoping to be able automate the emailing process.

If todays date is equal to the DATE in a cell in column B, an email will be sent at TIME taken from column C same row, and will be sent to EMAIL from column D, same row.

I have a template email, in it I would have to have a variable LINK, that gets pulled from column A

Is any of this doable? If so, can anyone steer me in the right direction as to how I would go about doing it?

EXAMPLE:

A: LINK B: DATE C: TIME D: EMAIL
LINK1 January 25 9:00 [email protected]
LINK2 February 2 12:00 [email protected]

THANKS to everyone who takes the time to read this and/or help!

Miodragon

1
google-apps-scriptgoogle-sheetsgoogle-docs

1 Answers

1
votes

You can write a function that checks all rows in your spreadsheet and sends the email for each row that has not been sent already and whose datetime is before the current time. Add a column to your spreadsheet, say "EmailSent?", and mark each row as sent, after you send the email.

Then create a time-driven trigger which calls the function above every 10 minutes, or however often you want. Alternatively, you can create a trigger based on an on-change or on-form-submit event, depending on how data gets added to your spreadsheet.