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

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.

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

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.

Selecting your sheet and worksheet

Select "Choose file" . A pop-up will appear for you to select your Google Sheet. Then select the desired worksheet.

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

We recommend making any necessary modifications to your Google Sheet before completing the integration. This includes any and all types of alterations to the spreadsheet. 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.

Sheets or worksheets are not loading but they did previously

If you're unable to create or modify actions because your worksheets or sheets aren't loading and they used to, it's likely that the connection between us and Google has expired or been revoked.

There are several reasons why this might occur:

  • It has been over six months since you connected your Google Sheets account.
  • The Google account password has been changed.
  • Permissions have changed for the Google Workspace to which the account belongs.
  • Access has been revoked on Google's side.
  • Google may have invalidated the connection.

To fix this:

  1. Go to your account services.
  2. Locate the Google Sheets account you want to re-authorize.
  3. Click the "reconnect" button.
  4. Follow the prompts to authorize the connection again.

Note that this is slightly different from the section below.

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 or integrations previously connected with this Google account the toggle button for the integration will be automatically set to the "off" position. To reactivate them, revisit each integration → enter the configuration window for the Google Sheet integration(s) → select the appropriate Google account. After connecting the Google account return to the main integration page for the form and toggle the button to the "on" position.

Hint
If the toggle button for the integration is unavailable because the button is grayed out, link your Google account on the integration's configuration page and refresh. This will activate the enable/disable toggle button.

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.