Gravity Forms Google Sheets (2024)

Terminology

Before we get started, let’s clarify a few important words we’ll use throughout this documentation.

• Google Sheets: Refers to the Google product that allows you to create and manage spreadsheets.
• Google Sheet / spreadsheet: Refers to a collection of sheets.
• sheet: Refers to a specific sheet within a spreadsheet.
• GPGS: An acronym for this plugin, GP Google Sheets.

How do I enable this functionality?

After installing and activating GF Google Sheets, you can create Google Sheets feeds in any form. Use these feeds to pass data from your forms to Google Sheets. You can connect your Google account directly in the feed settings.

Feed Settings

When configuring GP Google Sheets feeds, you will be presented with the following settings.

After you have either created a new Google Sheet or selected an existing, the settings will then change to the following.

Google Sheets Settings

Use these settings to select an existing Google Sheet or create a new one. There are three steps to connecting a spreadsheet to a Google Sheets feed.

Account: Select the Google account to which your desired spreadsheet belongs or in which a new spreadsheet will be created. You can authorize a new account by selecting the Authorize New Account option or select a previously authorized account.

Spreadsheet: Select the spreadsheet in which entry data should be captured. You have three options.

1. Create a new spreadsheet specifically for this feed by selecting Add New Spreadsheet.
2. Connect an existing spreadsheet that has not previously been connected by selecting Connect Existing Spreadsheet.
3. Select an previously connected spreadsheet.

Sheet: Select the specific sheet in your spreadsheet in which entry data should be captured.

Select an Existing Sheet

If you have an existing spreadsheet already in Google Sheets, you can select it using “Connect Existing Sheet.”

After selecting Connect Existing Sheet, a new tab will open with a secure Google Picker that will allow you to select the spreadsheet you would like to connect. This spreadsheet will now be available for selection on the current feed, any other feed, and via Populate Anything, if enabled.

After connecting and selecting the spreadsheet, you will be presented with the option to select which sheet (or “tab”) to use in the spreadsheet.

Create New Sheet

Selecting “Create New Sheet” will create a new Google Sheet in the root of your Google Drive with the name of the feed.

The column headings will match the keys entered in the Column Mapping section.

After Connection

Once a sheet is selected or created, you will see an option to Disconnect the sheet as well as a handy Insert Test Row button to confirm that everything is working as expected.

Column Mapping

Use this section to map your form fields to columns in your Google Sheet. For a new spreadsheet, you can write in any column names you’d like. For existing spreadsheets, you can select existing columns or add additional columns by selecting “Add a Custom Key” from the drop down.

Additional Options

Update & Delete Rows

If you would like to keep your Google Sheet data synced to your entry data, enable the Update & Delete Rows option. When enabled, rows will be edited when entries are edited and deleted when entries are trashed or marked as spam. If an entry is restored from trash or “unspammed”, the entry row will be reinserted at the end of the spreadsheet.

Conditional Logic

You may not want to send data from every entry to your Google Sheet. In this case, use conditional logic to filter which entries get sent or which entries do not. Add as many conditions as you need.

Post Payment Actions

GP Google Sheets integrates with Gravity Forms “delayed payment” system, allowing feeds to only be processed after a payment has been received. This means you can wait to insert the entry row into a Google Sheet until after the user has completed their payment.

Please note: This setting will appear on the feed settings for your payment add-on rather than the GP Google Sheets feed. Here is an example from the Stripe add-on.

Manage Google Accounts

You can manage authorized Google accounts on the plugin settings page. The Google Accounts section provides a list of authorized accounts, including the WordPress user who authorized the account and the feeds and spreadsheets connected via that account.

You can delete a Google account to disconnect it from the current site by clicking the Delete button. You can authorize a new Google account by clicking the Add Google Account button at the bottom of this section.

Troubleshooting Issues

The Issues section will give you a summary of…

• any feeds that cannot access their configured spreadsheet.
• any form fields being populated via GP Populate Anything that cannot access their configured spreadsheet.

A few common reasons this can happen are the associated Google account’s password has changed, the spreadsheet has been deleted, or GPGS’ access has been manually removed from the account.

If an account becomes disconnected for any reason, the authorizing user can reconnect the account by clicking the Reconnect button for the disconnected account in the Google Accounts section.

