cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

excel online error

trouble?
very good day maybe someone has an answer, I have a flow in power automatice that executes an excel script online the script is a Buscav which if I execute it from excel it works but from power automatic it doesn't, does anyone know why and if it is possible fix?

 

What am I looking to do?
I have two tables in excel oline (Table1 and Table5) these two tables in different sheets, I seek to bring the mail when the auxiliary is filled

 

Table1

 

 

 

Table1.PNG

 

Table5Table5.PNG

 

script (the script works if i run it directly in excel online)

function main(workbook: ExcelScript.Workbook) {
  let plantilla = workbook.getWorksheet("plantilla");
  plantilla.getRange("AS2").setFormulaLocal("=BUSCARV(Table1[@AUXILIAR],Table5[#Todo],2,0)");
}

error script excel.PNG

 

Thank you very much

 

 

3 REPLIES 3
Anonymous
Not applicable

hello, as the problem is the formulas power automate does not run complex formulas do a test with the following

rocorfisterra
New Member

Hi, I have exactly the same problem and I think that "setFormulaLocal" is causing the trouble.

If I change it to only "setFormula" inside the script, the flow in PowerAutomate works but the result is "#NAME"; I have to manually click on the formula bar and press enter (without doing anything else), and that solves the #NAME problem.

If you have found a solution I would appreciate sharing it.

Saludos!

rocorfisterra
New Member

FormulaLocal doesn't work; the below script should do (just translate the buscarv function to English)
 
function main(workbook: ExcelScript.Workbook) {
  let plantilla = workbook.getWorksheet("plantilla");
  plantilla.getRange("AS2").setFormula("=VLOOKUP(Table1[@AUXILIAR],Table5[#Todo],2,0)");

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Users online (1,586)