SurveyJS & Google Sheets: No data possibly due to novel question types

[yogat3ch]

Hi Rafał, Thank you for publishing this article! I was trying to come up with a means of implementing SurveyJS just like this over the past few days, and here you post an article detailing how to do it! Quite a synchronicity! I created and deployed a survey on my website by using the "safe" jQuery syntax such that it works on Wordpress. I've tested the alert method and it outputs the responses to the rating and comment type questions like so:

I've set up a sheet and replaced the necessary values in the script below. I've published as a web app with "Anyone, even anonymous" publishing permissions.

However, I'm not getting any data populating the sheet.

I think it might have to do with the Rating & Comment question types not being supported yet:

//TODO: Support other types of questions

and the headers not matching datetime or raw_data, so the conditional opts for the final else statement only suited for multiple choice questions - so the sheet reports back with an error:

The JSON payload looks like this:

I'm going to see if I can come up with some javascript such that the sheet will accept the data. I'll share them when I complete them.

The survey has nine main questions named with a single word, with a -detail optional question using the same name.

If you have any simple solutions offhand to handle these questions types that would be great!

Update 2020-12-20T15:53:59: When I look at the Executions in the Google Apps Script dashboard there doesn't seem to be any record that it's actually running the doPost function so I wonder if the POST requests are actually being received?

Update 2020-12-20T16:06:37: I manually sent a CURL POST request with a very simple JSON body and I'm still getting error: "exception"

Update 2020-12-20T16:10:55: I used the "Test Web App" link when publishing some changes to the GAS. Turns out it was erroring because there wasn't a doGet function. Apparently that is required, even if the app will only be receiving POST requests. After adding doGet I'm finally getting data in the sheet 🎉

I've updated the script below with the current implementation with some lines for debugging/logging:

var SHEET_NAME = "raw"; //  Enter sheet name where results will be collected

var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service

function doPost(e){
    console.log(JSON.parse(e.postData.contents));
    return handleResponse(e);
}

function test() {
  var doc = SpreadsheetApp.openById("Sheet_ID");
  var sheet = doc.getSheetByName(SHEET_NAME);
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  console.log(headers)
}

function handleResponse(e) {
    // The LockService allows you to have only one invocation of the script or portions thereof run at a time.
    // More about: http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
    var lock = LockService.getPublicLock(); //Public lock locks for any invocation of script.
    lock.waitLock(30000);

    try {
        // Alternatively, you can hard code spreadsheet here
        // eg. SpreadsheetApp.openById("1AcsuboS3xxk0kj02ACcE_j4ASb8GrxyZscTU5IM-wqc")
        var doc = SpreadsheetApp.openById("1LIwic5pkgyLX0sWlvTBIKCyfr3WRBHJTjifclOBteOA");
        var sheet = doc.getSheetByName(SHEET_NAME);

        var data = JSON.parse(e.postData.contents);
        console.log(data);
        var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
        var nextRow = sheet.getLastRow()+1; // get next row
        var row = [];

        for (i in headers){
            if (headers[i] == "datetime"){
                row.push(new Date());
            } 

            else if (headers[i] == "raw_data"){
                row.push(JSON.stringify(data));
            }

            else {

              row.push(data[headers[i]]);

                //TODO: Support other types of questions
            }
        }

        sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
        // return json success results
        return ContentService
            .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
            .setMimeType(ContentService.MimeType.JSON);
    } catch (e){
        // if error return this
        return ContentService
            .createTextOutput(JSON.stringify({"result":"error", "error": e}))
            .setMimeType(ContentService.MimeType.JSON);
    } finally {
        //Release public lock from line 19
        lock.releaseLock();
    }
}

function setup() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    SCRIPT_PROP.setProperty("key", doc.getId());
}

Here's the Javascript as it appears on the site:

<script src="https://unpkg.com/survey-jquery@1.8.21/survey.jquery.min.js"></script>

