ServiceNow On-Call Sync Automation Artifacts

Create On-Call Rotation Schedule.js

(function () {
	try {
		var lastRowProp = 'rota.last_row_processed';
		var assignmentGroupId = '<assignment-group-id>';
		var startRow = parseInt(gs.getProperty(lastRowProp), 10);
		var googleSheetId = '<google-sheet-id>';
		var range = 'Rotation!A' + startRow + ':U' + (startRow + 7);

		// Initialize the helper
		var syncHelper = new GoogleSheetsOnCallSync(assignmentGroupId);

		// Define the structure of THIS team's sheet
		var teamConfig = {
			dateIdx: 0,
			levels: [
				{
					emailIdx: <l1-email-index>, // L1
					primaryRoster: '<primary-roster-id>',
					secondaryRoster: '<secondary-roster-id>',
				},
				{
					emailIdx: <l2-email-index>, // L2
					primaryRoster: '<primary-roster-id>',
					secondaryRoster: '<secondary-roster-id>',
				},
			],
		};
		var request = new sn_ws.RESTMessageV2('Google Sheets On-Call Sync REST Message', 'GET');
		request.setStringParameter('sheet_id', googleSheetId);
		request.setStringParameter('range', range);

		var response = request.execute();
		if (response.getStatusCode() === 200) {
			var rows = JSON.parse(response.getBody()).values;

			if (rows && rows.length > 0) {
				syncHelper.processRosterRows(rows, teamConfig);

				// Evening check to advance pointer
				var isMorning = new GlideDateTime().getTime().getHourOfDayLocalTime() < 12;
				if (!isMorning) {
					gs.info('Create On-Call Rotation Schedule :: Updating System Property :: ' + (startRow + 1).toString());
					gs.setProperty(lastRowProp, (startRow + 1).toString());
				}
			}
		}
	} catch (ex) {
		gs.error('Create On-Call Rotation Schedule :: ' + ex.message);
	}
})();

GoogleSheetsOnCallSync.js

var GoogleSheetsOnCallSync = Class.create();
GoogleSheetsOnCallSync.prototype = {
    initialize: function (assignmentGroupId) {
		this.assignmentGroupId = assignmentGroupId;
		this.today = new GlideDate().getValue();
		this.maxDate = new GlideDate();
		this.maxDate.addDays(7); // Default window
		this.maxDate = this.maxDate.getValue();
	},

	/**
	 * @param {Array} rows - The raw data from GSheets
	 * @param {Object} config - Mapping of cell indices to Roster IDs
	 * Example config: {
	 * dateIdx: 0,
	 * levels: [
	 * { emailIdx: 5, primaryRoster: 'id', secondaryRoster: 'id' },
	 * { emailIdx: 7, primaryRoster: 'id', secondaryRoster: 'id' }
	 * ]
	 * }
	 */
	processRosterRows: function (rows, config) {
		if (!rows || rows.length === 0) return;

		for (var i = 0; i < rows.length; i++) {
			var rowDate = rows[i][config.dateIdx];

			// Validate Date
			if (rowDate < this.today) continue;
			if (rowDate > this.maxDate) break;

			var nextDate = this._getNextDay(rowDate);

			// Loop through each level (L1, L2, L3...) defined in config
			for (var j = 0; j < config.levels.length; j++) {
				var level = config.levels[j];
				var email = rows[i][level.emailIdx];

				if (!email) continue;

				var userSysId = this._getUserByEmail(email);
				if (userSysId && this._isUserInGroup(userSysId)) {
					this.upsertMember(level.primaryRoster, userSysId, rowDate, rowDate);
					this.upsertMember(level.secondaryRoster, userSysId, nextDate, nextDate);
				}
			}
		}
	},

	upsertMember: function (rosterId, userSysId, fromDT, toDT) {
		var memberGR = new GlideRecord('cmn_rota_member');
		memberGR.addQuery('roster', rosterId);
		memberGR.addQuery('from', fromDT);
		memberGR.addQuery('to', toDT);
		memberGR.query();

		if (memberGR.next()) {
			if (memberGR.getValue('member') != userSysId) {
				memberGR.setValue('member', userSysId);
				memberGR.update();
				gs.info('On-Call Sync :: Updated ' + fromDT + ' to ' + userSysId);
			}
		} else {
			var newMember = new GlideRecord('cmn_rota_member');
			newMember.initialize();
			newMember.roster = rosterId;
			newMember.member = userSysId;
			newMember.from = fromDT;
			newMember.to = toDT;
			newMember.insert();
		}
	},

	_getUserByEmail: function (email) {
		var userGR = new GlideRecord('sys_user');
		if (userGR.get('email', email)) return userGR.getUniqueValue();
		gs.error('On-Call Sync :: User not found: ' + email);
		return null;
	},

	_isUserInGroup: function (userSysId) {
		var grMember = new GlideRecord('sys_user_grmember');
		grMember.addQuery('user', userSysId);
		grMember.addQuery('group', this.assignmentGroupId);
		grMember.query();
		return grMember.hasNext();
	},

	_getNextDay: function (dateStr) {
		var gdt = new GlideDateTime(dateStr);
		gdt.addDaysLocalTime(1);
		return gdt.getDate().getValue();
	},

    type: 'GoogleSheetsOnCallSync'
};