Excel VBA REST ServiceNow Connection

[JosephW1]

Hello,

Does anyone know a more secure method of authenticating to SN and running queries from within Excel? Multiple sources mention that the type of authentication used here - HTTP Basic Authentication - is not advised due to "inherent security vulnerabilities." So, does anyone know a more secure live integration that can be manipulated via Excel VBA? It would be nice (but not likely) if it would work with SSO! Well, thanks!

ServiceNow to Microsoft Excel Integration [servicenowelite.com] offers detailed instructions of how to integrate an Excel spreadsheet to a ServiceNow instance via the use of REST web services, which is really exciting. This could be used to build within Excel a GUI where customized queries into the realtime SN tables could be developed and ran at-will, providing a configurable search into the SN instance within Excel that could then be used for non-SN-standard data analysis.

However, my Excel (Office 365) was returning one error after another on the given code - the code was originally published August 2015, by the way, so some things may have changed since then.

This is a documentation of my journey getting the supplied code working and improving upon it. Hopefully this is useful for someone.

The First Error: "Compile error: User-defined type not defined"
Trigger Line: rest As New DOMDocument
Resolution: Change "DOMDocument" to "MSXML2.DOMDocument60" to provide compatibility with XML 6.0 library

The Second Error: "Run-time error '9': Subscript out of range"
Trigger Line: "Set ws = Sheets("incidents")
Resolution: Creation of an "incidents" sheet (embarassing, lol)

The Third Error: "Run-time error '1004': Application-defined or object-defined error"
Trigger Line: "Range("A1").Select"
Resolution: Replace the "ws.Select & ... & Range("A1").Select & Cells.Clear" lines with "ws.UsedRange.Clear"

 Here is the final code which is improved and works to integrate with my PDI and allow queries from within.

ADDITIONAL FEATURES: As compared to the code currently hosted at ServiceNowElite.com
Improved Indentation
Local Base64 Authorization Code Generation
Credentials Storage Migration to InputBox(no more saving your password in the worksheet)
MSXML2.DOMDocument60 fix
ExpandedColumns Setting (For COUNT vs table-building functionality)
Query Timer
Array Compatibility Upgrade: LBound & UBound
Case Code Cleanup
OrderBy Setting
Dedicated Query Record Limit Setting
Cleaner Worksheet Select Code
Parameters Renovation
Error Handling: Missing Worksheet
Error Handling: Incorrect Column Name
Error Handling: PDI Hibernating
Error Handling: PDI Wake In Progress
Error Handling: Incorrect ServiceNow URL
Error Handling: Incorrect ServiceNow Credentials

NOTE: You will have to enable "Microsoft WinHHTP Services, version 5.1", "Microsoft XML, v6.0", & "Microsoft Forms 2.0 Object Library" in Excel VBA via Tools > References to execute this code without error. Also, it is convenient to attach this macro to a CommandButton for ease of execution.

Private Function EncodeBase64(ByRef arrData() As Byte) As String
 Dim objXML As MSXML2.DOMDocument60
 Dim objNode As MSXML2.IXMLDOMElement

 Set objXML = New MSXML2.DOMDocument60
 Set objNode = objXML.createElement("b64")
 objNode.DataType = "bin.base64"
 objNode.nodeTypedValue = arrData
 EncodeBase64 = objNode.Text

 Set objNode = Nothing
 Set objXML = Nothing
End Function

