CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
swolfe2
swolfe2

Reputation: 451

How do I capture difference in date/time as seconds:milleseconds with Apps Script?

I'm trying to create a stopwatch function within Google Sheets, and I'm having some trouble in getting var diff to show the difference in seconds:milleseconds between var currentTime and var previousTiming. For some reason, I'm only retrieving a 'NaN' value.

The thought is that there will be an image on the "Timer" sheet, which users will click to start timings, along with a "lap" function which moves the active cell down 1 row from the current to capture the next time. Right now, I have it placing the current time it captures in the activeCell(), but would like to change it to the var diff value, if I can get it to have the right format.

Any help/advice you all could provide would be greatly appreciated.

function timer() {
    // Getting Active Sheet Details
    var spreadsheetName = SpreadsheetApp.getActive().getName();
    var spreadsheetTimeZone = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
    var currentTime = Utilities.formatDate(new Date(), spreadsheetTimeZone, "MM/dd/yyyy' 'HH:mm:ss:ms");
    var ss = SpreadsheetApp.getActive();
    var dataSheet = ss.getActiveSheet();
    var sheetName = dataSheet.getName().toString();

    // If the active sheet name contains "tim", continue
    if (sheetName.indexOf("Tim") > -1) {
        var totalBreakpoints = dataSheet.getRange("A2:A").getValues().filter(function(row) {
            return row[0];
        });
        var headers = dataSheet.getRange("C1:1").getValues().filter(function(row) {
            return row[0];
        });

        // Get the total number of rows/columns to look at
        var numberRows = totalBreakpoints.length;
        var numberColumns = ss.getLastColumn();

        // Get the range that can accept times
        var valuesRange = dataSheet.getRange(2, 3, numberRows, numberColumns);

        // Getting Active Cell Details
        var activeCell = dataSheet.getActiveCell();
        var activeRow = activeCell.getRow();
        var activeColumn = activeCell.getColumn();
        var cellAbove = activeCell.offset(-1, 0).getValue();
        var previousTiming
        if (activeRow <= 2 || cellAbove == '') {
            previousTiming = currentTime;
        } else {
            previousTiming = Utilities.formatDate(new Date(cellAbove), spreadsheetTimeZone, "MM/dd/yyyy' 'HH:mm:ss:ms");
        };

        // Check to see if within range            
        if (activeCell.getRow() >= valuesRange.getRow() && activeCell.getRow() <= valuesRange.getLastRow() &&
            activeCell.getColumn() >= valuesRange.getColumn() && activeCell.getColumn() <= valuesRange.getLastColumn()) {
            activeCell.setValue(currentTime);
            dataSheet.getRange(activeRow + 1, activeColumn).activate();
            var diff = Math.abs(currentTime - previousTiming);
        }
    }
}

Upvotes: 0

Views: 1589

Answers (2)

Cooper
Cooper

Reputation: 64140

function calcTimeDifference(Start,End)
{
  if(Start && End)
  {
    var second=1000;
    var t1=new Date(Start).valueOf();
    var t2=new Date(End).valueOf();
    var d=t2-t1;
    var seconds=Math.floor(d/second);
    var milliseconds=Math.floor(d%seconds);
    return 'ss:ms\n' + seconds + ':' + milliseconds;  
  }
  else
  {
    return 'Invalid Inputs';
  }
}

Note: valueOf() just returns the numerical value in milliseconds. Play around with this a bit.

function  ttestt(){
  var dt1=new Date();
  var dt2=dt1.valueOf()-100000;
  Logger.log('dt1=%s dt1-dt2=%s',dt1.valueOf(),dt1.valueOf()-dt2.valueOf());
}

Upvotes: 2

tehhowch
tehhowch

Reputation: 9872

The reason you get NaN as the value for diff is because Utilities.formatDate() returns a String - you are subtracting two strings, which is most certainly not a number.

The solution is to only apply the formatting when printing to the cell, and let currentTime and previousTime remain native Date objects, e.g.

activeCell.setValue(Utilities.formatDate(....));

Probably a better solution is to apply a datetime number format to the cells which hold the timing information, and write the actual number (not a formatted string) to the cells in question. This way you can be sure that when you read the value of the previous row's cell, that it is read as a number and can be used as such.

e.g.

// Time in col A, differential in col B
function lapImageClicked() {
  var now = new Date();
  var sheet = SpreadsheetApp.getActiveSheet();
  var last = sheet.getRange(sheet.getLastRow(), 1, 1, 2);
  // Write date into A, and milliseconds differential in B
  last.offset(1, 0).setValues([ [now, now - last.getValues()[0][0]] ]);
}

Upvotes: 1

Related Questions