Qualtrics + Google Spreadsheet integration

Question

I'm looking for a way to use a Qualtrics survey as a data source for a Google spreadsheet. My client wants a google spreadsheet that automatically updates based on the latest survey results. I've searched the web and looked at Qualtrics's documentation, but nothing has come up.

Qualtrics does have a REST API for retrieving survey results, and Google has an API for piping data into a spreadsheet, so in theory it's already possible.

Answer 1

This is certainly possible, and I implemented this last year. Just set up a timer to run this. You can also use Google sheets query function, but it is not as reliable and can create a lapse of data if it doesn't run properly, whereas this simply pastes it into the spreadsheet on run.

/*

function ImportfromCSV() {

  var rawData = getcsv();   
  var csvData = CSVToArray(rawData, ",");
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

  for (var i = 0; i < csvData.length; i++) {
    sheet.getRange(i+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
  }
}

function getcsv() {

  var url = 'https://survey.qualtrics.com//WRAPI/ControlPanel/api.php?API_SELECT=ControlPanel&Version=2.4&Request=getLegacyResponseData&User=ENTER YOUR API KEY INFO HERE'; // Change this to the URL of your file
  var response = UrlFetchApp.fetch(url);
  var data = response.getContentText();
  return data;
}


function CSVToArray( strData, strDelimiter ){
  Logger.log(strData);
  strDelimiter = (strDelimiter || ",");
  var objPattern = new RegExp(
    (
      "(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +
      "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +
      "([^\"\\" + strDelimiter + "\\r\\n]*))"
    ),
    "gi"
  );
  var arrData = [[]];
  var arrMatches = null;
  while (arrMatches = objPattern.exec( strData )){
    var strMatchedDelimiter = arrMatches[ 1 ];
    if (
      strMatchedDelimiter.length &&
      (strMatchedDelimiter != strDelimiter)
    ){
      arrData.push( [] );
    }
    if (arrMatches[ 2 ]){
      var strMatchedValue = arrMatches[ 2 ].replace(
        new RegExp( "\"\"", "g" ),
        "\""
      );
    } else {
      var strMatchedValue = arrMatches[ 3 ];
    }
    arrData[ arrData.length - 1 ].push( strMatchedValue );
  }
  return( arrData );
}

*/