CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsscriptingtimestamp
Sassquatch
Sassquatch

Reputation: 23

Static TIMESTAMP for Google Sheets

In the spreadsheet, need a button that writes the sheet's timezone current time and date in a cell, so everytime the button is pressed the timestamp is updated in that specific cell.

After trying different scripts, rejecting some because they don't use the format dd/mm/yyyy hh:mm:ss or do not allow the change of format, reached this point:

function TIMESTAMP() {
  SpreadsheetApp.getActiveRange().setValue(new Date());
  var sheet = SpreadsheetApp.getActiveSheet();
  var addedDate = sheet.getRange(1,1).getValue();
  var addedTime = Utilities.formatDate(addedDate, SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "hh:mm a");
}

when the button, with the script, is pressed the timestamp with the correct timezone shows in the current active cell BUT an error shows up:

Exception: The parameters (String,String,String) don't match the method signature for Utilities.formatDate.

How to designate the cell where the Timestamp should show up, make its values update everytime the button is pressed and solve the parameters error?

Upvotes: 2

Views: 5505

Answers (5)

Julian Lugod
Julian Lugod

Reputation: 1

I use this code for my Timestamp. Then use the Custom Date and Time at the Format Panel. I hope this helps.

function TS() {
  const today = new Date();
  return today;
}

[Image for the Custom Date and Time Panel][1]
[1]: https://i.sstatic.net/errzV.png

Upvotes: 0

Brandon Cassio
Brandon Cassio

Reputation: 9

so I found a solution that seems to work on my product worklog:

=IF(A6="","",IF(B6<>"",B6,now()))

A6 is where the employee leaves their name, but that could be a checkbox, or any other condition you you might want to satisfy.

B6 is where the timestamp appears when the A6 Condition is satisfied.

Upvotes: 0

Cooper
Cooper

Reputation: 64140

Try:

function TIMESTAMP() {
  var ss=SpreadsheetApp.getActive();
  var sheet=SpreadsheetApp.getActiveSheet();
  sheet.getRange(1,1).setValue(Utilities.formatDate(new Date(sheet.getRange(1,1).getValue()), ss.getSpreadsheetTimeZone(), "dd/MM/yyyy HH:mm:ss"));
}

Upvotes: 0

Michiel the Temp
Michiel the Temp

Reputation: 270

How about this:

function TIMESTAMP() 
  {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var addedDateAndTime = Utilities.formatDate(new Date, ss.getSpreadsheetTimeZone(), "dd/MM/yyyy HH:mm:ss");
    //Optional if you only want the date: var addedDate = Utilities.formatDate(new Date, ss.getSpreadsheetTimeZone(), "dd/MM/yyyy");
    //Optional if you only want the time: var addedTime = Utilities.formatDate(new Date, ss.getSpreadsheetTimeZone(), "hh:mm:ss");
    sheet.getRange(1,1).setValue(addedDateAndTime)
    }

Upvotes: 1

Wicket
Wicket

Reputation: 38436

Looking at you code the cause of the error message is that active cell is not A1 and that A1 is blank (as a getValue returns and empty string) or has a text value

The solution without changing your code is that before running the function, you click on A1, then run the function.

Your code already is designating the cell to show up the timestamp. On this line

SpreadsheetApp.getActiveRange().setValue(new Date());

it's using getActiveRange(). To use another cell most of the examples that I have seeing use one of getRange methods of Class Sheet.

By the way there are a several questions on this site about adding timestamps in Google Sheets. One example is Automatic timestamp when a cell is filled out

Reference

Upvotes: 0

Related Questions