2

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))
}
Marie
  • 21
  • 1
  • 3
  • 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 Jan 09 '17 at 21:51
  • There are also getHours(), getMinutes(), etc. See this [Date/Time Functions tutorial](http://javascript.info/tutorial/datetime-functions) – Karl_S Jan 09 '17 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 Jan 10 '17 at 05:34
  • 1
    Agreeing with @ShyamKansagra -- there's a few more details needed. Nonetheless, the issue is simply comparing the two dates in the code (generated by `Begin()` and `End()`). Possible duplicate of http://stackoverflow.com/q/11174385/4625829. – AL. Jan 10 '17 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 Jan 10 '17 at 22:01
  • Also there will be several lines. – Marie Jan 10 '17 at 22:09
  • I think you are getting the error in column C because you are passing a calculation, not a value. Calculate (finish - start) and then call addRecord3 with the result. – Sam Scholefield Jan 11 '17 at 08:02

1 Answers1

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
}
Sam Scholefield
  • 772
  • 2
  • 6
  • 20