How Do You Document a Log of Notifications of Sheet Changes

GMichal
GMichal ✭✭✭✭
in Smartsheet Basics

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.?

Tags:
· Share on FacebookShare on Twitter

Answers

  • Julie Fortney
    Julie Fortney Overachievers

    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:

    image.png

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


    image.png

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


    image.png


    · Share on FacebookShare on Twitter
  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 01/18/24

    @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
    image.png

    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.


    · Share on FacebookShare on Twitter
  • GMichal
    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.

    · Share on FacebookShare on Twitter
  • Julie Fortney
    Julie Fortney Overachievers

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

    · Share on FacebookShare on Twitter
  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 01/19/24

    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

    image.png


    image.png


    · Share on FacebookShare on Twitter
  • GMichal
    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.

    · Share on FacebookShare on Twitter
  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭

    Happy to help!😁

    · Share on FacebookShare on Twitter

Categories

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video