0

I am trying to reference another file from my blank script file. I just want to use the script to clear filters using this method via power automate. I do not want power query or other methods.

The typescript here will only look the current opened excel, how can I run this for another file? The reason I cannot put this code in my target file is due to restrictions. so I am making a copy of the file via power automate , then want to run a script to clear filters.

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet= workbook.getWorksheet("Draft");
selectedSheet.getAutoFilter()
  .clearCriteria
selectedSheet.getAutoFilter()
  .remove

}

any help is appreciated.

Jonnyboi
  • 505
  • 5
  • 19

1 Answers1

2

Office Scripts are generic scripts that, if written properly, can be executed over multiple workbooks. However, from a script, you can't "reference" any other workbooks other than the current workbook, this is by design.

This isn't the exact example you're wanting but it shows how to make one or more workbooks talk to each other. The mechanism is very indirect and it's achieved by using PowerAutomate.

https://learn.microsoft.com/en-us/office/dev/scripts/resources/samples/combine-worksheets-into-single-workbook

So bottom line, if you want to run the script over different workbooks in an automated fashion, you need to use PowerAutomate/LogicApps.

It's also possible to pass in parameters that will also be made available when you configure the script in PowerAutomate/LogicApps.

https://learn.microsoft.com/en-us/office/dev/scripts/develop/power-automate-parameters-returns

Operation

Run Script

Skin
  • 9,085
  • 2
  • 13
  • 29
  • thanks, my whole objective is to 1) Create a copy of the master file 2) delete a few rows from the top 3) remove autofilters (because i want to create a table) 4) Create a table 5) read this data into power app. – Jonnyboi Mar 17 '23 at 13:17
  • Sorry, just saw this comment. Unfortunately, you have to string it all together, you simply cannot reference another workbook because the scripts are independent of a workbook, they can be used and referenced from any workbook. You can, however, return data from a script that is then able to be referenced in the PA/LA flow. – Skin Apr 05 '23 at 02:58