Build a cloud smartsheet solution, in Python code

OptimEyes

hi Community

We've built a solution to manage process controls in SmartSheet.

Smoothly integrated with datashuttle.

It works via Python, but "on Premise" via Visual studio Code.

Our GOAL:

We want to move the code and the solution to the Cloud.

Our smartsheet customers can then request updates via our Control center.

We are considering Azure Functions or AWS lambda.

Might this be suitable?

Or are there more appropriate tools on the market?

jmyzk_cloudsmart_jp

Hi @OptimEyes

To move your Smartsheet solution to the cloud, you can use serverless options like Google Cloud Functions, AWS Lambda, or Azure Functions. These platforms can execute your Python code in response to events, providing scalability, cost-effectiveness, and minimal maintenance.

Solution Overview:

• Choosing the Cloud Platform: Azure Functions, AWS Lambda, or Google Cloud Functions are all good choices for integrating with Smartsheet. I use GCF.
• Using Webhooks to Trigger Functions: The only webhook* available in Smartsheet is for sheet changes, with a sub-scope limited to columns**. Please note that you can not use a sheet the SCC will create. You must select a specific sheet, such as the intake or portfolio summary sheet. Changes in these sheets will trigger the webhook to call your cloud function endpoint, executing your Python code.

https://smartsheet.redoc.ly/tag/webhooksDescription/ (*)

https://developers.smartsheet.com/blog/introducing-webhooks-for-columns (**)

• Integration Steps:

1. Step 1: Set up a webhook in Smartsheet to point to your cloud function.
2. Step 2: Configure the function to handle HTTP requests and process data from Smartsheet.
3. Step 3: Ensure the function is prepared to manage various Smartsheet events.

Creating the webhook is the most challenging part.

In my experience, creating a webhook, especially responding to Smartsheet's challenge, is the most difficult part.

Also, you must get what changed using the Webhook event data, such as sheet_id, row_id, etc.

CallbackEvent Object Example
{

  "id": 3285357287499652,

  "columnId": 0,

  "rowid": 0,

  "userid": 48569348493401210,

  "objectType": "sheet",

  "changeAgent": "string",

  "eventType": "created",

  "timestamp": "2019-08-24T14:15:22Z"

}

OptimEyes

thanks

Do you know somebody who can help us set this up ?

jmyzk_cloudsmart_jp

Hi @OptimEyes

To set up a cloud-based solution using the Smartsheet API and webhooks, you might want to connect with some professional service providers experienced in Smartsheet API development involving webhooks. One option is to contact Smartsheet Professional Services, who should have expertise in various use cases.

Integrating webhooks becomes much more straightforward if you can access Bridge by Smartsheet.

I use Google Cloud Functions written in Python to set up webhooks, which has been quite adequate for my needs.

Feel free to ask if you have any other questions or need more guidance!

Best.

Bridge Trigger Example

Teun Jochems

Hi @jmyzk_cloudsmart_jp can you give me an example of the header response? I am trying to verify the webhook with azure functions but it keeps giving me an error:

My Python function:

@ app.route(route="Therminon_cloud")def Therminon_cloud(req: func.HttpRequest) -> func.HttpResponse:    logging.info('Python HTTP trigger function processed a request.')
 # Get the Smartsheet-Hook-Challenge from the headers   

challenge_header = func.HttpRequest.get('Smartsheet-Hook-Challenge')
 # Return a 200 response with the challenge echoed back   

