Send survey responses to Google Sheets

Configure a Google Apps Script and Widget Studio survey settings to automatically send survey responses to a Google Sheet via HTTP POST.

If you want to collect responses from your widget in Kameleoon and export them to CSV, refer to the Survey data export article.

In this article, you’ll learn:

• How to configure your Google Sheet to receive survey data.
• How to set up the Survey Settings tab in the Widget Studio to send responses to Google Sheets.

Configure your Google sheet

1. Go to Google Sheets and create a new spreadsheet.
2. Name the tab Sheet 1 (which is the default).
3. In the first row, define the columns as follows:

Details:

• Column A > Enter Date. Kameleoon automatically populates this cell with the date and time of each submission.
• From Column B onward > Enter the name of each survey or form element added to your widget.

You can find an element’s name in the elements tree in the Content tab of the Design section.

When you select an element (for example, a scale), its name appears at the top of the editing sidebar.

Consider this widget as an example:

This widget has two survey elements, so enter the following in the first row of the Google Sheet:

• Column A: Date
• Column B: Scale
• Column C: Long Answer

4. Add a Google App Script

• Click Extensions > App Script. A new Google Script opens. Rename it; for example, use “form data.”
• Replace all code with the following:

const sheetName = 'Sheet1'
  const scriptProp = PropertiesService.getScriptProperties()
  function initialSetup () {
    const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
    scriptProp.setProperty('key', activeSpreadsheet.getId())
  }
  function doPost (e) {
    const lock = LockService.getScriptLock()
    lock.tryLock(10000)
  try {
      const doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
      const sheet = doc.getSheetByName(sheetName)
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
  const nextRow = sheet.getLastRow() + 1
  const newRow = headers.map(function(header) {
  return header === 'Date' ? new Date() : e.parameter[header]})
   sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
   return ContentService   .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))   .setMimeType(ContentService.MimeType.JSON)
  }
  catch (e) {
      return ContentService
        .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
        .setMimeType(ContentService.MimeType.JSON)
    }
  finally {
      lock.releaseLock()
    }
  }

If you can’t name your tab “Sheet 1,” change the first line of the script and replace “Sheet 1,” with your tab’s actual name.

When you’re done, your script should look like this:

1. Click Save project to Drive (to the left of Run).
2. In the dropdown to the right of Debug, select initialSetup (usually selected by default).
3. Click Run.

You might see a pop-in asking for permissions. Click Review permission and continue to the next screen.

Because Google hasn’t reviewed the script, a warning message may appear. Click Go to Mailing List (unsafe) to set up the correct permissions to update your form.

After you grant the script correct permissions, the script editor console shows the following output:

In the left menu, click Triggers to add a trigger for the script.

Then, click Add trigger.

In the window that appears, select the following options:

• Choose which function to run: doPost
• Choose which deployment should run: Head
• Select event source: From spreadsheet
• Select event type: On form submit

Click Save.

Click Deploy > New deployment.

Click the cog icon next to Select type > Web app.

In the form that appears, select the following options:

• Description: Use a descriptive name, such as “form data.”
• Web app > Execute as: Me
• Web app > Who has access: Anyone

Click Deploy.

Who has access: Anyone is mandatory for the correct transmission of results.

Copy the URL.

Configure survey settings in the Widget Studio

1. In the Survey Settings tab, select the button that users click to submit their response.
2. Under How are responses collected?, choose HTTP Request (external).
   • Request Name: Name your request; for example, “send data to Google Sheet.”
   • Method: Select POST.
   • Action URL: Paste the URL generated by your Google Apps Script.
3. Save your configuration.

Test your setup

1. Preview your widget.
2. Fill out the survey and Submit.
3. Go back to your Google Sheet.

You should now see a new row with your response.