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 spreadsheet2) loopSurveyIDs.gs
var ss = SpreadsheetApp.getActiveSpreadsheet();
// get Config sheet
var configSheet = ss.getSheetByName('Config');
// 1) Qualtrics API Token
var apiToken = welcomeSheet.getRange(8, 2).getValue().trim();
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 array3) getSurveyName.gs
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);
This Function gets the name of the survey from the Qualtrics API:
// Qualtrics API Url4) getSurveyData.gs
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'];
This Function makes a call for the survey results data from the Qualtrics API:
// file format for output data5) extractSurveyData.gs
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
};
This Function extracts the actual results data from the downloaded file and parses it for adding to the Google Sheet:
// get 'results' from datalogEvent.gs
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);
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()popupBox.gs
.createMenu('Admin')
.addItem('Reset spreadsheet', 'reset')
.addToUi();
This Function creates a popup box if the user has missed filling in some of the requested information:
// create Alert popup boxreset.gs
var result = ui.alert(
'Missing Information',
'Please check you have completed all relevant boxes on the Welcome page.',
ui.ButtonSet.OK);
This Function is used to reset/clear the spreadsheet of entered information to start again:
// clear Qualtrics API Token boxstatusUpdate.gs
var apiToken = welcomeSheet.getRange(8, 2).clearContent();
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.
No comments:
Post a comment