CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
bdpolinsky
bdpolinsky

Reputation: 491

Time Arithmetic Calculation Error

I have the following code on a gsheet - 

var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var task = ss.getRange(1,2).getValue(); //ss.getRange("A2").getValue();
var date = new Date();

function onEdit(e) {
  if (e.range.getA1Notation() == 'B2') {
    if (/^\w+$/.test(e.value)) {   
     // console.log(e.value);

      eval(e.value)();
      e.range.clear();
    }
  }
}

function Start() {
var last = ss.getLastRow();
  ss.getRange(last+1, 1).setValue(task);
  ss.getRange(last+1, 2).setValue(date);
}


function End() {
var last = ss.getLastRow();        
    ss.getRange(last, 3).setValue(date);

     var endTime = ss.getRange(last, 3).getValue();
     var startTime = ss.getRange(last, 2).getValue();
     ss.getRange(last, 4).setValue(endTime-startTime);
}

Whenever the cell in B2 is edited, it runs one of the validated names of the functions - either Start or End.

If 'start', it puts the value of cell b1 (validated list of task names) in first column, and current time in MM/DD/YYYY HH:MM:SS format.

If 'end', its the current time in MM/DD/YYYY HH:MM:SS format in the third column, and attempts to put the difference between the "start" time and "end" time in the fourth column, or a calculation.

Here's the error (or my lack of understanding how gsheets works)

The code produces the following:

Task|     Start         |      End          | Duration
DOS | 8/2/2017 16:44:28 | 8/2/2017 16:44:31 | 2,418.00

Question - what is the 2,418? The total duration should be 2 seconds, or 00:00:02. Given the above code, is it a code issue or a format of the cell issue?

when I put in the cell d2 = c2-b2, it works fine as long as column is formatted as a duration. But I'd rather not do it, as the inserting or start/end times is dependent upon the last row - so if I copy/paste the formula down to the bottom on the gsheet the data will not be continuous.

Upvotes: 1

Views: 48

Answers (1)

user6655984
user6655984

Reputation:

JavaScript timestamps are in milliseconds. Divide by 1000 to get seconds.

Also, using eval like you do seems a pretty bad idea. I would use a switch statement there, and display an error for the user if the entered function is not one of options. As is, your code silently fails if they enter "stat" instead of "start".

Upvotes: 1

Related Questions