DEV Community

Kwan Sing
Kwan Sing

Posted on

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

Top comments (0)