Dynamic Publishing - is it possible?

Andrée Starå

My thinking is that if changes happen, then that would trigger the automation and unlock the Status row and send a new Approval Request either as new or using the original one. Not entirely sure how to structure it without seeing more details.

Can you describe your process in more detail and maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

What do you think? Would that work?

gery.elrahweise

Hi Andrea,

Please see my post on 19 July 2019 - which provided additional context/background information. And also how we're setting it up. 

The approval process works. But as mentioned on my post previously, editing/updating pre-approved row WILL trigger for another approval request BUT in the meantime while waiting for the approval the status of that row remains "Approved". If approver only approves once a week for instance, then that's one week of that row incorrectly show the status of "Aproved". 

This status becomes really important to us, as it's what we use to decide whether we include that row to be displayed on a website or not. 

Paul said he's doing some testing already. Hoping we can come up with a solution shortly. 

Thank you.  

 

Andrée Starå

Happy to help!

Yes, and I think we could solve it with changing it to Submitted again. I've done something similar in the past for a client but Smartsheet changed the way it works with the new structure so it would help with a Screenshot of the structure you have already.

Something like.

Submitted 1

Approved 1

Submitted 2

Approved 2

Would that work?

Paul Newcome

Ok. So here's my thought process on this. Forget Approvals. I'm thinking Update requests. Here's a rundown...

.

Basic "Directory" of each of the Approvers in one column [Directory Name], and their email in the other [Directory Email].

.

In the main portion of the sheet, this will flow into two more columns. A Contact column named Approver and a text/number type named Email.

.

The Approver column is where you set who the "Approval" Update Request is sent to. In the Email column use:

 

=INDEX([Directory Email]:[Directory Email], MATCH(Approver@row, [Directory Name]:[Directory Name], 0))

.

This will autopopulate the email address for whomever you have listed to receive the update request.

.

We are also going to need another text/number type column [Approved?], a checkbox column [Needs Approved?], and a system generated [Modified by] column.

.

In the [Needs Approved?] checkbox column:

=IF([Modified by]@row <> Email@row, 1)

.

This will check the box if the email address in the [Modified by] column does not match the email address in the Email column.

.

You can set your Update Request and your Reporting to look at this checkbox column. If it is checked, send an Update Request to the contact in the Approver column. 

 

Reporting will display only those rows where the box is NOT checked (meaning the email addresses match).

.

For the Update Request itself, the approver would simply change the data in the [Approved?] column. This could be something as simple as using the number 1 for the first approval, then adding 1 for each approval after that (helps track the number of approvals per row if needed), but in all reality, the approver can just mash the keyboard in the [Approved?] column and save it.

This would update the [Modified by] column to the approver's email address and uncheck the box which would then be displayed through your reporting.

.

How does something like this sound?

.

.

EDIT:

I just read your latest response to Andree.

This will also immediately pull the row from being displayed as soon as a row update that has not yet been approved has been saved. Even if the approver doesn't check it for another week, the box being checked is automatic and immediate.

 

If you NEED the verbiage of Submitted and Approved, you could switch the [Needs Approved?] column over to a text/number type and use a formula along the lines of

 

=IF([Modified by]@row <> Email@row, "Submitted", "Approved")

.

You would want to keep in mind though that if anyone changes that particular column, it will override the formula and thus the automation.

Dave.Robinson

Hi Guys.

 

I'm the developer working with Gery on this.  I thought that I may be able to explain a little differently which may assist in us finding a resolution.

Here's the scenario...

Many sheets have been built from a template and are used to enter info regarding gifts.  The Sheet has a "Status" column.

The Status column is locked so that only the Admin or Owner can edit it.  This is necessary as a User should not be able to approve the content (i.e. Row).

There is an Approval Workflow configured to notify an address when the Sheet changes (add rows, delete rows, edit columns etc). 

If the Admin approves the change, the Status column is set to "Approved". 

If the Admin declines the change, the Status column is set to "Declined".

The problem we have is that if there is a Row with a Status of "Approved", the user can edit the other details, and the status is still set to "Approved".

Ideally, we would have a second workflow to set the Status column to "Submitted" upon any edit, however we can't see how to create a workflow that sets a cell value.

Further information as to why we want these three states:

There is a separate application that uses the Status column to decide what rows should be visible on a website.  "Approved" equates to "Approved to publish".

If someone decides to edit an existing row, and the Status is still set to "Approved", then the row is still "Approved" for publishing on the website.  This is obviously a problem as the row hasn't been vetted, and as such, should not be "Approved".

 

Dave

Paul Newcome

Dave,

 

