Static TIMESTAMP for Google Sheets

2
votes

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?

4
google-apps-scriptgoogle-sheetsscriptingtimestamp
So you want to insert a data and a timestamp? And this in different cells? In which cell do you want to insert the timestamp? – ziganotschka
Wanted to insert it in A1. – Sassquatch

4 Answers

1
votes

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

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

0
votes

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"));
}
0
votes

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.