cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sam91
Frequent Visitor

Is it possible to use flow to copy cell formatting (Cell Highlight) ?

Hi All, 

I am pretty new to using Power Automate and office scripts. I would appreciate all the help I can get. 

I am trying to copy data (including cell formatting) from one excel table to another excel sheet. 
I have followed this post (@yutao ) successfully to create 2 scripts and running it with power automate. 
Thanks @Yutao 


 https://powerusers.microsoft.com/t5/Using-Flows/Trying-to-use-Office-Scripts-and-Flow-to-copy-from-o... 

 
My problem is that I do not know how to use range.getFormat / other scripts / using powerautomate to copy the highlighted cell format to the new excel sheet. 


Thanks!


Regards

Samantha

6 REPLIES 6
GeoffRen
Microsoft
Microsoft

Can you post what you have so far? Is the formatting for the entire range consistent (so the same formatting for ever cell in the range)? If so, you just need to:

1. Modify the export script to return the format data with the values

2. Modify the import script to accept the format data as a parameter

3. Modify the import script to apply the format data to the range you want

Sam91
Frequent Visitor

Hi @GeoffRen , 

Yes. I created 2 sample excel sheet called A Test and B Test. 
In A Test, I have a table called TableA with below data and cell highlight. 

 

3.PNG

 

I have created an Import and Export Script and incorporated them into Power Automate. The flow ran successfully and the values are copied over. But not the Cell Highlights. 

Script: "Export range"

function main(workbook: ExcelScript.Workbook, sheetName: string, rangeAddress: string): string {
  let sheet = workbook.getWorksheet(sheetName);
  let range = sheet.getRange(rangeAddress);
  return JSON.stringify(range.getValues());
}

 

Script: "Import range:

function main(workbook: ExcelScript.Workbook, sheetName: string, rangeAddress: string, values: string) {
    let sheet = workbook.getWorksheet(sheetName);
    let range = sheet.getRange(rangeAddress);
    range.setValues(JSON.parse(values));
}

 

Flow:

1.PNG2.PNG

 

 

I have tried recording a macro and know that it uses the below script. I can't figure out how to incorporate this into my import and export scripts. 

 

selectedSheet.getRange("A1").copyFrom(format.getRange("A1:C5"), ExcelScript.RangeCopyType.all, false, false);

 

Regards

Samantha

For exporting try this script:

function main(workbook: ExcelScript.Workbook, sheetName: string, rangeAddress: string) {
  let sheet = workbook.getWorksheet(sheetName);
  let range = sheet.getRange(rangeAddress);
  const colors = [];
  const rowLength = range.getRowCount();
  const colLength = range.getColumnCount();
  for (let rowIdx = 0; rowIdx < rowLength; rowIdx++) {
    const rowColors = [];
    for (let colIdx = 0; colIdx < colLength; colIdx++) {
      rowColors.push(range.getCell(rowIdx, colIdx).getFormat().getFill().getColor());
    }
    colors.push(rowColors);
  }
  return { values: JSON.stringify(range.getValues()), colors: JSON.stringify(colors) };
}

And for importing try:

function main(workbook: ExcelScript.Workbook, sheetName: string, rangeAddress: string, values: string, colors: string) {
  let sheet = workbook.getWorksheet(sheetName);
  let range = sheet.getRange(rangeAddress);
  range.setValues(JSON.parse(values));

  const colorsArr = JSON.parse(colors);
  const rowLength = range.getRowCount();
  const colLength = range.getColumnCount();
  for (let rowIdx = 0; rowIdx < rowLength; rowIdx++) {
    const rowColors = [];
    for (let colIdx = 0; colIdx < colLength; colIdx++) {
      range.getCell(rowIdx, colIdx).getFormat().getFill().setColor(colorsArr[rowIdx][colIdx]);
    }
  }
}

Though this will only copy the cell colors over, and very iteratively. I'm not sure if there's a better way that can just get all of the formatting and set all of the formatting for an entire range at once.

Sam91
Frequent Visitor

Hi @GeoffRen , 

Thanks for this script! I tried it on my test files and I was able to copy the colors over.
However, this iterative approach to copy the cell colors works only for a small sample size.

 

I have tried to run this on my original data set which has 400++ rows and my flow has timed out with "Bad Gateway Error 504". I am considering of doing the export and import in batches of 50 by making the cell range dynamic and looping through it in the flow. (Let me know if there are better options) 🙂 

 

Will post back to update in awhile. Hoping someone comes through with a better way of copying the formatting at once. 

 

 

 

4.PNG 

Sam91
Frequent Visitor

Hi @GeoffRen , 

I have tried to run the scripts in a do until loop for 256 records successfully in batches of 6 loops.  
In my flow I have initialized variables so that the script can copy and paste dynamically. 

 

Hence, I increase the pagination count for the "List rows present in a table" to 2000.
When I did that, I encountered a "Out of call volume quota. Quota will be replenished in 16:02:44."

 

I have reverted the pagination and set the loop to only run once and waited for >2hours.
However, I am still seeing the out of call volume error.

Please note that my organization is on the Microsoft Power Automate Free license. I suspect that this could be the reason. Any idea when it will refresh or what is the quota?

 

Flow now: 

Sam91_0-1615882146203.png

 

Sam91_1-1615882172159.png

 



Currently each user is limited to 200 uses of the action per day, which is why you're seeing that error. Per the error message, the reset time for this quota was in 16 hours. 

 

It looks like you only need to run 120 actions in your flow, do you need to do this multiple times per day?

 

For optimizing the script, for your scenario do you need to copy formatting from one workbook to a different workbook? If you only needed to copy the formatting from one worksheet to another worksheet in the same workbook then there is a different api that is built for this purpose, cross workbook formatting copying isn't as well supported.

 

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

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.

Top Solution Authors
Users online (1,588)