How to enable users to view entire sheet, but only edit rows assigned to them

I want all the users in the dept to view the entire sheet, but only able to edit rows with their name in the "project owner" column. How can I do that?

We use one sheet to list all projects in our dept. So we are adding and removing rows as new projects start and old ones end.

We want all the dept personnel to be able to view the entire sheet, but only edit those rows with their name in the "project owner" column.

I currently schedule weekly update requests to users by rows assigned to them. But these rows change over time, so this is not a scalable process to manually monitor what rows have been added or removed per user.

I tried creating a report for each user filtering for only rows with their name in the "project owner" column. This works great to automatically keep up to date on which rows are theirs.

But since they have only view rights on the master sheet, they cannot edit the report view in order to update "their" rows.

Find more posts tagged with

Comments

Yonnette Joseph

I asked a similar question - basically the next level would be assigning user permissions! In my case, I want to be able to assign cell permissions on update requests that we're sending to external partners.

Jeff K.

Not sure if this is possible right now.

Workaround:

Have everyone as a Viewer to the sheet (Master Sheet)

Create 1 report per person so that when the report is runned, it will only contain the rows with their name (e.g. "Assigned To" column). Give that person Edit privilges to that report so they can modify the cells

When the report is saved, it should automatically update in the Master Sheet which the report built from.

Clark Walliser

Hi Jeff,

Your suggestion of a report is the first thing I tried. But my test case did not work.

I gave one user View only permissions to the main sheet.

Created a report showing only rows assigned to him.

Shared the report with him as editor (cannot share).

He was unable to edit any of the columns on the report.

Are you saying that this idea can work?

If so, any helpful hints on what I might have missed?

Jeff K.

Are any of the columns and rows locked in the main sheet?

If not, then I suggest you email smartsheet support. It seems like something is broken.

Another thing I can think of is the person isn't a free user in your group (he received an email to join Smartsheet as a user in QQQ team/group), instead he had a trial account of some sort.

Brett Evans

Turn it around.

Create a sheet for each user that just has their projects and only that individual (and you as admin) can edit. Give other other users view access to sheets they cannot edit.

Then create a report that pulls in all individula sheets and give all users edit access. The users will only be able to edit the rows that they had access to in the underlying sheet, but everyone will be able to see all of the projects in the report.

Clark Walliser

Hi Jeff, Based on your response, it sounds like you think it should work.

I think I will contact smartsheet support.

Hi Brett, creating separate sheets for each person goes counter to our desire to consolidate information. While it would technically work, it's not practical for our situation. But I like the out of the box thinking

Richard Rymill SBP

Clark

The reason whay my colleague Brett has turned it around, is that you cannot do what you originally asked about " I want all the users in the dept to view the entire sheet, but only able to edit rows with their name in the "project owner" column. How can I do that?"

The answer is that Smartshee cannot do that. Giving an editor the right to edit parts of a sheet relevant to them, also gives them edit rights to the rest of the sheet, which often not desirable, ( for the Report to work with edit rights, they also need to Edit the whole sheet, which was not clear in Jeff K's respose) , so using the tools offered, Brett has suggested a way to achieve this within Smartsheet architecture as it exists right now. I suggest you take another look at that?

Failing that, if you need to "stick with a master project sheet" you can get what you want, but you need to use Appsheet WITH Smartsheet, which then allows users to only see and edit, what the PMO wants them to edit and nothing else.

As you will appreciate the above solution has many benefits and is being taken up by numerous of our clients right now because it extends what Smartsheet can be used for which is terrific.

Hope that helps?

RichardR

Clark Walliser

Hi Richard,

Thanks for the idea about Appsheet.

When I looked it up, I get the impression that it is used for developing mobile apps. But we will be mostly working on laptops. Is it designed to create apps for computer UI also?

If there is no standard way to control permissions by row, maybe the best long term solution is Brett's suggestion to build separate sheets for each project and use a report to roll them up. With over 100 current projects, that sounds like a lot of work, but maybe it is the least work in the long run.

"If you work with the system, the system will work".

Thanks,

Clark

Richard Rymill SBP

Clark, totally agree. Use Smartsheet for what it is good at, which is plenty and if you do need to stretch it further by using API links or such as Appsheet, I would just make sure you "Sweat the Smartsheet asset first" as any fool can make things complicated, it takes a clever workflow designer to keep it simple (for the users).

You are right Appsheet is really good with mobile or ANY device really, but @ half our clients use it on a PC or Laptop for the considerable added functionality it brings to Smartsheet, Excel and Google sheets by tying them together to create controlled conditional Logic solutions.

Let us know how you get on. I'm sure Brett will be pleased if his idea works!

Regards

RichardR

stevek116

I am a new user. I was reading this post from February. Is anyone aware of a subsequent release addressing this need? Thanks in advance

Johne Johnson

The easiest way to accomplish this is to think of the sheet like a database and not a sheet at all. Build the sheet and then highlight the area you want them to contribute to and send them a request to update.

Highlight the first row and go to the bottom of the selection and use a "Shift" click to highlight the last row. Then right click and go into the "Send Update Request" to get them to fill in the information. You can also automate this like I did to send the same rows daily, Weekly, Monthly or Yearly" under the Delivery Options tab.

laurie.stack44711

I stumbled across this year-old post. The later answers in the thread are correct. Your ability to edit items in a report depends on your access level to the master sheet. This is an important benefit (even though it seems like a limitation). Otherwise, another user could put a report on top of your secured sheet and open access to cells you don't want to give access to. Usually I've found that Smartsheet dev is based on providing great innovation while limiting unintended consequences.

That said, there is a solution for this problem now (as of Oct 2018) for Enterprise-level users. The Dynamic View module allows you to set up what I would call "secure filtered views",

ffonse5109711

Hello.

I think smartsheet did not allow you to protect specific cell or create a password in order to protect certain range. The only solution that I found is the follow. Maybe it will work for some of you.

You have your main file whit all your precious information. You want some other user to edit just part of the spreadsheet but you don not want to allow him to edit compleaty.

The solution that i found is as a follow:

Create a copy of your layout in a new spreedsheet with the same fields at the top. Then underneath your fields, set up a link (There is an icon at the top of your smarthsheet menu) with the information that you want to share but do not want it to be edited. The link will gave you the same Information from your original file, but just the information. Then, you can set up some cell to be filled by your user. (this will not affect your original file)

The Link allows you to link part of the information from other sheet, the only restriction is that just gave you the opportunity to share just 500 cells. But you can create so many links (that solve the problem of the restriction)