← Documentation

Google Sheets

Google Sheets is a powerful, web-based spreadsheet application that enables real-time collaboration. The Google Sheets addon allows you to seamlessly bridge WordPress form submissions directly to your Google Spreadsheets, transforming how you manage and analyze form data.

The addon

The Google Sheets API is a RESTful interface that lets you read and modify spreadsheets data. This API use the OAuth 2.0 protocol for authentication and authorization. To talk with this API the addon implements a custom bridge class based on the generic REST bridge that requires Bearer credentials.

A Google Sheets bridge will store your form submissions as a table on a Google Spreadsheets. Each field of your forms will be translated to a table column, and every form submission inserted as a new row of the table.

In addition, the addon comes with a couple of form templates as bridge examples. While using the addon, you can use a template as a starter point, but not limited to them: The flexibility of the spreadsheets tables allows you to store any kind of form submissions.

How to use

The first step to use the addon is to activate it on the addons table on the general settings tab.

Once activated, a new tab named Google Sheets will be visible on the settings page. If it’s your first time, it should looks like this:

The template wizard

The recommended way to set up a new bridge is to use a template as a starter point. The addon’s template wizard will set up a new Bearer credential, a backend connection and a form, all of them linked on a new bridge.

With this setup ready, you can edit the bridge’s form to fit your requirements or manually add new forms and bridge them with the backend / credential you’ve configured on the previous step.

The template wizard will lead you through four steps:

1. Credential

On this step, Forms Bridge will register a new Bearer credential. To set up this authentication, you have to register a new OAUth Client on the Google Cloud Platform and get the Client ID and Client secret. Check out our tutorial about How to set up a new OAuth Client on Google Cloud Console.

Once you get your form fulfilled with a unique name, the Client ID and the Client secret, the Wizard will ask you to authorize the new credential. Click on Authorize button and you will be redirected to the consent screen of your Google Cloud project. Grant access to Forms Bridge to your Drive account and Spreadsheet files and then you will be redirected to the plugin settings page.

2. Backend

The backend step only requires a unique name to identify the new backend connection. By default, it proposes Sheets API as its name, but you can rename it at will.

3. Form

In this step you have to choose the title of the form the template are going to create. If there is a compatible form, the wizard allows you to reuse the existing form and skip the form creation.

4. Bridge

On the last step of the wizard, you have to set a unique name for the new bridge, select the spreadsheet you want your form to get bridged, and the spreadsheet’s tab name.

After that, click on submit and Forms Bridge will generate the credential, backend, form and bridge setting. It only remains to publish the new form on a public page and wait for a new form submission!

Manual set up

With templates, you can save a lot of time while configuring your bridges, but you can achieve the same result with a manual set up.

A Google Sheets bridge will require the following components:

1. Credential

As mentioned before, the Sheets API implements the OAuth 2.0 schema to grant access to its resources. To solve this authentication requirement, the addon requires you to register a Bearer type of credential with the credentials of an Google OAuht Client. Check out our tutorial about How to set up a new OAuth Client on Google Cloud Console.

Once you got the Client ID and Client secret of the Google OAuth client, you have to go to Settings > Forms Bridge > HTTP > Authentication and register a new authentication credential.

Select Bearer as the authentication schema and put the Client ID and Client Secret in the respective fields. For the Authorization URL, you have to set https://accounts.google.com/o/oauth2/v2 and for the Scope https://www.googleapis.com/auth/drive.readonly https://www.googleapis.com/auth/spreadsheets.

Once registered, Forms Bridge will ask you to Authorize the new credential. Click on the button and a new browser’s tab will be opened asking you for the authorization to Forms Bridge to access your account data. Click on Accept and then you will be redirected again to the HTTP tab of the plugin settings page with your credential authorized.

2. Backend

To configure the Sheets API as a backend follow this steps:

1. Register a new backend with a unique name (Sheets API, for example), and a URL with https://sheets.googleapis.com as value.
2. Select JSON as the Encoding Schema
3. Add Accept as an HTTP header with application/json as value.
4. Select a Bearer credential you’ve configured on the previous step.

3. Form

The addon supports any kind of form. Get your preferred form builder and create a new form. Once created, you can choose it as the target form to be bridged on the bridges panel of the addon.

4. Bridge

The addon’s bridge is an extension of the generic REST bridge. Because of this they looks so similar. Like the REST bridge, the addon’s bridge requires a unique name, a backend, a form, an endpoint and an HTTP method. In addition, this kind of bridges will require and additional param: the tab name where you want to get your form submissions.

The endpoint of a Google Sheets bridge should be like /v4/spreadsheets/${spreadsheet_id}. The spreadsheet ID can be extracted from its URL. For example, the spreadsheet ID in the URL https://docs.google.com/spreadsheets/d/abc1234567/edit#gid=0 is “abc1234567”.

Troubleshooting

To troubleshooting your bridges you can use the plugin’s debug console from the General Settings page. Enable the debug mode and submit a sample form response to see what’s going on under the hood. Take a look to our tutorial about how to use the debug console.

Common issues

1. Authentication failures
   • Verify OAuth credentials
   • Check scope permissions
   • Regenerate client secret
   • Validate spreadsheet permissions
   • If using external audience, check that your project has been verified
2. Missing data
   • Verify column names
   • Check field mapping
   • Consider API quotas and potential costs

Addon’s templates

The addon comes packed with the following template:

• Contacts 🔎
  Simple contact form connected to a spreadsheet

• WC Orders 🔎
  Sale order bridge template. The resulting bridge will convert WooCommerce orders into new lines on a Google Spreadsheet with order and customer information.

Workflow jobs

The addon comes packed with the following workflow jobs:

• Format date fields 🔎
  Gets date, hour and minute fields and merge its values into a date with format Y-m-d H:M:S

• ISO2 country code 🔎
  Gets the ISO2 country code from country names and replace its value

• ISO3 country code 🔎
  Gets the ISO3 country code from country names and replace its value

• Skip submission 🔎
  Skip submission if condition is not truthy

Supported form builders