Sub ServiceNowRestAPIQuery()
    Dim AuthorizationCode As String
    Dim LoginInput As String
    Dim Beginning As Single: Dim Ending As Single
    
    On Error GoTo ErrMsg:
    ' Replace with your Service Now Inctance URL
    InstanceURL = "https://dev#####.service-now.com"
    ' Replace with your Authorization code
    LoginInput = InputBox("Enter the ServiceNow login and password as login:password.")
        If StrPtr(LoginInput) = 0 Or LoginInput = vbNullString Then
            MsgBox ("The authentication prompt was cancelled or no credentials were entered. Cancelling query.")
            End
        End If
    Beginning = Timer()
    AuthorizationCode = "Basic " & EncodeBase64(StrConv(LoginInput, vbFromUnicode))
    ' Add more tables as comma seperated with no spaces
    TableNames = ("incident_sla,incident_metric,asmt_metric_result,asmt_assessment_instance")
    
    Dim ws As Worksheet
    Dim objHTTP As New WinHttp.WinHttpRequest
    Dim Columns As String
    Dim Header As Boolean
    Dim jsonString As String
    Dim Resp As New MSXML2.DOMDocument60
    Dim Result As IXMLDOMNode
    Dim ColumnsArray As Variant
    Dim ExpandedQuery As Boolean: ExpandedQuery = TRUE
    
    TablesArray = Split(TableNames, ",")
    
    For x = LBound(TablesArray) To UBound(TablesArray)
    
        'Table Choices
        Select Case TablesArray(x)

        Case "incident_sla"
            Columns = "inc_number"
            ExpandedColumns = "inc_assignment_group,taskslatable_business_duration"
            Conditions = "active%3Dtrue"
            OrderBy = "inc_number"
            Limit = 10
         
        Case "incident_metric"
            Columns = "inc_number"
            ExpandedColumns = "inc_assignment_group,mi_business_duration"
            Conditions = "state=1"
            OrderBy = "inc_number"
            Limit = 10
        
        Case "asmt_metric_result"
            Columns = "instance.task_id"
            ExpandedColumns = "instance.task_id.assignment_group,metric.name,actual_value,sys_updated_on"
            Conditions = ""
            OrderBy = "instance.task_id"
            Limit = 10
        
        Case "asmt_assessment_instance"
            Columns = "number"
            ExpandedColumns = "sys_updated_on,state,task_id.assignment_group,metric_type"
            Conditions = ""
            OrderBy = "number"
            Limit = 10
        
        End Select
    
        'Case Preparation & Cleanup
        Set ws = ThisWorkbook.Sheets(TablesArray(x))
        If ExpandedQuery Then Columns = Columns & "," & ExpandedColumns
        ColumnsArray = Split(Columns, ",")
        Query = "&sysparm_query=" & Conditions & "^ORDERBY" & OrderBy
        QueryLimit = "&sysparm_limit=" & Limit
        ExpandedColumns = ""
        Limit = ""
        Conditions = ""
        OrderBy = ""
        
        'URL Construction
        Url = InstanceURL & "/api/now/table/"
        Table = TablesArray(x) & "?"
        sysParam = "sysparm_display_value=true&sysparm_exclude_reference_link=true" & QueryLimit & Query & "&sysparm_fields=" & Columns
        Debug.Print sysParam
        Url = Url & Table & sysParam
        objHTTP.Open "get", Url, False
        objHTTP.SetRequestHeader "Accept", "application/xml"
        objHTTP.SetRequestHeader "Content-Type", "application/xml"
    
        ' Authorization Code
        objHTTP.SetRequestHeader "Authorization", AuthorizationCode
        objHTTP.Send
    
        'objHTTP Debug & Error Hooks
        Debug.Print objHTTP.Status
        Debug.Print objHTTP.ResponseText
        If InStr(objHTTP.ResponseText, "sleepy_personal_peveloper") Then Err.Number = 77: GoTo ErrMsg: 'sleepy PDI
        If InStr(objHTTP.ResponseText, "Service Interruption:") Then Err.Number = 771: GoTo ErrMsg: 'waking PDI
        If InStr(objHTTP.ResponseText, "Required to provide Auth") Then Err.Number = 777: GoTo ErrMsg: 'incorrect credentials

        Header = False
        i = 1
        ws.UsedRange.Clear
    
        Resp.LoadXML objHTTP.ResponseText
        For Each Result In Resp.getElementsByTagName("result")
            For n = LBound(ColumnsArray) To UBound(ColumnsArray)
                If Header = False Then
                    ws.[A1].Offset(0, n).Value = ColumnsArray(n)
                End If
                ws.[A1].Offset(i, n).Value = Result.SelectSingleNode(ColumnsArray(n)).Text
            Next n
            i = i + 1
            Header = True
        Next Result
        'MsgBox Time
    Next x
