Send form data to Google Sheets

If you don't have a form on your site or a web service to collect information, you can send all filled user data to a Google Sheet.

note

If you want to send your form data using a URL of your site or via a web service, read this article.

In this section, you will learn:

• How to configure your Google Sheet to collect data
• How to set up the Events section in the Widget Studio to send form data to Google Sheets.

Configure your Google Sheet

1. Go to Google Sheets and create a new sheet.
2. Name your sheet "Sheet1" (which is the default name given to your tab if English is the language used in your Google Account)
3. Set the following headers in the first row:

	A	B	C
1	Date	Form element name // field 1	Form element name // field 2
2

• Always write Date in column A. The column will be filled with the date and time the user submits the form.
• Beginning with column B, each column corresponds to a specific field in your form. For each column, enter the form element name that corresponds to the field.

The form element name is different from the element name. A form element name is automatically attributed to your field when you add it to your form.

To find the form element name, select each field element and unfold the Form settings section.

An input field is selected in the image above (from our use case) The element's name is Email, but the form element name is different. In the example above, the form element name is input_kn8u23ie9sy.

So, cell B1 will be like this:

	A	B	C
1	Date	input_kn8u23ie9sy

Additional information about form element names

• You can edit a field's form element name.
  • For example, if you want to write "email" in cell B1 instead of "input_kn8u23ie9sy", replace "input_kn8u23ie9sy" with "email".
• Form element names are case sensitive. Their case must match in the Widget Studio and Google Sheets.

Add a Google App Script

To add a Google App Script:

1. Click Extensions > Apps Script. A new Google Script opens.
2. Rename the Google Script.
3. Replace all text in the Script with the following code:

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()
    }
  }

note

If you can’t name your tab "Sheet1", you should change the first line of the script, and replace "Sheet1" with your tab's name.

4. Click the Save project icon.
5. Select initialSetup to the right of the Debug action.
6. Click Run. You might be asked to review permissions. Click Review permissions and continue to the next screen.

Because Google has not reviewed this script, it can generate a warning message before you can continue. Click Go to Mailing List (Unsafe) to update the script's permissions.

Once you set the correct permissions, you should see the following output in the script editor console:

7. Click Triggers in the left menu.

8. Click Add trigger.

9. 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

10. Click Save.

11. Select Deploy > New Deployment from the dropdown.

12. Click the Select type icon and select Web app.
13. In the form that appears, select the following options:

• Description: My Form Data (This can be anything that you want. Just make it descriptive.)
• Web app → Execute As: Me
• Web app → Who has access: Anyone
• Click Deploy.

14. Copy the URL.

Now you must configure the Events section to complete your Google Sheets setup.

Set up the Events section on the Widget Studio

1. Open the Widget Studio.
2. Click Events > Add event > Mouse.
3. Select On element clicked.
4. Click your submit button's name. A new line is added to the Events page.
5. In the new line, click Add action > Request > Send data to. A new pop-in opens.
6. In the pop-in:
   • Attribute a name to this action in Request Name.
   • Select POST method.
   • Paste the URL you copied from your Google Sheet in Action URL.
   • In Form ID, select the name of the form whose data you want to send to your Google Sheet.

Once you select your form, a summary of all the fields sent to your Google Sheet will appear below. Ensure that the data sent is accurate and the form element name matches the column names in your Google Sheet's first row.

1. Click Select
2. Click Save or Update (depending on the status of your widget).

You are now ready to send your form data to your Google Sheet.

To test, link your widget to a campaign (A/B Test or Personalization), run your campaign, fill out your form, submit it, and a new row will appear in your Google Sheet with your responses.