Troubleshooting Action Scheduler

If you’ve confirmed that your Google account is connected and your GP Google Sheets feed is configured correctly, you may be experiencing an issue with Action Scheduler. This tool powers GPGS’ ability to handle temporary failures by automatically attempting to process the feed or entry update again.

Confirm Loopback Requests are Supported

The first thing to confirm is that Action Scheduler can function as expected. The most common issue we see is sites that cannot perform loopback requests.

You can confirm if your site supports loopback requests by navigating to Tools › Site Health. If the “Your site can perform loopback requests” test is failing, try whitelisting the 127.0.0.1 IP and test again.

If the test still fails, check your web server to see if there are any rejected requests in the logs. Look at the IP address for these rejected requests and whitelist that IP as well.

If none of this makes sense, your hosting provider can help!

Purge Action Scheduler

As a last resort, you may need to purge all GP Google Sheets’ schedule actions.

To confirm if this step might be helpful, navigate to Tools › Scheduled Actions in your WordPress admin menu. If there are a high number of failed actions, follow these steps to purge the action scheduler.

1. Add the following PHP snippet to your site via a code manager or directly in your theme’s functions.php file.
   

   add_filter( 'gpgs_show_danger_zone', '__return_true' );

2. Go to Forms › Settings › Google Sheets and click the Purge Action Scheduler button in the Danger Zone section.
   
3. After clicking this button, you should see a confirmation like this:
   
4. Remove (or deactivate) the snippet. You don’t want to accidentally click this button!

Send Existing Entries to Google Sheets

If you’re implementing GP Google Sheets on a form that has existing entries, you may want to send those entries to a spreadsheet. Here are two ways to do this.

Manually Process Feeds

Gravity Utility is a tool that adds a new Process Feeds bulk action on the entries list page.

To get started, first download and install the Gravity Utility plugin, which you can find at the bottom of this page.

Once Gravity Utility is installed, navigate to Gravity Forms › Settings › Utility. Make sure Manually process feeds is enabled:

To manually process existing entries, open the entries list view on the form that’s connected to your Google Sheets Feed.

On the left hand side, check each entry you want to process. When finished, click on the Bulk Actions dropdown at the top of the entry list, and choose Process Feeds.

Click Apply to start the process:

You’ll see a pop up modal where you can select the feeds you’d like to process. Choose the feed(s) you want to send your entries to and click Process Feeds:

Once finished, the selected entries should appear as new rows on your Google Sheet:

Automatically Process Feeds on Import

GravityImport allows you to automatically trigger the feed for each imported entry.

Entry Detail Meta Box

Use the Google Sheets meta box on the Entry Detail view for an overview of which Google Sheets feeds have been processed for the entry and which were not processed. You can see the time the entry row was inserted into a given spreadsheet and, if the option is enabled, when the entry row was last updated.

Please note, when reprocessing a feed, the feed’s conditional logic is ignored.

Error Handling

If there is an error processing a feed, GP Google Sheets will log that error as a note for the corresponding entry. If the error is due to a configuration issue, you can reprocess the feed via the Entry Detail Meta Box after correcting the configuration issue.

Error Notifications

GPGS will attempt to reprocess any feed that fails to process. If the error persists, a notification with be sent to the site administrator with recommendations on next steps. This notification will only be sent once per hour.

If you would like to be notified immediately of every error, you can create your own notification using Gravity Forms’ form notifications and selecting the “GP Google Sheets Error” notification event.

Action Scheduler

To further increase the robustness of GP Google Sheets, any interactions with an entry that triggers a change in a Google Sheet (adding, editing, and deleting entries) will be run through Action Scheduler.

Action Scheduler is a battle-tested job queue framework that was created by the WooCommerce team. It is an integral part of WooCommerce and many other popular WordPress plugins today.

By using Action Scheduler, you have more visibility into actions and whether or not they’ve already successfully run or failed.

If any of the GP Google Sheets actions in Action Scheduler fail, GP Google Sheets will automatically schedule follow-up retries using an exponential backoff strategy to help ensure that new entries, edited entries, and deleted entries are reflected in Google Sheets.

Accessing Action Scheduler

