🚀 Supercharge Your App with Google Apps Script + Google Sheets Integration

If you’re building modern web applications, chances are you’ve needed a simple backend for tasks like storing form submissions, feedback requests, or lightweight data logs. Instead of spinning up a server or managing databases, what if you could just use Google Sheets as your backend? Enter Google Apps Script (GAS)—your secret weapon for lightweight serverless backends.

Let’s dive in.

---

💡 What is Google Apps Script?

Google Apps Script is a JavaScript-based platform that lets you automate tasks across Google Workspace—including Sheets, Docs, Gmail, and more. It runs on Google’s servers, so you don’t have to worry about hosting, scaling, or maintenance. For small to medium-sized apps, it's a brilliant, cost-free solution.

Here’s what we’ll build:
✅ A feedback submission system
✅ A Google Sheets backend powered by Apps Script
✅ A frontend in Next.js that talks to the backend

---

🌐 The Architecture

Here’s the system flow:

[User Feedback Modal]
          |
          V
[Next.js API Route (/api/submit-feedback)]
          |
          V
[Google Apps Script Webhook]
          |
          V
[Google Spreadsheet (Icons Requests)]

---

Example:

1️⃣ User clicks "Request Icon"
2️⃣ Feedback Modal opens (powered by Zustand)
3️⃣ User submits form
4️⃣ API call hits Google Apps Script
5️⃣ Google Sheets logs the request
6️⃣ You review icon requests in a simple, structured spreadsheet

---

📊 Step 1: Set Up Google Apps Script with Google Sheets

1️⃣ Create a Google Sheet

• Open Google Sheets

• Create a new sheet, name it something like "Icons Requests"

• In the sheet, add these column headers:

  • Timestamp

  • Icon Name

  • Description

2️⃣ Create the Apps Script Backend

• In your sheet, go to Extensions > Apps Script.

• Replace the default code with this:

const SHEET_ID = 'YOUR_SHEET_ID_HERE'; // Replace with your actual Sheet ID
const SHEET_NAME = 'YOUR_SHEET_NAME'; // Replace with your actual sheet name (Bottom-left corner)

function doPost(e) {
  try {
    const sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName(SHEET_NAME);
    const data = JSON.parse(e.postData.contents);

    sheet.appendRow([new Date(), data.iconName, data.description || '']);

    return ContentService.createTextOutput(JSON.stringify({ result: 'success' }))
      .setMimeType(ContentService.MimeType.JSON);
  } catch (error) {
    return ContentService.createTextOutput(JSON.stringify({ result: 'error', error: error.message }))
      .setMimeType(ContentService.MimeType.JSON);
  }
}

function doGet(e) {
  return ContentService.createTextOutput('Hello from Google Apps Script!');
}

• Deploy the script:
  Go to Deploy > New deployment
  Choose Web app, give it a name, set access to Anyone, and deploy.
  Copy the URL—you’ll need it for the frontend!

---

🌍 Step 2: Add Webhook URL in .env

Add the webhook URL in your Next.js project:

GOOGLE_SHEET_WEBHOOK_URL=https://script.google.com/macros/s/your-script-id/exec

Never hardcode this in your code. Always use environment variables for security.

---

🧩 Step 3: Connect Your Next.js App to Google Sheets

Here’s a minimal FeedbackModal component that connects to your Apps Script backend:

"use client";

import { useEffect, useState } from "react";
import { Dialog, DialogContent, DialogTitle, DialogDescription, DialogClose } from "@/components/ui/dialog";
import { useFeedbackStore } from "@/lib/useFeedbackStore";
import { Button } from "./ui/button";
import { toast } from "sonner";

type FeedbackModalProps = { requestedIcon?: string };

