0

It works as follows: Identify the value just edited is "Completed"or "Cancelled". If true, select the "Timestamp" value in previous column. Lookup the "Timestamp" in a different tab called "mainSheet". When found, write the value in the 59th column of that row. The code works until the if(sheetCells[i][0]===TimeStamp) condition where it cannot compare the cell and assign value of i to rowValue. I've run the Logger inside the loop and it DOES have same values! Am I missing something here?

function onEdit(e){
var completed = "Completed";
var cancelled = "Cancelled"
var s = SpreadsheetApp.getActiveSheet();
var value = s.getActiveCell().getValue();
if(value == completed || value == cancelled){
    var statusColumn = 59, rowValue=0;
    var TimeStamp = SpreadsheetApp.getActiveRange().offset(0,-1).getValue();
    var mainSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 1");
    var sheetCells = mainSheet.getDataRange().getValues();
    for(var i=1; i<sheetCells.length; i++){
        Logger.log(sheetCells[i][0]+" "+TimeStamp);
        if(sheetCells[i][0]===TimeStamp){
            rowValue = i;
        }
    }
    //sheetCells[rowValue][statusColumn].setValue(value);
    Logger.log(rowValue);
}

}

Rubén
  • 34,714
  • 9
  • 70
  • 166
Pixel92283
  • 51
  • 6
  • 1
    I guess the thing here is about comparing dates, so this probably can be useful: https://stackoverflow.com/questions/492994/compare-two-dates-with-javascript#493018 – a-change Nov 03 '17 at 20:58
  • Possible duplicate of [Compare two dates Google apps script](https://stackoverflow.com/questions/11174385/compare-two-dates-google-apps-script) – Rubén Nov 03 '17 at 23:13

1 Answers1

0

Try if(sheetCells[i][0]==TimeStamp) since The == operator will compare for equality after doing any necessary type conversions. The === operator will not do the conversion, so if two values are not the same type === will simply return false.But even == didn't work in your case. So,I believe you are getting time stamp and In javascript there should be specific method to compare time stamps or you have to calculate difference in time using math method and then if difference stands as 0 then the time stamp is same else there is some difference

Mike
  • 512
  • 3
  • 16
  • Can you share o/p of the logger ? – Mike Nov 03 '17 at 19:38
  • Okay, so I stored the cell value in a variable "CellValue" and used .toString() on both TimeStamp and CellValue and somehow this worked. I don't understand why though. – Pixel92283 Nov 03 '17 at 19:39
  • This is because both the value does not have same type for e.g false == 'false' will result to false since both has same value but different data type. When you convert each value to String and then come for e.g 'false' == 'false' the it will result as true. – Mike Nov 03 '17 at 19:43
  • Yeah, but in that case, '==' should've worked, right? – Pixel92283 Nov 03 '17 at 19:45
  • Yeah It should but I believe you are getting time stamp and In javascript there should be specific method to compare time stamps or you have to calculate difference in time using math method and then if difference stands as 0 then the time stamp is same else there is some difference – Mike Nov 03 '17 at 20:01
  • Why don't you submit this as an answer? I'll accept it – Pixel92283 Nov 03 '17 at 20:49