You’re offline. This is a read only version of the page.
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
Thanks!
Regards
Samantha
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.
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:
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.
For exporting try this script:
Share
Report
All responses (
Answers (
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.
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.
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:
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
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
#1
Tomac
986
Moderator
#2
stampcoin
699
Super User 2025 Season 2
#3
Riyaz_riz11
577
Super User 2025 Season 2
