Azure/Graph add rows to protected Excel worksheet

Azure/Graph add rows to protected Excel worksheet
0

Upvotes

Upvote

 Jul 14 2023
2 Comments (2 New)
New

I'm using an Azure web app to add a row to an Excel workbook worksheet table.  This works fine, adding rows as needed, right up till I turn on worksheet protection for this worksheet.

 

My Azure account was created by the same user (email address) as my Office 365 Business Basic account (the one that is currently $6/month), so Excel ought to see my row being inserted by the Workbook owner.

 

I enable protection and tick the "add rows" box to allow rows being added.  I invite "myself" for sharing, using the e-mail address that is the Office 365 Business license owner and also the Azure app owner.

 

Soon as worksheet protection is enabled, the azure app gets 403 forbidden when it attempts to add a row.  Turning worksheet protection back off, the azure app resumse inserting rows just fine.

 

My goal here is to all the Azure App and Office Business owner to add rows to a worksheet of defined structure, and to prevent humans from breaking the structure and thus breaking the app. (While i realize a database would be the preferred method for this scenario, with Excel reading its worksheet table data from the database, I don't get to make that call.  "Excel only" is a requirement I have to work with)

 

I don't care about password protection.  I just want the owner to be able to add rows, and for humans the workbook file is shared with, they need to be able to work on other worksheets to do their pivot tables and whatever all else, using the data worksheet as their source, but never being able to change the data worksheet.

 

I think this functionality does not exist and/or does not work, but maybe my azure app identity as seen by Excel isn't the Excel sheet owner?  If that's the case, my idea submission here will be for the 403 forbidden response, or maybe "access denied" log  on the Excel side, to report attempted accesse where permission was denied, and to supply the attempted credential.  That way it would be apparent if the Azure app connected via Graph lacks "owner" status and perhaps that my worksheet sharing grant on the azure app owner email address isn't "enough" for the azure app to get its job done, adding rows to a protected worksheet where "add rows" is set as being permitted.

Comments
Copper Contributor

Forgot to say - when I enable worksheet protection and share with the email address of the azure app account (which, agian, is the same account at the Microsoft (Office) 365 account, the result of submitting the share dialog is a message about how anyone with the link can access the excel worksheet. 

I suspect maybe with protection enabled and "add rows" allowed, the sharing URL for the Excel worksheet might be required. Graph, however, uses a Graph URL.  Maybe that is the issue.  Sharing would need to be permitted for the account having the e-mail address I shared with, but I think maybe sharing is just allowed via a sharing URL rather than based on the user I've shared with, considering the message that anyone with the URL can access the worksheet despite my (attempt at) sharing the worksheet with a particular account such that the share is permitted via user account rather than being permitted via a particular URL (which is not a Graph URL)

Copper Contributor

Bing AI bot tells me "This should grant permission for the Azure app to edit the file via Microsoft Graph URL even when worksheet protection is enabled" about going into Protect Worksheet and ticking "insert rows" but alas this seems not to work.  Rows do add until I set protection with "insert rows' and then it's all 403.