How Do You Document a Log of Notifications of Sheet Changes

GMichal

One complaint that I am getting back from shared users of a sheet is that many times they don't know when something has changed. I can certainly make this manual task outside of the Smartsheet but I'd really like it to be captured in the sheet.

For instance, I could use "File --> Email Shared Users" as the method to create the message and easily send to all users but the history of these notifications does not appear to be retained in the sheet anywhere.

Likewise, I could use a single row and send out the sheet update messages from that row which would keep a history but tagging all of the sheet users would be a challenge not to mention that there is no way to anchor (freeze) that row right under the column headings so that the users could always check the comments in that row to see what changes have occurred and when.

Is there any way to easily document the changes that I decide to document (not View Activity Log) for the user group to keep them informed of updates, changes, improvements, etc.?

Julie Fortney

Hi @GMichal

You're not alone in wanting a history of the notifications that have been sent from a sheet. Unfortunately that only gets recorded when you send an Update Request for a row.

Here are a few ideas that might help:

If you want users to be able to quickly spot changes, you could use the highlight changes feature:

You could also create an automation to alert users of changes:

And while it's not the most efficient way to view changes, you can right-click on a cell to view its history.

jmyzk_cloudsmart_jp

@GMichal

I created a demo solution to document sheet changes and notify shared users of some important changes in their sheets.

Automation Approach

I checked Automation, and the following triggers are currently available

1. Add or change a row
2. Add or change an attachment
3. Add or change a comment
4. Delete a row
5. Change share

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

So, for trigger events 1 through 3, I copied the rows to a sheet for the record to see them in chronological order in the report.

In the demo dashboard, I filtered by a specific user to whom the task was assigned, with the most recent at the top. As a result, I created a historical documentation of a task.

(Note that the comments are recorded by adding the latest comment column and changing the rows, but you can also change the helper column values by adding or changing them, as in the attachment).

API Approach

(This is just a survey.)

As for "File --> Email Shared Users", I also checked Smartsheet API, but there seems to be no way to get the history now.

The following methods are available in Smartsheet API.

• List Events
  • This method can determine if someone has Deleted a sheet, who did it, etc. (available for enterprise plans).
• List Cell History
  • This method includes formatting, cell links, etc., and can be used when it is difficult to retrieve or repair.
  • The development will take time because you must specify Sheet_ID, Row_ID, and Column_ID to retrieve them individually.

GMichal

I like your ideas here but, in most cases, I am notifying the user in plain English of the changes made to satisfy their request. Many are Smartsheet users and not creators which means they cannot decode the Smartsheet terminology so I end up having to write them an explanation of the general work that was done in an an email.

Unfortunately, it is then needing to be documented in my Smartsheet Maintenance Tracker with automation sending the results to the requestor but not captured in the individual sheet itself in date order so that there is a running history of my explanations of the work that was done for any and all sheet users to view whenever they want.

Julie Fortney

@jmyzk_cloudsmart_jp I'll be keeping your dashboard in mind for the future - neat idea!

jmyzk_cloudsmart_jp

Hi @GMichal & @Julie Fortney

Instead of writing individual emails, I thought of a solution where we add the content of the emails to the 'Info' column of a sheet and use it as one of the 'Placeholders' in 'Customize a message in a workflow.' This automates incorporating the content of 'Info' into emails. By copying the content of this 'Info' to another sheet through the 'Copy Rows' automation, I created a report called 'log of Info', which serves as a 'Document a Log of Notifications of Sheet Changes'.

While updating the demo solution, I noticed the following. The changes are recorded on the 'Report of Sheet Change' sheet due to the automation of the initial solution. This includes the addition or modification of the 'Info' content. I then devised a method to prevent 'Info' from being copied again to the 'Report of Sheet Change' sheet if its content is changed.

A column named 'New Info' was created, and the following formula was used to determine if it is the latest information related to that column. If it is the latest, it gets copied to 'Record of Info'.

[New Info] =IF(JOIN(COLLECT({Record of Info Range Info}, {Record of Info Range Row ID}, [Row ID]@row, {Record of Info Range Latest Info}, 1)) = Info@row, 0, 1)

{Record of Info Range Latest Info} in the 'Record of Info' sheet uses the following formula to determine if the 'Info' is the latest for the task identified by the Row ID, referring to the range of that column. (When updating 'Info', it overwrites the old 'Info').

[Row No] =MATCH(Created@row, Created:Created)

[Latest Info] =IF(MAX(COLLECT([Row No]:[Row No], [Task Name]:[Task Name], [Task Name]@row)) = [Row No]@row, 1, 0)

With the newly added automation rule, when the content of 'Info' is changed, a customized email is sent to [Assigned To] and [Stakeholders] contacts, and [everyone shared to this sheet]. Secondly, if [New Info] is checked, the row is copied to 'Record of Info'. (The previous automation solution has been adjusted to not copy the row to 'Record of Sheet Changes' if [New Info] is checked.)

Although it is impossible to put content customized for each user in the email, by recording the email content as 'Info' in a column of the sheet and sending it automatically, we can meet the needs of users like Stakeholders who want to obtain information through email. This approach also enables us to document Sheet Changes and create a Log of 'Notifications' of Sheet Changes.

https://app.smartsheet.com/b/publish?EQBCT=3067c935de8749399a904039d1e97bc9

GMichal

I love it! So many times my first response to someone requesting a Smartsheet change is "no, that is not built-in functionality but give me some time to sleep on it and I might be able to come up with a workable solution". I appreciate you coming up with that workable solution.

jmyzk_cloudsmart_jp

Happy to help!😁