Automatically import a Google Drive Sheet (CSV) into TablePress using Automatic Periodic Table Import
To keep this post short-and-sweet, which will be the aim for future articles, I am experimenting with the Situation, Task, Action, Result (STAR) method for this write-up. Do let me know if you think it works or not in the comments or the contact form.
Situation
A TablePress table listing sport fixture results (e.g. football, hockey etc.) requires regular updating.
This had been done by one person using an (offline) Excel spreadsheet and manually importing the .csv file. But the job has now been shared amongst a team of three (in different locations), so maintaining an offline spreadsheet no longer seemed practical.
Thus it made sense to create and maintain a single version on Google Sheets that could be edited by all.
But exporting and importing the file requires extra action. Could this process be automated?
Task
Automatically and periodically import a Google Sheet into TablePress to replace a table of fixture results
Action
- Download, install and enable the TablePress Automatic Periodic Table Import extension. Please note this is a premium plugin and so I would strongly encourage you to make a donation to Tobias Bäthge if you find it useful (the current recommended amount is $18).
- Login to Google Docs and open the sheet you wish to export.
- Click on File → Share → Anyone with the link can view to ensure the extension can access the sheet; simply enabling File → Publish to the web does not always work for the .csv format as this only makes the HTML output public, but not the .csv version.
- Copy the URL of the sheet, which should have the following format (where YOUR_DOCUMENT_KEY is the primary ID of your Google Sheet):
https://docs.google.com/spreadsheets/d/YOUR_DOCUMENT_KEY/edit?usp=sharing - Paste the URL into the Source field for the relevant table and replace:
edit?usp=sharing
with
export?format=csv
- The final format is as follows:
https://docs.google.com/spreadsheets/d/YOUR_DOCUMENT_KEY/export?format=csvUPDATE with props to Alessandro: The above will import the first sheet (‘Sheet1’) or gid=0 so if you are looking to import a specific tab/sheet in a multi-sheet document, you will need to identify the GID=YOUR_GID_KEY reference and include it in the url.
The final format of a specific sheet is as follows:
https://docs.google.com/spreadsheets/d/YOUR_DOCUMENT_KEY/export?format=csv&gid=YOUR_GID_KEY - Click the check-box to activate the Auto Import, set how frequently this should occur and save changes.
Result
A single online spreadsheet hosted on Google Docs/Sheets editable by multiple authors, which is then automatically and periodically imported into TablePress to replace a table of fixture results.
Demo: https://www.wpsnippets.com/tablepress-auto-import-using-google-sheets/
References
- WordPress.org TablePress Support – Auto-Import Google Drive Spreadsheet
- WordPress.org TablePress Support – Automatic Periodic Table Import + Google Sheets
- New Google Spreadsheets publish limitation comment by s4tori
- CSV Auto Fetch using Google Drive Spreadsheet – Step 2
- New Google Spreadsheets publish limitation comment by brilletju
Thanks! Works great. (and STAR method works as well )
@Steven: Many thanks for your comment. I am delighted to hear that the Google import tutorial was helpful to you (and that STAR works too! :D) If you have any suggestions for this article or ones you might like to see on this site in future, please do feel free to let me know.
Not sure if it’s an issue with the plug-in or Google Docs, but it’s only possible to import the first sheet of a spreadsheet this way. Followed all the instructions and tried twice, but it only imported the first sheet…
Thanks for this article, very useful.
I’ve found how to import a specific sheet and even a specific range in the sheet on this stack overflow answer http://stackoverflow.com/a/23394860/666223
Auto import failed ?
I have placed the same url in the source type that i used to import the table first.
when i import a table it works fine,but get failed error with auto import.
Btw i am using google spreadsheet to test it.
@Asim: I have tested it again today and Auto Import seems to be working fine, where clicking through to “TablePress -> Import a Table” in the WP-Admin shows me when the csv files were last imported. Can you share the url format that you have been using for your test please? Thanks
@Alessandro: Thanks for the link. I have just tested it and can confirm this format
https://docs.google.com/spreadsheets/d/YOUR_DOCUMENT_KEY/export?format=csv&gid=THE_SHEET_GIDworks so will update the article to reflect this 🙂Very helpful, and I like the STAR approach. Just to note that you can test your url before submitting to Tablepress by copying and pasting the link into a browser. If coded correctly, it should download a .csv file. Mine did.
@Ralph: Thanks for your comment – appreciate the tip on testing the URL and am glad you found the article helpful!
That “Source” field is in the Import page of TablePress, correct? When I choose Import Source URL it shows me an input field Datei-URL (data/file url). That’s where I insert my modified Google Spreadsheet link.
After saving I can no longer edit that source entry, correct?
The URL is automatically inserted as Table Name and Description, changing that won’t affect the URL to the spreadsheet?
Kind regards
Boris
This saved me a lot.. I mean a lot of time and effort. thank you very much.
@Fatmir, glad you found it helpful and thank you for taking the time to comment and let me know, it is appreciated!
Hi @Flick
How can i display the Last Update Date & time like your demo?
(Last updated on: 2020-11-18 00:46:50)
Hi R-Web, thanks for your question. I generate this using the following code, where you’ll need to change the # to your table ID:
[table-info id=# field="last_modified" format="raw" /]Hope this works for you as well.