How to connect WordPress tables with Google Sheets API?

Video version

Step by step instructions

Say no more to cache issues! It is possible to automatically sync your already created, published and shared Google Sheets in wpDataTables. Furthermore, you can create WordPress tables with your Private Sheets as well.

To enable this feature, please follow the instructions below on how to connect WordPress tables with Google Sheets API.

The first step is to access the Google API dashboard over this link. When you access it, please click on the “Select a project” button as is shown on the image on the left.

After clicking on the “Select a project” button, the pop-up window will show up. What you need to do next is to click on the “New project” button in the top right corner as it is shown on the image on the left.

Now, you need to give your project a name (for this demo we will name it “Connect to Sheets API“). When you finish, please click on the “Create” button.

After creating the project, you need to enable APIs for this project. Please, click on the “Library” in the left side menu as is shown on the image.

Next, you need to enable Google Drive API. Please, click on the Google Drive API section as is shown on the image on the left.

Click on the “Enable” button.

After that, you will be redirected to Google Drive API Overview. We need to enable one more API so please return to the Google API dashboard by clicking on the “Google APIs” in the top left corner.

After that, click on the “Library”, once again.

Then, you need to enable Google Sheet API. Please click on the Google Sheet API section as is shown on the left image.

Click on the “Enable” button.

After that, you will be redirected to Google Sheet API Overview. To continue, please return to the Google API dashboard by clicking on “Google APIs” in the top left corner.

If you have done everything correctly, the APIs you have enabled will be listed as on the image on the left.

After that, we need to create credentials for this project. Please click on “Credentials” in the top left menu.

To create credentials for this project, please click on the “CREATE CREDENTIALS” button.

In the drop-down menu, please choose “Service account” as is shown on the image on the left.

Then, you will need to set your Service account name (in our case, we set ‘wpDataTables connection to Sheets API’). When you finish, please click on the “Create” button.

When you finish with Service account details, you will need to select a role for this account. Please click on “Select a role” and in the drop-down menu choose Editor as is shown on the left image.

When you finish with the role, please click on the “CONTINUE” button.

To finish creating a service account, please click on the “DONE” button.

Nice job! Your service account is now created.

Please click on your service account like on the image on the left, to get JSON data.

Afterwards, you will need to add a key for this service account. Please, click on the “ADD KEY” drop-down button.

In the drop-down menu, please choose option “Create new key”

The popup modal will show up, there you will choose key type before creating a private key for your service account. The key type needs to be JSON (it is already selected so just leave it as is) and then, click on the “CREATE” button.

After creating the private key for your service account, the file will be downloaded on your computer in JSON format.

It will have a format like is shown on the image on the left. You will need to copy all this data from the file.

When you copy data from that file, please go to your server where wpDataTablesis installed:

1. Click on “Settings” in the wpDataTables admin menu,

2. switch on tab Google Spreadsheet settings.

You will need to paste data that you copied from the JSON file in the field “Google service account data”.

When you finish,  please click the ‘Save’ button under this field.

If everything is right,  your Google service account email will be shown. It will be used for connection with your published and shared sheets.

By clicking on the “Remove account” button you can remove this account from wpDataTables anytime.

Instructions how to create tables from Private Google Sheets

When you finish all steps needed for creating your Google service account email, you will be able to create tables from Private Sheets (not published or shared with everyone).

Below are instructions on how to do that.

Copy the Google service account email from wpDataTables Google Spreadsheet settings.

Go to your Google spreadsheets that are not published or shared with everyone and click on the “Share” button.

In the field “Add people and groups” insert/paste your Google service account email that you copied from wpDataTables settings. When you finish, click “Save” button.

After that, uncheck the option “Notify people” and then click on the “Share” button.

Now this private sheet is shared only with your Google service account email and you will be able to create a table in wpDataTables just by providing a sheet URL from the browser when you Create a table from Google Spreadsheet.

Please note, you don’t need to publish it on the web or to share it with everyone.