cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DamoBird365
Super User
Super User

Excel Office Script Timezone vs Power Automate Timezone

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.

DamoBird365_1-1623222574538.png

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

 

DamoBird365_2-1623222684915.png

 

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?

 

 

4 REPLIES 4
Yutao
Microsoft
Microsoft

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

Yutao
Microsoft
Microsoft

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.

Yutao
Microsoft
Microsoft

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

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

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,980)