Done:
    AuthorizationCode = "" 'ensure credentials are never stored
    Ending = Timer()
    Duration = Format(Round(Ending - Beginning, 2), "#0.00")
    MsgBox "The query successfully executed in " & Duration & " seconds."
    End 'also strips variable values
ErrMsg:
    AuthorizationCode = "" 'ensure credentials are never stored
    Select Case Err.Number
        Case 9
            MsgBox "The sheet name for """ & TablesArray(x) & """ is missing. Please create the sheet or correct the TableNames variable and then try again.", vbCritical, "Missing Worksheet"
        Case 77
            MsgBox "The Personal Developer Instance is in hibernation mode." & vbNewLine & "Please wake the instance and try again.", vbCritical, "PDI Hibernating"
        Case 91
            MsgBox "An incorrect column name of """ & ColumnsArray(n) & """ was queried for table """ & TablesArray(x) & """. Please fix the name and try again.", vbCritical, "Incorrect Column Name"
        Case 771
            MsgBox "It appears the PDI is in the progress of waking up." & vbNewLine & "Please wait and try again", vbCritical, "PDI Wake In Progress"
        Case 777
            MsgBox "Incorrect ServiceNow credentials were provided, please try again.", vbCritical, "Incorrect ServiceNow Credentials"
        Case -2147012889
            MsgBox "Invalid ServiceNow URL:  """ & InstanceURL & """" & vbNewLine & "Please correct the URL and try again.", vbCritical, "Incorrect ServiceNow URL"
        Case Else
            MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"
    End Select
    End 'also strips variable values
End Sub

 You can further improve the authorization method by migrating the credentials prompt from an InputBox to a UserForm. This is because in UserForms you can hide text input behind bullet points akin to a modern web password box. (You can do this with an InputBox too, but it requires lengthy coding, taking up more filespace.) See this StackOverflow question for how to configure a UserForm for this task, and this thespreadsheetguru.com blog post for making UserForms appear ontop of your Excel window. Since all of this is too VBA-technical for this site, I'm just posting links instead of going into the details.

Kind Regards,

Joseph

[JosephW1]

After conducting additional research into this error, it appears that the main culprit of the second error is an incorrect array configuration. However, I don't know how to debug it since the debug option is not in the error window. I have also created the How to fix Subscript out of range question on StackOverflow.com to address this issue, since this may be more of an Excel issue rather than a ServiceNow issue, and the folks at StackOverflow are used to troubleshooting Excel. Sorry about the possible confusion in posting location.

Hopefully I'm able to get this integration up and running!

UPDATE: The resolution to the second error as found on StackOverflow was that the sheet "incidents" was missing. Pressing F8 allowed me to execute the code line-by-line until the error occurred, allowing me to find the offending line, which I have added to the original post along with the resolution.

[JosephW1 · Accepted solution]

After solving the second error, I encountered a third error on the "Range("A1").Select" code.

As answered by BigBen in my How do I fix a Range Select 1004 question on StackOverflow, so resolve that error I replaced 

ws.Select
...
Range("A1").Select
Cells.Clear

with

ws.UsedRange.Clear

I no longer get any errors and now the query successfully integrates the queries into tables in my Excel spreadsheets! Woohoo!

[Dennis Hancy]

Hello,

One question I have is how to find the ServiceNow login and password.

When I sign in through our web app, It does not ask me for either.  "Somehow" it authenticates my login automatically.  I found a groovy script that appears to look up this information via environment variables.  I then reviewed the available environment variables available to me via VBA, but the SN user name and password are not on that list.

Any idea how I can look those up programmatically via VBA?  Thank you!

Dennis