return func.HttpResponse(        status_code=200,        headers={            "Smartsheet-Hook-Response": challenge_header  # Include the challenge in the header        }    )

The response:

{"callbackUrl": "https://therminoncloudsolution.azurewebsites.net", "events": ["."], "name": "CLMS_listener", "scope": "sheet", "scopeObjectId": 6440681832664964, "version": 1}
{"data": {"callbackUrl": "https://therminoncloudsolution.azurewebsites.net", "createdAt": "2024-09-24T10:56:55+00:00Z", "disabledDetails": "Response was missing verification response in both header and body, or body JSON may have been invalid. (ref id: dbqqcd)", "enabled": false, "events": ["."], "id": 2443247713838980, "modifiedAt": "2024-09-24T10:56:58+00:00Z", "name": "CLMS_listener", "scope": "sheet", "scopeObjectId": 6440681832664964, "sharedSecret": "4yd1942p1vn7iool8g5377lyw", "status": "DISABLED_VERIFICATION_FAILED", "subscope": {}, "version": 1}, "message": "SUCCESS", "result": {"callbackUrl": "https://therminoncloudsolution.azurewebsites.net", "createdAt": "2024-09-24T10:56:55+00:00Z", "disabledDetails": "Response was missing verification response in both header and body, or body JSON may have been invalid. (ref id: dbqqcd)", "enabled": false, "events": ["."], "id": 2443247713838980, "modifiedAt": "2024-09-24T10:56:58+00:00Z", "name": "CLMS_listener", "scope": "sheet", "scopeObjectId": 6440681832664964, "sharedSecret": "4yd1942p1vn7iool8g5377lyw", "status": "DISABLED_VERIFICATION_FAILED", "subscope": {}, "version": 1}, "resultCode": 0}

jmyzk_cloudsmart_jp

Hi @Teun Jochems

The 'enable' should be 'True', and 'status' should be 'ENABLED'.

The "status": "DISABLED_VERIFICATION_FAILED" message in Smartsheet webhooks means that the webhook was initially created but the verification process did not succeed. This typically occurs when Smartsheet sends a verification challenge to your webhook's callback URL, but the expected response is not received. (See the image at the bottom.)

To break it down:

• When you create a webhook, Smartsheet sends a verification request (challenge) to the webhook's callback URL.
• The webhook must respond to this request with a specific response, confirming that the URL is set up to handle webhook notifications.
• If your server fails to respond correctly to the verification challenge (such as not returning the correct response or not responding in time), Smartsheet will assign it this "DISABLED_VERIFICATION_FAILED" status.

To resolve this:

1. Check the callback URL endpoint to ensure it's correctly set up to handle verification challenges. (See the Step by Stem image below.)
2. Ensure that your webhook responds with the correct challenge response as specified in Smartsheet's API documentation.
3. After fixing the issue, you can re-enable the webhook by sending another enable request.

Enabled Webhook Response Example (Get webhook)

{'webhook_id': 6XXX120984102788,

 'webhook_details': {'callbackUrl': 'https:/XXX/',

  'createdAt': '2024-09-25T00:52:49+00:00Z',
'enabled': True,

  'events': ['*.*'],

  'id': 6XXX120984102788,

  'modifiedAt': '2024-09-25T00:52:50+00:00Z',

  'name': 'API Service Request V4.0',

  'scope': 'sheet',

  'scopeObjectId': 1XXX463797366660,

  'sharedSecret': '3XXXXXXXXXj9qkvvovwihb3cqn',
'status': 'ENABLED',

  'subscope': {'columnIds': [7909199274266500,

    3405599646896004,

    5094449507159940,

    590849879789444]},

  'version': 1}}

{'webhook_id': 6XXX120984102788, 'webhook_details': {'callbackUrl': 'https://XXX', 'createdAt': '2024-09-25T00:52:49+00:00Z', 'enabled': True, 'events': ['*.*'], 'id': 6XXX20984102788, 'modifiedAt': '2024-09-25T00:52:50+00:00Z', 'name': 'API Service Request V4.0', 'scope': 'sheet', 'scopeObjectId': 1XXX463797366660, 'sharedSecret': '3XXXXXXXXXj9qkvvovwihb3cqn', 'status': 'ENABLED', 'subscope': {'columnIds': [7909199274266500, 3405599646896004, 5094449507159940, 590849879789444]}, 'version': 1}}

Most probably, you failed at step 4.

jmyzk_cloudsmart_jp

Hi @Teun Jochems

Somehow, I can not post comment with webhook response example.

The community system looks like sanitizing message with code looking message.

You can get response sample from here.

https://smartsheet.redoc.ly/tag/webhooks#operation/getWebhook

"enabled": true,

"status": "ENABLED",

»»»»»

The "status": "DISABLED_VERIFICATION_FAILED" message in Smartsheet webhooks means that the webhook was initially created but the verification process did not succeed. This typically occurs when Smartsheet sends a verification challenge to your webhook's callback URL and the expected response is not received.

To break it down:

• When you create a webhook, Smartsheet sends a verification request (challenge) to the webhook's callback URL.
• The webhook must respond to this request with a specific response, confirming that the URL is set up to handle webhook notifications.
• If your server fails to respond correctly to the verification challenge (such as not returning the correct response or not responding in time), Smartsheet will disable the webhook and assign it this "DISABLED_VERIFICATION_FAILED" status.

To resolve this:

1. Check the callback URL endpoint to ensure it's correctly set up to handle verification challenges.
2. Ensure that your webhook responds with the correct challenge response as specified in Smartsheet's API documentation.
3. After fixing the issue, you can re-enable the webhook by sending another enable request.

Most likely, you failed at Step 4.

https://smartsheet.redoc.ly/tag/webhooksDescription/#section/Creating-a-Webhook/Step-by-Step

Does this clarify the issue for you?