Excel and Automated Script

Question

I'm using Power Automate to update an excel spreadsheet with entries from an MS Form. To ensure that the pivot table I've set-up gets updated correctly, I'm using a script to clear any filters and re-sort the data with in the table. I also have a calculated column, whose values will change if someone were to filter out entries so I am copying the values of the calculated column to a new column, and using it for my pivot table.

The problem I'm running into is that the script is limiting the range to whatever I started with. I'm having trouble uploading a screenshot, but here is the code. You'll see that the copy and pasted range are Q2:Q7 and O2:O7. I want this range to grow as more entries are added to the spreadsheet.

function main(workbook: ExcelScript.Workbook) { let use_Records = workbook.getTable("Use_Records");// Clear auto filter on table use_Recordsuse_Records.getAutoFilter().clearCriteria();// Custom sort on table use_Recordsuse_Records.getSort().apply([{key: 5, ascending: true}, {key: 9, ascending: true}, {key: 8, ascending: true}], false);let selectedSheet = workbook.getActiveWorksheet();// Paste to range Q2:Q7 on selectedSheet from range O2:O7 on selectedSheetselectedSheet.getRange("Q2:Q7").copyFrom(selectedSheet.getRange("O2:O7"), ExcelScript.RangeCopyType.values, false, false);let eNF_Pivot_Table = workbook.getPivotTable("ENF Pivot Table");// Refresh eNF_Pivot_TableeNF_Pivot_Table.refresh();}

My question is whether there is a way to have the script copy an entire column in a table and then paste only the values into a second column?

Answer 1

Hi Scott,

If you'd like to copy an entire table column, you should be able to use a structured reference, like so:

 selectedSheet.getRange("Q2:Q7").copyFrom(selectedSheet.getRange("TableName[ColumnName]"), ExcelScript.RangeCopyType.values, false, false);

Alternatively, you could get the range of a table column like so:

workbook.getTable("TableName").getColumnByName("ColumnName").getRangeBetweenHeaderAndTotal()

You'll need to make sure that the dimensions of the destination range match that of the source range. If you run into issues with that, you might need to construct the destination range address using the row count of the source range.

Let me know if that helps or if you have other questions!

Answer 2

While the script can be used by power automate, it's generated by Excel automated, which is why I posted it here.

Answer 3

Dear Scott Scifres,

Good day! Thank you for posting to the Microsoft Community.

This forum primarily focuses on Microsoft 365 and Office built-in features.

Since your issue is related to the utilization of a Power Automate script and our forum has limited knowledge on this topic, I sincerely recommend posting your concern to the specific channel. Microsoft Power Platform Community by clicking on Post a question.

Members and engineers over there are proficient in the knowledge of Power Automate issues. We are sure that our experts from that team can address your query effectively and accurately.

Thanks for your understanding and cooperation! Hope you have a nice day!

Sincerely,

Ralph Chawatama | Microsoft Community Moderator