cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MattBrown
New Member

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

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
  });
1 ACCEPTED SOLUTION

Accepted Solutions

@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")

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

8 REPLIES 8
Drrickryp
Super User
Super User

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.

 

 

 

 

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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

@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")

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

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

Chipper
Helper I
Helper I

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

RandyHayes
Super User
Super User

@Chipper 

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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
Top Kudoed Authors
Users online (2,354)