See my solution above regarding an Update Request as opposed to an Approval. You can change the checkbox column that is automated based on the email addresses matching to be a dropdown type column. From there you could say that if the emails match "Approved", otherwise "Submitted". 

 

This will trigger a change that sends out the update request. You could even go so far as to use an additional date type column for the Approver to enter the date it was approved.

 

The logic you are currently using on the Status column to determine whether or not the row should be displayed could be changed to look at the automated column.

 

This will then have your logic looking at a column that will automatically update to "Submitted" as soon as an edit is made by anyone other than the approver.

Dave.Robinson

Hi Paul.

Regarding the "Approvers" Email Address, we wish to use the email address of a Distribution List, as opposed to a single person.  This is necessary as the solution is is expected to work with no administration (i.e. we cannot periodically update the email addresses of "Approvers"), and as previously mentioned, we envisage that there will be more than one person expected to "Approve" changes.

As this email address is never going to be the actual email address of a single person editing or reviewing/approving the sheet, it sounds to me that your suggestion won't work.

If I have misunderstood, please feel free to advise accordingly.

Thanks

Paul Newcome

Ah. Ok. Well that definitely changes things.

 

Without being able to maintain a list of email addresses that can be referenced by the sheet, you are correct. My solution will not work for you.

 

However... If you should decide that a list of email addresses that only needs updated periodically WILL work for you, you can use the CONTAINS function to compare the email address of the person who last updated to that list.

.

Having said that...

 

It sounds like Andree's suggestion of multiple approval columns would be the best way to go. You can use a formula in the column that is being used to determine whether or not the row should be displayed to populate "Submitted" or "Approved" based on how many of the additional Approval columns have been used and what their statuses are.

 

The only downside to this is that you are limited to how many times a row can be updated by how many additional approval columns you have built into the sheet.

.

.

Thinking out loud here, and Andree would be a better one than I to say whether it can be done or not...

 

I am wondering if there is a way through the API, a third party tool, a connector, or a series of those that would allow a list of email addresses to be automatically updated within SS as an outside distribution list is updated so that you can use the email list without having to maintain it inside of SS.

 

Maybe a regularly scheduled export from the email platform to an excel sheet which is then automatically imported to SS using the Live Data Connector or something.

 

Again... I am not very familiar with the API, third party tools, or add-ons. I am just throwing ideas out here.

Andrée Starå

Thanks, Paul!

 

Dave and Gery,

I think I could set something up to work with a third-party integration/service like Zapier.

Is that an option?

Dave.Robinson

Hi Andree.

My personal thoughts are that we wouldn't want to bring a third party into the mix.

Regardless, Gery has worked something out that may be good enough.

(I'm still using a trial account, so I don't have access to the workflow functionality)

If I could get the authorisation/callback functionality working, this would probably be a non-issue, as I could validate any and all changes as they occur.  

(My issue with callbacks is that your requirements of requesting and approving the authorisation code have so-far been too difficult to accomodate.  I use AWS Lambda, and don't have a framework to assist as per the documentation suggests [https://smartsheet-platform.github.io/api-docs/#request-an-authorization-code].  This is the topic for another post)

 

gery.elrahweise

Hi all, 

We got this sorted. Ended up using Paul's solution... not exactly as is but building on what Paul suggested. 

What I did:

1. Create a column of approvers [Approver List]

2. Create a column of check-box [Admin Reviewed]. This column will be ticked only if the change on that row last made by someone included in [Approver List] column.

3. Create a column to add comment [Comments]. 

3. Setup Update Request workflow to everyone listed in [Approver List]

4. When email notification received for update request, the Approver must change the status in [Status] column to either "Approved" or "Declined".

5. When previously approved row is edited, the [Status] column will remain "Approved", but the [Admin Reviewed] column will become unticked - i.e. it will not be displayed in our report / website.

6. When email notification received for update request of this previously approved row, the Approver must change something in the row. If this previously approved row is still approved even after the edit, approver then needs to enter a comment in [Comment] column. 

7. We add another [Publish] column. IF [Admin Reviewed] is ticked AND [Status] is "Approved, then set [Publish] to be "Yes". Everything else "No".

8. The report / APIs will then only read rows with [Publish] column value of yes - and display it on the web.

Thanks for your help. 

Andrée Starå

Ok.

EDIT: I saw Gery's post! Excellent!

Sounds like we are close to a solution.

Andrée Starå

Excellent!

Happy to help and thanks for sharing!

Have a fantastic weekend!

Paul Newcome

Nicely done! I know that maintaining a list can become cumbersome, but a lot of that depends on how frequently that list actually changes.

 

Happy to help and glad you were able to find a solution that works for you!