Google Sheets as CMS with NextJS.

#googlesheets #tutorial #learning #cms

Google Sheets to CMS

1. Go to https://docs.google.com/spreadsheets/ and create a new sheet.

2. Put some data in the google sheet as follow:

3. Click Extension > App Script. Copy the following script.

function doGet() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues();

  // set row 1 as headers
  const headers = data[0];

  let arr = [];
  if(data.length > 0) {
    for (let i = 1; i < data.length; i++) {
      const row = data[i];
      const record = {};
      for (let j = 0; j < row.length; j++) {
        record[headers[j]] = row[j];
      }
      arr.push(record);
    }
  }
  return ContentService.createTextOutput(JSON.stringify(arr))
    .setMimeType(ContentService.MimeType.JSON);
}

4. On the top right, click Deploy > New deployment.

5. On select type, click Web app

6. Description - ''
Execute - me
Who has access - Anyone

7. Click Deploy.

8. After deployment success, you will get Deployment ID and URL

9. Opening this url on a new tab should display the json data.

10. Next we can create NextJS or any frontend framework to fetch this API.

Creating the frontend

1. pnpm create next-app

2. create an api route to fetch the data

// src/app/api/getData/route.ts

export async function GET() {
  const apiKey = process.env.GOOGLESHEET_API;
  const res = await fetch(`https://script.google.com/macros/s/${apiKey}/exec`);
  const data = await res.json();
  return Response.json({ data });
}

// .env
GOOGLESHEET_API=AKfycbzWiN2AVyBLOdZz2t604BFWYdz3RDbfmFuI6XnR1QsswAScajuBiS7DSJqXTIgKCddc

3. Create a page to retrieve the data

// src/app/page.tsx

import Image from "next/image";

export default async function Home() {
  const res = await fetch("http://localhost:3000/api/getData");
  const data = await res.json();
  return (
    <div>
      {data.data.map((x) => (
        <div>
          <div>{x.header}</div>
          <div>{x.data}</div>
        </div>
      ))}
    </div>
  );
}

4. Add some style with Tailwind

import Image from "next/image";

export default async function Home() {
  const res = await fetch("http://localhost:3000/api/getData");
  const data = await res.json();
  return (
    <div className="flex gap-4">
      {data.data.map((x) => (
        <div className="p-4 y-4 flex flex-col border">
          <div>{x.header}</div>
          <div>{x.data}</div>
        </div>
      ))}
    </div>
  );
}

5. Success!.

Thoughts

Pros: Free, simple and fast to spin up a CMS for a project.
Cons: Updating the content, requires new deployment and update the deployment ID as API key in our application.