Connect to ServiceNow from Excel ( NO ODBC)

akrambary · ‎08-14-2015

Hello everyone,

Still many of us need to do things the old school way with excel or for a specific reason we need to download and work with data in excel.

ODBC connection was always painful for me and i decided to try something new.

and here it's a direct connection from Excel using Rest API, hopefully it will be useful for others as well.

Please forgive my accent in the video as i'm not a native English speaker 🙂

As this is my first post so i'm not sure if i'm publishing in the right place, so please let me know if it's n't.

so here it's the tutorial and will appreciate your feedback.

</p>

ohhgr · ‎08-14-2015

That is quite a good way to retrieve data in an excel.

Thanks for sharing...

amacqueen · ‎08-17-2015

Worked well until I tried to get a little more sophisticated and now it only returns one record with a DOM node error DOM node.PNG

I have no idea about VB, would you be able to have a quick look at my code?

akrambary · ‎08-17-2015

It's hard to tell from the error code what exactly went wrong , but if you can tell me what you exactly changed or share your code (withoud URL and authorization code) maybe i can help

amacqueen · ‎08-18-2015

Many thanks Akram. FWIW initially I tested with fewer fields and it did what I expected ie bring in all the closed or completed incidents for last month it seems that only after I changed columns line to bring in more data that it failed. My code is below:-

ub ServiceNowRestAPIQuery()

' Replace with your Service Now Inctance URL

InstanceURL = "https://instance.service-now.com"

' Replace with your Authorization code

AuthorizationCode = "Basic 123456"

' Add more tables ascomma seperated with no spaces

TableNames = ("incident,problem")

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 DOMDocument

Dim Result As IXMLDOMNode

Dim ColumnsArray As Variant

TablesArray = Split(TableNames, ",")

For x = 0 To UBound(TablesArray)

'Table Choices

Select Case TablesArray(x)

Case "incident"

Set ws = Sheets("incidents")

columns = "number,category,escalation,caller_id,assigned_to,assignment_group,location,u_verify_the_business_service,u_secondary_business_services,short_description,u_completed,closed_at,priority,opened_at,"

ColumnsArray = Split(columns, ",")

OtherSysParam = "&sysparm_limit=10000"

SysQuery = "&sysparm_query=u_completedONLast%20month%40javascript%3Ags.beginningOfLastMonth()%40javascript%3Ags.endOfLastMonth()%5EORclosed_atONLast%20month%40javascript%3Ags.beginningOfLastMonth()%40javascript%3Ags.endOfLastMonth()"

Case "problem"

'Sheet name

Set ws = Sheets("problem")

'Columns to Query

columns = "number,short_description,state"

ColumnsArray = Split(columns, ",")

'Query filter Parameters

OtherSysParam = "&sysparm_query=state=1"

'Other Query Parameters

SysQuery = ""

End Select

URL = InstanceURL & "/api/now/table/"

Table = TablesArray(x) & "?"

sysParam = "sysparm_display_value=true&sysparm_exclude_reference_link=true" & OtherSysParam & SysQuery & "&sysparm_fields=" & columns

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 '("{" & Chr(34) & "short_description" & Chr(34) & ":" & Chr(34) & "Test API2" & Chr(34) & "}")

Debug.Print objHTTP.Status

Debug.Print objHTTP.ResponseText

ws.Select

Header = False

i = 1

Range("A1").Select

Cells.Clear

Resp.LoadXML objHTTP.ResponseText

For Each Result In Resp.getElementsByTagName("result")

For n = 0 To UBound(ColumnsArray)

If Header = False Then

ActiveCell.Offset(0, n).Value = ColumnsArray(n)

End If

ActiveCell.Offset(i, n).Value = Result.SelectSingleNode(ColumnsArray(n)).Text

Next n

i = i + 1

Header = True

Next Result

'MsgBox Time

Next x

End Sub

Many thanks

amacqueen · ‎08-18-2015

When I pastet the code in it missed the S in the first line but it is there.

akrambary · ‎08-18-2015

I don't see any issue in the code.

My guess is that some of the fields could have some illegal characters corrupting the xml format.

try add fields one by one while testing the code to figure out which field is causing the issue.

amacqueen · ‎08-19-2015

Many thanks Akram, I did try something similar however I will try the fields one by one.

amacqueen · ‎08-19-2015

I messed about with the VB code, removing all the fields except number and added them back in one by one testing each time and it all works now!!

Great solution Akram as I couldn't find a way using ODBC to just select all last months completed incidents. This will save me modifying my report each time.

Many thanks

akrambary · ‎08-20-2015

yw and so glad it was useful for you.

bharu · ‎01-23-2017

Hi,

This is really cool stuff and worked well for me. Now i want to pass login credentials dynamically. I placed a button on excel sheet and assigned this macro the button. On click of the button, it should prompt for userid and password. After entering the credentials, data should import.

Is this kind of solution possible? Let me know your thoughts.

altin · ‎03-16-2017

Cdata Software has developed The ServiceNow Excel Add-In, which is a powerful tool that allows you to connect with live ServiceNow data, directly from Microsoft Excel. Use Excel to read, write, and update Schedules, Timelines, Questions, Syslogs, etc. Perfect for mass imports / exports / updates, data cleansing & de-duplication, Excel based data analysis, and more!

See how easy it is to use Macro (VBA) functions and Cdata Excel formulas at: CData Excel Add-In for ServiceNow 2016 - Macro (VBA) Functions

Sidharth5 · ‎12-17-2019

How to apply filters at the VBA end, for example i want to be specific on records