<script type="text/javascript">
jQuery(document).ready(function() {
Survey.StylesManager.applyTheme("bootstrap");

var surveyJSON = {"completedHtml":"<h3> Thank you for taking the time to provide feedback for your consulting experience! Your answers help Stephen to better consult on projects like yours!</h3>","pages":[{"name":"pg-availability","elements":[{"type":"rating","name":"timeliness","title":"Timeliness","description":"On a scale of 1-10, was Stephen prompt to meetings and timely in completing tasks by pre-arranged deadlines?","rateMax":10,"minRateDescription":"Mostly Late/Untimely","maxRateDescription":"Mostly Prompt/Timely"},{"type":"comment","name":"timeliness-detail","visibleIf":"{timeliness} <= 8","title":"Timeliness - in depth:","description":"Can you provide additional details about your experience ? (specific examples are helpful) \nDo you have suggestions for how Stephen might better meet your expectations?","hideNumber":true,"enableIf":"{timeliness} <= 8"},{"type":"rating","name":"responsiveness","title":"Responsivenes","description":"On a scale of 1-10, was Stephen prompt to requests in a reasonable amount of time?","rateMax":10,"minRateDescription":"Mostly Unresponsive","maxRateDescription":"Always Responsive"},{"type":"comment","name":"responsiveness-detail","visibleIf":"{responsiveness} <= 8","title":"Responsiveness - in depth:","description":"Can you provide additional details about your experience ? (specific examples are helpful) \nDo you have suggestions for how Stephen might better meet your expectations?","hideNumber":true,"enableIf":"{responsiveness} <= 8"}],"title":"Availability"},{"name":"pg-ability","elements":[{"type":"rating","name":"competence","title":"Competence","description":"On a scale of 1-10, were Stephen's s competencies suited to the project's demands?","rateMax":10,"minRateDescription":"Mostly incompetent","maxRateDescription":"Always competent"},{"type":"comment","name":"competence-detail","visibleIf":"{competence} <= 8","title":"Competencies - in depth:","description":"Can you provide additional details about your experience ? (specific examples are helpful) Do you have suggestions for what competencies could be expanded upon?","hideNumber":true,"enableIf":"{competence} <= 8"},{"type":"rating","name":"knowledgeability","title":"Knowledgeability","description":"On a scale of 1-10, was Stephen knowledgeable about the tasks involved in completing the project?","rateMax":10,"minRateDescription":"Lacking knowledge","maxRateDescription":"Always knowledgeable"},{"type":"comment","name":"knowledgeability-detail","visibleIf":"{knowledgeability} <= 8","title":"Knowledgeability - in depth:","description":"Can you provide additional details about your experience ? (specific examples are helpful) Do you have suggestions for areas in which Stephen can focus his learning?","hideNumber":true,"enableIf":"{knowledgeability} <= 8"}],"title":"Ability"},{"name":"pg-accountability","elements":[{"type":"rating","name":"planning","title":"Planning","description":"On a scale of 1-10, was Stephen able to clearly define a timeline to complete the project?","rateMax":10,"minRateDescription":"Mostly Haphazard","maxRateDescription":"Well-planned"},{"type":"comment","name":"planning-detail","visibleIf":"{planning} <= 8","title":"Planning - in depth:","description":"Can you provide additional details about your experience ? (specific examples are helpful) Do you have suggestions for where you would have liked to have more clarity or better strategy in the plan?","hideNumber":true,"enableIf":"{planning} <= 8"},{"type":"rating","name":"follow_through","title":"Follow-through","description":"On a scale of 1-10, did Stephen complete the strategic plan to a satisfactory degree?","rateMax":10,"minRateDescription":"Largely Incomplete","maxRateDescription":"Mostly Thorough"},{"type":"comment","name":"follow_through-detail","visibleIf":"{follow_through} <= 8","title":"Follow-through - in depth:","description":"Can you provide additional details about your experience ? (specific examples are helpful) Can you identify areas where you would have liked more follow-through?","hideNumber":true,"enableIf":"{follow_through} <= 8"}],"title":"Accountability"},{"name":"pg-affability","elements":[{"type":"rating","name":"amicability","title":"Amicability","description":"On a scale of 1-10, did Stephen communicate in a friendly and amicable manner?","rateMax":10,"minRateDescription":"Often difficult","maxRateDescription":"Always Amicable"},{"type":"comment","name":"amicability-detail","visibleIf":"{amicability} <= 8","title":"Amicability - in depth:","description":"Can you provide additional details about your experience ? (specific examples are helpful) Were there times when you felt communication could have been better? How would you improve it?","hideNumber":true,"enableIf":"{amicability} <= 8"},{"type":"rating","name":"clarity","title":"Clarity","description":"On a scale of 1-10, did Stephen communicate clearly and truthfully?","rateMax":10,"minRateDescription":"Mostly unclear / untruthful","maxRateDescription":"Always clear & truthful"},{"type":"comment","name":"clarity-detail","visibleIf":"{clarity} <= 8","title":"Clarity - in depth:","description":"Can you provide additional details about your experience ? (specific examples are helpful) Can you provide examples of where and how clearer and more truthful communication could have been beneficial?","hideNumber":true,"enableIf":"{clarity} <= 8"}],"title":"Affability"},{"name":"pg-affordability","elements":[{"type":"rating","name":"affordability","title":"Affordability","description":"On a scale of 1-10, did the services Stephen rendered seem fair for the price?","rateMax":10,"minRateDescription":"Mostly unfair","maxRateDescription":"Always fair"},{"type":"comment","name":"affordability-detail","visibleIf":"{affordability} <= 8","title":"Affordability - in depth:","description":"Can you provide additional details about your experience ? (specific examples are helpful) Which services did you feel could have been improved to make them worth the cost or alternatively, reduced in cost to make the service seem fairly priced?","hideNumber":true,"enableIf":"{affordability} <= 8"}],"title":"Affordability"}],"showProgressBar":"bottom","progressBarType":"questions"}

function sendDataToServer(survey, options) {
    options.showDataSaving();
    jQuery.ajax({
        url: '[REPLACE WITH YOUR URL]',
        type: 'post',
        data: JSON.stringify(survey.data),
        headers: {
            "Content-Type": "text/plain"
        },
        processData: false,
        complete: function(res, status) {
            if (status == 'success') {
                options.showDataSavingSuccess();
            }else {
                options.showDataSavingError();
            }
        },
    });
}

var survey = new Survey.Model(surveyJSON);
jQuery("#surveyContainer").Survey({
    model: survey,
    onComplete: sendDataToServer
});
})

