I have a shared spreadsheet that is used by people from many different time zones around the world. On the sheet is a button that runs a Google App Script function which when finished updates a cell next to the button showing the date and time the function completed execution, set like so SpreadsheetApp.getActive().getSheetByName(SHEET_NAME).getRange(DATE_CELL).setValue(new Date()); This date and time is currently displayed in the time zone of the spreadsheet, United States Eastern Time. How can I have this cell show the date and time in the viewing users local time?
i.e. If the cell displays 2024-07-16 9:00 PM to a user in New York, a user in Sydney Australia should see 2024-07-17 11:00 AM and a user in London should see 2024-07-17 02:00 AM.
Note that I want to show the time in local time for whoever is looking at the sheet, not the local time of the person who last clicked the button. The person viewing the sheet may not ever need to click the button but the date and time of the last time it was clicked should still be shown to them in their local time.
7
As far as I know Google Appscript doesn’t have a direct way to fetch the user’s timezone. However, you can use an API to fetch timezone depending on Geolocation. You can try to integrate that to your appscript.
Something like this:
Fetch location via API > parse response > extract timezone and write to cell > CreateButton > Edit button function to getTimeZone on click.
Hope this helps!
Try this:
function todaysdateandtime() {
let dt = new Date();
let date = Utilities.formatDate(dt,Session.getScriptTimeZone(),"MMM dd, yyyy");
let time = Utilities.formatDate(dt,Session.getScriptTimeZone(),"HH mm ss");
Logger.log("date: %s time: %s",date,time)
}