0

I'm hoping you'll be able to give me some good ideas for this scenario in SharePoint Online.

I need to create a document library (or something else if you have any ideas) with the following features:

  • a. All these will be XLSX files, no need to lock down but for your info. For this example, let's say two columns per row, title and quantity.

  • b. Separate editor/approver for each file, perhaps set by choosing department from List Lookup column on the file.

  • c. Different permissions on each file, only visible by editor/approver (set using same method as b.) and manager/admins.

  • d. First stage approval is between editor and approver.

  • e. Once the first approval stage is passed, a single manager must pass the file through 4 further stages of editing and/or approval.

  • f. A flow to keep the original editor informed on the document's progress and (the difficule part) highlight changes to the document, eg this item quantity changed from 10 to 5.

I've tried my best to simplify this to be able to post, hopefully you can understand the functionality I need.

0

Create a site with and give permissions only to the owners/admins who need access to all files. When a new XLSX file is created, the admin will need to share it out, giving appropriate permissions to the editor/approver. Add a status column to the library, and create a flow with multiple approval actions. The flow could update the status field so that the current approval state was visible to users.

The hard part: tracking changes. Excel doesn't support this OOB. There is a compare spreadsheets tool that is part of office, but it wasn't designed to work with different versions of the file in sharepoint. IF the scenario was as simple as you stated, the simple solution would be to simply have a duplicate column "original quantity", lock it down to prevent changes, and another field "difference" that showed the difference between quantity and original. If the worksheet is more complicated, you could create a copy of the sheet and set a conditional formatting rule to format the cell if its value didn't match the value of the same cell on the other worksheet.

  • Thanks for the reply! I'm not too familiar with approval permissions - only really have experience of automated approval workflow to manager. Is there a specific sharing permission which needs to be created by the admin to specify who is editor and who is approver? Ideally, for the change tracking, it would just be an email from flow saying, this file is now at this status, these changes have been made... – clearwater Apr 25 at 9:26
  • When you select the item and click share, the option to set it to read only or read/write should be part of the sharing dialog. It's simple to email the status, who modified the file, when it was modified, any other metadata, etc. it would require code to be able to send an email with the changes made to the excel file itself. – Mike2500 Apr 25 at 12:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.