How to create a sheet that a user can search and update

ougrin

Can I generate a spreadsheet containing account numbers, allowing other users to search for specific account numbers and modify certain columns like address or phone number, while keeping other columns protected from editing? Is this functionality available in Smartsheet?

Brian_Richardson

No not at all - I just was answering the question on locking columns. Locking a column restricts Editors from changing any data for any row in that column.

For managing restricted access to data there's a few options. For me it depends on how picky I need to be about access. Typically I trust people… it's rare for someone to have time or interest to mess with other people's data, but certainly there's cases where you want to be very restricted.

• Easiest, but least restricted: Create a report that has only the columns you want on it. Set any column that you do not want updated to be locked on the source sheet. Those columns will be uneditable in the report. Share the report and sheet with your users for edit. Pro: easy to manage and setup, full functionality, users can search in the report. Con: you "secretly" have to give access to the whole sheet. So..if a user is smart they can browse and find it and edit anything they want that isn't locked.
• Middle of the road: Create the same report but share it via a Workapp. Workapp permissions override the core Smartsheet permissions. You do not have to grant permission to the underlying sheet or even to the report in Smartsheet, just have to grant permission to the report in the Workapp. Pro: more locked down, still uses familiar interface, restricts users. Con: more convoluted to setup and maintain, users cannot change view modes in Workapp (ie if you want to let them do cards or timeline views etc)
• Locked down: Create a Dynamic View from the sheet that just has the columns and filters that you want to apply. You don't need to share underlying source sheet, Dynamic View permissions override sheet permissions. Pro: most controllable Con: different look and feel, limited functionality esp for sorting/searching

Brian_Richardson

Yes you can lock columns, that prevents anyone other than admins modifying the data in those columns.

ougrin

So users will have to access the sheet directly? There's no way to search/update using Dynamic View or Forms to update the sheet? Was hoping to not need to give users access to edit the sheet directly but instead use a form or dynamic view to simplify things. I have tried searching in dynamic view and the only options I saw were for filters (needing to create a filter for each item searched which is unproductive when you have over 11,000 fields of data) or using Ctrl+Alt but this will only search what is currently viewed (won't scroll up or down within the sheet), basically useless.

Brian_Richardson

No not at all - I just was answering the question on locking columns. Locking a column restricts Editors from changing any data for any row in that column.

For managing restricted access to data there's a few options. For me it depends on how picky I need to be about access. Typically I trust people… it's rare for someone to have time or interest to mess with other people's data, but certainly there's cases where you want to be very restricted.

• Easiest, but least restricted: Create a report that has only the columns you want on it. Set any column that you do not want updated to be locked on the source sheet. Those columns will be uneditable in the report. Share the report and sheet with your users for edit. Pro: easy to manage and setup, full functionality, users can search in the report. Con: you "secretly" have to give access to the whole sheet. So..if a user is smart they can browse and find it and edit anything they want that isn't locked.
• Middle of the road: Create the same report but share it via a Workapp. Workapp permissions override the core Smartsheet permissions. You do not have to grant permission to the underlying sheet or even to the report in Smartsheet, just have to grant permission to the report in the Workapp. Pro: more locked down, still uses familiar interface, restricts users. Con: more convoluted to setup and maintain, users cannot change view modes in Workapp (ie if you want to let them do cards or timeline views etc)
• Locked down: Create a Dynamic View from the sheet that just has the columns and filters that you want to apply. You don't need to share underlying source sheet, Dynamic View permissions override sheet permissions. Pro: most controllable Con: different look and feel, limited functionality esp for sorting/searching

Brian_Richardson

Sorry I forgot one. If you Publish a sheet as editable then users do not need permission in Smartsheet to the sheet in order to edit data. This is a fairly full featured option, but one huge drawback is the published link is usable by anyone who finds that link. Most companies tend to restrict Publishing for that reason, but it's an option. You can limit the published link to just your account to mitigate some of that risk. The other drawback is you won't know who is editing your data as they are anonymous when coming over a published link.

ougrin

Thank you for your quick response. Right now, I have it on the easiest option as it allows searching the entire document with certain columns locked. I think this will work for my purpose, but it would be really nice to have a search function in dynamic view.