CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formulagoogle-sheets-query
BBRODIN
BBRODIN

Reputation: 1

Issue formatting date values in Google Sheet Query

I created a shared shipment tracker with google sheets where updates are made in a master tab, then filtered into several regional tabs via query function to better visualize shipment status by region. Query used:

=IFERROR(Arrayformula(QUERY(Filter!A2:P&"","select * WHERE Col1 = 'BA'",0)),"No Data Yet")

I made one column (column P) a time stamp column that auto-populates the current date via a simple script every time an update is made in any given cell of the master tab.The script does its job formatting the date as desired, this is the section of the script where I add the date format:

setValue(new Date()).setNumberFormat("mm/dd/yyyy hh:mm:ss");

The problem is that when this data is pulled into any regional tab by the query function, the date format turns into a serial number. I've tried messing with the column formatting in all tabs, however the only way the regional tabs will show the date in my desired format (mm/dd/yyyy hh:mm:ss) is when I manually format the date values in the master tab as 'Plain Text'. Is there a way around this to display the desired date format in both the master and regional tabs without having to continually format the timestamp column in the master tab as plain text every time an update is made?

Upvotes: 0

Views: 698

Answers (1)

Cooper
Cooper

Reputation: 64072

I'm not sure but how about this:

setValue(Utilities.formatDate(new Date(),Session.getScriptTimeZone(),"MM/dd/yyyy HH:mm:ss");

Just use a string.

Upvotes: 0

Related Questions