Answered

How do I run a script recorded in excel via power automate?

(0) Share

Report

Posted on by MattBrown

I am trying to run a simple script I have recorded on Excel which I want to run as a step in power automate.
When I try and run the script I get this error message in power automate

We were unable to run the script. Please try again.
WorksheetCollection.getActiveWorksheet() is not allowed when run via Power Automate. Please modify your script to specify a target worksheet.

The script it copied below - I suspect this is something very easy - but have limited experience of scripts / VBA other than using the record function and cant find the solution anywhere - so any help would be most appreciated.

Many thanks

Matt

function main(workbook: ExcelScript.Workbook) {

let selectedSheet = workbook.getActiveWorksheet();

// Replace Stongly Agree with 5 on sheet student data

selectedSheet.replaceAll("Stongly Agree", "5", {

completeMatch: false,

matchCase: false

});

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (1)

Drrickryp Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Hi @MattBrown

I don't think you can run an Excel script from within PowerApps. However, you should consider using a PowerApps function to accomplish the same thing. It looks like you want to make some changes in the data. Perhaps you could use Patch() or UpdateIf() to solve the problem.

Was this reply helpful? Yes No
MattBrown 15 on at

Like (0)

Report
Copy link

Link copied!

Thanks Drrickryp for the swift responce - it is really appreciated.

I will have a look at Patch() and Updateif() to see if that works - however having spent a bit of time today looking at the microsoft documentation e.g. https://docs.microsoft.com/en-us/office/dev/scripts/tutorials/excel-power-automate-manual it does look like you can now run office scripts in excel - however, in all the video tutorials they provide it works like clockwork - there is obviously something I am missing about how to target the script to a specific worksheet - hence the error message I am getting.

If you do have any more ideas about that I would really appreciate any thoughts

Cheers

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!

@MattBrown

You might consider re-reading the docs on the use of scripts:

Power Automate can't use relative references like Workbook.getActiveWorksheet to access workbook components. So, we need a workbook and worksheet with consistent names that Power Automate can reference.

That is in fact the very thing you are doing in your script...so, you'll need to rewrite that accordingly.

Was this reply helpful? Yes No
MattBrown 15 on at

Like (0)

Report
Copy link

Link copied!

Thanks - that makes perfect sense.

However, this is where my lack of expertise is really embarrassing - how do I do I stop the references being relative - I have played around with the script but just cant get it to work and everything I search for is too complex for me - I dont suppose you (or anyone else) can point me in the direction of something that can show me how in a fairly straight forward way?

Thanks

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like (2)

Report
Copy link

Link copied!

@MattBrown

Take a close look at step #3 in the Create and Office Script of the link you referenced.

The key is that you need to get things by their names. So, instead of let selectedSheet = workbook.gertActiveWorksheet() replace that with let selectedSheet = workbook.getWorksheet("theNameOfYourWorksheet")

Was this reply helpful? Yes No
MattBrown 15 on at

Like (2)

Report
Copy link

Link copied!

Thanks @RandyHayes -you are a total star - who has saved me potentially hours of work.

I had tried entering the sheets name beforehand- but suspect I hadn't removed the "active" from the line.

thanks ever so much

Matt

Was this reply helpful? Yes No
Chipper 116 on at

Like (0)

Report
Copy link

Link copied!

Is there any other thoughts on this? I am getting the same error still.

Was this reply helpful? Yes No
RandyHayes 76,299 Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!

@Chipper

Since this post is quite old, I would recommend opening a new post with the issue you are experiencing.

Was this reply helpful? Yes No