Google Sheets

Overview

The AFI plugin allows integration with Google Sheets, enabling data sent from a sender platform to automatically create a new row in a specified sheet with the provided information.

Authentication

First, we need to create a Google API project to grant access to the AFI plugin. Please follow the steps outlined below:

Go to Google Developer Console and create a New Project.

Enter an appropriate project name, then click the CREATE button.

Select the project from the dropdown.

Go to Top Left Menu > API & Services > Library

Both Google Drive API and Google Sheets API need to be enabled.

Google Drive API is needed for reading the available spreadsheets list. 

Google Sheets API is needed to read write data inside a spreadsheet. 

Navigate to the OAuth consent screen menu, select “External,” and click the CREATE button.

• Put an App name as you want and select User support email.

• In the Authorized domains section, click on ADD DOMAIN button, and enter yourdomain.xxx. This is the domain of your website where the plugin is installed.

• Put your email address in the Developer contact information input box.

• Click on SAVE AND CONTINUE.

On the Scopes page, click on ADD OR REMOVE SCOPES button. Search and select Google Drive API. Mark the read-only access checkbox. 

Again search and select Google Sheets API. Mark the spreadsheet access checkbox. Then click the UPDATE button.

Make sure that both Google Drive’s readonly and Google Sheets spreadsheets scopes are added. Click on SAVE AND CONTINUE.

Nothing need to be done on Test users page. Click on SAVE AND CONTINUE.

You are now on the Summary page. Click on BACK TO DASHBOARD.

Click on PUBLISH APP and then CONFIRM.

Do not click on the button PREPARE FOR VERIFICATION. It is needed when you distribute the app for public use. But here you are the only user.

Go to Credentials menu, click on +CREATE CREDENTIALS. Select OAuth Client ID.

Select Web Application as Application Type. Put any name. In the Authorized redirect URIs section click on ADD URI. Insert URI like https://xxxxxx.xxx/wp-json/advancedformintegration/googlesheets, here xxxxxx.xxx is your website domain. Click on CREATE.

A new pop-up window will appear that contains the Client ID and Client Secret. Copy both the plugin settings page. Click on Save & Authorize and finish the authorization process.

Unverified App

In the process of creating the app and while authorizing, you might get an unverified app warning and the system will ask you to verify the app. Usually, if using sensitive scopes, a public app is required to go through a Google verification process, when the app is intended to be used by many people. But for AFI, it is an admin plugin and you (admin) are the only user of your own app. In this case, Google support suggested to keep using it with the unverified screen. So you can just skip the verification process and use it as it is. 

Here is a video tutorial that can help you to understand:

https://youtu.be/VJIHgJkyyCM

Creating A New Integration

Here below a brief description on how easily you can start an integration.

1. At first, make sure that the authorisation is done and the status is showing connected in the settings page.
2. Click on Add New menu and setup the trigger platform.
3. A default title will be auto-filled in the Integration Title input box. You can change it to something relevant.
4. In [direction]Trigger > Form/Data Provider[/direction] select Contact Form 7.
5. The [direction]Form/Task Name[/direction] dropdown will show a list of form names. Select the form you want to connect.

6. In [direction]Action > Platform[/direction] dropdown, select the platform name to where you want to send the data. If you don’t see the platform, go to plugin settings page and activate it. Complete the fields mapping. It can be different for different platforms. Please check out the documentation page for Receiver Platforms.

7. Conditional Logic can be applied if required.
8. Click on Save Integration button and you are done.

Now start integration

Platform: Select Google Sheets.

Task: Select Add New Row. New section will appear were you can select the spreadsheet, worksheet name and map fields.

Spreadsheet: Select the correct sheet name.

Worksheet: You may have several tabs in the sheet. All names will be automatically populated in the dropdown. Select the correct one.

Map Fields: After selecting the worksheet/tab all table column headers will appear and you will be able to map correct fields. Please note that table titles must be in first row of the sheet. Otherwise AFI plugin can’t recognize the titles.

Common Problems and Solutions

Error 403: access_denied The developer hasn’t given you access to this app. It’s currently being tested and it hasn’t been verified by Google

Go to Google API projects, and select the project from the upper dropdown menu. Then go to the OAuth consent screen menu. Check Publishing status. If it is on testing, click the button PUBLISH APP.

Empty Spreadsheet or Worksheet dropdown

Two types of accesses are needed for full operation. Google Drive API access is needed to read and populate the spreadsheet list. And Google Sheets API access for getting the details of a sheet, like tabs list. So check your Google API project library that both Google Drive API & Google Sheets API were enabled and scopes added.

After I click the Save & Authorize button, the page just reloads and nothing happens

Please go to the Google App Permissions page and look for a previous authorization for this app and click on REMOVE ACCESS. Then try authorizing again.

After I click the Save & Authorize button, getting a 404 page

Go to
WordPress admin > Settings > Permalinks
. Now select Post name and save the changes. Try authorizing again.

Is there any usage limit?

The plugin itself doesn’t impose any limit. However, Google Sheets API has limits like a maximum of 500 requests per 100 seconds. Please check the Google Sheets Usage Limit page for more details.

 

Data added to wrong column

Column shifting occurs when there are columns in the table without titles. To fix this, either remove those columns or give them titles. After that, visit the integration page and adjust the field mappings. If you prefer not to send data to those columns, you can leave them blank in the integration settings, but make sure they still have titles.