Gravity Connect Google Sheets

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.
• GCGS: An acronym for this plugin, the GC Google Sheets Connection.

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.

	Navigate to your desired form and click the “Google Sheets” item under the “Settings” menu.
	Click the “Add New” button to add a new Google Sheets feed.
	Connect your Google account by selecting “Authorize New Account” under the Account setting.
	Connect your feed to a spreadsheet by adding a new spreadsheet or connecting an existing spreadsheet.
	Map your form’s fields to columns in the spreadsheet.

Feed Settings

When configuring feeds for the Google Sheets Connection, 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.

Auto-mapping Columns

On large forms, mapping columns to fields can be quite a chore. Auto-mapping will dramatically expedite this process.

For new spreadsheets, the “Map All Fields” button will add a column for each form field, preselect the corresponding field, and name the column based on that field.

For existing spreadsheets, the “Map All Columns” button will add a mapping row for each column and preselect the the best-fit field based on the column name. You should always validate the mapping to ensure you’re happy with the results.

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

The Google Sheets Connection 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 Google Sheets Connection feed. Here is an example from the Stripe add-on.

Usage Examples

• Guide: Two-Way Connect Gravity Forms with Google Sheets

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 GCGS’ 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 Google Sheets Connection feed is configured correctly, you may be experiencing an issue with Action Scheduler. This tool powers GCGS’ 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 of the Google Sheets Connections’ scheduled 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 the Google Sheets Connection on a form that has existing entries, you may want to send those entries to a spreadsheet. Here are two ways to do this.

Bulk Process Feeds

Our free GF Feed Forge plugin allows you to bulk process feeds for most feed-based add-ons (including the Google Sheets Connection). This is by far the easiest way to send existing entry data to your Google Sheets.

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 Update & Delete Rows 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, the Google Sheets Connection 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

GCGS 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 “Google Sheets Feed Error” notification event.

Action Scheduler

To further increase the robustness of the Google Sheets Connection, 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 Google Sheets Connection’s actions in Action Scheduler fail, the Google Sheets Connection 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

The Google Sheets Connection 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 GCGS access?

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

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

How does GCGS authentication work?

As of version 1.1, the Google Sheets Connection allows you to authenticate multiple Google accounts per user and no longer requires a “global” account. Any user with the capability to create new GCGS 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.

GCGS 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 GCGS’s access to your Google account.

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

Legacy Tokens

GCGS 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

The Google Sheets Connection integrates seamlessly with our popular Populate Anything plugin (available with the Gravity Perks suite), 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 the Google Sheets Connection. 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

How do I upgrade from the GP Google Sheets to the Google Sheets Connection?

GP Google Sheets was previously part of the Gravity Perks suite. It has now moved to the Gravity Connect suite and been renamed to GC Google Sheets. For up-to-date instructions on upgrading form GPGS to GCGS, see our FAQ on the migration announcement.

Does the Google Sheets Connection support files in shared drives?

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 the QR Code merge tag for 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. The Google Sheets Connection 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!

Can I map a label instead of a value?

Yes! When setting up the Value for column mapping, select Add Custom Value. Then, click on Insert Merge Tags and insert the merge tag for the field. By default, Gravity Forms will replace merge tags with the label.

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 GCGS plugin.

Is the Google Sheets Connection asynchronous?

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

Can the Google Sheets Connection 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 the Google Sheets Connection?

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?

The Google Sheets Connection 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 like wpDataTables to sync your spreadsheet with a local database table which Populate Anything can query/filter much more performantly.

Why does the Google Sheets Connection get disconnected from my Google Account?

There are a few different reasons why the Google Sheets Connection may be disconnected from your Google Account. Changing your Google password, revoking access to GCGS, or API changes from Google can all cause the connection between your Google account and GCGS 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 the Google Sheets Connection?

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.

How do I Insert the New Entry Row at the Top of the Google Sheets?

Google makes this nearly impossible to do reliably programmatically. However, there’s a workaround that requires a little tweak in your Google spreadsheet. Here’s what you’ll do;

1. Create a new sheet on the Google Spreadsheet.
2. Set up the new sheet using the query below, to import the data from the original sheet and sort it in descending order of a column (e.g., by Entry ID or timestamp).
3. Update Original in the query to the name of the main Google Sheets, and set A and D to the column range you want to copy to the new sheet.
4. Include the column you’ll use for sorting as part of the range and enter that column in the query, to order the data in descending order.

=Query(Original!A:D, "SELECT * ORDER BY A DESC")

Known Limitations

• The Google Sheets Connection 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.
• Similar to mapped columns requiring a header, hiding the first column that is mapped can cause Google’s appending logic to add the row to the top of the sheet rather than the bottom.

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 free Loco Translate plugin to create translations for any of our products. If you’ve never used Loco translate before, here’s a tutorial written for beginners.

Hooks

• gcgs_allow_user_entered_values
  
• gcgs_gppa_cache_columns_expiration
  
• gcgs_gppa_cache_query_expiration
  
• gcgs_gppa_cache_rows_expiration
  
• gcgs_gppa_cache_sheets_expiration
  
• gcgs_gppa_http_request_timeout
  
• gcgs_reset_row_metadata
  
• gcgs_row_value
  
• gcgs_should_delete_google_sheets_row
  
• gcgs_should_send_error_notification
  
• gcgs_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!