{"id":41819,"date":"2021-02-24T16:21:01","date_gmt":"2021-02-24T16:21:01","guid":{"rendered":"http:\/\/icloud.pe\/blog\/?guid=6397a31f7178f4717d1c0f8b65ee3762"},"modified":"2021-02-24T16:21:01","modified_gmt":"2021-02-24T16:21:01","slug":"how-to-build-a-cms-with-react-and-google-sheets","status":"publish","type":"post","link":"https:\/\/icloud.pe\/blog\/how-to-build-a-cms-with-react-and-google-sheets\/","title":{"rendered":"How to build a CMS with React and Google Sheets"},"content":{"rendered":"


\n Jessica Cregg<\/a><\/span>
\n <\/span><\/p>\n

\n
\n
24 Feb, 2021<\/span><\/div>\n<\/p><\/div>\n<\/div>\n

\n<\/a><\/p>\n

\n

\u200b<\/span>Launching a website can feel like a landmark moment. It\u2019s the digital age equivalent of hanging an \u2018Open For Business\u2019 sign in your window. If your proverbial sign is static and rarely requires updates – for example, if it\u2019s simply intended to communicate basic operating practices like opening hours, contact information and a list of services – then you can likely build a simple site in HTML and CSS<\/a>, and wrap the project there. But for most businesses, something far more dynamic is required.<\/p>\n

As the term implies, a Content Management System, or CMS, is a structure that enables users to model, edit, create and update content on a website without having to hard-code it into the site itself. Many businesses use off-the-shelf CMS tools<\/a> like WordPress, Joomla, Magento or Squarespace to handle this function.<\/p>\n

When it comes to pin-pointing the factors that really make a really good CMS stand out, the most successful out-of-the-box solutions share one common trait – they\u2019re easy to interpret. For any CMS, its success as a product completely relies on how easy it is for teams to create, manage and update logical data structures. The more pain-free this process is, the easier it becomes for teams to create robust content models. That being said, as any engineer will tell you, the implementation of relational databases<\/a> is in itself its own art form.\u00a0<\/p>\n

The problem with these out-of-the-box solutions is that they can often include far too many features, cater to completely different levels of tech literacy, and what\u2019s more, can make it far too easy for you to rack up an expensive bill<\/a>. An alternative option is to build your own custom CMS that\u2019s designed to meet your specific requirements.<\/p>\n

While building the thing that you need is often viewed as the scenic route to solving your problem, when tackled the right way, it can turn out to be the quickest solution. An excellent way of streamlining a project with the potential for infinite scope-creep, while putting any information that\u2019s lying dormant in a spreadsheet to good use, is to use Google Sheets<\/a> as the foundation for your CMS.\u00a0<\/p>\n

Despite the best efforts of AirTable, Notion and even Smartsheets, there\u2019s a reason why spreadsheets have stood the test of time<\/a> as a widely accepted file format. Chances are, no matter the skill level or aversion to technology, most people within a business<\/a> are going to know how to navigate their way around a spreadsheet.\u00a0<\/p>\n

In the following tutorial, you’ll see how using popular JavaScript tools<\/a> including the Node.js framework\u00a0 and React library, you can stand up a dynamic web application built right on top of Google Sheets. We\u2019ll create a Node project to act as a container for our API queries<\/a>, and then harness React to parse our data, which will then be presented and served to the user via a dynamic front-end. Here we\u2019ll be taking advantage of React\u2019s tendency to abstract away a lot of the internal complexities of our application\u2019s inner workings, along with its reusable components. The latter feature is perfect if you\u2019re building out a broad website with a multitude of pages that you\u2019d like to all have a consistent look and feel.\u00a0<\/p>\n

For this example, we\u2019ll be using the \u2018Meet the Team\u2019 page for a fictional technology enterprise business. If you\u2019d like to use the example data<\/a> we\u2019re using for the purposes of this demonstration, you\u2019ll find the spreadsheet linked below.\u00a0<\/p>\n

<\/drupal-entity><\/p>\n
\n
\n
<\/div>\n<\/div>\n<\/div>\n

<\/span><\/p>\n<\/div>\n

What you\u2019re essentially going to do is access your spreadsheet as an API, querying it in order to pull out the content for your front-end React page. We\u2019ll be accessing the data from the sheet in JSON format, and then using the dotenv package in order to store the credentials needed to access the information from our Node project. This will parse the information and feed it through to the front-end, presenting it in a far more polished and stylised format.\u00a0<\/p>\n

First up, let\u2019s use the terminal to generate a new project from which we\u2019ll be working from. Create a new directory for your project by running \u2018mkdir\u2019 followed by your project name, and then step into that directory using the \u2018cd\u2019 command. To create our Node project we\u2019ll firstly run \u2018$ npm init -y\u2019 from the terminal, before creating two additional files we need to get up and running, using the \u2018touch\u2019 command.\u00a0<\/p>\n

One of these is our .env file which will contain any sensitive keys, credentials or variable settings we\u2019ll need in order to access our Sheet. Remember to keep this section in your .gitignore if you decide to share your repository publicly to prevent your keys from being deactivated and your credentials from being stolen. The last step, as illustrated in the code snippet below, is to install a few external packages we\u2019ll be using in our project. We\u2019ve covered off dotenv, and the googlesheetsapi is no surprise. The final one is Express, which we\u2019ll be using as our Node.js framework due to its lightweight nature and its ability to quickly spin up servers.<\/p>\n\n\n\n
$ mkdir project-name<\/p>\n

$ cd project-name<\/p>\n

$ npm init -y<\/p>\n

$ touch index.js .env<\/p>\n

$ npm i express google-spreadsheet dotenv<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Once you\u2019ve installed your external packages, open up your node project in your preferred text editor and initialise your server using the below code snippet:\u00a0<\/p>\n\n\n\n
require(“dotenv”).config()<\/p>\n

const { GoogleSpreadsheet } = require (“google-spreadsheet”)<\/p>\n

const { OAuth2Client } = require(‘google-auth-library’);<\/p>\n

const express = require(“express”)()<\/p>\n

\u00a0\/\/ process.env global var is injected by Node at runtime<\/p>\n

\/\/ Represents the state of the sys environment<\/p>\n

const p = process.env<\/p>\n

\u00a0\/\/ Set up GET route in Express<\/p>\n

express.get(‘\/api\/team\/’, async (request, response) => {<\/p>\n

\u00a0\u00a0response.send(“hello world”)<\/p>\n

})<\/p>\n

\u00a0\/\/ Express listener, on port specified in .env file<\/p>\n

express.listen(p.PORT, () =><\/p>\n

\u00a0\u00a0console.log(`Express Server currently running on port ${p.PORT}`)<\/p>\n

)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Here, we\u2019re essentially calling the packages which allow us to access the spreadsheets object, and on line six, we\u2019re setting a variable of \u2018p\u2019 that creates a shortcut to process.env. Essentially, this will represent the state of our system environment for the application as it starts running. By adding a shortcut, we\u2019ll be able to effectively access the spreadsheets object with far less effort.\u00a0<\/p>\n

The rest of the program is initialising our express GET route (a.k.a. how we\u2019ll be querying our sheet) and setting up a listen function in place to assist with our build. This will give us a handy prompt as to which port the express server is running on while we\u2019re working to connect our React front-end to the application.\u00a0\u00a0\u00a0\u00a0<\/p>\n

Lastly, head into your .env file and assign your port number as per below in plain text, \u201cPORT=8000\u201d and hit save.\u00a0<\/p>\n

Run node index.js from the root in your terminal, and you should see the listen console.log message appear stating which port your server\u2019s currently running on – in this case, it\u2019s port 8000.\u00a0<\/p>\n

<\/drupal-entity><\/p>\n
\n
\n
<\/div>\n<\/div>\n<\/div>\n

<\/span><\/p>\n<\/div>\n

If you head to your browser and access the port that your application is running on, you\u2019ll notice that your GET method is failing. Let\u2019s fix that.\u00a0<\/p>\n

At the moment, we\u2019ve got our method for querying our data established. We now need to get the data flowing through our server. The next step here is to assemble your Google Spreadsheet with both the headings and the content you\u2019d like your node project and React app to pull through. If you\u2019d like to follow along, feel free to make a copy of the \u2018Meet the Team\u2019 spreadsheet we\u2019ve created.\u00a0<\/p>\n

The first thing you\u2019re going to need to lift is your SPREADSHEET_ID. You can find this by copying the long string (or slug) found in the URL following the \/d\/. For example, in this case, our slug is \u201c1f7o11-W_NSygxXjex8lU0WZYs8HlN3b0y4Qgg3PX7Yk\u201d. Once you\u2019ve grabbed this string, include it in your .env file under SPREADSHEET_ID. At this stage, your .env file should look a little something like this:\u00a0<\/p>\n

<\/drupal-entity><\/p>\n
\n
\n
<\/div>\n<\/div>\n<\/div>\n

<\/span><\/p>\n<\/div>\n

Next, head to the Google Sheets Node.js Quickstart<\/a> page to enable the Google Sheets API. You\u2019ll need to click the blue \u2018Enable\u2019 button before naming your project, selecting \u2018Web Server\u2019 and entering your localhost URL when configuring the 0auth values.\u00a0<\/p>\n

If executed correctly, this step will have enabled the Google Sheets API from your Google Cloud Platform (GCP) account. To confirm that\u2019s happened, simply head over to your GCP console<\/a> and you\u2019ll spot your project in your profile page.\u00a0<\/p>\n

To authenticate this exchange of data, we\u2019ll need to generate an API key. This will authorise your app to access your Google Drive, identify your Spreadsheet via the SPREADSHEET_ID, and then perform a GET request to retrieve the data to be parsed and displayed on your React front-end.\u00a0<\/p>\n

To get hold of your API key, you\u2019ll want to navigate to the credentials section of the GCP console<\/a>, click blue “+ CREATE CREDENTIALS” and select the “API key” option.\u00a0<\/p>\n

<\/drupal-entity><\/p>\n
\n
\n
<\/div>\n<\/div>\n<\/div>\n

<\/span><\/p>\n<\/div>\n

Once your key\u2019s been generated, copy it and add it into your .env file under API_KEY.\u00a0<\/p>\n

Perfect! Now let\u2019s initialise and use the key within our code to authenticate our Google Sheets query. In the snippet below, you\u2019ll notice that we\u2019re using the await operator to coincide with the async function initiated at the beginning of the index.js program shown earlier in this tutorial. To view the complete code as a reference, you can head here<\/a> to review and even clone the repository.\u00a0<\/p>\n

Now we\u2019ve authorised our sheets object, it\u2019s time to minify the data. This is a process by which we remove any unnecessary, superfluous data from the JSON object, so that we\u2019re left with only the most vital sections of the object which we\u2019ll model within our React front-end.\u00a0<\/p>\n

Head to the index.js file of the linked repository<\/a> and you\u2019ll see how we\u2019ve been able to do this. We start off with an empty array, and then we iterate through the rows in the sheet assigning a key corresponding to the column header, and match the value to that of the cell data for that particular row.<\/p>\n

<\/drupal-entity><\/p>\n
\n
\n
<\/div>\n<\/div>\n<\/div>\n

<\/span><\/p>\n<\/div>\n

If you\u2019re familiar with React, then you\u2019ve more than likely used the package create-react-app<\/a> which is by far one of Facebook\u2019s greatest gifts to the world of application development. With one command, you\u2019re able to spin up an instance giving you the file structure and most of what you need to get going out of the box.\u00a0<\/p>\n

The \u2018create-react-app\u2019 command generates a locally hosted, single-page React application that requires no configuration in order to get going. When you run the command, create-react-app will run local checks on your target directory, builds your package.json, creates your dependency list and forms the structure of your bundled JS files.\u00a0<\/p>\n

Let\u2019s kick off this process by running the following at the root of our application:<\/p>\n\n\n\n
$ npx create-react-app client<\/p>\n

$ cd client<\/p>\n

$ npm start<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

If you run into any problems with the version of npm\/npx that you\u2019re running, then the below modified command with an added bit of cache clearance should steer you right:\u00a0<\/p>\n\n\n\n
$ npm uninstall -g create-react-app && npm i -g npm@latest<\/a> && sudo npm cache clean -f && npx create-react-app client<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

If your command has run successfully, you\u2019ll start to see create-react-app install its required packaged and build out the below file structure.\u00a0<\/p>\n

<\/drupal-entity><\/p>\n
\n
\n
<\/div>\n<\/div>\n<\/div>\n

<\/span><\/p>\n<\/div>\n

Lastly, you\u2019ll notice that your application now has two package.json files. We\u2019ll need to make an edit to the scripts section in the root, and then add one line below the \u2018private\u2019 section of your client directory. This is so that you can fire everything up with one simple command – the eternal saviour that is npm start.\u00a0<\/p>\n

In your root:<\/p>\n\n\n\n
“start”: “node index.js -ignore ‘.\/client’ “<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

And in your client:\u00a0<\/p>\n\n\n\n
“proxy”: “http:\/\/localhost:8000<\/a>“<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Now with one run of npm start you\u2019ll be met with your React front-end pulling through information from your Google Sheets CMS.\u00a0<\/p>\n

If you check out the back-end server (running on port 8000) in your browser, you\u2019ll be met with a JSON object displaying your CMS data in its raw, unformatted form.\u00a0<\/p>\n

<\/drupal-entity><\/p>\n
\n
\n
<\/div>\n<\/div>\n<\/div>\n

<\/span><\/p>\n<\/div>\n

But, more importantly for the many stakeholders eager to access your newly-built site, here\u2019s what you\u2019ll see displayed on the front-end.\u00a0<\/p>\n

<\/drupal-entity><\/p>\n
\n
\n
<\/div>\n<\/div>\n<\/div>\n

<\/span><\/p>\n<\/div>\n

What was once entries on a spreadsheet, is now a fully-formatted, somewhat shiny React web app, ready to be deployed through your favourite hosting service. The great thing about this solution is that it can be incorporated into your wider React-based application, and styled using one of many free themes and templates<\/a> out there, meaning that your final output can actually scale with you. There you have it – yet another reason to love spreadsheets.<\/p>\n

\u200b<\/span> <\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"

Jessica Cregg<\/p>\n

24 Feb, 2021 <\/p>\n

\u200bLaunching a website can feel like a landmark moment. It\u2019s the digital age equivalent of hanging an \u2018Open For Business\u2019 sign in your window. If your proverbial sign is static and rarely require…<\/p>\n","protected":false},"author":653,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[],"class_list":["post-41819","post","type-post","status-publish","format-standard","hentry"],"_links":{"self":[{"href":"https:\/\/icloud.pe\/blog\/wp-json\/wp\/v2\/posts\/41819","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/icloud.pe\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/icloud.pe\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/icloud.pe\/blog\/wp-json\/wp\/v2\/users\/653"}],"replies":[{"embeddable":true,"href":"https:\/\/icloud.pe\/blog\/wp-json\/wp\/v2\/comments?post=41819"}],"version-history":[{"count":1,"href":"https:\/\/icloud.pe\/blog\/wp-json\/wp\/v2\/posts\/41819\/revisions"}],"predecessor-version":[{"id":41820,"href":"https:\/\/icloud.pe\/blog\/wp-json\/wp\/v2\/posts\/41819\/revisions\/41820"}],"wp:attachment":[{"href":"https:\/\/icloud.pe\/blog\/wp-json\/wp\/v2\/media?parent=41819"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/icloud.pe\/blog\/wp-json\/wp\/v2\/categories?post=41819"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/icloud.pe\/blog\/wp-json\/wp\/v2\/tags?post=41819"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}