How do I integrate with Google Sheets?

Introduction

Our direct integration makes it easier than ever to connect Google Sheets with your forms!

Connect Google Sheets to Paperform

  • In the Form Editor navigate to "After Submission" → "Integrations & Webhooks".
  • Click on "Google Sheets" under the heading "Direct Integrations". Choose from the list of possible actions on the dropdown list provided.

An image showing the button to add new actions for a Google Sheets integration, which has the Google Sheets logo and a title with the text "Google Sheets".

  • Click on "Add Account +" to connect your Google Sheets account, prompting a popup window to appear requesting you log in and give permission for Paperform to connect your Google Sheets account.

An image showing the pop-up required for connecting Google Sheets to Paperform.

  • Once the popup window closes, your account will be successfully connected to Paperform!

You can remove or rename your Google Sheets account at any time in your account settings, but note this will also affect any forms using this integration connection.

Mapping data to columns

Some integrations like Google Sheets require you to create columns in the sheet before you can integrate with them. Once you have created the columns, you'll need to choose which answers go in each column. You can use the dropdown on the right of the column name to insert the answers you would like to go in each column.

An image showing an example of how the Colums section of a Google Sheets integration action may look. The screenshot also demonstrates how the dropdown menu to select fields on your form looks when populated with data.

If no columns appear in the list, you'll need to ensure that any columns you want to use contain a title in Row 1 of your Google Sheets document. You can then click the "Refresh" symbol to the right of this field to reload the column list.

Setting up an Integration Action

Our Google Sheets direct integration is built to be flexible around your use case, so we offer a number of different actions and functions.

Below you'll find an example of how to configure one of these actions.

Add Row to Sheet

This action will add a new row to your Google Sheets document when the form is submitted.

After setting up your Google Sheets connection for the action, complete the fields presented. The list of fields and what they do can be found below;

  • Drive - Choose which drive you'd like to view document listings from.

    • The available options are Don't use a specific Drive, and the drives your account has access to. The drives are listed by name.
  • Sheet - Choose which sheet you would like to connect the form to.

  • Worksheet - Choose the worksheet from the selected sheet to add the row to.

  • Columns - This will load the columns in the selected worksheet, and you can use the "+=" icon to the right of the input to map specific answers to the columns into the spreadsheet.

Once you have finished configuring the action, click the "Send Test" button at the bottom to test out the action. If you're happy with the results, click "Finish Setup" to finish setting up the action. This will automatically be live for future submissions.

Considerations

Sending a Test for your action

It is recommended that you make any necessary modifications to your Google Sheet before clicking the "Finish setup" button and finalizing the integration configuration. This includes the ability to add, remove, or rearrange columns from the sheet, and then run some tests to visualize how the data will populate. To view the updated changes you've made to your columns on your worksheet, simply click the "Refresh" button located at the top right of the Columns field.

For the "Send Test" button to work, you must have a recent submission that contains data for the fields being used in your integration action.

If you don't have a recent submission with the correct data set then you may encounter unexpected behaviors with integration action, or it may outright fail.

Gotchas

The data in Google Sheets shows up as #ERROR!

If you map data over to a column in Google Sheets that looks like it could be a formula, Sheets will try to parse it as a formula, which may result in some unexpected errors in cells.

To prevent this from happening and force Sheets to always treat the value as plain text (so, definitely not a formula) you can prepend the mapped value with a single quote — '.

Suppose you want to map a phone number question's answer over that typically is in the format +1 (xxx) xxx-xxxx with a key of {{ phone }}.

  • Treated as a formula: {{ phone }}
  • Treated as plain text: '{{ phone }}

Note the single quote, or apostrophe, before the answer piping.

Data is shifting right in the worksheet beyond specified columns

There are a number of things that may cause this. Primarily, we've found that keeping the integrated worksheet as plain as possible helps to prevent this issue.

Namely, we recommend against including:

  • formulas
  • graphs
  • checkboxes
  • dropdowns
  • gaps between column headings (empty data below a column heading is OK)
  • frozen rows and/or columns
  • formatting (hidden or visible)

That is, the integrated worksheet works best when it is kept as a place for raw data. Making any modifications or alterations to the connected Sheet, after it has been set up, may cause issues populating the data. If you wish to then transform, operate on, or visualize this data, we recommend using ImportRange from a sibling worksheet or another Sheet, depending on the desired usage.

Additionally, this issue may crop up if you have copied another form that had one or more integrations with Google Sheets.

Since you're copying the form and its configuration, the mapping for the action is also copied. When you then use a different worksheet, the copied mapping may remain, causing issues due to the inconsistency of column headings versus mapped data.

In the above instance, we recommend explicitly creating new actions on the form that is the copy to avoid running into inconsistent mappings that cause data to possibly shift right.

Reconnecting the Google Sheets Integration

Should you encounter difficulties with the submission data populating in your Google Sheet, please follow the steps below to resolve the issue:

  1. Disconnect the Google Sheets integration from your account by navigating to “Account Settings” → “Account Services”. Locate the Google Sheets integration and click the "delete" button to remove it.
  2. After disconnecting the integration, revisit the form's integration settings and reestablish the connection with your Google Account. To do this, click the “Add Account +” button and select the relevant account.
  3. Ensure that you save the changes by clicking the “Save Changes” button and activate the integration by toggling it on.

By following these guidelines, you should be able to effectively address any data submission issues and ensure a smooth integration between Paperform and Google Sheets.

If you have additional forms that were previously integrated with this Google account, kindly revisit each form and reactivate the integration. To do so, simply toggle the integration on the forms back to the "on" position.

What is Paperform?

Paperform is a web platform that enables anyone to create beautiful online forms, payment or product pages, quickly and intuitively, without any technical knowledge.

Want to be able to do more with Google Sheets? Let us know at support@paperform.co.