Using Google Sheets as your CMS

Spreadsheets. They are everywhere.

Managers especially seem to love them, which is why the world properly would stop functioning without them.

Like some twisted version of Murphy's law, it seems that anything that can fit into a spreadsheet eventually will end up in a spreadsheet.

This can result in abominations like aMicrosoft Excel database for Covid-19 results.

Well, following in the spirit of using spreadsheets other ways than intended, I thought - what if we could use a Google Sheet as a CMS?

Hold on. Hold on. Lower your pitchforks and torches - this is merely a proof of concept to see how it could work. However... there might be some use cases for this - stick around to the end to find out.

So first thing first, how do we do it in a performant way? We could set up a script to generate a static site, which is something other developers also have played around with before.

However, we don't want tohave totrigger a build each time we push a change in our sheet. We want to be able to use SSR, ISR, or CSR in a performant way.

What we want to do is decouple our Google Sheets so that it only works as a CMS and not a database.

Luckily, decoupling data is where Enterspeed really shines.

So let's get to it.

Step 1: Set up a Google Sheet

First, start by creating a Google Sheet. In our example, we're going to make a list containing all the movies from the best movie series in the world.

No, not Marvel or Star Wars. I'm of course talking about the one and only: Olsen Banden (The Olsen Gang).

The list contains the following columns:

• The number it has in the series (Number)
• The movies name (Name)
• Which year it was produced (Year)
• A link to the movie cover image (Image)
• A link to its IMDB page (IMDB)
• A short description (Description)

All content above is shamelessly copied fromIMDB.com.

Now that we have all of our data, it's time for step 2 - setting up a script that ingests all the data into Enterspeed.

Step 2: Set up a Google Apps Script

Inside your Google Sheet, click onExtensionsand selectApps script.

First, we create a function calledingestToEnterspeed.

Next, we declare two const to make the code easier to read:

• sheetwhich equalsSpreadsheetApp.getActiveSheet()- this grabs our active sheet.
• sheetDatawhich equals*sheet.getDataRange().getValues()*this grabs all the data from the active sheet.

Now, we take oursheetData-*const and run a*forEachfunction. We grab the index (i) of each, so we can:

• Skip the first row, since this is the header
• Know which row number to grab data from

Since our row numbers, unlike our index, don't start at 0, we declare a const calledrowNumberwhich equalsi + 1.

Next, we declare a const for theingest URL to Enterspeed(ingestUrl). The part after /v2/ will be our originId inside Enterspeed and needs to be unique.

Since we're only going to be ingesting "Olsen banden"-movies, we will simply use "olsen-banden-" + the movie's number in the series - here found in column A. We could also have made a function that grabs the sheet name and combines it with the row number instead, to make it more dynamic.

The function we use to grab the cells content is:sheet.getRange('A' + rowNumber).getValue()- this takes the active sheet, finds the defined column for the defined row number and gets the value.

Now, we create an object calledpayload, which specifies what we will send to Enterspeed.

In the object, we need to define three properties:

• A type (in our case we simply call it 'movie')
• A URL, if we want it to be routable (if not, we can simply writenull)
• Properties, which is our actual content

Our URL follows the same pattern as our originId "olsen-banden-" + the movie number.

Inside the properties object, we define all the data which we wish to use. The name of the keys can be whatever you like - to keep it simple we just matched the name of our columns.

Then we use the same function as before to grab the content from the cell we want.

Once we have defined our data (payload), we need to make anoptionsobject, which we will use in our fetch function.

Create amethodproperty and set it topost. Then create aheadersobject where you define ax-api-keyproperty. Insert the API key for your data source, which you have created inside Enterspeed. Lastly, create apayloadproperty and insertJSON.stringy(payload).

Next, for and giggles we can choose to log out each row inside Google Apps Script, to see what's going on, usingLogger.log('Ingesting: ' + row).

Now, it's time to ingest the data using the build-inUrlFetchApp.*Use the*fetch*method and set*ingestUrl*and*optionsas parameter:UrlFetchApp.fetch(ingestUrl, options);

ClickSave projectand then clickRun. You should now be able to see your content inside Enterspeed. Hooray

As a bonus, we can create a function that adds a menu item to your Google Sheets menu. This makes it easier to ingest data from the sheet.

Google Apps Scripts even support triggers, which means you can set it up to ingest at time-driven events. That's pretty neat.

Now that we got all of our data into Enterspeed, it's time to create a schema that generates the view we wish to use.

Step 3: Create a schema in Enterspeed

Open Enterspeed and create a new schema.

We're going to create a collection schema, which generates a single view for all the movies.

Since we have ingested all of our rows individually (with the URL property), we also have the option to make separate pages for each movie.

However, for now, we will simply make a collection schema.

We start by setting up our triggers, where we define which data source it should use - and which source entity type (the*type*we defined in our payload object earlier).

We then choose how we should be able to "get the content" - this can be either via a handle or via a URL. In our case, we choose a handle calledmovies.

In the properties-object is where we define the actual content. We create a property calledmovies, which have the typearray. In theinputproperty, which is where we define the items collection we want to work with, we use the$lookupand thefiltermethod to find source entities with the typemovie.

For ease of use, we set the collection iteration variable name tomovieinstead of the defaultitemusing thevarproperty.

Underitems, which is used for mapping results, we choose to grab all the data and wrap them in an object calledmovie. We're able to grab all the data by usingdynamic mapping.

That's it. Then we save the schema and deploy it to the environment we wish.

All that's left now is to fetch the data from the frontend.

Step 4: Fetching the data

Now to actually show the data.

We fetch it using theEnterspeed Delivery API. We call the URL and add the handle we wish to fetch (heremovies):https://delivery.enterspeed.com/v1?handle=movies

In our headers, we insert our Environment Client API key in theX-Api-keyproperty.

Afterward, we iterate over all the movies and insert them into a card component (in this case we have usedBootstrap).

Voil. We have successfully used Google Sheets as a CMS - but is this merely a proof of concept, or?

Google Sheets as a CMS - madness?

So could Google Sheets work as a CMS? In my opinion, yes.

Now, I know what you're thinking:"Madman! You're a madman! No man, developer or client, should use Google Sheets as a CMS!"

But oh, I've chosen my words carefully.

(P.s. if you don't get the reference -here you go).

Which CMS you should use all depends on the use case. Should you use Google Sheets for a blog? Properly not. A full fledge company website? Also no.

But say you are a small caf, that often switches up its menu and wants to display the dish of the day. Or a small soccer club, that wishes to showcase all their players. Then yes, it could definitely work.

Since we decouple the data from the actual Google Sheets, it doesn't really matter performance-wise where you edit your data.

Also, Enterspeed allows you to transform and combine multiple data sources, which means you could use Google Sheets only for a small section of your site - e.g. your team section.

So the moral of the story is - once you have decoupled your data, it doesn't really matter how/where you edit it.

I hope you enjoyed the article. You can find all the code used here:https://github.com/enterspeedhq/enterspeed-demo-google-sheets