G-Sheets Script: Auto-Send Email 24-hrs Before Different Deadlines

Hi guys, so I know there are many different guides how to send auto-email reminders from Sheets with add-ons but none of those I can apply here. Some help with a script, please?

1. I basically have days set to MON, TUE etc in column A

2. email addresses are in column W

3. users should receive an email reminder 24-hrs before preset deadline.

4. the 24-hr trigger set as:

- if column A is MON, email the reminder at 3pm on SATURDAY
- if column A is TUE, email the reminder at 3pm on SUNDAY
- if column A is WED, email the reminder at 3pm on MONDAY

and so on...

IMPORTANT: I travel, so Ideally, and if possible, the trigger should fire off my local time instead of some fixed timezone. can Sheets pick up time from a current location on my laptop? and if not, it's ok to have it triggered on a fixed timezone but I'd just need to know how to change it whenever I move to another country.

5. Can the email contents be taken from a cell in column X instead of having it in a script itself? simply because if the text needs to change for certain days, it could be done in a cell itself instead of modifying the script.

6. Can column Y generate Email Sent Report, just to let me know that the email went out successfully?

Attaching the file itself below and thanking you very much in advance.

https://docs.google.com/spreadsheets...it?usp=sharing

Answering this:
"I travel, so Ideally, and if possible, the trigger should fire off my local time instead of some fixed timezone. can Sheets pick up time from a current location on my laptop? and if not, it's ok to have it triggered on a fixed timezone but I'd just need to know how to change it whenever I move to another country."

With the spreadsheet already open in google sheets..
1. click FILE > Spreadsheet Settings > Time Zone
2. change the timezone
3. click save settings

there is one possible issue with repeatedly changing the sending to be suited to your local timezones, you might duplicate email when going back in time, and miss sending email when going forward in time... so like any business system once its setup to be automated, you dont keep changing the timezone of the automated components.. but its up to you.

EXTRA TIP: if the date format is always converting wrong, for example you are from Australia, and you type in 01/10/2021 (which is 01/October), but your spreadsheet thinks it is 10/January, then while in Spreadsheet Settings, change the locale from USA to Australia.