Unanswered

Update row in protected sheet - Excel

(1) Share

Report

Posted on by gmurnock

I'm trying to update a table (row) in a protected Excel Sheet however unable to see/navigate or even manually type the spreadsheet name in the "File" required field in the Excel Online (Business) - Update a row Action.

I successfully complete above on a non-protected spreadsheets but not a protected one. Any ideas?

Categories:

Building flows

All responses (11)

Answers (0)

Sort by

Pstork1 69,665 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

The whole point of a protected sheet in Excel is to keep users from changing it. So why would you think that MS would let you change it using Flow if you can't change it using Excel?
gmurnock 57 on at

Like
a
(0)

Report
Copy link

Link copied!

Discovered the issue is editing a row on a Macro enabled spreadsheet (xlsm). Anyone have knowledge of editing an Excel with Macros?
gmurnock 57 on at

Like
a
(1)

Report
Copy link

Link copied!

We are updating a hidden row, hidden from basic users so they can't change anything used in calculations, used for reference in another sheet.
Pstork1 69,665 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

I understand why you want to do it. I just don't find it a suprise that Flow can't see the row if its hidden and protected by a password. Flow is connecting to the worksheet using OAuth credentials so its really not in a position to respond to a unique password challenge from a protected sheet.
Alwaysme2022 16 on at

Like
a
(7)

Report
Copy link

Link copied!

Thank you for ManSplaining to us... it was clearly very helpful..
Pstork1 69,665 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

So would you have preferred the answer "No you can't". Sorry that answering a question that was asked with an explanation offends you.
yosefmeng 2 on at

Like
a
(0)

Report
Copy link

Link copied!

you can copy the content of the excel and recreate the file, then do the manipulation.
km3000 9 on at

Like
a
(1)

Report
Copy link

Link copied!

This is an old post and may be covered else where but figured I'd share.
My way around this was to create an Excel Script that would Unlock the worksheet as well as another Excel Script that would Lock the worksheet.
Within power automate, before the update row step, I added the connector/step to Run a Script and selected the Unlock script. Then, after the update row step, I added the same connector "Run a Script" but this time selected the Lock script.
NJMUser 25 on at

Like
a
(0)

Report
Copy link

Link copied!

Interesting, thank you! Does this work on Excel Business (online)? Because I don't think Macro-enabled Docs worked on sharepoint, and this is where I would need the excel doc housed.
km3000 9 on at

Like
a
(0)

Report
Copy link

Link copied!

Yes, this does work with Excel Business Online. That is where I implemented this.
Macros don't run on the Web, so this is why I had to go this route.
Works, but I have noticed the connectors for Run Script, can, intermittently, take unusually long while flow runs.