Google Script to create alert if 2 cells match

Question

I want to create an alert with a YES_NO question when the value of 2 cells are the same. 1 cell will have the current time [=now()], and the other cell has a time something is due. Can i have a ui alert when the 2 times match. Here's the code i have written so far, it will currently work even if the times do not match.

function LiDCOcheck(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ui = SpreadsheetApp.getUi();
  var buttons = ui.ButtonSet.YES_NO;
  var bed = ss.getRange("C2").getValue();
  var due = ss.getRange("D2").getValue();
  var time = ss.getRange("A2").getValue();

  if (due=time){    
    var lidco = ui.alert("It hsa been 24 hours since the LiDCO in Bed " + bed + " was calibrated.  Is it still required?", buttons);
    if (lidco == ui.Button.YES){
      ss.getRange("D2").setBackground('#6aa84f');}
    else{
      ss.getRange("D2").setValue('Not Required')}}
}

Thanks

Sure, you can. Note that the current time is measured to the millisecond. Review JavaScript `Date` objects — tehhowch, Jul 22 '18 at 17:41
Sorry i don't fully understand how i Script that into what i have already. Would you be able to give me an example based on my script? — Aidan Wilson, Jul 22 '18 at 17:51
What's `due.value`? Review methods to get value from a range. — TheMaster, Jul 22 '18 at 18:35
I have edited the script above. I think i have used the correct method to get value. I now want the script to only prompt when the 2 values match. — Aidan Wilson, Jul 22 '18 at 18:59
Be careful with your operators, and with [comparing `Date`s](https://stackoverflow.com/questions/492994/compare-two-dates-with-javascript) — tehhowch, Jul 22 '18 at 19:00
Again, i don't know what you mean, i am a novice at this. Are you able to help me practically please? — Aidan Wilson, Jul 22 '18 at 19:01
What's the operator to check for equality? Why have you used `=` instead of `==`?. Regardless try `if(due-time>0){}` — TheMaster, Jul 22 '18 at 20:16
Thanks for that suggestion, unfortunately it is producing the same problem, it triggers the alert even is the values are different. I don't know what an operator is, I am a novice at these scripts. I did try both = and == but both came out with the same issue. — Aidan Wilson, Jul 22 '18 at 20:48
Perhaps you do not want an **alert** - if you are asking for a **response** — tehhowch, Jul 22 '18 at 21:25
Use logs `Logger.log(due-time)`,`Logger.log(due)`. Completely read the Comparing dates link posted above — TheMaster, Jul 22 '18 at 22:26

score 0 · Answer 1 · answered Jul 23 '18 at 12:48

Note, in your original script that a single equals is an assignment and a double equals is required for comparison.

if (due=time) {

is wrong, while

if (due==time) {

is better. However, it still doesn't work!

I generally find that when dealing with dates or times from a spreadsheet that it is best to convert it to a javascript Date object for most kinds of manipulation, including comparisons.

var due = new Date(ss.getRange("D2").getValue());
var time = new Date(ss.getRange("A2").getValue());

A simple comparison is just to compare the results of the getTime() function, which is the time in milliseconds.

if (due.getTime()==time.getTime()){    

}

Thanks @Aidan, unfortunately that is giving the same problem, the alert is triggering even if the time doesn't match. What i need is a reminder question to pop up when a task is due (when the due time = now). Would this be possible? I've added a link to a mock sheet i have been using. [Test Sheet](https://docs.google.com/spreadsheets/d/1gwKoifSaZ9DqafXUSvqNTT3MwaJnSaGyV070iOZG6sU/edit#gid=0) — Aidan Wilson, Jul 24 '18 at 06:49

Google Script to create alert if 2 cells match

1 Answers1