Power Automate

Add row to Excel problem

(1) Share

Report

Posted on by RS-29010719-0

Hi,

Problem which is wrecking my head!!!

I have a flow which adds a row to an online Excel file when a form is submitted. The flow works fine.

However, I've been asked to protect the sheet because there is other data we don't want users to amend. Unless something has changed there doesn't seem to be a way to unprotect and re-protect a sheet from a flow.

Therefore, my idea was to create a hidden sheet, drop the data from the flow into this sheet, then create links from the main sheet to the hidden sheet which contains the table where the automation drops the data.

However, the automation works and the data is dropped, but because it's inserting a row to the table, the references from the other sheet don't pick up the new row.

For example, if I have a cell in the locked sheet pointing to cell =A2 of the table, when the row is automatically added, the formula changes to =A3

It's obviously because a row is being added, but is there any way to stop this?

Thanks.

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

trice602 15,404 Super User 2025 Season 2 on at

Like (1)

Report

Hi,

You can protect and unprotect with Office Scripts and run it from a cloud flow action and works pretty well. What I do is create a range of cells that are editable and then protect everything else.

Protect:

function main(workbook: ExcelScript.Workbook) {
let sheet = workbook.getActiveWorksheet();

// Protect the sheet with a password
sheet.protect({
password: "YourSecurePassword", // Change this password
selectUnlockedCells: true, // Allows users to select unlocked cells
allowAutoFilter: true, // Allows filtering
allowSort: false, // Prevents sorting
allowInsertRows: false, // Prevents row insertion
allowInsertColumns: false, // Prevents column insertion
allowFormatCells: false, // Prevents formatting changes
allowUsingPivotTables: false // Prevents pivot table usage
});

}

Unprotect:

function main(workbook: ExcelScript.Workbook) {
let sheet = workbook.getActiveWorksheet();

// Unprotect the sheet using the password
sheet.unprotect("YourSecurePassword");

}

------------------------------------------------

⭐ If this was helpful, please like and mark as a verified answer to help others find this too!

Always glad to help! 💯💯💯💯💯

Tom

Follow me on LinkedIn - Thomas Rice, PMP | LinkedIn

Was this reply helpful? Yes No
RS-29010719-0 69 on at

Like (0)

Report

@trice602

Thank you, however our central IT bods have disabled scripts.

Was this reply helpful? Yes No
trice602 15,404 Super User 2025 Season 2 on at

Like (0)

Report

Hey there!

I am following up on this post add row to excel problem in this category building flows from 02/11/2025. I know the post is a little older, but it is a common question here so wanted to take a moment and follow-up. I see there have been 2 replies and 1 likes and asking if your question has been answered. If so, please help the community out by completing one or more of the following actions! Thanks in advance!

Easy as 1-2-3 ‼️💯⭐😎🆒🙏

1) Mark a reply as a verified answer - this is essential, and it helps others with the same general question find this thread. ✅

2) You can also mark one of more replies as a verified answer. Please take a moment and give the author credit for volunteering their time. ✌️

3) Like one or more replies. Kudos always greatly appreciated! 👍

Pro tip: if you found your own solution or workaround, feel free to add a reply to your own post and mark it as the solution too! The goal is to get this thread marked with a verified answer by anyone that finds one or more replies helpful!

Again, thanks in advance for reading and responding, always glad to help! Tom 💯💯💯💯💯

Was this reply helpful? Yes No
trice602 15,404 Super User 2025 Season 2 on at

Like (0)

Report

If I can help with anything else please let me know or tag me in a future post, I enjoy reading and answering questions. Always glad to help! Tom 💯💯💯💯💯

Was this reply helpful? Yes No