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))
}

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))
}

• javascript
• google-apps-script
• google-sheets

Share Improve this question Follow edited Jan 10, 2017 at 22:55 Marie asked Jan 9, 2017 at 21:49 MarieMarie 2111 silver badge33 bronze badges 7

• 2 new Date() returns a Date object. a Date object has a get a getTime method that returns a number of milliseconds ... subtract one from the other to get the difference in milliseconds .. perform appropriate calculations to get years,months,days,hours,minutes,seconds as required – Jaromanda X Commented Jan 9, 2017 at 21:51
• There are also getHours(), getMinutes(), etc. See this Date/Time Functions tutorial – Karl_S Commented Jan 9, 2017 at 22:23
• 1 Few questions: What do you mean by pressing buttons? Have you inserted drawing and assigned script to those? If yes, then have you assigned Begin() and End() functions itself? Also, you want to calculate timestamps of beginning and end of an action, which action? Pressing of begin and then end button respectively? Also, time difference can be measured in various formats. What exactly are you looking for? – Shyam Kansagra Commented Jan 10, 2017 at 5:34
• 1 Agreeing with @ShyamKansagra -- there's a few more details needed. Nonetheless, the issue is simply paring the two dates in the code (generated by Begin() and End()). Possible duplicate of stackoverflow./q/11174385/4625829. – AL. Commented Jan 10, 2017 at 10:05
• Thanks for your responses! @ShyamKansagra, yes, I've assigned function "Begin" and "End" respectively to 2 buttons. What I want is that pressing button Begin generates a timestamp (in column A), and pressing button End generates another timestamp (ColB) + the duration between the two timestamps (ColC). A: 06/02/2017 06:00:12|| B: 06/02/2017 06:03:13 || C: 00:03:01. – Marie Commented Jan 10, 2017 at 22:01

 |  Show 2 more ments

1 Answer 1

Sorted by: Reset to default 3

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
}