You can navigate to Action Scheduler by going to Tools » Action Scheduler in the WordPress Dashboard.

Failed Actions

To get more insight into why new entries (or entry updates/deletions) are not making it to Google Sheets, you can navigate to the “Failed” tab of Action Scheduler.

Pending Actions

GP Google Sheets uses an exponential backoff strategy for any failed requests associated with an entry. Due to the exponential backoff, there will be a delay between each retry.

If you’re curious when the next retry will be or want to run the pending actions immediately using the “Run” button, you can access them under the “Pending” tab.

Google Sheet Security

Privacy and security are core tenets of our authentication flow. Your Google Sheets data will never touch our servers.

Our OAuth service will handle getting an access/refresh token and pass it directly back to your WordPress site. There is no database attached to the service.

You will only interact with our OAuth service during initial authentication, when using the “Select existing sheet” functionality, refreshing access tokens, or re-authenticating your connection. For all other requests, your site will communicate directly with the Google APIs.

What can GPGS access?

GPGS can only access spreadsheets that you authorize. This includes spreadsheets created by GPGS and any existing sheet that has been connected to a GPGS feed.

It is important to note that when you disconnect a Google Sheet from a feed, this does not remove GPGS’ access to this spreadsheet. It is not possible to remove GPGS’ access to a specific spreadsheet once authorized. Instead, you must remove GPGS’ access globally and then reauthorize each feed/spreadsheet individually.

How does GPGS authentication work?

As of version 1.1, GP Google Sheets allows you to authenticate multiple Google accounts per user and no longer requires a “global” account. Any user with the capability to create new GPGS feeds will be able to select from a list of previously connected feeds; however, each user must authorize their own Google account in order to connect their own spreadsheets.

Administrators can manage which Google accounts are connected via the Google Accounts section on the plugin settings page.

GPGS will do everything it can to maintain a connection with any authorized Google account; however, there are some scenarios where an account or spreadsheet must be manually reauthorized/reconnected. For example, changing your Google account password or removing GPGS’s access to your Google account.

Check out the Issues section on the plugin settings page for a list of any connection issues GPGS may be experiencing.

Legacy Tokens

GPGS Beta 1 used a different authentication schema to connect to Google Sheets. Tokens generated via this schema are now considered legacy tokens. We strongly recommend re-authenticating feeds connected via a legacy token with the current authentication method.

To re-authenticate a feed attached to a legacy token, follow these steps:

1. Disconnect the legacy token from your feed.
2. Re-connect by authorizing your Google account and choosing a new or existing sheet.

Integrations

Nested Forms

You can import parent and child forms to the same spreadsheet by creating a feed for both the parent and child form. The parent form feed will be processed first and then the child form feed.

Populate Anything

GP Google Sheets integrates seamlessly with our popular Populate Anything plugin, allowing you to populate data directly from Google Sheets into your form.

When activated, Populate Anything will have access to any sheet created by or authorized for use with GP Google Sheets. If you would like to authorize a sheet specifically for use with Populate Anything, you can do so from the Google Sheets plugin settings.

For full details on this integration, see the Populate Anything documentation.

FAQ

Yes, files in shared drives are fully supported. If you’re looking to select a shared sheet and it does not appear by default, try searching for the name of the shared sheet:

How can I send QR codes to Google Sheets?

When mapping your spreadsheet columns, select the Add Custom Value option and insert theQR Code merge tagfor the field you want.

In this example, the QR Code URL will be inserted into the Selected Option column in Google Sheets:

Can I rename or reorder columns?

Yes. GP Google Sheets supports reordering or renaming columns, even after they have been mapped. To re-order a column, all you need to do is drag and drop the column heading in your Google Sheet:

Once you’re finished making your changes in Google Sheets, the feed will automatically update to use the new column order and names the next time it gets processed. That’s it!

How can I allow multiple users to authorize Google Sheets?

Your best bet is to create a Google group account and add any users that should have access to authorize Google Sheets to this account. Then, ensure that all users use this same group account when interacting with Google Sheets via the GPGS plugin.

Is GP Google Sheets asynchronous?

Yes, the process to send form data to Google Sheets is asynchronous. This means the form will submit immediately without waiting for the GPGS feed to finish processing.

