How to set up an automated email reminder in Google Sheet

Question

I have repurposed this Google Sheet with script linked below:

https://docs.google.com/spreadsheets/d/1z1EfLPYRze0zWnk729ChCf_kOY-JPEV9dvjCbXHNZDc/edit?usp=sharing

Someone had previously shared this and I have tried to tweak it for my needs. It works fine for sending email reminders based on when "Date to send" becomes current. There's just a little something that I need it to do but I can't figure out as I've no experience with scripting/code.

I would like to write the script that needs to be added to my current script to do the following:

When script run on sheet daily based on trigger- and sends out email reminders for specific rows - it should update column1 of those rows with a new date- essentially advancing the existing date by the number of days in column 5 (Days to next reminder). ((Eg- Row 6 has date Feb 7,2020 - after email reminder is shot- it must add 180 days (Col 5 data) to Feb 7 and update Col 1 with new Date as Aug 5, 2020; So the sheet is ready to send the next reminder then. If Col 5 reads 0- then it should not update that date and just leave the row as it is.

And simultaneously in Col 6 - it should update not just "Email sent" but with added info "on this day" which is the date the email is sent out.

And finally the sheet should be sorted with - latest and highest date in col 1 rows - at the top and least date at the bottom.

I came up with this additional code but since I don't know any syntax, it's not working:

 var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  //Get the dates from the cell and convert them into Milliseconds since 1970/01/01
  var First = new Date(ss.getRange(row[1]).getValue().getTime();
  var dayInMs = 24*60*60*1000    //one day in Milliseconds
  //add row days to each date in milliseconds
  First = First + ((row[4])*dayInMs)
        //Convert Milliseconds to date use new Date(time in ms) and set Values of the cell
  ss.getRange(row[1]).setValue(new Date(First));

How can I proceed?

I've reworded this from a "plz fix it for me" to a "how can I fix this?". You'll find that on Stack Overflow, readers respond much better where you assume you'll be doing some of the work. Perhaps you will receive a pointer here, which will give you new themes for more research. — halfer, Feb 08 '20 at 07:48
Could you clarify "not working" in relation to your new code? For example, have you verified that it runs? I assume that one can do `console.log()` to output debugging information. Have you verified that `new Date(First)` is a valid date? Is the value in `row[1]` correct? Is the range in `ss.getRange(row[1])` valid? etc. — halfer, Feb 08 '20 at 07:50
Cant figure out how to vote thanks for you on this forum. But your edits are very helpful @halfer — adinous, Feb 09 '20 at 16:18
No worries, Adinous. I think newer editors (rep < 2000) get some points automatically for editing. Editors who've been around for longer do it just because they enjoy curation `:-)`. — halfer, Feb 09 '20 at 16:38

score 0 · Answer 1 · edited Feb 08 '20 at 10:32

Here is how to add 180 days to a date in a cell:

function myFunction() {
  var data = SpreadsheetApp.getActive().getActiveSheet().getDataRange().getValues();
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var car = row[1];
    var date = new Date(row[2]); // make the sheet value a date object
    Logger.log('original value = '+date);
    Logger.log('method 1 : '+new Date(date.getTime()+180*3600000*24));
    Logger.log('method 2 : '+new Date(date.setDate(date.getDate()+180)));
  }
}

See Google App Script Adding one Day to a date

The next question is simple:

"Email Sent On " + Utilities.formatDate(dt,"GMT", "dd-MM-yy")

Third question

var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];
 var range = sheet.getRange("A1:C7");

 // Sorts by the values in the first column (A)
 range.sort(1);

 // Sorts by the values in the second column (B)
 range.sort(2);

 // Sorts descending by column B
 range.sort({column: 2, ascending: false});

 // Sorts descending by column B, then ascending by column A
 // Note the use of an array
 range.sort([{column: 2, ascending: false}, {column: 1, ascending: true}]);

 // For rows that are sorted in ascending order, the "ascending" parameter is
 // optional, and just an integer with the column can be used instead. Note that
 // in general, keeping the sort specification consistent results in more readable
 // code. We could have expressed the earlier sort as:
 range.sort([{column: 2, ascending: false}, 1]);

 // Alternatively, if we wanted all columns to be in ascending order, we would use
 // the following (this would make column 2 ascending)
 range.sort([2, 1]);
 // ... which is equivalent to
 range.sort([{column: 2, ascending: true}, {column: 1, ascending: true}]);

How to set up an automated email reminder in Google Sheet

1 Answers1