Enabling Information Push and Pull in Smartsheet

mahmadi

I need guidance on effectively using Smartsheet to achieve a particular data interaction: the push and pull of information.

Here's a snapshot of my scenario:

• I have a master sheet with approximately 500 rows and 90 columns.
• I need to distribute specific column-based data subsets to 20 different personas.
• Each persona should have access to specific columns, with varying levels of read and write permissions.
• Some columns must remain read-only for certain personas, while others should be editable.
• Depending on the persona, the number of accessible columns will vary.

I've previously worked with Data Mesh, but it seems that this would necessitate two separate configurations for each sheet. I'd prefer a more streamlined approach.

However, before I get too deep into crafting a solution, I wanted to touch base with this community:

1. Is there a more straightforward or efficient method in Smartsheet to establish this type of push and pull mechanism, considering varying column permissions?
2. Are there any best practices or strategies you'd suggest for implementing this, especially in terms of managing permissions and data accessibility?

Note: I initially considered Dynamic View for this, but it won't be suitable for several reasons:

• Multiple personas need the capability to export data.
• The mass selection feature on sheets is crucial for us.
• Dynamic View seems to be tailored for one-by-one updates, which is not practical for our requirements.

@Paul Newcome thanks for having a conversation on this topic with me

jmyzk_cloudsmart_jp

@mahmadi

I use the following method to achieve this in the Core App only.

1. Sheet for display
   1. Auto number Row ID column.
   2. Read-only columns and editable columns for 20, say 40 people. (in my demo).
   3. 20 columns for 20 people to display the URL of the form created in step 2.
   4. Add formulas that retrieve the latest data from the sheet of the form created in 2 by INDEX (MATCH or COLUMN) using the row ID as a key.
2. Sheet for forms to update the sheet for display
   1. Create a form for every 20 users that adds a new row with a row ID column and editable columns.
   2. For Row ID, use a URL query string to prefill default values on a form, using 1. Sheet for display's Row ID.
      1. https://help.smartsheet.com/articles/2478871-url-query-string-form-default-values
3. Set up the form so that new inputs are added to the top of the sheet. (To make it easier to get the latest additions by the form in INDEX 1)
4. A report for each person that pulls only the relevant columns from the sheet and displays them
5. Create a dashboard for each person, add published reports in 3, and share the dashboard with view-only permissions.
6. Steps 1 to 4 will allow the person with view-only access to the dashboard to view and edit some columns of the sheet in 1 without having access to the above sheet.

Below is a demo dashboard I created using this method so you can try it out.

(Originally, this would only be shared with those who can view and edit as in 4, but for the sake of demonstration, we are making the dashboard available to the public.)

https://app.smartsheet.com/b/publish?EQBCT=139bd4e9697446b3a8f2b484b5333da5