Dynamic Publishing - is it possible?

gery.elrahweise

Hello, 

We have been using Smartsheet for a while but mainly to assist with our project management tasks and I am pretty much a very basic user in terms of skill level. However, we have a new requirement and exploring to see if this is something that we can use Smartsheet as well. 

We are looking to move our gifts registry to smarsheet. It includes basic information (mainly free text) on cells but some cells would be dates and names of people (to choose from a list). It's very easily done with Smartsheet. 

The bits that I'm not so sure of:

1. Is it possible to lock the sheets so when it is updated (any rows or cells), approval is required before the sheets are updated? 

2. If possible, what sort of information will be included in the approval work flow? Can it be configured to include information of the old value and new value, along with date/time and user account who made the changes?

3. I understand that we can publish a sheet, and then embed it else where. Is it possible to publish parts of a sheet? For example, the sheet will capture date information for all entries. Can I then publish just those rows with dates in January 2019 for instance? 

4. When publishing the sheet, I assume it will be up-to-date? I.e. if the data is updated, it will automatically be reflected in the published sheet?

Sorry for all these questions... but I'll greatly appreciate any help in clarifying the above

Thanks,

Gery

Paul Newcome

1: No, but you can use a locked/hidden column and set up an Approval Request to send the row to someone for approval. (This ties in with the rest of my answers).

.

2: The information included would be whatever columns you choose to send whether it be all columns for the row or only specific ones. To include who and when, you would use the two Modified system columns. The old information in a  changed cell will not be able to be sent though.

.

3: Technically your exact question of publishing only parts of a sheet would be "No". However... You can also build filters which can be used in the published view.

 

Reports on the other hand do not require you to build in a filter that can be turned on and off. It will only show the rows that you specify and can also be limited to only show specific columns.

.

4: Published sheets will be up to date every time they are accessed.

.

Suggestion: I would recommend setting up the approval process for any time a row is added or changed. Then use a report to pull only the rows and columns you want to display and that have been approved.

 

You could then publish the report which would be as View Only which will limit the interaction to only the users that have access to the sheet itself.

gery.elrahweise

Hi Paul,

Thank you for your feedback. This is very useful. 

It looks like it's doable with some workarounds (other than including the value of the old cell, for approval). View only report seems to be the way to go indeed for what I want to do. And I understand that published report gets updated as well to reflect the actual data on sheet, which is what I need. 

I will have look and try out your suggestions and write back if I bump into issues. 

Thanks

gery.elrahweise

I played around with the sheet a little bit and managed to implement what was suggested by Paul - setting up Approval Request and also use Report to filter the information I want to include. 

I now have the following problems:

1. Is there a basic plain HTML view only option when publishing the report? The only option I see is Read Only - Full publish option.

2. If not, how to display in report only those rows with value? When I publish the report, it shows the rows I'm after and also lots of empty rows.

3. Is there an option to embed using javacsript instead of using iFrame? With currently generated iFrame code, it comes with fixed height/weight of the frame. This is troublesome because if I change the size of the browser, or access from devices with difference screen size, the embedded sheets don't change size accordingly - and cause issues with the layout.

Thanks

Andrée Starå

Hi Gery

1. No
2. The empty rows are always included.
3. Not that I'm aware of.

I also have something to add to your original questions but I'll get back to that a little later.

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

Happy to help! 

 

I see Andree is already taking care of these questions. His responses cover all that I could say on them and then some, so I will defer the rest to him.

Andrée Starå

A possible workaround for the approval process could be.

1. Something like, Have columns set up to show the current information (live), old and new information and a checkbox or similar that accept the new data and that would change the data in the current live column.

Would that work?

Best,

Andrée

 

gery.elrahweise

Hello, 

I have come across another issue. 

We have Approval Request setup as per your suggestion and it works as expected. 

However, we realise now that when a particular row has been approved before (i.e. the status column is "Approved"), and then it turns out that the row needs updating... updating anything in that row will not require further approval as the value of status column remains "Approved". 

How do I deal with this problem? Is there a way to change the value of status column from "Approved" to be "Submitted" when updating previously approved row?

Thanks

Gery 

Andrée Starå

Hi Gery,

Not sure I follow!

Is the Approval request triggered by any change in the row and that is what you don't want to happen after it's initially approved?

Paul Newcome

The two options I can initially think of right off would be...

 

1. Manually change "Approved" to "Submitted".

 

2. Include a manually entered date for when it is changed to "Approved" and add a Last Modified Date column. Whenever that last modified column changes to anything later than the manually entered "Approval Date", then it needs to send another Approval Request.

gery.elrahweise

Hi Paul,

Option 2 is closer to what we want to do, but not quite.

To give a bit of context, we are using smartsheet for a registry. Using APIs, the entries within smartsheet are displayed on a (DRUPAL) website. We only want to display on the website those entries that are "Approved". 

Adding new entries work very well. As new entries will have a status of "Submitted" and will not be displayed on the website until it is approved - and the status is updated to "Approved". 

However, editing existing previously approved entry is a challenge. Because the status doesn't automatically become "Submitted" again when changes are made in a particular entry. I mean, we can ask the person entering data to manually change the status to be "Submitted", but this means that I have to unhide/unlock the status column at a risk of them manually changing the status column to "Approve" for any of the entries.  

Any better way to automate this?

Otherwise, what we can do is to make sure that when the APIs are reading the entries to be displayed, it will check that the status is "Approved" and check the manually entered dates are not later than the last modified date (along the line of what you suggested above). Can the approver enter the approval date as they approve? Or do they need to manually open the sheet and manually enter the approval date?

Thanks

 

Paul Newcome

I am testing a few things to work out some of the details. I will get back to you once I find something out. 

Paul Newcome

How many different people could be approving an entry whether it be a new entry or an updated one?

gery.elrahweise

Hi Paul, 

The approver / admin would likely be a small group of people.. about 2-3 (1 primary approver and the others are for backups in case the primary one is not available).

Thank you. 

Paul Newcome

Ok. Getting a little closer to a solution...

 

Hypothetical:

 

I submit an entry. You approve.

 

Would it be safe to say that if ANYONE other than you makes a change to the row, then you need to re-approve?

gery.elrahweise

Hi Paul, 

Yes - other than changes made by small group of approvers, ANY changes to the row will need re-approval.

I think with the way currently approval is setup, reapproval is requested each time changes introduced. The issue is that while waiting for re-approval, the "status" is not changed back to "Submitted", it stays "Approved" - which means it will continue on being displayed, until the approver rejects or approves that update. 

Note that the approval process is projected to be a weekly thing (resource issue), while adding/editing the sheets can be done at any point.