Bulk export Qualtrics survey results 2.0

The following tool is designed to connect to your Qualtrics account and export the results data for multiple surveys that you specify, in one go. The results will be appended to the Google Sheet using the survey-name to identify each sheet of data. The data itself is exported as CSV.

This post is an updated version of the previous tool that has become redundant due to changes in the Qualtrics API.

Screenshot of instructional sheet requesting Qualtrics information

Download

Bulk export Qualtrics survey results 2.0 download (please use 'File' > 'Make a copy' for your own version).

 

	/*
	Get values from the Spreadsheet to pass to other Functions.
	*/
	function getSpreadsheetData() {
	logEvent('Starting script.');
	// update status cell
	statusUpdate('Process is running', '#ff9900');
	// get current spreadsheet
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	// get Config sheet
	var configSheet = ss.getSheetByName('Config');
	// get Data Center ID
	var dataCenterId = configSheet.getRange(3, 2).getValue();
	Logger.log('Data Center ID is: ' + dataCenterId);
	// get Sleep time and convert to seconds
	var sleepTime = configSheet.getRange(5, 2).getValue();
	var sleepTimeSecs = sleepTime * 1000;
	Logger.log('Sleep time in secs is: ' + sleepTimeSecs);
	// get Welcome sheet
	var welcomeSheet = ss.getSheetByName('Welcome');
	// 1) Qualtrics API Token
	var apiToken = welcomeSheet.getRange(8, 2).getValue().trim();
	Logger.log('apiToken is: ' + apiToken);
	// 2) labels instead of recode value for answer choice Q's
	var labels = welcomeSheet.getRange(13, 2).getValue().trim();
	if (labels == 'Yes') {
	labels = true;
	}
	else if (labels == 'No') {
	labels = false;
	}
	else {
	labels = '';
	}
	Logger.log('labels is: ' + labels);
	// 3) 'SurveyIDs'
	var surveyIds = welcomeSheet.getRange(18, 2).getValue().trim();
	// run Function to check no blank values
	var blanks = popupBox(apiToken, labels, surveyIds);
	if (blanks == false) {
	// get array from comma-separated 'SurveyIDs'
	var surveyIdsArray = surveyIds.split(', ');
	Logger.log(surveyIdsArray);
	// create name:value pairs array to pass data to next Function
	var allData = {ss:ss, apiToken:apiToken, labels:labels, surveyIdsArray:surveyIdsArray,
	dataCenterId:dataCenterId, sleepTimeSecs:sleepTimeSecs};
	// run Function to loop through each Survey
	var loopSuccess = loopSurveys(allData);
	// check process ran correctly to provide relevant status updates
	if (loopSuccess) {
	// create Toast popup to inform user
	ss.toast("Process has now finished.", "Complete");
	// update status cell
	statusUpdate('Successfully completed', '#00ff00');
	logEvent('Script completed successfully.');
	}
	else {
	// create Toast popup to inform user
	ss.toast("Problem with survey - check your details otherwise contact IT Support.", "Error");
	// process did not complete successfully
	logEvent('Script terminated with errors.');
	// update status cell
	statusUpdate('Problem with survey - check your details otherwise contact IT Support.', '#ff0000');
	}
	}
	else {
	// there are blank values in the Sheet
	logEvent('Terminated due to blank values in Sheet.');
	// update status cell
	statusUpdate('Missing information - check boxes', '#ff0000');
	}
	}
	/*
	Get Survey Name.
	*/
	function getSurveyName(apiToken, surveyId, dataCenterId) {
	// Qualtrics API Url
	var baseUrl = 'https://' + dataCenterId + '.qualtrics.com/API/v3/surveys/' + surveyId;
	// authentication headers
	var headers = {
	"x-api-token": apiToken
	};
	// compile the above options
	var options = {
	'method' : 'get',
	'headers' : headers
	};
	// get response from API
	try {
	var request = UrlFetchApp.fetch(baseUrl, options);
	// extract API response data
	var resultText = request.getContentText();
	var responseCode = request.getResponseCode();
	logEvent("Response code for 'getSurveyName' is: " + responseCode);
	var resultObj = JSON.parse(resultText);
	// extract Survey Name from data
	var surveyName = resultObj['result']['name'];
	Logger.log('surveyName is: ' + surveyName);
	// return Survey Name to Parent Function
	return surveyName;
	}
	catch(e) {
	// return Flag as false if problem so script terminates cleanly
	logEvent("Problem with 'getSurveyName' Fetch: " + e);
	var surveyName = false;
	return surveyName;
	}
	}
	/*
	Get data for Survey.
	*/
	function getSurveyData(ss, attempt, apiToken, surveyId, labels, dataCenterId, sleepTimeSecs) {
	// file format for output data
	var fileFormat = 'csv';
	// Qualtrics API Url
	var baseUrl = 'https://' + dataCenterId + '.qualtrics.com/API/v3/surveys/' + surveyId + '/export-responses/';
	Logger.log('baseUrl is: ' + baseUrl);
	// authentication headers
	var headers = {
	"content-type": "application/json",
	"x-api-token": apiToken,
	};
	// compile JSON
	var requestPayload = JSON.stringify({ format: fileFormat, useLabels: labels });
	// compile the above options
	var options = {
	'method': 'post',
	'payload': requestPayload,
	'headers': headers
	};
	// get response from API
	try {
	var request = UrlFetchApp.fetch(baseUrl, options);
	// Add 10sec sleep here to allow time for Qualtrics to prepare export file
	Utilities.sleep(sleepTimeSecs);
	// extract API response data
	var resultText = request.getContentText();
	var responseCode = request.getResponseCode();
	logEvent("Response code for 'getSurveyData' is: " + responseCode);
	var resultObj = JSON.parse(resultText);
	// run Function to extract survey data
	var surveyResultsData = extractSurveyData(headers, baseUrl, resultObj);
	}
	catch (e) {
	// output error message into Log sheet
	logEvent("Problem with 'getSurveyData' Fetch: " + e);
	// extract message part of error
	var error = e.message;
	// look for text string match
	var errorMessage = error.match(/Export file not found/gm);
	// if error message matches and no re-attempts have been made
	if ((errorMessage) && (attempt == false)) {
	Logger.log('errorMessage is True and attempt is False');
	// create Toast popup to inform user
	ss.toast("Attempt 2", "Export file not found, retrying in 60 seconds.");
	logEvent("Re-running 'getSurveyData' attempt 2 in 60 seconds");
	// pause for 60 seconds to allow Qualtrics time to prep export file
	Utilities.sleep(60000);
	// set attempt value so only tries re-running once
	var attempt = true;
	// re-run this Function
	var surveyResultsData = getSurveyData(ss, attempt, apiToken,
	surveyId, labels, dataCenterId, sleepTimeSecs);
	}
	else {
	Logger.log('errorMessage is False, attempt is true');
	// reset attempt value so next Survey continues as normal
	var attempt = false;
	// return Flag as false if problem so script terminates cleanly
	var surveyResultsData = false;
	return surveyResultsData;
	}
	}
	// return Survey results data to Parent Function after successful 'try'
	return surveyResultsData;
	}
	/*
	Extract results data for Survey.
	*/
	function extractSurveyData(headers, baseUrl, resultObj) {
	// get 'results' from data
	var dataResults = resultObj['result'];
	// get 'progress id' from 'results'
	var progressId = dataResults['progressId']
	// create Url to check status of download request
	var requestCheckUrl = baseUrl + progressId;
	Logger.log('requestCheckUrl is: ' + requestCheckUrl);
	// create the options for authentication
	var options = {
	'method': 'get',
	'headers': headers
	};
	// get response from API
	var requestCheckResponse = UrlFetchApp.fetch(requestCheckUrl, options);
	// extract API response data
	var requestCheckResponseText = requestCheckResponse.getContentText();
	var requestCheckResponseObj = JSON.parse(requestCheckResponseText);
	// get percent complete & fileId from 'results'
	var responseResults = requestCheckResponseObj['result'];
	var percentComplete = responseResults['percentComplete'];
	var fileId = responseResults['fileId'];
	Logger.log('percentComplete is: ' + percentComplete);
	Logger.log('fileId is: ' + fileId);
	// create download Url
	var requestDownloadUrl = baseUrl + fileId + '/file';
	// get the file
	var blob = UrlFetchApp.fetch(requestDownloadUrl, options).getBlob();
	// unzip the file
	var unzippedBlob = Utilities.unzip(blob)[0];
	// get raw data from unzipped file
	var csvText = unzippedBlob.getDataAsString();
	//Logger.log(csvText);
	// parse data as an array for entering into spreadsheet
	var csvArray = Utilities.parseCsv(csvText);
	// return Survey results data to Parent Function
	return csvArray;
	}
	/*
	Loop through each 'SurveyID'.
	*/
	function loopSurveys(allData) {
	// extract values from name:value pairs array
	var ss = allData['ss'];
	var apiToken = allData['apiToken'];
	var labels = allData['labels'];
	var surveyIdsArray = allData['surveyIdsArray'];
	var dataCenterId = allData['dataCenterId'];
	var sleepTimeSecs = allData['sleepTimeSecs'];
	// get length of array for loop
	var surveyIdsArrayLength = surveyIdsArray.length;
	// loop through each 'SurveyID' in array **************************************
	for (var i = 0; i < surveyIdsArrayLength; i++) {
	// extract individual 'SurveyID'
	var surveyId = surveyIdsArray[i];
	Logger.log('surveyId is: ' + surveyId);
	// run Function to get Survey Name
	var surveyName = getSurveyName(apiToken, surveyId, dataCenterId);
	// create Toast popup to inform user
	ss.toast(surveyName, "Starting Survey:");
	// check Function ran successfully before proceeding
	if (surveyName) {
	logEvent('Starting to export data for Survey: ' + surveyName);
	// set attempt value to false as this is first try
	var attempt = false;
	// run Function to get Survey results data
	var surveyResultsData = getSurveyData(ss, attempt, apiToken,
	surveyId, labels, dataCenterId, sleepTimeSecs);
	// check Function ran successfully before proceeding
	if (surveyResultsData) {
	// workout dimensions of Survey results data array
	var width = surveyResultsData[0].length;
	var depth = surveyResultsData.length;
	// create new Sheet using Survey Name
	var newSheet = ss.insertSheet(surveyName);
	// insert survey data into sheet
	newSheet.getRange(1, 2, depth, width).setValues(surveyResultsData);
	// delete empty first column
	newSheet.deleteColumns(1, 1);
	// log success
	logEvent('Completed export data for Survey: ' + surveyName);
	}
	else {
	// break out of loop to stop script proceeding
	return false;
	}
	}
	else {
	// break out of loop to stop script proceeding
	return false;
	}
	}
	// loop through each 'SurveyID' in array **************************************
	// return true as script completed successfully
	return true;
	}
	/*
	Output log information to spreadsheet.
	*/
	function logEvent(action) {
	// get the user running the script
	var theUser = Session.getActiveUser().getEmail();
	// get the relevant spreadsheet to output log details
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	var logSheet = ss.getSheetByName('Log');
	// create and format a timestamp
	var dateTime = new Date();
	var timeZone = ss.getSpreadsheetTimeZone();
	var niceDateTime = Utilities.formatDate(dateTime, timeZone, "dd/MM/yy @ HH:mm:ss");
	// create array of data for pasting into log sheet
	var logData = [niceDateTime, theUser, action];
	// append details into next row of log sheet
	logSheet.appendRow(logData);
	}
	/*
	create popup box for user confirmation
	*/
	function popupBox(apiToken, labels, surveyIds) {
	// check relevant boxes are not blank before rest of script can proceed
	if (apiToken == '' || labels == '' || surveyIds == '') {
	// get Spreadsheet UI
	var ui = SpreadsheetApp.getUi();
	// create Alert popup box
	var result = ui.alert(
	'Missing Information',
	'Please check you have completed all relevant boxes on the Welcome page.',
	ui.ButtonSet.OK);
	// set true Flag as values are missing
	var blanks = true;
	}
	else {
	// set false Flag as no values are missing
	var blanks = false;
	}
	// return Flag to Parent Function
	return blanks;
	}
	/*
	Update the message in the Status cell on the Welcome sheet.
	*/
	function statusUpdate(message, colour) {
	// get current spreadsheet
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	// get Welcome sheet
	var welcomeSheet = ss.getSheetByName('Welcome');
	// get status cell
	var statusCell = welcomeSheet.getRange(22, 3);
	// update message in status cell
	statusCell.setValue(message);
	// update colour of status cell
	statusCell.setFontColor(colour);
	}
	/*
	Reset spreadsheet to start again.
	*/
	function reset() {
	logEvent("Running 'Reset' Function");
	// get current spreadsheet
	var ss = SpreadsheetApp.getActiveSpreadsheet();
	// get Welcome sheet
	var welcomeSheet = ss.getSheetByName('Welcome');
	// clear Qualtrics API Token box
	var apiToken = welcomeSheet.getRange(8, 2).clearContent();
	// clear labels box
	var labels = welcomeSheet.getRange(13, 2).clearContent();
	// clear 'SurveyIDs' box
	var surveyIds = welcomeSheet.getRange(18, 2).clearContent();
	// update status cell
	statusUpdate('Ready', '#00ff00');
	logEvent("Completed 'Reset' Function.");
	}

view raw Bulk export Qualtrics survey results 2.0.gs hosted with ❤ by GitHub