CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
J.Olufsen
J.Olufsen

Reputation: 13915

How to get the current time in Google spreadsheet using script editor?

There is an option to write a script and bind it to a trigger. The question is, how to get the current time in the script body?

function myFunction() {

  var currentTime = // <<???

}

Upvotes: 39

Views: 162385

Answers (5)

bytrangle
bytrangle

Reputation: 1139

Anyone who says that getting the current time in Google Sheets is not unique to Google's scripting environment obviously has never used Google Apps Script.

That being said, do you want to return current time as to what? The script user's timezone? The script owner's timezone?

The script timezone is set in the Script Editor, by the script owner. But different authorized users of the script can set timezone for the spreadsheet they are using from File/Spreadsheet settings menu of Google Sheets.

I guess you want the first option. You can use the built in function to get the spreadsheet timezone, and then use the Utilities class to format date.

var timezone = SpreadsheetApp.getActive().getSpreadsheetTimeZone();

var date = Utilities.formatDate(new Date(), SpreadsheetApp.getActive().getSpreadsheetTimeZone(), "EEE, d MMM yyyy HH:mm")

Alternatively, get the timezone offset from UTC time using Javascript's date method, format the timezone, and pass it into Utilities.formatDate().

This requires one minor adjustment though. The offset returned by getTimezoneOffset() runs contradictory to how we often think of timezone. If the offset is positive, the local timezone is behind UTC, like US timezones. If the offset is negative, the local timezone is ahead UTC, like Asia/Bangkok, Australian Eastern Standard Time etc.

const now = new Date();
// getTimezoneOffset returns the offset in minutes, so we have to divide it by 60 to get the hour offset.
const offset = now.getTimezoneOffset() / 60
// Change the sign of the offset and format it
const timeZone = "GMT+" + offset * (-1) 
Logger.log(Utilities.formatDate(now, timeZone, 'EEE, d MMM yyyy HH:mm');

Upvotes: 8

Andy
Andy

Reputation: 109

I considered with timezone in my Google Docs like this:

timezone = "GMT+" + new Date().getTimezoneOffset()/60
var date = Utilities.formatDate(new Date(), timezone, "yyyy-MM-dd HH:mm"); // "yyyy-MM-dd'T'HH:mm:ss'Z'"

my script for Google docs

Upvotes: 10

Vignesh R
Vignesh R

Reputation: 609

The Date object is used to work with dates and times.

Date objects are created with new Date().

var date= new Date();

 function myFunction() {
        var currentTime = new Date();
        Logger.log(currentTime);
    }

Upvotes: 1

Jeff B
Jeff B

Reputation: 30099

use the JavaScript Date() object. There are a number of ways to get the time, date, timestamps, etc from the object. (Reference)

function myFunction() {
  var d = new Date();
  var timeStamp = d.getTime();  // Number of ms since Jan 1, 1970

  // OR:

  var currentTime = d.toLocaleTimeString(); // "12:35 PM", for instance
}

Upvotes: 71

Francis Avila
Francis Avila

Reputation: 31621

Use the Date object provided by javascript. It's not unique or special to Google's scripting environment.

Upvotes: 8

Related Questions