Populate Gravity Forms with Google Sheets

• November 18, 2021: Updated filter behavior to be case-insensitive to match the behavior of core Populate Anything Object Types.

Overview

With the advent of Google Sheets, you can now access spreadsheets on nearly any device with internet access. Even better, with spreadsheets being in the cloud, it’s possible to fetch data directly and perform some magical spells.

Google Sheets can be great for:

• Keeping up-to-date lists and directories
• Pricing tables and calculators
• Business Analysis
• Managing a food menu
• Tracking product inventory
• …and so many more use-cases!

By pulling data from Google Sheets into your Gravity Forms, you can leverage the vast array of capabilities in Google Sheets, including formulas, while leveraging the many data sources you can feed into Sheets.

If you have employees currently using Sheets, anything they update in Sheets will be reflected in the form. No more sending a WordPress login unless required!

1. Overview
2. Getting Started
   1. Prerequisites
      1. Sheet Requirements
      2. Video Walkthrough
      3. Google Cloud Platform Project
   2. Step 1 – Enable required Google APIs
      1. Enable Google Sheets API
      2. Enable Google Drive API
   3. Step 2 – Create Service Account
   4. Step 3 – Provision and set JSON key for service account
   5. Step 3 – Share Spreadsheets with service account
   6. Step 4 – Populate Fields with Spreadsheet Data via Populate Anything
3. Limitations
4. Send and Sync Gravity Forms data with Google Sheets

Getting Started

Prerequisites

Confirm that you have Gravity Forms and Populate Anything installed and activated and that you’ve installed the plugin below.

Sheet Requirements

This plugin works best with sheets that are arranged like the example below. The column names will become properties much like “Post Title” and “Post Content” are available properties for the Post Object Type.

Additionally, only the first sheet in a spreadsheet will be accessible. To work around this, you can add a new sheet as the first sheet and use references from other sheets.

Video Walkthrough

This video walkthrough details all of the configuration steps for connecting Google Sheets with Populate Anything.

Google Cloud Platform Project

You will need a Google Account (Gmail and Google Workspace accounts work!) as well as a Google Cloud Platform project before continuing.

To create one, navigate to the Google Cloud Platform Console and you should be greeted with a wizard to create a project if you do not already have one.

Select Country, Agree & Continue

Enter in Project Name. This can be a website name, company name, or whatever you would like. For this example, we’ll simply use “Google Sheets API.”

Step 1 – Enable required Google APIs

Before the plugin can access the Sheets, we need to activate the Google Sheets and Google Drive APIs.

Enable Google Sheets API

The first API to enable is the Google Sheets API. This is the primary API that is used to read values from Google Sheets.

(Quick link)

Enable Google Drive API

The second API to enable is the Google Drive API. This API is needed to list out the available spreadsheets.

(Quick link)

Step 2 – Create Service Account

The method used by this plugin to authenticate with Google is through a service account. This is preferable as it allows you to create service accounts specific to websites and only share the spreadsheets pertinent to the website at hand.

1. Navigate to APIs & Services » Credentials

2. Click Create Credentials

3. Select Service account

4. Enter a Service account name that will make sense to you in the future, such as the website or client name. For this example, we’ll use Google Sheets + Populate Anything. We’ll also use google-sheets-gppa as the Service account ID.

5. Click “Done” – Skip steps 2 & 3.

6. Refresh until the new service account shows up under Service Accounts. Once it shows up, copy the service account email and save it for later.

Step 3 – Provision and set JSON key for service account

The next step after creating the service account is to generate a JSON key that will be loaded into the plugin settings so it can authenticate with the Google Sheets and Google Drive APIs.

1. Click on Edit service account icon.

2. Navigate to Keys.

3. Click on Add Key then Create new key.

4. Ensure that JSON is selected then click Create.

5. Your browser should now download a JSON file. Keep this in a safe location.

6. Open the downloaded JSON key file with a text/code editor and paste contents into Service Account JSON Key setting under Forms » Settings.

Step 3 – Share Spreadsheets with service account

To make spreadsheets accessible to the service account and subsequently, the Google Sheets object type, you’ll need to share spreadsheets with the service account.

We’re confident in this approach as spreadsheets don’t have to be owned by anyone specifically and you’re not exposing your personal/company Google Drive. 🧙

To share a spreadsheet with a service account, enter the service account email just like you would any other Google account that you’re sharing to.

Step 4 – Populate Fields with Spreadsheet Data via Populate Anything

With all of the hard bits out of the way, now comes the fun part! Wiring up your forms to Google Sheets.

If everything is set up correctly, you should see a new “Google Sheet” Type. Once you select Google Sheet, the spreadsheets that the service account has access to should show up under Spreadsheets.

Limitations

• The first row of the spreadsheet must be column names.
• Only the first sheet in a spreadsheet is accessible.

Send and Sync Gravity Forms data with Google Sheets

We provide a separate solution for automatically sending (and syncing) Gravity Forms data with Google Sheets. This is done securely and without the need for additional automation services (that often carry another monthly cost).

• Instantly (and automatically) sync Gravity Forms Data
• Make your data actionable and accessible (functions, charts, collaboration)
• Secure your data safely with high level encryption
• Use Gravity Forms data with Data Studio (aka Looker Studio), BigQuery, and any others

Learn more about Gravity Forms Google Sheets.