first
This post describes how to record approver names in Smartsheet's automated approval workflows using the Smartsheet API.
https://gist.github.com/jmyzk/18161ba9d0cc643a9cebceab3f739f8d
Question summary
- If an approval request is automatically approved, how do I record the approver's name?
- Approver cannot access Smartsheet or the sheet.
- The column will be locked for future auditing.
Answer summary
Two methods are presented in the discussion.
- Use the cell history API
This method uses the Smartsheet API to retrieve cell history and record changes, including approver names. (It has been verified that the approver name can be recorded using the code released by Cloudsmart's jmyzk.) - Use update requests
This method uses an update request instead of an approval request and manually enters the approver's name. (This has not been verified, but it may be possible. Please note that manually entering approver names may not be valid for auditing purposes.)
How to use the cell history API
- Run your Python code in an environment such as Google Colab.
- Get cell history using the Smartsheet API.
- Extracts approver name from cell history.
- Store the approver's name in a separate column.
Use update requests
- Create update requests instead of approval workflows.
- Add a field for the approver's name in the update request.
- Approvers enter their name when approving an update request.
Example code for recording approver names in an automatic approval workflow using the Smartsheet API
Summary
There are two ways to record approver names in automatic approval workflows. Using the Cell History API is more automated, but requires technical knowledge. Using update requests is easy, but requires manual input.
Key Points
- Using the Cell History API requires knowledge of the Smartsheet API.
- To use the update request, approvers must enter their name.
- Whichever method you use, the column must be locked for future auditing.
Additional Information
- Smartsheet API documentation: https://smartsheet.redoc.ly/
- Cell history API: https://smartsheet.redoc.ly/tag/cells#operation/cellHistory-get
Translation of correspondence
Syed Muhafzar ✭✭✭✭✭
2024/01/22 Edit 02/19/24
Hello,
I'm looking for a solution to record approver names when using approval request automation. There seem to be some questions and discussions regarding this, could someone please give me a solution – without using Zapier 🙂
For example, a report approval request is sent to John, Jack, and Jerry. Jerry approves. I would like to automatically store Jerry's name in her one of the columns.
条件
- John, Jack, and Jerry can't access Smartsheet or the sheets.
- Automation is not available because the column is updated as automation.
- The column will be locked for future auditing. This can be done using lock automation.
Any ideas?
best answer
jmyzk_cloudsmart_jp ✭✭✭✭✭✭
Hello@Syed Muhafzar
We use Google Colab for testing.
https://colab.research.google.com/?utm_source=scs-index
Click the link and sign in with your Google Account.
Paste my code and do the following:
Click the play button.
Click the play button.
answer
jmyzk_cloudsmart_jp ✭✭✭✭✭✭
2024/01/22 Edited on December 2024, 1
Hello@Syed Muhafzar
You can retrieve cell history using the Smartsheet API. (https://smartsheet.redoc.ly/tag/cells#operation/cellHistory-get）
Bridge is another option, as the following discussion mentions “Your favorite Bridge solution? – Getting approver names in approval workflows.” (You may need to use the Smartsheet API anyway, as the Smartsheet integration does not show a method to get cell history.) (https://community.smartsheet.com/Discussion/104380/questions-about-bridge-from-a-potential-newbie）
The sample code below retrieves the "Modified By" email that changes the approval status to "Approved" and allows you to update a row with that email.
Get Approvers.ipynb
https://gist.github.com/jmyzk/18161ba9d0cc643a9cebceab3f739f8d
The Modified By column captures the approver's email regardless of access to Smartsheet or the sheet. However, the Modified By value will change. I tried the Copy Row automation, but the Modified By value changes to "automation@smartsheet". .com”. 😅
The code updated the email authorization value. (The approver's email is then lost in the "Modified" column.)
Syed Muhafzar ✭✭✭✭✭
@jmyzk_cloudsmart_jp
Hello. Thank you for your reply. Please bear with me as the API is still immature 😀, Where do I install the Get Approvers code? Can I run it in Smartsheet?
jmyzk_cloudsmart_jp ✭✭✭✭✭✭
Hello@Syed Muhafzar
We use Google Colab for testing.
https://colab.research.google.com/?utm_source=scs-index
Click the link and sign in with your Google Account.
Paste my code and do the following:
Click the play button.
Click the play button.
Syed Muhafzar ✭✭✭✭✭
Thank you very much@jmyzk_cloudsmart_jp I'll try this. I tried it before, but the column ID didn't work for me. According to research, you might also want to look into Bridge :(
Another possible solution at this time is to use update requests instead of approval requests. Let's play around with it a bit, but thanks for your input. thank you.
robert man ✭✭
In my experience, the "Modified by" column does not and cannot record the approver's email address, it simply retrieves the cell data from the approver column (i.e. the forwarded approval The email column will definitely contain the wrong value). Therefore, this is not a true audit log.
jmyzk_cloudsmart_jp ✭✭✭✭✭✭
Hello@Robert Mann
In the answer “ "Modified By"I should have been more careful when I wrote ``to obtain ``.
The "Modified By" I was referring to was obtained using the API codemodifiedBy object for CellHistory objectis. The code isInstead of getting the value of the "Modified By" column,Cells in the Approval Status columnGet historical values.
Therefore, I consider "Approver Email" to be a valid audit log since the cell history has the actual audit value.
{
"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"
}
]
}
robert man ✭✭
I understand, great job!!