https://www.pbainbridge.co.uk/2022/08/bulk-export-qualtrics-survey-results-20.html

Bulk export Qualtrics survey results 2.0.gs

/*
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.");
  
}