</script>

[yogat3ch]

Raw data is finally coming in!

Update 2020-12-20T16:10:55: I used the "Test Web App" link when publishing some changes to the GAS. Turns out it was erroring because there wasn't a doGet function. Apparently that is required, even if the app will only be receiving POST requests. After adding doGet I'm finally getting data in the sheet 🎉

[yogat3ch]

Alright, seems that the missing doGet method was the culprit all along. For anyone who reads this, every script must have a doGet method to function.

Here's the final code (with script ID omitted and also redacted from above):

var SHEET_NAME = "raw"; //  Enter sheet name where results will be collected

var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service

function doGet(e) {
  return 0;
}

function doPost(e){
    console.log(e);
    return handleResponse(e);
}

function handleResponse(e) {
    // The LockService allows you to have only one invocation of the script or portions thereof run at a time.
    // More about: http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
    var lock = LockService.getPublicLock(); //Public lock locks for any invocation of script.
    lock.waitLock(30000);

    try {
        // Alternatively, you can hard code spreadsheet here
        // eg. SpreadsheetApp.openById("REPLACE WITH YOUR ID")
        var doc = SpreadsheetApp.openById("1LIwic5pkgyLX0sWlvTBIKCyfr3WRBHJTjifclOBteOA");
        var sheet = doc.getSheetByName(SHEET_NAME);

        var data = JSON.parse(e.postData.contents);
        Logger.log(data);
        var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
        var nextRow = sheet.getLastRow()+1; // get next row
        var row = [];

        for (i in headers){
            if (headers[i] == "datetime"){
                row.push(new Date());
            } 

            else if (headers[i] == "raw_data"){
                row.push(JSON.stringify(data));
            }

            else {

              //To support multiple choice question (checkboxes)
                if(Array.isArray(data[headers[i]])) {
                    row.push(data[headers[i]].join('|'));
                }else {
                    row.push(data[headers[i]]);
                }
                //TODO: Support other types of questions

            }
        }

        sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
        // return json success results
        return ContentService
            .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
            .setMimeType(ContentService.MimeType.JSON);
    } catch (e){
        // if error return this
        return ContentService
            .createTextOutput(JSON.stringify({"result":"error", "error": e}))
            .setMimeType(ContentService.MimeType.JSON);
    } finally {
        //Release public lock from line 19
        lock.releaseLock();
    }
}

function matchHeader (head, data) {

}

function setup() {
    var doc = SpreadsheetApp.getActiveSpreadsheet();
    SCRIPT_PROP.setProperty("key", doc.getId());
}

[yogat3ch]

Hi @fischerbach , So this script broke for me a short while after getting it working - and I think it had to do with the ES6 upgrade on Google Apps Scripts.

After a good long period of trying to figure out what the issue is I realized that the i in the for loop isn't formally declared as a variable. Changing this to var i in headers allowed me to run it with test data using the debugger. However, I'm unable to get the web app to respond in POST requests from the site or from using curl on my local machine.

I added some new questions to the survey and updated the sheet headers accordingly but that's the only change made since it was last functioning.

Any ideas?