Approving Changes Made to A Shared Sheet

Hi all, long-time listener, first-time caller.

I have been tasked with discovering a way that a sheet owner can control updates being made to a shared sheet through some form of 'approval' feature or process.

My scenario is this: there is a master sheet displaying all approved, company-wide projects, complete with columns for resources allocated to each project, project/resource start and end dates, color balls tracking schedule, cost & scope and a few other static columns used to document the parent row level (project name).

The sheet is shared with all resources (Viewer) and Project and Portfolio/Program Managers (Editor). The issue has been some PMs have updated resource allocation percentages (as most resources are bing used on multiple projects for X amount of time) on other projects, along with other data fields which has created a free-for-all environment, if you will.

Aside from locking columns that can be edited, or spitting the projects into their own sheets and setting permissions there (too many sheets will need to be created), is there a way to have the sheet owner approve a change that is submitted, enabling the updates to populate the field, or reject an update, disallowing the field to be updated?

My idea was to duplicate the master sheet and share the duplicate; receive alerts on updates to the duplicate and somehow link those approved changes into the master sheet (which is viewable to the Execs). However, upon thinking of this solution, it seems that there is a high level of manual effort for the sheet owner, which is what we want to avoid. Is there a workflow/check-box/formula solution? Web form that can update cells instead of adding a new row? Or other ideas?

Thanks in advance for the feedback.

- Eric

Find more posts tagged with

Sheets

Sharing and Collaboration

Update Requests

Email Notifications

Account and User Management

Comments

Ajay Jindal

Another suggestion (instead of creating a duplicate sheet) - lock down the columns in your master sheet (so only Admins can change them). Create additional columns that the PMs can modify. Set up notifications when those are changed.

Then you can review the "change request" and copy and paste the changes from the "change request" of columns to the locked columns - perhaps even add a note about approving or rejecting change. Not too different from your idea of creating two sheets, except that it is all in one sheet.

J. Craig Williams

Eric,

I don't like the copy&paste process.

"Copy and Paste is the work of the Devil" - Craig Williams.

I've been experimenting with Zapier (the free version - www.zapier.com) and AppSheet for this situation.
Zapier can move and update rows.

AppSheet allows a form to update a row instead of adding one.

I think the answer lays there.

One of the underlying Smartsheet design items is collaboration and having a
"reject" button is counter to that (in my opinion). That is not to say it isn't what we want, but that's why we need to find work-arounds.

Craig

Brad Jones

+1 to this idea.

Build into this the ability to send a rejection message coupled to an Update Request.

That way, if a user submits something that does not make sense or is invalid, then the admin can reject the change and ask for it to be corrected all in one step. Best part of the deal is you are preventing data corruption before it happens, rather than trying to send requests to careless people who don't always clean up after they've made a mess.

J. Craig Williams

It really depends on the autonomy of the users and the role of the "admin".

From your posts Brad, your role seems very much of a Smartsheet Champion, keeping the users on track and using Smartsheet per your processes.

And trying desparately to prevent them from breaking something.

I get notifications on several dozen sheets across half-a-dozen different accounts - but nearly all of them are stuffed away in case I get an email (from a person) saying something has broken, I'm not being paid to open those emails until there is a problem.

Thinking back to when I had a team, my expectations were that if I sent an Update Request it would be updated. When we started, I would check that the data was updated with reasonable values and updated at all - if they weren't, training or a clarificaton of expectations would be done. As trust grew, I would check less.

For those employees that seemed to perform better when I was watching and didn't improve that over time, I'd start looking for new employees.

That said, Update Requests has needed an overhaul and having there be a second (or subsequent) rounds of communication is a good thing.

Craig

J. Craig Williams

Understood and agree.

Craig

Brad Jones

Believe me, I'm trying to get the managers on site to take over that role, but it has been a hard sell. None of the people work for me directly, so I'm doing what I can with the system designed as it is.

I don't consider this feature to be one of the nicest ones that I've bumped. Most of my users understand my questions and respond correctly. But for my users who made three mistakes on the row, and I ask for three fixes but get 1/2 of a fix, then I'd like to be able to spot that faster.