CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Marie
Marie

Reputation: 21

Calculate time duration with google script

I'm new to coding and I found a script that I slightly adapted for my purpose: record on a timesheet the timestamps of the beginning and the end of an action, when you press buttons "Begin" and "End". I would also like the script to calculate the time duration between the two timestamps, which at first I thought would be easy but I'm unable to find a solution, so I'm asking for your help.

function setValue(cellname, value) {
SpreadsheetApp.getActiveSpreadsheet().getRange(cellname).setValue(value);
}

function getValue(cellname, value) {
return SpreadsheetApp.getActiveSpreadsheet().getRange(cellname).getValue();
}

function getNextRow() {
return SpreadsheetApp.getActiveSpreadsheet().getLastRow() + 1;
}
function getLastRow() {
return SpreadsheetApp.getActiveSpreadsheet().getLastRow();
}

function addRecord(a, b) {
var row =getNextRow();
setValue('A' + row, a);
setValue('B' + row, b); 

}

function addRecord2(c) {
var row =getLastRow();
setValue('C' + row, c);
}

function Begin() {
addRecord(getValue('B1'), new Date());
}
function End() {
addRecord2(new Date());
}

Thanks everybody, I wrote a new version of my script after reading your suggestions. But I get a #NUM! error in column C.

var start = 0;
var finish = 0;
function setValue(cellname, value) {
SpreadsheetApp.getActiveSpreadsheet().getRange(cellname).setValue(value);
}

function getNextRow() {
return SpreadsheetApp.getActiveSpreadsheet().getLastRow() + 1;
}
function getLastRow() {
return SpreadsheetApp.getActiveSpreadsheet().getLastRow();
}
function addRecord(a) {
var row =getNextRow();
setValue('A' + row, a);
}
function addRecord2(b) {
var row =getLastRow();
setValue('B' + row, b);
}
function addRecord3(c) {
var row =getLastRow();
  setValue('C' + row, c);
}
function begin() {
start=addRecord(new Date().getTime());
}
function end() {
finish=addRecord2(new Date().getTime());
  addRecord3((finish-start))
}

Upvotes: 2

Views: 7888

Answers (1)

Sam Scholefield
Sam Scholefield

Reputation: 1250

var start = 0;
var finish = 0;
var yourCell = SpreadsheetApp.getActiveSpreadsheet().getRange(cellname);

function begin(){
  start = new Date().getTime();
}

function end(){
  finish = new Date().getTime();
  writeElapsed(start, finish)
}

function writeElapsed(start, finish){
  yourCell.setValue(finish - start); //will give elapsed time in ms
}

Upvotes: 3

Related Questions