Using Google Sheets as a CMS

Many times websites are maintained by people who themselves are not developers. Say for example a small restaurant running a website to get prospective clients to come eat out or order food online.

Editing spreadsheets is usually much simpler than editing code - by placing for example the images used on a website in a google sheet and then pointing the image source in the website code into a specific cell of that spreadsheet anyone with editing access to the spreadsheet can make updates and keep the content relevant. This saves time, money and helps to create more up to date and engaging web experiences for e-commerce sites.

Google Sheets as a CMS offers a cost-effective solution for real-time content updates. With its familiar spreadsheet interface, easy collaboration, and seamless integration with other Google Workspace tools, Google Sheets simplifies website content management. In this blog post, we will explore how Google Sheets can help website content management, provide step-by-step instructions on setup and usage, and share practical tips and best practices for creating dynamic website content.

We are going to use the sheet to store values for images and texts displayed on our website. If we want to make changes to any of the content we just have to navigate to our sheet, change the respective cells and the content will be updated. Alright enough theory let's dive right in.

Step 1: Set up your Google Sheet

Simply create a new google sheet, from your Google Docs account. There is a free version available if you don't have access to a Google workspace organization.

Next navigate to file/share/Publish to web and click on that.

Next you want to select the comma separated values option and hit 'Publish'.

Now anything you write into this sheet will be accessible from the web, also from any browser frontend. We will take advantage of that to build a very basic CMS based on this sheet.

Now you need to pick a structure for the type of content you want to make dynamic later, depending on the complexity you might want to work with different columns for different types or pages etc. Keep in mind you will need reference the specific cells later on. We will use a very simple structure for this example:

The page we are going to use to test this approach is from our How to build beautiful, fully customizable landing pages in vanilla python in minutes.

As you can see we are going to make the background image and the hero text dynamic. The value placed in the respective cells will be transferred 1 to 1 as a string to the website. For images we can point to images placed locally in our website code folder like /background.webp or we could point to any image file https link. You can also upload an image to your Google drive, publish it similar like you did with the sheet and copy the share link into your sheet.

Step 2: Insert dynamic values to your website code

This step depends somewhat on the framework used for your website. We are using Python's requests library to pull the values from the sheet. It will be very similar in Javascript or PhP. Technically this also works for low-code applications like Wordpress, but that would be somewhat besides the point, changing text and images doesn't require code changes anyways.

We store the url to our spreadsheet in an environment variable, just to keep that out of the codebase and somewhat less exposed publicly.

Next you will want to append '&range=YOUR_CELL' to each text or image element you want to pull from the sheet.

SheetURL = os.getenv('SHEET_URL')

bg = requests.get(SheetURL+'&range=B1').text
print(bg)
hero1 = requests.get(SheetURL+'&range=B2').text
hero2 = requests.get(SheetURL+'&range=B3').text
hero3 = requests.get(SheetURL+'&range=B4').text

Next we will reference them in the code, in this case our pynecone app.

"""This is an example of a product presentation landing page built in pynecone"""
from pcconfig import config

import pynecone as pc
import requests
import os

SheetURL = os.getenv('SHEET_URL')

bg = requests.get(SheetURL+'&range=B1').text
print(bg)
hero1 = requests.get(SheetURL+'&range=B2').text
hero2 = requests.get(SheetURL+'&range=B3').text
hero3 = requests.get(SheetURL+'&range=B4').text

filename = f"{config.app_name}/{config.app_name}.py "


class State(pc.State):
    """The app state."""

    pass


def index() -> pc.Component:
    return pc.center(
		pc.grid(
			pc.grid_item(
				pc.heading(hero1, font_size="4em", color="white"),
				pc.heading(hero2, font_size="4em", color="white"),
				pc.heading(hero3, font_size="4em", color="white"),
				pc.text("Welcome to Olive.Co! We pride ourselves in the quality of our olives.",
						color="white"),
				pc.text("The purest olive oil you will find. Period.", 
						color="white"),
				pc.button("Try it now", color="white", size="lg", variant="outline", 
						  margin_top="2em",
					),
				row_span=1, col_span=1, align_self="center", gap=2),
			pc.grid_item(
				pc.image(src="/olive.webp", width="14em"),
				row_span=1, col_span=1),
			template_columns="1fr 1fr",
			gap=0,
		),		
        padding_top="10%",
		##background_image="/background.webp",
		background_image=bg,
		background_position="center",
		background_size="cover",
		background_repeat="no-repeat",
		height="100vh"
    )


# Add state and page to the app.
app = pc.App(state=State)
app.add_page(index)
app.compile()

That's it. The benefit in this very simple example is limited as making the changes in the code also isn't too complicated but at least we can make changes to website content without touching the code now.

For small restaurants etc. this can be a real game changer, easy to setup and maintain, cheap to run and suitable for less technical people maintaining and updating the website.

Our full working example is hosted on Codesphere and can be viewed here: https://39413-3000.2.codesphere.com/