How to get the current time in Google spreadsheet using script editor?
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
}
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
Use the Date
object provided by javascript. It's not unique or special to Google's scripting environment.
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');