Unanswered

Excel Office Script Timezone vs Power Automate Timezone

(0) Share

Report

Posted on by DamoBird365

8,942 Microsoft Employee

I've built a proof of concept which filters a table of data using office scripts and returns an image of the table of data that has been filtered. I am using the Today expression to apply a dynamic filter. My locale is UK GMT+1 and i noticed by accident that if I run the script from Power Automate prior to 8am, it will return yesterday's data but if I run it from Office Scripts, it will return today's. Therefore it looks like the Script is running UTC -7 via Power Automate but honours my locale from the Office Script when run from Excel.

Here is a basic office script demonstrating my problem, it's the definition of "ExcelScript.DynamicFilterCriteria.today" which is misbehaving for me. As well as the image, I return the Date() known by the script purely for debug:

function main(workbook: ExcelScript.Workbook) {
	let table1 = workbook.getTable("Table1");
	// Apply dynamic filter on table table1 column Date
	let selected = table1.getColumnByName("Date").getFilter().applyDynamicFilter(ExcelScript.DynamicFilterCriteria.today);
	let tableimage = table1.getRange().getImage();
	
	return ([tableimage, Date()])
}

I have a table in the excel file as follows:

Date

08/06/2021 13:01

08/06/2021 14:01

08/06/2021 15:01

08/06/2021 16:01

08/06/2021 17:01

08/06/2021 18:01

08/06/2021 19:01

09/06/2021 13:01

09/06/2021 14:01

09/06/2021 15:01

09/06/2021 16:01

09/06/2021 17:01

09/06/2021 18:01

09/06/2021 19:01

When testing this out, I have run the flow before and after 8am GMT+1. You will see below that I have two different results, the first returning 8th June, the 2nd, 9th June i.e. today.

I have also output the utcnow() time from Power Automate and the Date() time from Excel Script which both match with GMT (remember that we are in GMT+1 due to summer).

If I check my timezone from Excel I am UK

Any ideas why I might be experiencing this? Has anyone else had timezone issues when running an office script in Excel vs from Power Automate?

If you want to watch my proof of concept that posts charts to teams / email using office scripts see here https://youtu.be/Tj2jasorczU

Thanks

Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

@GeoffRen any ideas? Is it something obvious?

Categories:

Using connectors

I have the same question (0)

All responses (4)

Answers (0)

Yutao Huang Microsoft Employee on at

Like (0)

Report
Hey @DamoBird365 -

This is indeed an interesting problem!

As the date value in Excel doesn't store the time zone information, the time zone difference would probably always pose a challenge to not only the scenario you demonstrated here (running Office Script in Excel Online vs. in Power Automate), but also to scenarios when running Office Scripts in Excel Online from different time zones.

Imagine if you share your workbook to a coworker in China and ask them to run your script in Excel Online, their script result might also be different from what you get because you two are in two different time zones.

A possible workaround is to "pin" a fixed time zone in your script (or pass it in as a parameter). Below is a proof-of-concept script that filters the table based on today's date of a "pinned" time zone ("Europe/London" in this case, you can find more time zone names from https://en.wikipedia.org/wiki/List_of_tz_database_time_zones), no matter where this script is being executed.

function main(workbook: ExcelScript.Workbook) { // Please find timezone names from https://en.wikipedia.org/wiki/List_of_tz_database_time_zones const timeZone = "Europe/London"; const now = nowInTimeZone(timeZone); const table = workbook.getTable("Table1"); const dateTimeFilter = { date: dateTimeInISOFormat(now, timeZone), // This has to be in the ISO date format specificity: ExcelScript.FilterDatetimeSpecificity.day // Filtering by "day" }; table.getColumnByName("Date").getFilter().applyValuesFilter([dateTimeFilter]); console.log(table.getRange().getVisibleView().getValues()); } function nowInTimeZone(timeZone: string): Date { const locale = new Intl.DateTimeFormat().resolvedOptions().locale; const currentTimezone = Intl.DateTimeFormat().resolvedOptions().timeZone; const sourceDateTime = new Date; const sourceDateTimeText = sourceDateTime.toLocaleString( locale, { timeZone: currentTimezone, dateStyle: "short", timeStyle: "long", hour12: false }); console.log(`${sourceDateTimeText} <== Current time in current time zone (${currentTimezone})`); const targetDateTimeText = sourceDateTime.toLocaleString( locale, { timeZone: timeZone, dateStyle: "short", timeStyle: "long", hour12: false }); console.log(`${targetDateTimeText} <== Current time in target time zone (${timeZone})`); return new Date(targetDateTimeText); } // Hack the date into a string in ISO format without actually changing the date/time value. // This format is required by the date time filter. function dateTimeInISOFormat(source: Date, timeZone: string): string { const temp = source.toLocaleString( new Intl.DateTimeFormat().resolvedOptions().locale, { timeZone, hour12: false, year: "numeric", month: "2-digit", day: "2-digit", hour: "2-digit", minute: "2-digit", second: "2-digit" }); const result = temp.match(/(?<month>\d+)\/(?<date>\d+)\/(?<year>\d+), (?<time>[0-9\:]+)/); const iso = `${result.groups.year}-` + `${result.groups.month.toString().padStart(2, "0")}-` + `${result.groups.date.toString().padStart(2, "0")}T` + `${result.groups.time}.` + source.getMilliseconds().toString().padStart(3, '0') + 'Z'; console.log(`${iso} <== Current time in target time zone (${timeZone}) in ISO format`); return iso; }

Unfortunately I couldn't find a way to tweak ExcelScript.DynamicFilterCriteria.today to respect the time zone, so I had to switch to using ExcelScript.FilterDatetime.

Honestly I feel all these date time JavaScript hacks indeed feel a bit too tedious. Some 3rd party libraries like Moment.js might be able to simplify them, although Office Scripts doesn't support external libraries yet at the moment.

Hope this helps!

- Yutao
Ask me about Office Scripts | Check out my Twitter / GitHub

Was this reply helpful? Yes No
Yutao Huang Microsoft Employee on at

Like (0)

Report

Sorry obviously I posted the reply too soon... I just realized there is a bug in my code when converting a date to the ISO format without changing its actual value. It's not working properly when running in Power Automate. Will need to tweak it a little bit and get back to you later.

Was this reply helpful? Yes No
Yutao Huang Microsoft Employee on at

Like (1)

Report

Now I think it should be working now. Too bad that I had to manually convert a date into the ISO format (without changing its time zone to UTC as the default Date.toISOString method would do), since the date time filter in Excel requires the ISO format but there isn't (?) really a good way in JavaScript that does the conversion and behaves the same in both Excel and Power Automate.

I have updated the script code in my original reply. Please give it a try when you get time. Let me know what you think.

Yutao

Was this reply helpful? Yes No
DamoBird365 8,942 Microsoft Employee on at

Like (1)

Report

Hi @Yutao

Wow. I’ll need some time to look at this next week and of course run it before and after 8am to test. This is definitely not an easy fix but appreciate your mega effort. I will let you know how I get on.

Damien

Was this reply helpful? Yes No