Power Automate considering local sheet an external connection when using setFormulaLocal()

Copper Contributor

Hello everyone, 

 

I have been struggling to use a simple Index/Match function in one of my scripts. I making a Cloud Flow generate reports, but I need the reference of an external sheet. 

 

Today it is possible to reference external sheets in Excel for the web easily, but Excel is forcing to "Enable content." Therefore, my formula doesn't bring the referenced value because the external file, curiously in on the same SharePoint library, is not trusted. 

 

To overcome this "limitation," I have added new steps to copy the external sheet into the local sheet I want to use just to avoid external links.

 

I thought the problem was solved, but when I tried to run the automation, now using the function setFormulaLocal(), now referencing a local sheet, Excel was asking again to "Enable content' as it was considered an external link. 

 

After examining the cell, the formula used a full URL pointing to the same file, and Excel considered it an external link. I believe this is a bug (Update 2: It's not a bug). But I still haven't found a way to avoid this "Enable Content" on Excel for the Web. Locally I know how to change the Trust Center options, but that didn't work for my web-based excel file.

 

MatheusAntunes321_0-1659914671939.png

 

MatheusAntunes321_1-1659914689571.png

 

The script used:

 

 

 

// Get Report sheet
let finalReportSheet = workbook.getWorksheet("Report");

// Applying Formula - Sub 1 (Column B on Current Subs)
finalReportSheet.getRange("G2").setFormulaLocal(`=IFERROR(INDEX('CurrentSubs'!B:B,MATCH([@Article],'CurrentSubs'!A:A,0)), "-")`);

 

 

 

- When running from Power Automate, this is what is in the cell:

 

 

=IFERROR(INDEX('https://companyname.sharepoint.com/sites/E-commerce/Macros/Automation Files/Job Aid Generator/Input/[CurrentSubs]CurrentSubs'!B:B,MATCH([@Article],'https://companyname.sharepoint.com/sites/E-commerce/Macros/Automation Files/Job Aid Generator/Input/[CurrentSubs]CurrentSubs'!A:A,0)), "-")

 

 

- When running Office Script "Run" button:

 

 

=IFERROR(INDEX(CurrentSubs!B:B,MATCH([@Article],CurrentSubs!A:A,0)), "-")

 

 

 

Update 1: Attempt to remove links using removeAll() [Failed]

I tried to remove the link using a simple replaceAll, but it did not work. The link comes back, and my report keeps breaking. Again, it works when executing it manually by pressing "Run" (Code editor), but it does not work when it comes from Power Automate. Please, folks, I need your help.  :\

 

 

// Attempt to Remove External Link
finalReportSheet.replaceAll("https://companyname.sharepoint.com/sites/E-commerce/Macros/Automation Files/Job Aid Generator/Input/[CurrentSubs]", "", { completeMatch: false, matchCase: false });

 

 

Update 2: I have found what was wrong [Problem Solved]

I saw that I had another setFormulaLocal(), and it was working perfectly. But why?

As I mentioned, I am bringing the external sheet to the report so I can perform lookups without using external links as the "Enable Content" thing would ruin my report. I realized I was running the script (Power Automate) before adding the sheet, and that was the reason Excel for Web was generating a link instead. But my second question about the "Enable content" remains open.

 

Questions:

1) How do I ensure the formula is pasted without using a link?

    R: Make sure you have it available in your workbook before referencing it. [Solution]

2) How to disable "Enable content" on Excel for Web?

 

 

0 Replies