The Problem
If you've tried importing large questionnaire templates into ServiceNow's Third Party Risk Management (TPRM) module using the out-of-the-box Excel import UI, you may hit this wall — the import starts, processes a few hundred rows, then silently fails leaving you with an incomplete template and no clear error message.
The root cause is the UI mechanism itself. The import button triggers a GlideOverlay iframe loading the vrm_import_excel Service Portal page. Processing runs synchronously inside that iframe's HTTP session. When the file is large — particularly when sections carry heavy answer choice payloads averaging 1,700+ characters per row — the session exhausts before the import completes.
This isn't a data problem. Your Excel file is fine. It's a session timeout constraint baked into the UI delivery mechanism.
Why the Standard Workarounds Don't Help
Splitting the file into smaller chunks seems like the obvious fix. And for simple questionnaires it works. But TPRM questionnaire templates have a dependency model — the Depends on column references the Serial Number of a parent question. The VRMImportExcelAPI Script Include that backs the import resolves these dependencies in memory during a single import run using a dependencyMap array. Split the file and you break that in-memory chain.
You could increase glide.session.timeout on the instance, but that's a platform-wide change with broader implications, and on managed instances it may not be available to you at all.
The Solution — Import Set with Transform Scripts
The cleanest fix is to bypass the UI entirely using ServiceNow's Import Set framework. This gives you background processing with no session timeout, full visibility into row-level errors, and a repeatable load mechanism you can run again if anything needs correcting.
The approach replicates the logic of VRMImportExcelAPI directly in the transform scripts — category creation, data type mapping, answer choice definitions, dependency wiring, and displayed when configuration — all handled without touching the original Script Include.
Setup
- Create a Data Source and using Questionnaire Template provided by Servicenow
Navigate to System Import Sets → Data Sources → Load data:
- Type: File
- Format: Excel
- Sheet number: 1
- Header row: 1
- Attach your questionnaire Excel file with header row based on the sample questionnaire template provided by Servicenow.
Load the Data Source. ServiceNow auto-creates a staging table — in this example with one column per.
- Create a Transform Map
Navigate to System Import Sets → Transform Maps → New:
- Source table: your staging table
- Target table: asmt_metric
- Application: Global
Delete any auto-generated field maps. All field mapping is handled in the transform scripts below.
Transform Scripts
onStart — creates the metric type record:
var FILE_NAME = 'Your_Questionnaire_Name.xlsx';
var CLASSIFICATION = 'questionnaire_template';
// Clean up orphaned templates from prior runs
var cleanGr = new GlideRecord('asmt_metric_type');
cleanGr.addQuery('name', 'STARTSWITH', FILE_NAME);
cleanGr.query();
while (cleanGr.next()) {
cleanGr.deleteRecord();
}
// Create metric type
var typeGr = new GlideRecord('asmt_metric_type');
typeGr.initialize();
typeGr.name = FILE_NAME;
typeGr.description = 'Imported from Excel';
typeGr.evaluation_method = 'vdr_risk_asmt';
typeGr.classification = CLASSIFICATION;
typeGr.publish_state = 'published';
var typeId = typeGr.insert();
gs.setProperty('spq.metric_type_id', typeId);
gs.setProperty('spq.cat_order', '10');
gs.info('Metric type created: ' + typeId);
onBefore — runs per row, sets all target fields and wires dependencies:
var DELIMITER = new RegExp(gs.getProperty('sn_vdr_risk_asmt.excel_choice_delimiter', ','));
var metricTypeId = gs.getProperty('spq.metric_type_id');
function mapDataType(dt) {
if (!dt) return undefined;
var m = {
'checkbox':'checkbox', 'yes/no':'boolean', 'boolean':'boolean',
'attachment':'attachment', 'date':'date', 'date/time':'datetime',
'datetime':'datetime', 'percentage':'percentage', 'string':'string',
'number':'long', 'choice':'choice', 'multiple selection':'multiplecheckbox',
'numeric scale':'numericscale', 'ranking':'ranking'
};
return m[(dt + '').toLowerCase().trim()];
}
function isPositive(val) {
val = (val + '').trim().toLowerCase();
return val === 'true' || val === 'yes' || val === 'y' || val === '1' || val === 'checked';
}
function isBlank(val) {
if (val === null || val === undefined) return true;
val = (val + '').trim().toLowerCase();
return val === '' || val === 'null' || val === 'blank';
}
function getOrCreateCategory(name) {
var catGr = new GlideRecord('asmt_metric_category');
catGr.addQuery('metric_type', metricTypeId);
catGr.addQuery('name', name);
catGr.setLimit(1);
catGr.query();
if (catGr.next()) return catGr.sys_id + '';
var catOrder = parseInt(gs.getProperty('spq.cat_order', '10'));
var newCat = new GlideRecord('asmt_metric_category');
newCat.initialize();
newCat.name = name;
newCat.metric_type = metricTypeId;
newCat.weight = 10;
newCat.order = catOrder;
var catId = newCat.insert() + '';
gs.setProperty('spq.cat_order', (catOrder + 10) + '');
return catId;
}
var serialNumber = (source.u_serial_number + '').trim();
var category = (source.u_category + '').trim();
var question = (source.u_question + '').trim();
var order = (source.u_order + '').trim();
var dataType = mapDataType(source.u_data_type + '');
var description = (source.u_description + '').trim();
var dependsOn = (source.u_depends_on + '').trim();
var displayedWhen = (source.u_displayed_when + '').trim().toLowerCase();
var correctAnswer = (source.u_correct_answer + '').trim().toLowerCase();
var scaleDef = (source.u_scale_definition + '').trim().toLowerCase();
var minVal = (source.u_min + '').trim();
var maxVal = (source.u_max + '').trim();
var weight = (source.u_weight + '').trim();
if (!category || !question || !dataType) {
ignore = true;
gs.warn('Row ' + serialNumber + ' skipped — missing mandatory field');
}
target.metric_type = metricTypeId;
target.category = getOrCreateCategory(category);
target.name = question;
target.question = question;
target.order = order;
target.datatype = dataType;
target.description = description;
target.mandatory = isPositive(source.u_mandatory);
target.scored = isPositive(source.u_scored);
target.allow_not_applicable = isPositive(source.u_allow_not_applicable);
target.allow_add_info = isPositive(source.u_allow_additional_information);
target.is_excel_import = true;
if (!isBlank(minVal)) target.min = minVal;
if (!isBlank(maxVal)) target.max = maxVal;
if (!isBlank(weight)) target.weight = weight;
if (!isBlank(scaleDef)) target.scale_definition = scaleDef;
if (!isBlank(correctAnswer)) {
target.correct_answer = correctAnswer;
switch (dataType) {
case 'checkbox':
target.correct_answer = isPositive(correctAnswer) ? '1' : '0';
target.correct_answer_checkbox = target.correct_answer;
break;
case 'boolean':
target.correct_answer = isPositive(correctAnswer) ? '1' : '0';
target.correct_answer_yesno = target.correct_answer;
break;
case 'choice':
target.correct_answer_choice = correctAnswer;
break;
}
}
// Dependency wiring — safe because parents always carry lower serial numbers
// and are therefore already inserted before child rows process
if (!isBlank(dependsOn)) {
var parentRow = new GlideRecord('u_supplier_profile_questionnaire_new');
parentRow.addQuery('u_serial_number', dependsOn);
parentRow.query();
if (parentRow.next()) {
var parentQuestion = (parentRow.u_question + '').trim();
var parentMet = new GlideRecord('asmt_metric');
parentMet.addQuery('metric_type', metricTypeId);
parentMet.addQuery('name', parentQuestion);
parentMet.setLimit(1);
parentMet.query();
if (parentMet.next()) {
target.depends_on = parentMet.sys_id + '';
if (!isBlank(displayedWhen)) {
switch (parentMet.datatype + '') {
case 'checkbox':
target.displayed_when_checkbox = isPositive(displayedWhen) ? '1' : '0';
break;
case 'boolean':
target.displayed_when_yesno = isPositive(displayedWhen) ? '1' : '0';
break;
case 'choice':
case 'multiplecheckbox':
case 'numericscale':
var choiceList = displayedWhen.split(DELIMITER);
var choiceSysIds = [];
for (var cl = 0; cl < choiceList.length; cl++) {
if (!choiceList[cl].trim()) continue;
var defLookup = new GlideRecord('asmt_metric_definition');
defLookup.addQuery('metric', parentMet.sys_id + '');
defLookup.addQuery('display', 'CONTAINS', choiceList[cl].trim());
defLookup.query();
while (defLookup.next())
choiceSysIds.push(defLookup.sys_id + '');
}
target.displayed_when = choiceSysIds.join(',');
break;
default:
target.displayed_when = displayedWhen;
}
}
} else {
gs.warn('Parent metric not found — serial: ' + dependsOn);
}
}
}
onAfter — creates answer choice definition records:
var DELIMITER = new RegExp(gs.getProperty('sn_vdr_risk_asmt.excel_choice_delimiter', ','));
var ansChoices = (source.u_answer_choices + '').trim();
var ansValues = (source.u_answer_choice_values + '').trim();
var correctAnswer = (source.u_correct_answer + '').trim().toLowerCase();
var dataType = target.datatype + '';
function isBlank(val) {
if (val === null || val === undefined) return true;
val = (val + '').trim().toLowerCase();
return val === '' || val === 'null' || val === 'blank';
}
if (!isBlank(ansChoices) && !isBlank(ansValues)) {
var choiceArr = ansChoices.split(DELIMITER);
var valueArr = ansValues.split(DELIMITER);
var defOrder = 10;
for (var i = 0; i < choiceArr.length; i++) {
var displayVal = (choiceArr[i] + '').trim();
if (!displayVal) continue;
var defGr = new GlideRecord('asmt_metric_definition');
defGr.metric = target.sys_id + '';
defGr.display = displayVal;
defGr.order = defOrder;
if (!isBlank(valueArr[i] + ''))
defGr.value = (valueArr[i] + '').trim();
defGr.insert();
defOrder += 10;
}
}
if ((dataType === 'choice' || dataType === 'numericscale') && !isBlank(correctAnswer)) {
var cList = correctAnswer.split(DELIMITER);
var cSysIds = [];
for (var c = 0; c < cList.length; c++) {
if (!cList[c].trim()) continue;
var cDefGr = new GlideRecord('asmt_metric_definition');
cDefGr.addQuery('metric', target.sys_id + '');
cDefGr.addQuery('display', 'CONTAINS', cList[c].trim());
cDefGr.query();
while (cDefGr.next())
cSysIds.push(cDefGr.sys_id + '');
}
if (cSysIds.length > 0) {
var metUpd = new GlideRecord('asmt_metric');
metUpd.get(target.sys_id + '');
metUpd.correct_answer_choice = cSysIds.join(',');
metUpd.update();
}
}onComplete — verify counts and clean up:
var FILE_NAME = 'Your_Questionnaire_Name.xlsx';
var tGr = new GlideRecord('asmt_metric_type');
tGr.addQuery('name', FILE_NAME);
tGr.query();
if (tGr.next()) {
var typeId = tGr.sys_id + '';
var mGr = new GlideRecord('asmt_metric');
mGr.addQuery('metric_type', typeId);
mGr.query();
gs.info('Questions loaded: ' + mGr.getRowCount());
var catGr = new GlideRecord('asmt_metric_category');
catGr.addQuery('metric_type', typeId);
catGr.query();
gs.info('Categories: ' + catGr.getRowCount());
var defGr = new GlideRecord('asmt_metric_definition');
defGr.addQuery('metric.metric_type', typeId);
defGr.query();
gs.info('Answer choice definitions: ' + defGr.getRowCount());
var depGr = new GlideRecord('asmt_metric');
depGr.addQuery('metric_type', typeId);
depGr.addNotNullQuery('depends_on');
depGr.query();
gs.info('Dependencies wired: ' + depGr.getRowCount());
}
gs.setProperty('spq.metric_type_id', '');
gs.setProperty('spq.cat_order', '');
gs.info('Transform complete');
Result
The Import Set approach processes all rows cleanly, wires all dependency relationships, creates answer choice definition records, and completes without any session constraint. The transform log gives you full visibility into any skipped rows and the onComplete script confirms exact counts against expected values.
If this helped you, drop a comment below — happy to discuss variations for different questionnaire classifications or larger template volumes.