Can GP Google Sheets apply colors and formatting to my Google Sheet?

Not at this time. Instead, we recommend formatting your sheet directly within Google Sheets itself in order to have the most control and flexibility. Check out these handy guides that show how you can format your Google Sheets:

• GCF Global – Formatting Cells Tutorial
• Lido – Color every other row in Google Sheets
  

Why can’t I select a spreadsheet when setting up GP Google Sheets?

Google’s File Picker tool requires cookies to be enabled. Please make sure that cookies are enabled in your browser by following the steps below:

• Enable cookies in Chrome
• Enable cookies in Firefox
• Enable cookies in Safari

Additionally, sometimes ad blocking plugins can create unexpected issues. If you’re using any form of ad blocker, try disabling it and start the setup process again.

Why doesn’t the date format in my Date field match the date format in Google Sheets?

• When sending a Date field to Google Sheets, the format is always set to: YYYY-MM-DD. This is the standard format that Google Sheets uses and is the most compatible with all of Google Sheets’ capabilities.
  • Solution: To display the date in a different format, you can always format the date column directly in Google Sheets.

Do Populate Anything requests count towards Google’s API quotas?

GP Google Sheet’s uses a special, highly performant API for requesting data for population via Populate Anything. In our testing, we found that these requests do not count towards Google’s API quotas.

Google could change this policy at any time so we use intelligent caching to reduce the number of requests made to Google’s API. Even still, we do not recommend populating dozens of fields with spreadsheet data or excessive chaining of multiple fields that are populated from Google Sheets.

If you need to populate lots of data across many fields from a spreadsheet, we recommend using something likewpDataTablesto sync your spreadsheet with a local database table which Populate Anything can query/filter much more performantly.

Why does GP Google Sheets get disconnected from my Google Account?

There are a few different reasons why GP Google Sheets may be disconnected from your Google Account. Changing your Google password, revoking access to GPGS, or API changes from Google can all cause the connection between your Google account and GPGS to break.

Thankfully, it’s easy to reconnect your Google Account via the Google Accounts plugin setting.

How do I migrate from the free “GP Populate Anything – Google Sheets Object Type” plugin to GP Google Sheets?

The following steps will need to be repeated for each form and field that uses the old plugin.

1. Open your form in the form editor and navigate to the field that is using the old plugin to populate from Google Sheets.
2. Click on the Object Type select and select whichever “Google Sheet” option is not selected.
   
3. You can be confident you’ve selected the correct “Google Sheet” object type as you will see separate settings for Spreadsheet and Sheet.
   
4. Reconfigure filters and choice/value templates as needed.
5. Save the form.

Known Limitations

• GP Google Sheets requires a header for each column that is mapped, and any column that comes before a mapped column. Even a character as simple as - works as a header.
  • Advanced: If a column comes after all mapped columns, a header is not required.

If any of these known limitations impact you, we’d love to hear from you. While some limitations are beyond our control, understanding how these limitations impact you is incredibly important in how we prioritize ongoing product development.

Translations

You can use the freeLoco Translate pluginto create translations for any of our Perks. If you’ve never used Loco translate before, here’sa tutorial written for beginners.

Hooks

• gpgs_allow_user_entered_values
  
• gpgs_gppa_cache_columns_expiration
  
• gpgs_gppa_cache_query_expiration
  
• gpgs_gppa_cache_rows_expiration
  
• gpgs_gppa_cache_sheets_expiration
  
• gpgs_gppa_http_request_timeout
  
• gpgs_row_value
  
• gpgs_should_delete_google_sheets_row
  
• gpgs_show_gppa_integration
  

Related Resources

• Gravity Forms Data Export: A Step-by-Step Guide
  Learn how to export Gravity Forms data in this step-by-step guide. Explore the benefits of exporting form data and how to get more out of your data export.
• Guide: Two-Way Connect Gravity Forms with Google Sheets
  Learn how to send Gravity Forms data to Google Sheets and populate data from Google Sheets into Gravity Forms, even within the same form.
• How to Dynamically Populate Drop Down Fields in Gravity Forms
  Learn how to dynamically populate Drop Down fields in Gravity Forms with users, form entries, database tables, and more!