🔹Problem Statement
HR team maintains employee data in Excel / Google Spreadsheet.
This data must be fetched every Monday and stored in a ServiceNow custom table for further processing.
✅ Solution
Use ServiceNow REST Message (Outbound Integration) with a Scheduled Job.
*Step by Step Implementation
#1: Prepare Spreadsheet
Create a Google Spreadsheet
Add data in tabular format:
Name | City | Age | Mobile
Share access as Anyone with link (View)
#2: Use Spreadsheet CSV Endpoint
Google Sheets provides a direct CSV export API.
Endpoint format:
https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/export?format=csv&gid=0
📌 This endpoint returns spreadsheet data in CSV format.
#3: Create REST Message
Go to:
System Web Services → Outbound → REST Message
Create new REST Message
Configure:
Method: GET
Endpoint: CSV export URL
#4: Test REST Message
Click Test
Response received like:
Name,City,Age,Mobile
Aditya,Pune,21,1234767857
Om,Pune,22,4567
✔ Confirms successful data fetch
#5: Create Scheduled Job
Go to:
System Definition → Scheduled Jobs
Create a Weekly Scheduled Script
Set:
Day: Monday
Time: As required
#6: Fetch Data in Scheduled Job
Use REST Message code (Preview Script) inside Scheduled Job:
var r = new sn_ws.RESTMessageV2();
r.setEndpoint("CSV_ENDPOINT_URL");
r.setHttpMethod("GET");
var response = r.execute();
var csvData = response.getBody();
#7: Parse CSV & Insert Records
Split CSV rows
Loop through records
Insert into custom table
#8: Add Validations (Enhancement)
✔ Avoid duplicate records (based on mobile / email)
✔ Validate mobile number length
✔ Mandatory field checks
✔ Error handling & logging
✅ Final Outcome
✔ Automated weekly data sync
✔ HR continues using Excel
✔ ServiceNow stores clean data
✔ Zero manual effort
