Bulk export Qualtrics survey results

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.

 

Update 18/08/2022

Unfortunately recent changes to the Qualtrics API means this tool no longer works. There is now an updated version of the tool.

Screenshot of instructional sheet requesting Qualtrics information

1) getSpreadsheet.gs
This Function gets data from the Google Sheet that is then used throughout the rest of the script:

// get current spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();

// get Config sheet
var configSheet = ss.getSheetByName('Config');

// 1) Qualtrics API Token
var apiToken = welcomeSheet.getRange(8, 2).getValue().trim();

2) loopSurveyIDs.gs
This Function loops through each survey ID provided and calls the relevant other Functions so that each survey name/data can be acquired:

// extract values from name:value pairs array
var ss = allData['ss'];
var apiToken = allData['apiToken'];

// create new Sheet using Survey Name
var newSheet = ss.insertSheet(surveyName);

// insert survey data into sheet
newSheet.getRange(1, 2, depth, width).setValues(surveyResultsData);

3) getSurveyName.gs
This Function gets the name of the survey from the Qualtrics API:

// 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,
    'muteHttpExceptions' : true
};

// 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'];

4) getSurveyData.gs
This Function makes a call for the survey results data from the Qualtrics API:

// file format for output data
var fileFormat = 'csv';

// Qualtrics API Url
var baseUrl = 'https://' + dataCenterId + '.qualtrics.com/API/v3/responseexports/';

// compile JSON
var requestPayload = JSON.stringify( {format:fileFormat, useLabels:labels, surveyId:surveyId} );

// compile the above options
var options = {
    'method' : 'post',
    'payload' : requestPayload,
    'headers' : headers,
    'muteHttpExceptions' : true
};

5) extractSurveyData.gs
This Function extracts the actual results data from the downloaded file and parses it for adding to the Google Sheet:

// get 'results' from data
var dataResults = resultObj['result'];

// create download Url
var requestDownloadUrl = requestCheckUrl + '/file';

// get the file
var blob = UrlFetchApp.fetch(requestDownloadUrl, options).getBlob();

// unzip the file
var unzippedBlob = Utilities.unzip(blob)[0];

// parse data as an array for entering into spreadsheet
var csvArray = Utilities.parseCsv(csvText);

logEvent.gs
This Function allows for log information to be ouput into the Google Sheet to support troubleshooting any problems.

onOpen.gs
This Function creates a menu item for running the Reset Function for remove any entered data into the Google Sheet:

SpreadsheetApp.getUi()
  .createMenu('Admin')
  .addItem('Reset spreadsheet', 'reset')
  .addToUi();

popupBox.gs
This Function creates a popup box if the user has missed filling in some of the requested information:

// 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);

reset.gs
This Function is used to reset/clear the spreadsheet of entered information to start again:

// clear Qualtrics API Token box
var apiToken = welcomeSheet.getRange(8, 2).clearContent();

statusUpdate.gs
This Function updates the message in the status cell on the Welcome sheet to help inform the user of the progress.

Bulk export Qualtrics survey results download.