WP Data Sync

The only data sync API for WordPress websites.

Developer Documentation

Filed Under:

Google Sheets

Connecting Google Sheets to your WP Data Sync API portal can be accomplished in a few easy steps. In this tutorial, we assume you have already created a Google Sheet and connected your website to the WP Data Sync API.

Steps to connect Google Sheets

  • Grant WP Data Sync permission to access your Google Sheets (private sheets only)
  • Create a Data Source to manage the connection with your Google Sheet

Grant WP Data Sync permission to access your Google Sheets

To grant permission, visit the Connected Apps page in your account. Please note, you must have an active plan to access this page.

  1. Click the Connect Now button for Google Sheets API
WP Data Sync - Connected Apps

2. Follow the steps to grant access to your Google Sheets account. Once you are done, you will see the connected message.

WP Data Sync connected apps connected

Create a Data Source to manage the connection with Google Sheets

You will need to setup a new Data Source using a Google Sheet as the Source Type. Follow the usual steps to create a data source. If you have not created a data source review the documentation for data source mapping.

Choose Source type

WP Data Sync - Google Sheets Source Type

Google Sheet Name and ID

To obtain the Google Sheet name, look at the bottom of the Google Sheet.

WP Data Sync Google Sheet Name

The Google Sheet ID is in the URL of the Google Sheet. Use this portion of the URL {THIS_PORTION_OF_URL}. It is generally about 40+ random characters.

https://docs.google.com/spreadsheets/d/{THIS_PORTION_OF_URL}/edit#gid=789654123

Once you have the name and ID of the Google Sheet. Paste these into your Data Source settings. Save your changes.

WP Data Sync Google Sheets name and ID

Mapping Your Data Source for Google Sheets

Mapping your data source for a Goggle Sheets is the same process as any other source type. However, Google Sheets does not provide a primary ID. If you do not have a field that is unique like product SKU, UPC code, or email address. You can create a column or combine columns of the Google Sheet to create a primary ID. Follow the steps below.

Create a Primary ID for Google Sheets

While a unique primary ID is required for WP Data Sync to relate your items together, this step is optional. You can combine fields like Timestamp, Name, Email, etc… to create a unique primary ID.

For this example, we will use theTimestamp to create the primary ID.

Create the Primary ID Column

  1. Click to add a new column at A1 of your Google Sheet
  2. Paste the formula into the A1 column. This will create the PrimaryID label and all PrimaryIDs for you. (see screenshot below)

Google Sheets PrimaryID Formula

=ArrayFormula({"PrimaryID";if(len(B2:B),text(B2:B,"yyyy-mm-dd-hh-mm-ss"),)})

NOTE: This formula creates a primary ID based on the timestamp. Therefore, if you have multiple entries at exactly the same second in time, the ID will not be unique. You do have the ability to combine fields in the data source to create a unique primary ID.

Google Sheet screenshot

Final Steps to Connect a Google Sheet

  1. Complete the required field mapping
  2. Activate the Data Source

Within a few minutes, you should see the data from the Google Sheet start to populate into the Available Items in your WP Data Sync API portal.