CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstimestamp
Joey Lopez
Joey Lopez

Reputation: 21

Script suggestion to insert a timestamp when button is pressed

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

Upvotes: 2

Views: 7898

Answers (1)

Robin Gertenbach
Robin Gertenbach

Reputation: 10776

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

Upvotes: 4

Related Questions