How to integrate Google Sheet

You can integrate Google Sheets with BlockSurvey to receive a copy of the responses.

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

Here's a quick guide on how to do it.

  1. Create a New Google Sheet by signing in with your Gmail account.
  2. Go to Extension -> Apps Scripts, by default it create an Untitled project as shown below.

  1. Now, let's replace the code.gs content with the provided code to add data column by column in the Google sheet. Here what we are doing is to map the question answers to the column in the sheet.

Note: For ease of mapping, you can rename the question reference first from the 'Design' in the builder so that script is readable. 

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 the sheet
  var sheet = SpreadsheetApp.getActiveSheet();

//sheet.appendRow([data.answers, data.answers.source]);
  var lastRow = Math.max(sheet.getLastRow(),1);
  sheet.insertRowAfter(lastRow);
  var timestamp = new Date();

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.
// Example:
// sheet.getRange(lastRow + 1, 3).setValue(data.answers.question3);
// sheet.getRange(lastRow + 1, 4).setValue(data.answers.question4);

SpreadsheetApp.flush();

return HtmlService.createHtmlOutput("post request received");
}

Once done, replace name highlighted to your reference name in the order of your questions want to add it to the sheet. Feel free to add more lines by incrementing the column number also along with question reference. Sample instructions below. 

sheet.getRange(lastRow + 1, 1).setValue(data.answers.name);

// sheet.getRange(lastRow + 1, [column_number]).setValue(data.answers.[question_reference]);
  1. Save it, Click Deploy, and select New Deployment.
  2. Select Web APP for the Deployment Type.

  1. Make sure to select Anyone from Who has access and click on Deploy.

  1. Authorize using your Gmail account for access. 

Note: You may not see this error if you have a Google Workspace account.

  1. Copy the Web app URL and use it in the BlockSurvey Webhook integration. 

  1. Open your form/survey in BlockSurvey and go to Integrate -> Webhook, and click on Add New Webhook.
  2. Enter a Name and paste the Web app URL, Save and Send Test Request to see if the data comes through to Google Sheets.

  1. Once you've done a live test and see that the responses are coming through to Google Sheets, you're all set!

  1. If you need to auto-fit the columns, just select all the columns and double-click on the last column edge to autofit.

Great job! The integration with Google Sheets in BlockSurvey is all set up now.

Note: If you are experiencing any errors while using the script in the code.gs file for BlockSurvey webhook integration, here are some troubleshooting steps you can follow:

  1. Check the question reference name: Ensure that you have added the correct question reference name in the code.gs file. The question reference name should match the one specified in the BlockSurvey builder. Double-check the spelling and capitalization to make sure they are identical.
  2. Review the code: Carefully review the code.gs file to ensure there are no syntax errors or missing/wrong function names. Make sure the code is correctly implemented, and there are no typos or missing characters.
  3. Set the correct permissions: In the deployment settings of the Google Apps Script, make sure you have selected "Web app" and the option "Anyone" under the "Who has access " section. It means that anyone who has the URL can access the web app, including users who are not signed in to a Google account. This option is commonly used when you want to create a public-facing web application or provide open access to the functionality of the script.
  4. Verify the webhook URL: Check the URL entered in the webhook configuration in BlockSurvey. Make sure the URL is updated and matches the URL of the deployed Google Apps Script. If you have made changes to the code.gs file, you need to save it and redeploy the script to obtain a new URL.

By following these steps, you should be able to identify and resolve the most common errors that may occur during the integration of the Google Sheets in BlockSurvey.

If you are still running into issues, contact us at [email protected] at any time.

Still need help? Contact Us Contact Us