November 18, 2021: Updated filter behavior to be case-insensitive to match the behavior of core Populate Anything Object Types.
Stop! This plugin is deprecated.
This plugin is not compatible with the current version of GP Populate Anything. This feature is built into GP Google Sheets!
This article requires the Gravity Forms Populate Anything perk.
Buy Gravity Perks to get this perk plus 47 other premium Gravity Forms plugins!
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!
- Overview
- Getting Started
- Limitations
- 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.
Column 1 | Column 2 | Column 3 |
Sample Column 1 Value | Sample Column 2 Value | Sample Column 3 Value |
Sample Column 1 Value | Sample Column 2 Value | Sample Column 3 Value |
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.
Enable Google Drive API
The second API to enable is the Google Drive API. This API is needed to list out the available spreadsheets.
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.
Did this resource help you do something awesome with Gravity Forms?
Then you'll absolutely love Gravity Perks; a suite of 47+ essential add-ons for Gravity Forms with support you can count on.
Seems to only show 500 or so of thousands of records? is that expected? When I use ‘enhanced user interface’ to search a list of universities, it always fails to find values that are a long way down the list. Please can you advise, thanks!
Hi Daniel,
The default limit for GPPA queries is 500. It can only be changed with the gppa_query_limit hook. You will find some examples of how to use this filter hook on the documentation page. https://gravitywiz.com/documentation/gppa_query_limit/
Best,
Hi,
Is there a row limitation on how many items I can have in my dropdown? I have a company list that is very long.
Thanks!
Hi Anna,
There shouldn’t be any limit on the number of items that can be displayed in a dropdown.
Best,
Even removing some relating plugins, Ver0.1.4 & 0.1.5 still show nothing under Forms » Settings, but get a new “Google Sheet” Type. No ways to input the JSON Key setting.
Could it (Service Account JSON Key setting) possible be manually activated via snippet ? Thx
Hi Chih,
There is a special plugin that you’ll need to install and activate to use with the GP Populate Anything Perk to get the JSON Key Settings. You’ll find the plugin in this documentation above.
Best,
Hi, Actually I meant, I had activated these 2 plugins and disabled any other related. (1) Gravity Forms Populate Anything (2) Populate Gravity Forms with Google Sheets) Ver 0.1.4 ~ Ver 0.1.6
But it still did not show “GPPA+Sheets” under Forms » Settings.
https://gravitywiz.com/app/uploads/2021/09/image-1024×380.png
Hi Chih,
When testing locally it seems to be working as expected.
If you have an active Gravity Perks License, you can get in touch with us via our support form with your account email address and we’ll be happy to dig into this further.
Cheers,
Does this work with Spreadsheets housed in Google’s Shared Drives?
Hi Tamra,
I’m not completely sure, but if you can create an API for the Google Shared and a Google Sheet API, it may work. Could you give it a try and see if it works for you. In case it doesn’t work, you can get in touch with us via our support form with additional details so we take a look into this.
Best,
Does this work the opposite way around? Can you populate Google Sheets from Gravity Form entries? I know if can be done through third parties but not directly via Gravity Forms.
Hi Pete,
We already replied in the ticket, but I wanted to follow up here also. We’re actually planning on creating a perk for this. We’re hoping to have it ready by the end of the year.
Hello,
Does this works with any API (like https://www.themoviedb.org/documentation/api for example ?)
Thanks for your amazing work!
Hi Veronique,
Thanks for the kind words. Currently, this only works with Google Sheet API. I will pass your comment to our developers as a feature request.
Best,