Hello Everyone,
I would like to share one of my work that might helpful to my fellow ServiceNow colleagues i.e. integration between ‘Microsoft SharePoint’ and ‘ServiceNow’. It can be used to transfer data between ‘Microsoft SharePoint’ and ‘ServiceNow’ with a ‘Single Click’ only.
Notes:-
1) Generally my articles are lengthy in nature as I try to give all details, so whoever goes through it get all details and able to complete the job end to end.
So, please be patient and take some time to go over my articles.
2) I am just sharing my way of completion and being owner of ‘very less ego’ & ‘always learner mentality’ I welcome for ‘feedback’. So, please feel free to comment on my articles without any hesitation.
Step by Step Integration Details
Pre-requisite for Success
1) Microsoft Outlook should remain open during the entire time of execution.
2) Anything marked as - <*XXXX*> please update with your specific details.
First Step - VBS Script Creation
This step is to call/wake up the excel macro without opening it and run it in the background. Now, please copy the below mentioned script to a ‘notepad’ and save it with any name but the save extension must .vbs
Script
Set objExcel = CreateObject("Excel.Application")
objExcel.Application.Run "'<location of macro enabled excel file>\<name of the excel>.xlsm'!Module1.RefreshConnections" //location and name can be taken from the Second Step mentioned below.
objExcel.DisplayAlerts = False
objExcel.Application.Quit
Set objExcel = Nothing
Second Step – First Copy download from Microsoft Sharepoint
In this step please download the ‘specific view’(Like my below example screenshot view is – ‘Ankan-Test’ )in excel format and to do that open the SharePoint URL in ‘Internet Explorer’ and click on the ‘List’ in the menu bar and then click on ‘Export to Excel’.
Upon Clicking ‘Export to Excel’ the following pop-up will come at the bottom of the screen.
Now click on ‘Save’ and the following confirmation of saving will show up.
Then please click on the ‘Open’ from the above screenshot and the below screen will come up and click ‘Enable’ from it and your first file will get downloaded from ‘Microsoft SharePoint’ and save it with .xlsm extension.
Mostly this ‘owssvr’(Which is basically a ‘Microsoft Excel Web Query File’) will save in the ‘Downloads’ folder and this is a onetime download for ‘one specific system’.
Third Step – Macro Enabled Excel Creation for capturing insert/update from Microsoft Sharepoint
Now, open it up and go to the ‘File’ in the menu of the excel and then click on the ‘Options’ and then click on the ‘Customize Ribbon’ and finally in the ‘Main Tabs’ section check the ‘Developer’ and click on ‘OK’. Please refer the screenshots below --
Now, please ‘save’ the excel and open it up again. After opening the excel click on the ‘Developer’ menu and click on ‘Macros’.
Now, please give a name – RefreshConnections and click on ‘Create’.
Then just ‘copy & paste’ the below mentioned code as per below screenshot.
Script
ThisWorkbook.RefreshAll
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:="<*Please specify Save location*>", FileFormat:=xlWorkbookDefault
Call Mail_workbook_Outlook
End Sub
Sub Mail_workbook_Outlook()
Dim OutApp As Object
Dim OutMail As Object
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.to = "<*ServiceNow Email address of the instance*>"
.CC = ""
.BCC = ""
.Subject = "<*Please provide any subject*>"
.Body = ""
.Attachments.Add ("<*Please specify Save location*>\<*Excel Sheet Name*>.xlsx")
.Send
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Fourth Step – Create Transform Map(s) considering loading it manually into ServiceNow from Microsoft Sharepoint
Create a new ‘Load Data’ table as per mentioned screenshot below by just giving any meaningful label name for the purpose and then select the file from the saved location and click on ‘Submit’.
Then create Transform Map(s) to use the ‘Load Data’ functionality to load the data saved in the excel mentioned in the ‘Second Step’ and then using certain Transform Map(s) to add/update details into destination ServiceNow table and keep ‘sys_id(s)’ handy for those Transform Map(s).
Try this combination once to test it’s working successfully or not.
Fifth Step – Create inbound email actions for ServiceNow
Now, type ‘system policy’ in the navigation pane and then click on ‘Inbound Actions’ under ‘Email’.
Then Click on the ‘New’ as per below screenshot.
Then please fill up the ‘When to run’ as mentioned below and whatever is not filled that can be filled as per requirement.
After that please click on the ‘Actions’ tab and just ‘copy & paste’ the below script into there and fill out the necessary sections you need to in the script.
Script
(function runAction(/*GlideRecord*/ current, /*GlideRecord*/ event, /*EmailWrapper*/ email, /*ScopedEmailLogger*/ logger, /*EmailClassifier*/ classifier) {
var importSetTableName = "<*Mention the newly Load Data table name created on Third Step*> ";
var transformMapIDs = "<*sys_id for newly created transformation map(s) created on Third Step*>"; //Use a comma to specify multiple transform maps
var applicatonScope = "Global";
var date = new GlideDate();
// Create the datasource record
current.name = "<*Please provide any name*>";
current.import_set_table_name = importSetTableName;
current.file_retrieval_method = "Attachment";
current.type = "File";
current.format = "Excel"; // For Excel Files
//current.format = "CSV"; // For CSV Files
current.header_row = 1;
current.sheet_number = 1;
current.sys_package.setDisplayValue(applicatonScope);
current.sys_scope.setDisplayValue(applicatonScope);
var dataSourceID = current.insert();
/*
* Schedule Load of Attachment
*
* This inbound email action will generate an import data source, however the attachment isn't copied to the data source until
* after the insert of the record. Scheduling the import to happen 30 seconds later so that attachment has time to be copied.
*/
new global.EmailFileImportUtils().scheduleImport(dataSourceID, transformMapIDs);
})(current, event, email, logger, classifier);
Sixth Step – Create Script include
Last but not least the need is to create a ‘Script Include’ exactly as below and then copy paste the script mentioned below
Script
var EmailFileImportUtils = Class.create();
EmailFileImportUtils.prototype = {
initialize: function() {
},
scheduleImport: function(dataSourceID, transformMapIDs) {
/**
* Create scheduled job to process import
*
* The inbound email action will generate an import data source, however the attachment isn't copied to the data source until
* after the insert of the record. The code below will create a scheduled job to process the import 30 seconds later
* so that attachment has time to be copied to the data source from the email.
**/
var schRec = new GlideRecord("sys_trigger");
schRec.name = "Load Data Source: " + dataSourceID;
schRec.trigger_type = 0; // Run Once
schRec.script = "new global.EmailFileImportUtils().loadImportSet('" + dataSourceID + "', '" + transformMapIDs + "')";
var nextAction = new GlideDateTime();
nextAction.addSeconds(30); // 30 seconds should be enough time however this can be changed.
schRec.next_action = nextAction;
schRec.insert();
},
loadImportSet: function(dataSourceID, transformMapIDs) {
// Get Datasource Record
var dataSource = new GlideRecord("sys_data_source");
dataSource.get(dataSourceID);
// If CSV and header isn't on row 1, recreate attachment with empty rows removed, and delete original
if (dataSource.getValue("format") == "CSV" && dataSource.getValue("header_row") > 1) {
var attachmentRec = new GlideRecord("sys_attachment");
attachmentRec.addQuery("table_sys_id", dataSource.getValue("sys_id"));
attachmentRec.query();
if (attachmentRec.next()) {
var oldAttachmentID = attachmentRec.getValue("sys_id");
var inputStream = new GlideSysAttachment().getContentStream(oldAttachmentID);
var textReader = new GlideTextReader(inputStream);
var ln = " ";
var newLine = "";
var lineCounter = 0;
var headerRow = parseInt(dataSource.getValue("header_row"));
while((ln = textReader.readLine()) != null) {
lineCounter += 1;
if (lineCounter < headerRow) {
continue;
}
if (ln.length > 1) {
newLine += ln + "\n";
}
}
new GlideSysAttachment().write(dataSource, 'Changed ' + attachmentRec.getValue('file_name'), "text/csv", newLine);
//Delete original email attachment so that the new one is processed. If two remain, API may get confused on which one to process.
attachmentRec.deleteRecord();
}
}
// Process data source file
var loader = new GlideImportSetLoader();
var importSetRec = loader.getImportSetGr(dataSource);
var ranload = loader.loadImportSetTable(importSetRec, dataSource);
importSetRec.state = "loaded";
importSetRec.update();
// Transform import set
var transformWorker = new GlideImportSetTransformerWorker(importSetRec.sys_id, transformMapIDs);
transformWorker.setBackground(true);
transformWorker.start();
},
type: 'EmailFileImportUtils'
};
