Creating Tables in WordPress from Google Spreadsheets

Video versionA video overview of creating wpDataTables from Google Spreadsheets

wpDataTable Google Spreadsheet integration

NEW! 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. More on this link.

Google Spreadsheets is an extremely popular way to create and edit tables online because of its advanced functionality. It’s free, it does not require installing any software on a client’s computer, it supports importing data from popular office software programs, and it stores all the data online. We could not of course ignore this format, so with version 1.6 wpDataTables now supports Google Spreadsheets as an input data source.

See this example (published and shared Google spreadsheet), and the table rendered from this Google Spreadsheet below.

The step-by-step tutorial on creating wpDataTables from Google Spreadsheets is below the table.

Please note: Once you create a table from a Google Spreadsheet, changes in the core file from which a table is created, like:

  • Changing the column’s name,
  • Changing the column’s order,
  • Deleting the column, and
  • Adding a new column.

are highly not recommended, because the old table will not work, at the moment. If you make any of these changes, please recreate the table again.

When using Google Spreadsheets as the data source please note:
1. By default, the data is read from the data source every time the page loads, so when your tables are large (more than 2.000 – 3.000 cells) the pages with large tables will load slowly, or even crash after reaching a certain size.

However, with the new caching option that was implemented in wpDataTables 5.0, this issue is resolved for large tables linked to an existing data source.

2. Please note that while using this method, there is cache on Google’s side, and it may take up to 15 minutes for new or edited data to appear in the table linked to the Google Spreadsheet file. Please check out how you can use Google Sheets API on this link in order to avoid caching issues.
3. Formatting applied in the original file (font families, color, size, fill color, etc.) will not be copied over to the wpDataTable, but you can make whatever adjustment you may need in WpDataTables.
4. Tables linked to an existing Google Spreadsheet are not editable, nor are they saved in the database.
5. If you have an SSL certification error after you copy the Google Spreadsheet link to create a table, open the file ../wp-content/plugins/wpdatatables/source/class.wdttools.php, and around line 202 you will find a setting from cURL. Please add the following lines:

curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, false);
curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);

For more detailed instructions on using SSL, please check out this FAQ thread.

Instructions to create the table

Open Google Spreadsheets, find the spreadsheet you would like to use in wpDataTables; or create one.

To create a new Google Spreadsheet click the “plus” button in the right bottom corner:

Once you’ve opened the Google Spreadsheets editing mode, you can enter the data that you need. Alternatively, you can copy&paste the table data from your favorite software – e.g., MS Excel, Numbers, or LibreOffice.

GSheets-publish

The data is auto-saved on the Google Spreadsheets side whenever you change it, but to be able to use it in wpDataTables you first need to publish it.

Go to File -> Share -> Publish to web 

Publish link for Google Spreadsheet

then choose Entire document, and click Publish to get the link. Note: Please, don’t copy the link that is first shown in Publish section.

Instead, copy this link from your browser, since you will use it in wpDataTables (the links aren’t same, and with the first one you will not be able to create the table).

Google changed their API recently and block direct access on already published sheets.

What you need to do in your Google sheet is to share this sheet with anyone.

On your already published sheet, you need to click on the button Share from the top right corner of your sheet.

On the modal that pops up, click on the Change to anyone with link text.

  • If you are going to use a Google Spreadsheet with formulas, please follow this – Tutorial
  • Keep in mind that wpDataTables doesn’t support Google Spreadsheet with merged cells.
  • Also, don’t apply any formatting in this new sheet. You can make whatever adjustment you may need in WpDataTables.

Create wpDataTable from a Google Spreadsheet

Once the Google spreadsheet is prepared, the next step is to go to your WordPress admin and create a new wpDataTable:

Create data table from source

1. Go to wpDataTables -> Create a Table, and choose Create a table linked to an existing data source option.

2. Set a name for your new wpDataTable to help you to find the table later.
3. Choose Google Spreadsheet as the Input data source type.
4. Paste the spreadsheet link that you copied in step 1 to the Input file or URL input field.

Once you do this, click “Save Changes“. wpDataTables will read the table content and columns structure, and try to guess the column data types.

(OPTIONAL) - define additional table and columns settings

For any wpDataTable, you can define any settings you need for the table itself, and for all the columns. The table might be responsive or not, it may or may not have table tools or advanced filtering; you can choose the filter types for each column, etc. In our example we set the filter type for Language column as “Select box”

You can dynamically filter the table by using placeholders as predefined filtering values.

Insert the wpDataTable in your page or post

wpDataTable shortcode

When a wpDataTable is created, you can use it in a post or page.

Create a post or page, or open a post/page that you already have for editing.

Pick a place where you would like to paste your table and place the cursor there; then, click the “Insert a wpDataTable” button, and pick the table that you created in step 2:

As an alternative, you can simply copy & paste the generated wpDataTable shortcode.

When you save the post or page and open it in the front-end, you will see the wpDataTable with the data from your Google Spreadsheet.

If you are using Gutenberg editor, Elementor, or WPBakery page builder, you can check out the section for Adding wpDataTables shortcodes on the page.