Integrating Excel and ServiceNow

I was recently asked if it was possible to read records from an Excel spreadsheet and create the related incidents inside of ServiceNow. This is very possible and is the purpose of this post. Let’s dive into it.

To start with, we will create an Excel spreadsheet. We can do so using the Microsoft Excel thick client or the web version. We can create the following columns and ensure that we create a data table so that Logic Apps can consume the data from Excel. I chose to store my Excel file in OneDrive, but SharePoint will also work.

After we create the ServiceNow ticket, we want to update the spreadsheet to reflect that we have done so that we don’t create a duplicate ticket. We will use the ID column as our Key Column which will allow us to update just a specific record. We will see the Key Column being used in the Excel connector later in this post.

Our process will be to create a recurring schedule that will check for any records in this spreadsheet that have a Status of pending. For each of these records, we will create a ticket and then update this spreadsheet by providing the ServiceNow ticket number in the Ticket Number field and changing the Status to Ticket Created.

Since we will have a recurring schedule, we will start our logic app using the Recurrence trigger and setting our interval to 1 hour.

Now, we need to retrieve our unprocessed records from our Excel spreadsheet. I have decided to store my spreadsheet in OneDrive. We can retrieve these records by using the List rows present in a table action. It is important that we include a Filter Query of Status eq ‘pending’. This allows us to only retrieve records that haven’t been processed. After we create our ticket, we will update this value.

ServiceNow has some specific requirements when creating an incident. It needs to reference the person who is logging the ticket. In this case, we need to retrieve this value by Listing Records from the User table. We can filter out records by including a Query. In this case we will filter by providing the following expression: email=Caller where our Caller value comes from our Excel spreadsheet and represents the email address of a user in our ServiceNow system.

Also note that since Excel can return 1 or more records, we will include a For each loop to include these actions in.

We are going to need the SYS ID from this response when we create our ServiceNow incident. To simplify accessing this value (and avoiding a nested loop) we can use a Compose action and an expression of first(body('List_Records_-_User_ID')?['result'])?['sys_id'] to access this value.

Note: The previous expression is dependent upon the naming used in my List Records action.

We will now perform a similar set of operations to retrieve the SYS ID for our Assignment Group. This will allow us to assign a ticket to a specific group configured in ServiceNow. We will use the Group value from Excel and pass it into our Query of name=Group.

Similar to our situation with the Caller above, we want to extract the SYS ID without the need for an additional loop. We will once again rely upon a Compose action and an expression of:

first(body('List_Records_-_Assignment_Groups')?['result'])?['sys_id']

Once again, please pay attention to the name of your ServiceNow action and ensure that is reflected in your expression.

We can now use the data that we collected from Excel and our calls to ServiceNow to go ahead and create a record in the Incident table and map the following fields: Caller, Short description, Assignment group and Urgency.

When we create an incident record in ServiceNow, it will return our ticket number in the response payload in the Number attribute. We can use this value when updating our spreadsheet so that we track it.

Note: This Excel action requires the use of a Key so that it can update the appropriate record. In this example, we are going to use the ID value that was retrieved from Excel as it acts as a unique identifier for us.

Conclusion

In this post, we discussed how we can use Azure Logic Apps to consume a list of issues from an Excel spreadsheet and create tickets for each row in the Excel spreadsheet.
If you would like to consume this content in video format, please check out the following YouTube video: Integrating Excel and ServiceNow using Azure Logic Apps.