Record an Approver Name once Approved using Automation
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
Best Answer
-
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.
Answers
-
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 "automation@smartsheet.com".😅
The code updated the Approve Email values. (Then the Modified column lost Approver emails.)
-
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?
-
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.
-
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.
-
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 tennis.swimming55@gmail.com 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, "jane.doe@smartsheet.com" is the "Modified By" email.
{ "pageNumber": 1, "pageSize": 50, "totalPages": 25, "totalCount": 136, "data": [ { "modifiedAt": "2019-08-24T14:15:22Z", "modifiedBy": { "email": "jane.doe@smartsheet.com", "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!!
