Send survey responses to Google Sheets

​

Configure your Google Sheet

1. Go to Google Sheets and create a new spreadsheet.
2. Name the tab Sheet1 (which is the default).
3. In the first row, define the columns as follows:
   
   Details:
   • Column A: Enter Date. The script fills 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. Click Extensions > Apps Script. A new Google Script opens. Rename it. For example, use “form data.”
   
5. 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 “Sheet1,” change the value of sheetName in the first line of the script to your tab’s actual name.
   
6. Click Save project to Drive (to the left of Run).
7. In the dropdown to the right of Debug, select initialSetup (usually selected by default).
8. 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 form data (unsafe) to grant the permissions that let the script update your spreadsheet.
   
   After you grant the permissions, the console shows Execution completed.
   
9. In the left menu, click Triggers to add a trigger for the script.
   
10. 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
    
11. Click Save.
12. Click Deploy > New deployment.
13. 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
14. Click Deploy.
    Who has access: Anyone is required for the widget to send responses to your spreadsheet.
    
15. Copy the URL.

​

Configure survey settings in the Widget Studio

1. In the Survey Settings tab, turn on Collect survey data.
2. Under When is the survey submitted?, select A button is clicked, then choose the button that visitors click to submit.
3. Under How are responses collected?, choose Externally (HTTP Request).
   • Request name: Name your request. For example, use “send data to Google Sheet.”
   • Method: Select POST.
   • Action URL: Paste the URL generated by your Google Apps Script.
4. Save your configuration.

​

Test your setup

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