Pass value from excel script to dynamic content in Power Automate

Question

I have a value in an excel script which I am able to see and to execute.
I thought i can push it via return to the dynamic content list in Power Automate.

  console.log(VALUE); return VALUE();

But there is nothing present in Power Automate. So how can I push variables to Power Automate?

Answer 1

Here's an example

let countA = 0; let countB = 0; let countC = 0;

for (let i = 0; i < usedRange; i++) { if (!range.getCell(i, 0).getHidden()) { if (i < 10) { countA++; } else if (i < 20) { countB++; } else { countC++; } } }

console.log("countA: " + countA); console.log("countB: " + countB); console.log("countC: " + countC);

// Create object with named variables let outputObj = { CountA: countA, CountB: countB, CountC: countC };

// Set output to object return outputObj;

You can then use the output of this script in Power Automate as follows:

set(variables('excelOutput'), outputs('Run_script')?[' body'])

This will store the output of the "Run script" action in a variable called "excelOutput".

To use the individual counts in other actions in your flow, you can add "Compose" actions for each count and enter the following expressions to retrieve the count from the "excelOutput" variable:

variables('excelOutput')['CountA'] variables('excelOutput')['CountB'] variables('excelOutput')['CountC']

Answer 2

Hi Max

I'm AnnaThomas and I'd happily help you with your question. In this Forum, we are Microsoft consumers just like yourself.

Based on the screenshots, it looks like you're trying to use the "Compose" action to pass a value from your Excel script to dynamic content in Power Automate.

Here's how you can do that:

In your Excel script, use the following code to set the value of a variable:

var excelValue = <your value>;

Replace "<your value>" with the actual value you want to pass to Power Automate.

In the "Compose" action in Power Automate, enter the following expression:

variables('excelValue')

This will retrieve the value of the "excelValue" variable and use it as the output of the "Compose" action.

Save and run your Power Automate flow. The value from your Excel script should now be stored in the output of the "Compose" action and can be used in other actions in your flow.

Note: Make sure that the data type of the "excelValue" variable matches the data type of the output you want to pass to other actions in your flow.

I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

Best Regards,

AnnaThomas

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.

Answer 3

Thanks for your reply I am still struggling with the output:
My code in excel automate:

let countA = 0; for (let i = 0; i < usedRange; i++) {if (!range.getCell(i, 0).getHidden()) {countA++;}}

console.log("countA ") console.log(countA);

//NEW suggestion from our side

 var excelValue = countA;

Output Log:
CountA

7

This ne works with my countA

Now in Power Automate i have added the compose:

The Teams Message:
as you can see is:

Answer 4

Great to hear that your code is working and that you're able to see the value of countA in the output log! To pass the value of countA to Power Automate, you can modify your code as follows:

let countA = 0; for (let i = 0; i < usedRange; i++) { if (!range.getCell(i, 0).getHidden()) { countA++; } }

console.log("countA "); console.log(countA);

return countA;

This will set the output of your Excel script to the value of countA, which can be used as dynamic content in Power Automate. To use the output in Power Automate, you can add a "Compose" action and enter the following expression:

outputs('<your Excel script name>')?[' body']

Replace "<your Excel script name>" with the actual name of your Excel script.

Answer 5

Thanks again for you help, now the first step works.
A little bit different in the power Automate GUI but it works.
Results is:     "inputs": "@outputs('Run_script')?['body/result']",

So a last question, how can I pass more than one variable and name them?

This on worked now with your suggestion of:

  return CountA;

But I also want to pass CountB - CountM and it would be also nice to see CountX in dynamic contents instead of "result"