Smartsheet integration with ServiceNow

[Joshuu]

Hi All,

I have the below requirement to configure ServiceNow - Smartsheet integration.

Whenever few fields in Business Impact Analysis table changes, then the updated field values should be transferred to Smart Sheet.

Please assist.

Thanks & Regards.

[Ajay_Chavan]

@Joshuu 

please try below:

(function executeRule(current, previous /*null when async*/ ) {
    var appId = current.number;

    // Call REST Message to get all rows
    var r = new sn_ws.RESTMessageV2('Smartsheet Search Rows', 'get');
    r.setStringParameterNoEscape('sheetId', 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
    var response = r.execute();
    var responseBody = response.getBody();
    var data = JSON.parse(responseBody);

    var rowId;
    data.rows.forEach(function(row) {
        row.cells.forEach(function(cell) {
            if (cell.columnId == 'xxxxxxxxxxxxx' && cell.value == appId) {
                rowId = row.id;
            }
        });
    });

    if (rowId) {
        // Call REST Message to update the row
        var update = new sn_ws.RESTMessageV2('Smartsheet Update Row', 'put');
        update.setStringParameterNoEscape('sheetId', 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

        var body = JSON.stringify([{
            id: rowId,
            cells: [
                {
                    "columnId": 4762925636210564,
                    "value": current.u_solution_architect.toString()
                },
                {
                    "columnId": YOUR_PRODUCTION_HOSTING_COLUMN_ID, // Replace with actual column ID for Production Hosting
                    "value": current.u_production_hosting.getDisplayValue()
                }
                // Add more fields as needed
            ]
        }]);

        update.setRequestBody(body);
        // Execute the request and capture the response object
        var response = update.execute();
        var responseBody = response.getBody();
        var httpStatus = response.getStatusCode();

        // Check for success (HTTP 200)
        if (httpStatus == 200) {
            gs.info('Smartsheet integration successful. Status: ' + httpStatus + appId);
        } else {
            // Log the full response body of the error
            // so you can see why Smartsheet rejected the update.
            gs.error('Smartsheet integration failed. Status: ' + httpStatus + ' Body: ' + responseBody);
            // This is a more robust way to handle the error
            try {
                var errorBody = JSON.parse(responseBody);
                gs.error('Smartsheet error details: ' + JSON.stringify(errorBody));
            } catch (e) {
                gs.error('Failed to parse Smartsheet error response: ' + e.getMessage());
            }
        }
    } else {
        gs.warn('No row found in Smartsheet for the given App ID.');
    }
})(current, previous);

[Joshuu]

Hi @Ajay_Chavan ,

Thank you for your response. I have another 2 queries.

1) It is working. But from ServiceNow end the field is a list type. Means we can select multiple options. Currently the single value is only getting updated in the Smartsheet. Do they need to change field type in Smartsheet. 

 "type": "PICKLIST"

currently the type is Picklist.

2)  There is another field called business criticality in the business application table. We also need to update this as well. This is a choice field. So, can we use "getDisplayValue()" for this field too? 

Please assist.

Thanks & Regards.

[Ajay_Chavan · Accepted solution]

Hey @Joshuu ,

1. you can change it to MULTI_PICKLIST and send an array of values.

2. You can also use the getDisplayValue() function for choice fields. Alternatively, you can check what values are accepted at Smartsheet and then create a mapping between ServiceNow Choice and Smartsheet Choice.

Let me know if you have any questions!

[Joshuu]

Hi @Chavan AP ,

Thank you for your response.

1) Smartsheet column type has been changed to MULTI_PICKLIST.  Could you please help me with the script to send an array of values.

below is the response from postman for this field.

{
            "id": 1234567891234567,
            "index": 43,
            "options": [
                "AliCloud",
                "Amazon Web Services (AWS)",
                "Azure",
                "CenturyLink (DC4), CenturyLink (SC9)",
                "Google Cloud Platform (GCP)",
                "Installed On Device",
                "On Property (Hotel)",
                "Other",
                "Vendor Hosted (PaaS)",
                "Vendor Hosted (SaaS)"
            ],
            "title": "BR: Hosting",
            "type": "MULTI_PICKLIST",
            "validation": true,
            "width": 150,
            "version": 2
        },

2) The business criticality choice field, I am getting 200 as response from PUT call in ServiceNow rest message. But when I update the field, it is not getting updated in Smartsheet.

Below is my script. only solution architect is working.

(function executeRule(current, previous /*null when async*/ ) {
    var appId = current.number;

    // Call REST Message to get all rows
    var r = new sn_ws.RESTMessageV2('Smartsheet Search Rows', 'get');
    r.setStringParameterNoEscape('sheetId', 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
    var response = r.execute();
    var responseBody = response.getBody();
    var data = JSON.parse(responseBody);

    var rowId;
    data.rows.forEach(function(row) {
        row.cells.forEach(function(cell) {
            if (cell.columnId == '7942223278854020' && cell.value == appId) {
                rowId = row.id;
            }
        });
    });

    if (rowId) {
        // Call REST Message to update the row
        var update = new sn_ws.RESTMessageV2('Smartsheet Update Row', 'put');
        update.setStringParameterNoEscape('sheetId', 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');

        var body = JSON.stringify([{
            id: rowId,
            cells: [{
                    "columnId": 4762925636210564,
                    "value": current.u_production_hosting.getDisplayValue()
                },
                {
                    "columnId": 5367811912454020,
                    "value": current.u_solution_architect.email.toString()
                },
                {
                    "columnId": 7453438367715204,
                    "value": current.u_application_tier.getDisplayValue()
                }
                // Add more fields as needed
            ]
        }]);

        update.setRequestBody(body);
        // Execute the request and capture the response object
        var response = update.execute();
        var responseBody = response.getBody();
        var httpStatus = response.getStatusCode();

        // Check for success (HTTP 200)
        if (httpStatus == 200) {
            gs.info('Smartsheet integration successful. Status: ' + httpStatus + appId);
        } else {
            // Log the full response body of the error
            // so you can see why Smartsheet rejected the update.
            gs.error('Smartsheet integration failed. Status: ' + httpStatus + ' Body: ' + responseBody);
            // This is a more robust way to handle the error
            try {
                var errorBody = JSON.parse(responseBody);
                gs.error('Smartsheet error details: ' + JSON.stringify(errorBody));
            } catch (e) {
                gs.error('Failed to parse Smartsheet error response: ' + e.getMessage());
            }
        }
    } else {
        gs.warn('No row found in Smartsheet for the given App ID.');
    }
})(current, previous);

 Please help.

Thank you.

[Joshuu]

Hi @Ajay_Chavan  @Ankur Bawiskar,

Could you please help me here.

Thanks & Regards.