export default function FeedbackModal({ requestedIcon }: FeedbackModalProps) {
  const { isOpen, close } = useFeedbackStore();
  const [iconName, setIconName] = useState(requestedIcon || "");
  const [description, setDescription] = useState("");
  const [loading, setLoading] = useState(false);
  const [error, setError] = useState("");

  useEffect(() => { setIconName(requestedIcon || ""); }, [requestedIcon]);

   const handleSubmit = async (e) => {
    e.preventDefault();
    setLoading(true);

    try {
      const response = await fetch("/api/submit-feedback", {
        method: "POST",
        headers: { "Content-Type": "application/json" },
        body: JSON.stringify({ iconName, description }),
      });

      const result = await response.json();
      if (result.result === "success") {
        toast.success("Feedback submitted!");
        setIconName(""); setDescription(""); close();
      } else {
        throw new Error(result.error || "Submission failed.");
      }
    } catch (error: any) {
      toast.error(error.message);
    } finally {
      setLoading(false);
    }
  };


  return (
    <Dialog open={isOpen} onOpenChange={close}>
      <DialogContent className="max-w-md">
        <DialogTitle>Request an Icon</DialogTitle>
        <DialogDescription>Please provide details about the icon you are looking for.</DialogDescription>
        <form onSubmit={handleSubmit} className="space-y-4">
          <div>
            <label htmlFor="iconName">Icon Name <span className="text-red-600">*</span></label>
            <input id="iconName" value={iconName} onChange={(e) => setIconName(e.target.value)} placeholder="e.g., Next.js" className="input" required />
          </div>
          <div>
            <label htmlFor="description">Description</label>
            <textarea id="description" value={description} onChange={(e) => setDescription(e.target.value)} placeholder="Any other details or use cases" className="textarea" rows={3} />
          </div>
          {error && <p className="text-red-600 text-sm">{error}</p>}
          <div className="flex justify-end space-x-3 pt-4">
            <DialogClose asChild><Button type="button" variant="destructive">Cancel</Button></DialogClose>
            <Button type="submit" disabled={loading}>{loading ? "Submitting..." : "Submit"}</Button>
          </div>
        </form>
      </DialogContent>
    </Dialog>
  );
}

---

🔗 Step 4: Connect Frontend to Backend

Here’s how the Next.js API route (/api/submit-feedback) sends feedback to your Google Apps Script:

// pages/api/submit-feedback.ts
export default async function handler(req, res) {
  if (req.method !== 'POST') return res.status(405).end();

  try {
    const response = await fetch(process.env.GOOGLE_SHEET_WEBHOOK_URL!, {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify(req.body),
    });

    const result = await response.json();
    res.status(200).json(result);
  } catch (error) {
    res.status(500).json({ result: 'error', error: error.message });
  }
}

---

🗃️Step 5: Zustand Store for Modal State

Simple state management for the feedback modal using Zustand:

import { create } from 'zustand';

interface FeedbackState {
  isOpen: boolean;
  open: () => void;
  close: () => void;
}

export const useFeedbackStore = create<FeedbackState>((set) => ({
  isOpen: false,
  open: () => set({ isOpen: true }),
  close: () => set({ isOpen: false }),
}));

---

🎯 Conclusion

This system lets you collect user feedback directly into your Google Sheets, using a combination of:

✅ Google Apps Script for webhook handling
✅ Next.js API routes for frontend/backend communication
✅ Zustand for state management in React
✅ Environment variables for configuration

---

🛡️ Why Use Google Apps Script?

✅ No Backend Hassle: No servers, no scaling headaches, no costs (up to quota).
✅ Integrates with Google Sheets: Your data is viewable, filterable, and exportable in Sheets.
✅ Rapid Prototyping: Build MVPs or internal tools fast.
✅ Secure: Authenticated via Google, no external DB leaks.

---

🚀 The Big Picture

By combining Google Apps Script + Google Sheets + Next.js frontend, you create a lightweight but powerful system:

• Google Apps Script acts as your backend.

• Google Sheets acts as your database.

• Next.js provides a sleek frontend.

This is the kind of lean architecture that helps you ship fast and stay agile—especially in early-stage projects or when testing ideas.