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.
Top comments (0)