2

I am new to coding and I would like your help in developing a simple script for Google Sheets. What I would like it to do is (see example):

Example

when I press the "START" button the current time automatically gets inserted in the next available empty cell in column A in HH:MM:SS. So every time I press the button a new current time stamp gets added in the next empty cell on column A. The "STOP" button would work exactly the same just that it would and the information in column B.

I have found a code for Visual Basic that does in Excel exactly what I would like to do, I just don't know how to do this is Google Sheets. The code for the "Start" button in Visual Basic for Excel is the following:

Sub StartTime()
    nr = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUP).Row + 1
    Cells(nr, 1) = Time
End Sub
Community
  • 1
  • 1
Joey Lopez
  • 21
  • 1
  • 5
  • 1
    If you have code, post it here. Posting it elsewhere is inconvenient for those who might help and some can't visit your link at all. You could start by creating a Date and a small function to format the time part using *getHours*, *getMinutes*, etc. – RobG Mar 08 '16 at 03:02
  • Thank you for you suggestion, I found a code for VBA which does exactly what I want to do in excel. I would just like to know how I can do this in google sheets. I had added the code so you can see. – Joey Lopez Mar 08 '16 at 03:30
  • See [Extend Google Docs, Sheets, and Forms with Apps Script](https://support.google.com/docs/answer/2942256?hl=en). – Rubén Mar 08 '16 at 15:31

1 Answers1

4

This simple solution assumes that the list of start stop times will always be the furthest down of everything in that Sheet (or ideally the only things).
If that is not the case you'll need to change it to find the last row of the range you need when inserting the data..

var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

function startTime() {
  ss.getRange(ss.getLastRow() + 1, 1).setValue(new Date());
}

function stopTime() {
  ss.getRange(ss.getLastRow(), 2).setValue(new Date());
}

The spreadsheet tab Sheet1 will have the same layout as yours with two pictures inserted and Columns A and B formatted as Times.

Now, if you select the picture of the button that you inserted there's a little arrow in the top right hand corner.
enter image description here
Here you click "Assign Script" and type the name of the function (startTime and stopTime respectively).

Robin Gertenbach
  • 10,316
  • 3
  • 25
  • 37
  • This works perfect! Thank you very much for your help and clear explination. – Joey Lopez Mar 09 '16 at 03:23
  • Hi Robin, another question and hopefully the last. Trying out the code, when I hit start it adds the time in cell 2A, perfect. But when I hit stop, it adds the time in 3B (not 2B) because it seems that it detects content in 2A. And if I hit start again it adds the time in cell 4A and not 3A. How could I tell it to only check for content within its own column? – Joey Lopez Mar 09 '16 at 04:16
  • You are right. Just remove the `+1` after `ss.getLastRow()` in stopTime() and it should work. If you want to do it for the column because you have other data, try checking [this](http://stackoverflow.com/questions/17632165/determining-the-last-row-in-a-single-column) – Robin Gertenbach Mar 09 '16 at 09:36
  • Thanks again for your help Robin. – Joey Lopez Mar 11 '16 at 15:47