I recently had the opportunity to attempt to create a simple little CMS for a single page on a website hosted on a Linux box somewhere out there in the cloud. We wanted to offer the client (who had no experience or patience to work in HTML) the ability to update the content on this page. So, with Google’s search engine as my reference material (who needs to buy programming books any more?) I launched into writing some PHP code that will read a published Google Spreadsheet and treat each of the sheets as a table.
Kinda nifty stuff!
Of course, if we are going to do this, I’d prefer the customer use their own spreadsheet so we aren’t held responsible for failure to ensure the existence of this spreadsheet and it’s content. So, here are the steps required to properly publish a Google Spreadsheet that can be consumed by another website:
Create your Google Spreadsheet – In this case, we’ve got 5 columns that contain information to be used in an unordered list typical of a Resources (links) page.
Publish your Google Spreadsheet – This is the “hardest step” we’d be asking of our client. It entails pulling down the Share button at top right of spreadsheet and selecting Publish as a web page.
Configure Published Data Link - In our scenario, we are publishing All Sheets, Automatically republishing this data when changes are made and we are NOT requiring a login to view this data (making it publicly visible). Once you’ve pressed the “Start Publishing” button, the bottom half of the form will become active, allowing you to create the correct link to the data in a format that can be programmatically consumed by our target web page. In this case, we want to select CSV data and we are only interested in the “Resource Links” sheet in this spreadsheet. We are taking all available cells. Copy and paste the provided URL and you are good to consume this data via the programming language of your choice.
“But Mike”, you say… “Why not just publish it as HTML to embed in a page” using the aptly named option on this dialog box? Good question. In this case, we want this data to be tied back directly to our client’s site rather than having the content iframed in from spreadsheets.google.com. Better to have this quality content showing up on the clients site for SERP and Page Rank purposes.
“But Mike”, you stammer again… “Why not use the GData APIs that have been written for PHP?” Another good question. In this case, I don’t know if the customer’s hosting environment has the API installed (it installs as an add-on to PHP) nor do I know if the host company will do this for our client even if we begged them to. So this provides us with a nice, lightweight way to address this in any hosting environment that supports later versions of PHP (anything greater than 4.02).
And that’s all there is to it. Perhaps if you are good, and ask really nicely, I’ll make the time to post the source code to consume this data.
At Over The Top and Exhibit A Communications, I've programmed solutions for Google as well as at least one other company that was later acquired by Google.
I've been CTO of an Internet SaaS company and spent my time pretty evenly between guiding the future technical strategy of the company, architecting software solutions for my dev teams, designing and running a data center to service our clients world-wide as well as being a technical evangalist/sales engineer to our media clients large and small.
I've also been Chief Photographer of the Daily Sun/Post newspaper back when it was a 5-day a week daily newspaper.
I also spent a great many years as a beach lifeguardfor the City of San Clemente as well as Jr. Lifeguard instructor and then as it's program coordinator.
Bragging rights
Private Pilot with Instrument rating and proud husband of soon to be 25 years (and counting).
- Setting up SSL on Amazon Linux Instance under EC2 - July 26, 2018
- Method Chaining of Objects in C# - January 16, 2017
- Native SQL Backup And Restores on AWS RDS - November 9, 2016