## 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 (

{data.data.map((x) => (

{x.header}

{x.data}

))}

); } ``` ![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 (

{data.data.map((x) => (

{x.header}

{x.data}

))}

); } ``` ![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.

kwansing

Google Sheets as CMS with NextJS.

Join Our Newsletter. No Spam, Only the good stuff.