Google Sheets as CMS with NextJS.
Tags: googlesheets, tutorial, learning, cms
Published: 2024-03-30 15:24:01

## 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ba203bymm9aw26myiova.png)
 
3\. Click Extension > App Script. Copy the following script.
```js
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.
![AppScriptSS](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1zrr9409khe6osnet6fa.png)



5\. On select type, click Web app
![AppScriptDeployment](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w9t9h0ev9d29v95lqsp7.png)

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

7\. Click Deploy.

8\. After deployment success, you will get Deployment ID and URL
![AppScriptSuccess](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/52ypzbtsqtbpgno8mk7t.png)

9\. Opening this url on a new tab should display the json data.
![datafromGoogleSheetAPI](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/c6zoj8aeeicery8evbhk.png)

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
```tsx
// 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 });
}

```
```ts
// .env
GOOGLESHEET_API=AKfycbzWiN2AVyBLOdZz2t604BFWYdz3RDbfmFuI6XnR1QsswAScajuBiS7DSJqXTIgKCddc

```
![dataFromLocalhostAPi](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cvdbgj2pvz942cyd1p3b.png)

3\. Create a page to retrieve the data
```tsx
// 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>
  );
}

```
![uiDataWithoutStyle](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/oolccsz2po6uuh63jyg4.png)

4\. Add some style with Tailwind
```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 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>
  );
}

```
![uiWithData](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6ot0h6msyhetwc03e0yh.png)

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.