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.

[Chavan AP]

@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);