How to integrate Google Sheet?

Here is a quick guide on how to integrate Google Sheet to BlockSurvey so that you can receive a copy of the responses. 

Note: In this integration, the data flow is without encryption. Don't send any sensitive data outside BlockSurvey. 

1. Create a New Google Sheet

2. Go Tools -> Script Editor

3. An untitled project is opened in a new tab as shown below. 

4. Replace the code.gs content with the following code. 

function doPost(e) {
    if (e == undefined) {
        console.log("no data");
        return HtmlService.createHtmlOutput("need data");
    }
    //Parse the JSON data
    var payload = JSON.parse(e.postData.contents);
    var data = payload.survey_response;
    // Insert a row to sheet 
    var sheet = SpreadsheetApp.getActiveSheet();
    sheet.appendRow([data.answers, data.answers.source]);
    return HtmlService.createHtmlOutput("post request received");
}

5. Save it and select 'New Deployment'

6. Select "Web APP" for Deployment Type

7. Ensure you select 'Anyone' from ' Who has access' and click on Deploy. 

8. Authorize using your Gmail account for access. 

9. Copy the script URL and use it in Blocksurvey 'webhook' integration. 

10. Sample test integration from BlockSurvey. Send 'test request' to see if the data comes to Google Sheet. Open your form/survey Go to -> Integrate -> Webhook


11. Do a live test. If you see responses coming to Google Sheet, you are all set. 

Note: 

To add data column by column, you can rename the question reference from the builder for easy script writing. 

Sample code below. Highlighted the changes. 

functiondoPost(e) {
    if (e == undefined) {
        console.log("no data");
        returnHtmlService.createHtmlOutput("need data");
    }

    //Parse the JSON data
    varpayload = JSON.parse(e.postData.contents);
    vardata = payload.survey_response;

    // Insert a row to sheet 
    varsheet = SpreadsheetApp.getActiveSheet();
    //sheet.appendRow([data.answers, data.answers.source]);
      varlastRow = Math.max(sheet.getLastRow(),1);
      sheet.insertRowAfter(lastRow);
      vartimestamp = newDate();
      sheet.getRange(lastRow + 1, 1).setValue(data.answers.name);
      sheet.getRange(lastRow + 1, 2).setValue(data.answers.email);
// add the questions here like above, where the name and email question reference changed in the form builder. 
      SpreadsheetApp.flush();
    returnHtmlService.createHtmlOutput("post request received");
}

Still need help? Contact Us Contact Us