Use Google Sheets as a CMS

April 20, 2021

But first, why?

Very easy to edit
Can access the sheet from any device
Sharing it with other people
Version control history

1. Create a sheet

I created this sheet about Dota 2 heroes info:

Sheet title is: heroes

also remember the sheet id:

https://docs.google.com/spreadsheets/d/[sheet-id]/edit#gid=0

2. Setup Google Cloud API

Go to Google API Console -> select project -> create a new project.

Go to Menu -> API & Services -> Library, select “Google Sheets API” and click “Enable”.

Now you need to create the credentials to authenticate & access the data.

Go to Menu -> API & Services -> Credentials -> Create Credentials -> Service account key.

Next, you will need to select a role: Current user -> Owner is good.

Next, you need to create a key, select service account -> Keys -> Add keys -> Create new key -> JSON.

Save the downloaded file as credentials.json

Now you need to grant the service account email permission to read and write on the sheet, go back to the sheet, click on Share -> add the account email address.

3. Read data from the sheet

Now we have everything ready to start building an app to read our data in the sheet.

I created a new simple Node app and published it here.

First, copy the credentials.json file to the project directory.

Then, create an .env file to store our GOOGLE_SHEET_ID (id from the sheet URL) and GOOGLE_SHEET_TITLE (“heroes”).

Here is the main script (index.js):

imports:

require("dotenv").config();
const { GoogleSpreadsheet } = require("google-spreadsheet");
const fs = require("fs");
const creds = require("./credentials.json");

authenticate and read the sheet content:

const doc = new GoogleSpreadsheet(process.env.GOOGLE_SHEET_ID);
await doc.useServiceAccountAuth(creds);
await doc.loadInfo();
console.log(`Sheet: ${doc.title}`);

const sheet = doc.sheetsByTitle[process.env.GOOGLE_SHEET_TITLE];
console.log(`Title: ${sheet.title}`);

await sheet.loadHeaderRow();
const rows = await sheet.getRows();
const formattedRows = rows.map((row) => ({
  Name: row.Name,
  Attribute: row.Attribute,
  "Attack type": row["Attack type"],
}));

writeContent(formattedRows);

write content to a file:

const writeContent = (rowData) => {
  const content = `
    const Heroes = ${JSON.stringify(rowData)};
    export default Heroes;
  `;

  console.log("Writing to file...");

  fs.writeFile("output/index.js", content, function (err) {
    if (err) return console.log(err);
  });

  console.log("DONE!");
};

add a script in package.json

"scripts": {
  "start": "node index.js"
}

run the script

output file

So we did successfully use Google Sheets API as our private CMS, from here, we can modify the sheet and run the app again to see the changes.

Now we can explore the API to add more functionalities like write, delete, update the cells, rows… of the sheet with convenience.

Reference

https://blog.usejournal.com/how-to-use-google-sheets-as-a-cms-or-a-database-f9d8e736fdce