ServiceNow Survey Responses in Rows

Getting ServiceNow survey results in rows can be quite useful, especially for export to other tools. Out of the box, ServiceNow provides a UI Action called View Reponses in Rows, however this action is not visible if your survey has more than 5 questions. You can change the visibility on the UI Action, but it’s still only going to show you the first 5 questions. In addition, if one of those questions is a Multiple Choice, it’s going to give you a row for each of the answers. So not very useful.

One solution to view the survey responses in rows is to copy the results into a table. Here are the steps for accomplishing this.

1. Create your table. Create a string column for each question in the survey.
2. Go to your survey definition and get the sys_id for each of the questions:

3. Build and object with the sys_id of the question and the name of the column in the target table created in step 1

{
    "e9b1c416979b211066c17076f053af90":"u_name",
    "688408d6979b211066c17076f053af8a":"u_what_do_you_like_to_eat",
    "85020c16979b211066c17076f053afff":"u_location",
    "29b1c416979b211066c17076f053af98":"u_address",
    "29b1c416979b211066c17076f053af9d":"u_company",
    "29b1c416979b211066c17076f053afa2":"u_title",
    "6db1c416979b211066c17076f053afcc":"u_employment_type",
    "2c124c16979b211066c17076f053afdc":"u_how_do_you_feel"

}

4. Run the below script in a backgroun script. For the line assGR.addQuery(‘metric_type’, ’19b14cd2979b211066c17076f053af5b’), put the sys_id of your survey.

//This script takes the row results from the survey instances and copies them to columns in the u_test_survey_results.

//the field map is the sys_id from the asmt_metric table and the column name in the target table (u_test_survey_results)
fieldMap = {
    "e9b1c416979b211066c17076f053af90":"u_name",
    "688408d6979b211066c17076f053af8a":"u_what_do_you_like_to_eat",
    "85020c16979b211066c17076f053afff":"u_location",
    "29b1c416979b211066c17076f053af98":"u_address",
    "29b1c416979b211066c17076f053af9d":"u_company",
    "29b1c416979b211066c17076f053afa2":"u_title",
    "6db1c416979b211066c17076f053afcc":"u_employment_type",
    "2c124c16979b211066c17076f053afdc":"u_how_do_you_feel"
}

var assGR = new GlideRecord('asmt_assessment_instance');
assGR.addQuery('metric_type', '19b14cd2979b211066c17076f053af5b'); //put the sys_id of your survey here
assGR.query();

while (assGR.next()) {

    // initialize a new record to dump the survey data into
    var resultGR = new GlideRecord('u_test_survey_results');
    resultGR.initialize();

    resultGR.setValue('u_assessment_instance', assGR.getUniqueValue());
    resultGR.setValue('u_date_taken', assGR.taken_on);

    //find all the question instances
    var questionGR = new GlideRecord("asmt_assessment_instance_question");
    questionGR.addQuery('instance', assGR.getUniqueValue());
    questionGR.query();


    while (questionGR.next()) {
        //look up the corresponding field name
        var fieldName = fieldMap[questionGR.metric];

        var fieldValue;
        if (questionGR.metric.datatype == 'reference') {
            var refTable = questionGR.metric.reference_table;
            var refGR = new GlideRecord(refTable);
            refGR.get(questionGR.reference_id);
            fieldValue = refGR.getDisplayValue();

        } else if (questionGR.metric.datatype == 'string' || questionGR.metric.datatype == 'date') {
            fieldValue = questionGR.string_value.getDisplayValue();
        } else {
            fieldValue = getDefinitionDisplayValue(questionGR.metric.sys_id, questionGR.value);
        }
        //if it is a multiple checkbox question, append the selection to the existing values (target record has one string field with all values separated by commas)
        if (questionGR.metric.datatype == 'multiplecheckbox') {
            var assGRValue = resultGR.getValue(fieldName);
            if (assGRValue) {
                resultGR.setValue(fieldName, assGRValue + ',' + fieldValue);
            } else {
                resultGR.setValue(fieldName, fieldValue);
            }

        } else {
            resultGR.setValue(fieldName, fieldValue);
        }




    }

    resultGR.insert();
}
function getDefinitionDisplayValue(metricSysId, value) {
    var definitionGR = new GlideRecord('asmt_metric_definition');
    definitionGR.addQuery('metric', metricSysId);
    definitionGR.addQuery('value', value);
    definitionGR.query();
    if (definitionGR.next()) {
        return definitionGR.display.getDisplayValue();
    } else {
        return value; //number fields don't have a display value
    }
}