Record an Approver Name once Approved using Automation

Syed Muhafzal
Syed Muhafzal ✭✭✭✭✭
edited 02/19/24 in Smartsheet Basics

Hi,

I am looking for a solution to record the name of the approver when using Approval Request automation. I can see there have been few questions/discussions on this, but can someone point me to the solutions - without using Zapier :)

For example, a request to approve a report is being sent to John, Jack and Jerry. Jerry approves it. I would like Jerry's name to be automatically stored in one of the columns.

Conditions

  • John / Jack and Jerry do not have access to Smartsheet nor the sheet.
  • Cant use automation as the column will be updated as automation.
  • Column will be locked for future audit purpose. I can do this by using Lock automation.

Any ideas folks?

S

Tags:

Best Answer

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 01/22/24

    Hi @Syed Muhafzal

    You can get cell history using the Smartsheet API. (https://smartsheet.redoc.ly/tag/cells#operation/cellHistory-get)

    The following discussion mentions "My fav Bridge solutions? -Capturing Approver Names in approval workflows" in the following discussion, so Bridge is another option. (I do not see the get cell history method in the Smartsheet integration, so you may have to use Smartsheet API, anyway. ) (https://community.smartsheet.com/discussion/104380/questions-about-bridge-from-a-potential-newbie)

    The sample code below could get the "Modified By" email that modified the Approval Status to "Approved" and updated the row with the email.

    The "Modified By' column does get the Approver email, regardless of access to Smartsheet or the sheet. But, the "Modified By" values change. I tried copy row automation, but the "Modified By" value changes to "[email protected]".😅


    The code updated the Approve Email values. (Then the Modified column lost Approver emails.)


  • Syed Muhafzal
    Syed Muhafzal ✭✭✭✭✭

    @jmyzk_cloudsmart_jp

    Hi there - thank you for your response. Quite raw with API so bear with me :D , where do you install the Get Approvers code in? Can it be done in Smartsheet?

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓

    Hi @Syed Muhafzal

    I use Google Colab for testing.

    Click the link and log in with your Google Account.

    Paste my code and do the following.

    Click the play button.


    Click the play button.

  • Syed Muhafzal
    Syed Muhafzal ✭✭✭✭✭

    Thank you so much @jmyzk_cloudsmart_jp let me give this a try. I did try earlier and having no luck with the column ID, which based on some research, I might need to look into Bridge as well :(

    Another solution I could think right now is to use Update Request, instead of Approval Request. Let me play around but I do appreciate your input! Thank you.

  • From my experience, the Modified By column does not/cannot record the approver's email address but simply pulls the cell data from the approver column (i.e. a forwarded approval email will definitely have the wrong value in the column). Thus it is not a true audit log.

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭

    Hi @Robert Mann

    I should have been more careful when I wrote 'get the "Modified By"' in my answer.

    The "Modified By' I was referencing is the modifiedBy object of CellHistory Object I got using the API code. The code does not get the "Modified By" column's value but the cell history values of the "Approval Status" column.

    So, as the cell history has an actual audit value, I consider the "Approver Email" a valid audit log.

    In the above image, Approve is the CellHistory Object's value and [email protected] is the modifiledBy object.

    CellHistory Object

    modifiedAt string or number

    modifiedBy object User object containing the name and email of the user that made the change.

    List Cell History Call Response Example

    In the response example, "[email protected]" is the "Modified By" email. 

    {
    "pageNumber": 1,
    "pageSize": 50,
    "totalPages": 25,
    "totalCount": 136,
    "data": 
    [
    {
    "modifiedAt": "2019-08-24T14:15:22Z",
    "modifiedBy": 
    {
    "email": "[email protected]",
    "name": "Jane Doe"
    },
    "columnId": 0,
    "columnType": "string",
    "conditionalFormat": "string",
    "displayValue": "string",
    "format": "string",
    "formula": "string",
    "hyperlink": 
    {
    "reportId": 0,
    "sheetId": 0,
    "sightId": 0,
    "url": "string"
    },
    "image": 
    {
    "altText": "string",
    "height": 0,
    "id": "string",
    "width": 0
    },
    "linkInFromCell": 
    {
    "columnId": 0,
    "rowId": 0,
    "sheetId": 0,
    "sheetName": "string",
    "status": "BLOCKED"
    },
    "linksOutToCells": 
    [
    {
    "columnId": 0,
    "rowId": 0,
    "sheetId": 0,
    "sheetName": "string",
    "status": "BLOCKED"
    }
    ],
    "objectValue": 
    {
    "objectType": "ABSTRACT_DATETIME"
    },
    "overrideValidation": true,
    "strict": true,
    "value": "string"
    }
    ]
    }
    


  • Got it and nice work!!