Excel Run Script Flow Doesn't Change Tab Color

[Kongol]

Hello!

Whenever I run my office script manually, the tab colors that I set do change.

However, whenever the script runs on behalf of my Power Automate flow, the tab color is unchanged despite the script returning a successful execution. Does anyone know why this could be? When calling for the worksheets to change the tab color, I'm not using "getActiveWorksheet", I am using the actual names of the sheets to modify the tab color. 

I know this issue exists with Power Automate and "getActiveWorksheet", but the worksheet names are hardcoded in the script. The color change just gets ignored. Thanks in advance for any help you can provide!

[Yutao]

Hi @Kongol ,

The following script worked for me:

function main(workbook: ExcelScript.Workbook) {
  workbook.getWorksheet("Sheet1").setTabColor("yellow");
}

I triggered a this script from a Power Automate flow and it indeed changed the tab color to yellow.

Can you try making some different changes (like set range value, etc.) with the script to the same workbook and see if that works?

[Kongol]

All other processes contained within that script are functioning properly. Here is the relevant code from my script:

  // Convoluted call to current month's sheet
  var d = new Date();
  const monthNames = ["Jan", "Feb", "Mar", "Apr", "May", "Jun",
    "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
  var month = monthNames[d.getMonth()];
  var year = String(new Date().getFullYear());
  var sheet = workbook.addWorksheet(month+year);

  // Convoluted call to last month's sheet
  var lastMonth = monthNames[d.getMonth()-1];
  if (month == "Jan"){
    var lastYear = String(new Date().getFullYear()-1);
  }
  else{
    var lastYear = String(new Date().getFullYear());
  }
  var lastSheet = workbook.getWorksheet(lastMonth+lastYear);

  // Sheet & Table formatting
  var lastTable = lastSheet.getTable(lastMonth+"_"+lastYear);
  var newHead = lastTable.getHeaderRowRange();
  sheet.getRange("A1:M1").setValues(newHead.getTexts());
  sheet.setTabColor("00FFFF");
  sheet.setPosition(0);
  lastSheet.setTabColor("D3D3D3");

Everything else including changing the position of the sheet in the workbook, setting the values of the header range within the sheet, & other modified properties irrelevant to this issue also function properly. Feel free to request more information or a demonstration of what I am experiencing and I will be more than happy to oblige!  Thank you for helping me out and apologies for the late response.

[Yutao]

Thanks for sharing the details. That is very helpful! Now I am able to repro the problem as well.

Sorry this might be a bug on our side. We will need to take some time to investigate and get back to you with an update as soon as possible.

[Kongol]

Apologies for not sharing the details sooner, still new to this environment and communicating my problems in a way that is concise while also providing enough elaboration to get everyone on the same page as me!

I really appreciate your effort to resolve this problem and I'm honestly a bit impressed in myself if I've found a bug in a system that a bunch of brainiacs utilize on a daily basis!! If there's another way I can accomplish my goal as a workaround that would be more than acceptable as well. Have a great day!

[Yutao]

@Kongol, just wanted to give you an update that we're still investigating the root cause of this problem. Thanks for your patience!

[Kongol]

Absolutely no problem at all! I’m still stoked that I even managed to be the first to find this bug. Every day that you guys don’t have a solution I take as a compliment knowing it is a complex issue. I live for stuff like this!

Please take all the time that you need, I’m sure you guys have much more urgent matters to attend to. I just appreciate your continuous effort to follow up on my problem!! Love this community.

[GeoffRen]

Hi @Kongol, just wanted to confirm some additional points with you:

 1. For this bug, did you open the workbook in the browser, then run the Power Automate script, and then look at the workbook from the first step to see that the color didn't change? Does it matter whether the workbook is open in the browser or not?

2. Will refreshing the workbook in the browser make the color change?

[Kongol]

@GeoffRen ,

I really appreciate your response!!! This solved my problem in a way however is more of a workaround than a fix. Turns out, if I have the workbook closed when I run the flow, upon opening the workbook the tab color is changed. However, If I have the workbook open when I run the flow, the tab color will not change. Refreshing/closing and reopening does not change the color either. I waited a couple days too and still no luck, never updated the tab color. 

My only concern is other employees having the workbook open when the script runs, resulting in the tab color not changing. Otherwise, having the workbook closed seems to accomplish what I'm looking for! I really appreciate your guys' help with this. Sorry for the late reply, last week was a mess!

[Yutao]

@Kongol,

Thanks for your patience!

Now we have identified the root cause of this problem. The ETA for the fix would be the next quarter for the earliest.

Thanks again for reporting this good bug!

[Kongol]

@Yutao,

Thank y'all for the continued effort to resolve this. This experience is one I will not forget and I look forward to further developing myself professionally with more intricate Power Automate flows. 

Eagerly will await confirmation that this has been fixed. Can't thank you enough!

[KGuthrie-NF]

Has this bug been fixed?  I am running into something similar.  When I run my script manually (through excel online) everything works as it should.  However, when the script runs via a flow, it doesn't actually do the work.  (even though it says everything was completed successfully.  

I have tested this every way I can.  When the sheet is open, the flow runs all good.  When the script is running through the sheet, all is good it works as it should.  When the sheet is closed, the flow runs, noting.  

[Yutao]

@KGuthrie-NF - could you share a little more about the issue you are running into? Is it also about the tab color or something else?

Regarding this particular tab color issue @Kongol originally reported, I'm happy to share an update that our team is working on it right now. Apologize this has been a while. But hope you can understand the challenges we've had with a giant queue of feature requests and bug fixes. Thank you again for all the patience!

[KGuthrie-NF]

My issue does not deal with tab color.  It is similar.  My script should be copying rows from one tab to another based on a new submission on a form.  

The process is:  A new submission on a form tracks the response on Form 1 on an excel sheet.  This also triggers a flow to copy the needed user responses to another sheet on the same excel sheet.  As I mentioned before the script works.  When I run it within the sheet, the row copies.  When I have the sheet open and submit a new form, the script runs and copies the row.  However, when the sheet is closed, I submit a new form, the flow says the script ran successfully, but when I open the sheet, the row did not copy. 

Not sure if it matters, but everything resides on the same SharePoint site.

[Yutao]

@KGuthrie-NF - What you described sounds like a very typical scenario that Office Scripts should support but we may be missing something here. Would you mind sharing your script? At the same time, you can also try using console.log(...) to output some debugging info (e.g., the steps in your script execution, the copied content, etc.), and see if the script indeed works as expected. You can find the output message from your flow's run history in Power Automate.

In your first message, you mentioned "When I run my script manually (through excel online) everything works as it should. However, when the script runs via a flow, it doesn't actually do the work. (even though it says everything was completed successfully."  It sounded to me that your script has never worked in Power Automate. But in your second post, it sounded to me the script can still work in Power Automate flow but the target workbook has to be open during the time the flow runs. Just wanted to confirm the exact behavior...

Thanks!

[KGuthrie-NF]

Below is the code in the script.  

function main(workbook: ExcelScript.Workbook)
{
  let Form1 = workbook.getWorksheet("Form1");
  let FRWB = workbook.getWorksheet("Facilities Request WB");

  //Get Date from Form1
  FRWB.getRange("B:B").copyFrom(Form1.getRange("B:B"), ExcelScript.RangeCopyType.values);
  console.log("Date Range Copied");

  //Get Submittion Email from Form1
  FRWB.getRange("C:C").copyFrom(Form1.getRange("D:D"));
  console.log("Submission Email Range Copied");

  //Get Requester from Form1
  FRWB.getRange("D:D").copyFrom(Form1.getRange("F:F"));
  console.log("Requester Range Copied");

  //Get Phone Ext from Form1
  FRWB.getRange("E:E").copyFrom(Form1.getRange("G:G"));
  console.log("Phone/Ext. Range Copied");

  //Get Issue from Form1
  FRWB.getRange("F:F").copyFrom(Form1.getRange("H:H"));
  console.log("Issue Range Copied");
 
  //Get Issue Location from Form1
  FRWB.getRange("G:G").copyFrom(Form1.getRange("I:I"));
  console.log("Issue Location Range Copied");

  //Get Area of Issue from Form1
  FRWB.getRange("H:H").copyFrom(Form1.getRange("K:K"));
  console.log("Area of Issue Range Copied");

  //Get Attachment from Form1
  FRWB.getRange("I:I").copyFrom(Form1.getRange("J:J"));
  console.log("Attachment Range Copied");

  //Set emailed and complete to NO
  let lastEmail = FRWB.getUsedRange()
  //console.log(lastEmail.getAddress());
  let sent = FRWB.getRange("K" + lastEmail.getRowCount())
  console.log(sent.getAddress())
  sent.setValue("No");
  console.log("Emailed indicator set in cell " + sent.getAddress());

  let done = FRWB.getRange("A" + lastEmail.getRowCount())
  //console.log(mail.getAddress())
  done.setValue("No");
  console.log("Request done indicator set in cell " + done.getAddress());

}

The flow works,  I have run through it multiple times.  The only part that doesn't work is the update script. The log of the flow says the script was successful.  

[Yutao]

So were you able to see those console logs output from your script and see how the code was executed? You can expand the "Run script" action from a run history and look into the body. For example:

[KGuthrie-NF]

The body contains this:

{
  "logs": [
    "[2022-03-28T16:19:19.9580Z] Date Range Copied",
    "[2022-03-28T16:19:20.0370Z] Submission Email Range Copied",
    "[2022-03-28T16:19:20.1300Z] Requester Range Copied",
    "[2022-03-28T16:19:20.2240Z] Phone/Ext. Range Copied",
    "[2022-03-28T16:19:20.3020Z] Issue Range Copied",
    "[2022-03-28T16:19:20.3960Z] Issue Location Range Copied",
    "[2022-03-28T16:19:20.4590Z] Area of Issue Range Copied",
    "[2022-03-28T16:19:20.5370Z] Attachment Range Copied",
    "[2022-03-28T16:19:20.7550Z] Emailed indicator set in cell 'Facilities Request WB'!K17",
    "[2022-03-28T16:19:20.9120Z] Request done indicator set in cell 'Facilities Request WB'!A17"
  ]
}

  Row 17 is the last row of the current sheet. 

[Yutao]

@KGuthrie-NF - I tried your script with a test workbook and test flow but still couldn't reproduce the issue. If you would like, please feel free to contact me through private message and we can further investigate your case.

[Yutao]

Hello @Kongol, I just heard back from my teammate that the tab color issue has been fixed now! I also verified it myself and my script indeed was able to update the tab color correctly no matter the workbook was open or closed. Please give it a try when you get time and let me know if there are any other issues. Thanks again for your patience!