DEV Community

Kwan Sing
Kwan Sing

Posted on

Google Sheets as CMS with NextJS.

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:
GoogleSheetCMS

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);
}
Enter fullscreen mode Exit fullscreen mode

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

5. On select type, click Web app
AppScriptDeployment

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

7. Click Deploy.

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

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

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 });
}

Enter fullscreen mode Exit fullscreen mode
// .env
GOOGLESHEET_API=AKfycbzWiN2AVyBLOdZz2t604BFWYdz3RDbfmFuI6XnR1QsswAScajuBiS7DSJqXTIgKCddc

Enter fullscreen mode Exit fullscreen mode

dataFromLocalhostAPi

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>
  );
}

Enter fullscreen mode Exit fullscreen mode

uiDataWithoutStyle

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>
  );
}

Enter fullscreen mode Exit fullscreen mode

uiWithData

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.

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more →

Top comments (0)

Image of Docusign

🛠️ Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more