Next.js 14 with Google Sheets (The Easy Way)

Next.js 14 with Google Sheets (The Easy Way)

Posted By

kamlesh paul

on

Dec 8, 2024
Copy Paste

Sometimes, you just need to save some data into Google Sheets quickly and efficiently without going through the hassle of setting up Google’s Sheets API and SDK. If you’re looking for a fast and straightforward way to handle data storage in your Next.js 14 application, then this tutorial is for you!

In this guide, we’ll explore how to integrate Google Sheets with a Next.js 14 application. This powerful combination allows you to use Google Sheets as a simple backend for storing and retrieving data, making your Next.js application more dynamic and interactive.

Table of contents

Setting Up Google Sheets and Apps Script

Step 1: Create a Google Sheet

  1. Open Google Sheets and create a new spreadsheet.
  2. Rename the first sheet to TODO for easy reference.
  3. Add headings: Task and IS_DONE.

Step 2: Create an Apps Script

  1. Click on Extensions in the menu and select Apps Script.
  2. Replace the default code with the following script.
 const sheets = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/<UniqueId>/edit?gid=0#gid=0");
 
 
 //if you have changed your sheet name then replace the below Sheet1 with your sheet name
const sheet = sheets.getSheetByName("Sheet1");
 
 
function doGet(e) {
  var data = sheet.getDataRange().getValues();
  
  return ContentService
    .createTextOutput(JSON.stringify({
      status: true,
      message: "Fetched Successfully.",
      data
    }));
}
 
function doPost(e) {
  const { task, is_done } = e.parameter;
 
  sheet.appendRow([task, is_done]);
 
  return ContentService
    .createTextOutput(JSON.stringify({
      status: true,
      message: "Saved Successfully.",
      data: e.parameter
    }));
}
  1. Replace <UniqueId> with your sheet’s unique ID.
  2. Save the script and deploy it as a web app.
  3. Click on Deploy > New Deployment.
  4. Select Web app and set the permissions to Anyone.
  5. Copy the Web app URL for use in your Next.js project.

Setting Up the Next.js 14 Application

Step 1: Create a New Next.js Project

  • Open your terminal and create a new Next.js project:
npx create-next-app@14 google-sheets-nextjs
cd google-sheets-nextjs

Step 2: Create API Functions

  1. Create a file lib/api.ts and add the following code to handle API requests:
lib/api.ts
const API_URL = 'https://script.google.com/macros/s/<id>/exec';
 
 
export interface ITodo {
  task: string,
  is_done: boolean
}
 
export const fetchData = async (): Promise<ITodo[]> => {
  try {
    const response = await fetch(API_URL);
    if (!response.ok) {
      throw new Error('Network response was not ok');
    }
    const data = await response.json();
    return data.data as ITodo[];
  } catch (error) {
    console.error('Error fetching data:', error);
    return [];
  }
};
 
export const addData = async (payload: FormData): Promise<ITodo[]> => {
  try {
    const response = await fetch(API_URL, {
      method: "POST",
      body: payload
    });
 
    if (!response.ok) {
      throw new Error('Network response was not ok');
    }
    const data = await response.json();
    return data.data as ITodo[];
 
  } catch (error) {
    console.error('Error adding data:', error);
    return [];
  }
};
  1. Replace <id> with your Web app ID from Apps Script.

Building the Frontend

Step 1: Create a Data Display Component

  1. Create a file components/Todo.tsx .
components/Todo.tsx
import React from "react";
 
const Todo = ({ todos }: { todos: any[] }) => {
 
  return (
    <div>
      {todos.length === 0 ? (
        <p className="text-gray-500">No tasks found.</p>
      ) : (
        <ul className="space-y-2">
          {todos.slice(1).map(([task, is_done], index) => (
            <li key={index} className="border border-gray-300 p-2 rounded">
              <span className={`task ${is_done ? "line-through" : ""}`}>
                {task}
              </span>
            </li>
          ))}
        </ul>
      )}
    </div>
  );
};
 
export default Todo;

Step 2: Fetch Data and Display in Home Page

  1. Update the app/page.tsx.
app/page.tsx
"use client";
 
import { useEffect, useState } from "react";
import { addData, fetchData, ITodo } from "./api";
import Todo from "./components/Todo";
 
export default function Home() {
  const [todos, setTodos] = useState<any[]>([]);
  const [form, setForm] = useState<ITodo>({
    task: "",
    is_done: false
  });
  const [loading, setLoading] = useState(false);
 
  useEffect(() => {
    const getData = async () => {
      setLoading(true);
      try {
        const data = await fetchData();
        setTodos(data);
      } catch (error) {
        console.error("Error fetching data:", error);
      } finally {
        setLoading(false);
      }
    };
    getData();
  }, []);
 
  const handleSubmit = async (e: any) => {
    e.preventDefault();
 
    setLoading(true);
    try {
      const formData = new FormData();
      formData.append('task', form.task);
      formData.append('is_done', form.is_done ? 'true' : 'false');
 
      await addData(formData);
      const updatedData = await fetchData();
      setTodos(updatedData);
      setForm({ task: "", is_done: false });
    } catch (error) {
      console.error("Error adding data:", error);
    } finally {
      setLoading(false);
    }
  };
 
  return (
    <div className="container mx-auto p-4">
      <h1 className="text-2xl font-bold mb-4">Next.js 14 with Google Sheets</h1>
      <Todo todos={todos} />
 
      {loading && (
        <div className="fixed inset-0 flex items-center justify-center bg-gray-100 bg-opacity-50">
          <div className="bg-white p-4 rounded shadow-md">Loading...</div>
        </div>
      )}
 
      <form onSubmit={handleSubmit} className="mt-4">
        <div className="flex flex-col space-y-2">
          <input
            type="text"
            value={form.task}
            onChange={(e) => setForm({ ...form, task: e.target.value })}
            placeholder="Task"
            className="border border-gray-300 p-2 rounded w-full"
          />
          <label className="flex items-center space-x-2">
            <input
              type="checkbox"
              checked={form.is_done}
              onChange={(e) => setForm({ ...form, is_done: e.target.checked })}
              className="form-checkbox"
            />
            <span className="text-sm">Completed</span>
          </label>
          <button
            type="submit"
            className="bg-blue-500 text-white p-2 rounded"
            disabled={loading}
          >
            Add Task
          </button>
        </div>
      </form>
    </div>
  );
}
  1. Update gloabl.css.
@tailwind base;
@tailwind components;
@tailwind utilities;

Now run npm run dev

and open http://localhost:3000/

It should look like something this.

nextjs 14 with google sheet.webp

It will add data realtime like below.

nextjs 14 with google sheet 1.webp

Your app should look like this

nextjs 14 with google sheet 2.webp

Conclusion

By following these steps, you’ve successfully integrated Google Sheets with a Next.js 14 application using Google Apps Script.

Share this article

26 views