Next.js调用Google Sheets API追加数据无效果问题求助

尝试通过API将表单数据追加到Google Sheets工作表，Payload格式正确，请求返回200状态码，但工作表未接收到完整数据。

网络面板响应信息 #

/spreadsheetID/=placeholder

data: {spreadsheetId: "/spreadsheetID/", tableRange: "Sheet1!A1:E1",…} spreadsheetId: "/spreadsheetID/" tableRange: "Sheet1!A1:E1"

updates: {spreadsheetId: "/spreadsheetID/", updatedRange: "Sheet1!A2"} spreadsheetId: "/spreadsheetID/" updatedRange: "Sheet1!A2"

Form.tsx传递的Payload #

{"name":"Mike","email":"mike@gmail.com","company":"fckspreadsheets","phone":"2435444244","comments":"testie"}

相关代码 #

route.ts #

import { google } from "googleapis";
import { NextApiRequest, NextApiResponse } from "next";
import { NextResponse } from "next/server";

type SheetForm = {
  name: string;
  email: string;
  company: string;
  phone: string;
  comments: string;
};

export async function POST(req: NextApiRequest, res: NextApiResponse) {
  if (req.method !== "POST") {
    return res.status(405).send({ message: "Only POST allowed" });
  }

  const body = req.body as SheetForm;

  try {
    const auth = new google.auth.GoogleAuth({
      credentials: {
        client_email: process.env.GOOGLE_CLIENT_EMAIL,
        private_key: process.env.GOOGLE_PRIVATE_KEY?.replace(/\\n/g, "\n"),
      },
      scopes: [
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/drive.file",
        "https://www.googleapis.com/auth/spreadsheets",
      ],
    });

    const sheets = google.sheets({
      auth,
      version: "v4",
    });

    const response = await sheets.spreadsheets.values.append({
      spreadsheetId: process.env.GOOGLE_SHEET_ID,
      range: "Sheet1",
      valueInputOption: "USER_ENTERED",
      requestBody: {
        values: [
          [body.name, body.email, body.company, body.phone, body.comments],
        ]
      },
    });

    return NextResponse.json({
      data: response.data,
    },
    { status: 200 }
  );
  } catch (e) {
    console.error(e);
    return NextResponse.json(
      { error: "Internal Server Error" },
      { status: 500 }
    );
  }
}

Form.tsx #

"use client";

import { motion } from "framer-motion";
import React, { FormEvent, useState } from "react";

type Props = {};

export default function Form({}: Props) {
  const [name, setName] = useState("");
  const [email, setEmail] = useState("");
  const [company, setCompany] = useState("");
  const [phone, setPhone] = useState("");
  const [comments, setComments] = useState("");

  const handleSubmit = async (e: FormEvent<HTMLFormElement>) => {
    e.preventDefault();
    const form = {
      name,
      email,
      company,
      phone,
      comments,
    };

    const response = await fetch("/api/submit", {
      method: "POST",
      headers: {
        Accept: "application/json",
        "Content-Type": "application/json",
        "Access-Control-Allow-Origin": "*",
      },
      body: JSON.stringify(form),
    });

  
    const content = await response.json();

    console.log("Response json: ", content);

    alert(content.data.tableResponse);
  };

  return (
    <motion.div
      initial={{ opacity: 0 }}
      whileInView={{ opacity: 1 }}
      transition={{ duration: 1.5 }}
      className="flex flex-col relative h-screen text-center md:text-left md:flex-row max-w-7xl px-10 justify-evenly mx-auto items-center"
    >
      <h3 className="absolute top-24 bottom-24 uppercase tracking-[20px] text-gray-400 text-2xl">
        Contact Us
      </h3>
      <motion.form
        initial={{
          x: -200,
          opacity: 0,
        }}
        transition={{
          duration: 1.0,
        }}
        whileInView={{
          x: 0,
          opacity: 1,
        }}
        viewport={{
          once: true,
        }}
        className=" z-50 lg:pl-36 lg:min-w-full lg:mx-auto lg:items-center "
        onSubmit={handleSubmit}
      >
        <input
          value={name}
          onChange={(e) => setName(e.target.value)}
          type="text"
          name="name"
          placeholder="Full Name"
          id="name"
          className="input mb-3 md:mr-5 bg-gray-700 input-bordered w-full max-w-xs lg:max-w-md"
        />
        <input
          value={email}
          onChange={(e) => setEmail(e.target.value)}
          type="text"
          name="email"
          placeholder="Email"
          id="email"
          className="input mb-3 input-bordered bg-gray-700 w-full max-w-xs lg:max-w-md"
        />
        <input
          value={company}
          onChange={(e) => setCompany(e.target.value)}
          type="text"
          name="company"
          placeholder="Comany Name"
          id="company"
          className="input mb-3 md:mr-5 input-bordered  bg-gray-700 w-full max-w-xs lg:max-w-md"
        />
        <input
          value={phone}
          onChange={(e) => setPhone(e.target.value)}
          type="text"
          name="phone"
          placeholder="Phone #"
          id="phone"
          className="input mb-3 input-bordered w-full  bg-gray-700 max-w-xs lg:max-w-md"
        />
        <textarea
          value={comments}
          onChange={(e) => setComments(e.target.value)}
          id="comments"
          name="comments"
          className="textarea mb-3 textarea-bordered bg-gray-700 w-full lg:w-[55vw] lg:mr-20 items-center "
          placeholder="Comments"
        ></textarea>
        <button
          type="submit"
          className="text-gray-50 bg-[#0b4042] hover:bg-[#0e5557] focus:ring-4 focus:ring-gray-800 font-medium rounded-lg text-sm px-5 py-2.5 mt-3 md:mt-0 dark:hover:bg-[#0e5557] focus:outline-none dark:focus:ring-gray-700"
        >
          SUBMIT
        </button>
      </motion.form>
    </motion.div>
  );
}

排查与修复方案 #

1. 路由类型不匹配（核心问题） #

你的route.ts混用了Next.js Pages Router的NextApiRequest/NextApiResponse和App Router的NextResponse，导致请求体无法正确解析，最终写入Sheets的是空值。

修正后的route.ts：

import { google } from "googleapis";
import { NextResponse } from "next/server";

type SheetForm = {
  name: string;
  email: string;
  company: string;
  phone: string;
  comments: string;
};

export async function POST(req: Request) {
  const body = await req.json() as SheetForm;

  try {
    const auth = new google.auth.GoogleAuth({
      credentials: {
        client_email: process.env.GOOGLE_CLIENT_EMAIL,
        private_key: process.env.GOOGLE_PRIVATE_KEY?.replace(/\\n/g, "\n"),
      },
      scopes: [
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/drive.file",
        "https://www.googleapis.com/auth/spreadsheets",
      ],
    });

    const sheets = google.sheets({
      auth,
      version: "v4",
    });

    const response = await sheets.spreadsheets.values.append({
      spreadsheetId: process.env.GOOGLE_SHEET_ID,
      range: "Sheet1!A:E", // 明确指定列范围，确保数据写入正确列
      valueInputOption: "USER_ENTERED",
      requestBody: {
        values: [
          [body.name, body.email, body.company, body.phone, body.comments],
        ]
      },
    });

    return NextResponse.json({
      data: response.data,
    }, { status: 200 });
  } catch (e) {
    console.error(e);
    return NextResponse.json(
      { error: "Internal Server Error" },
      { status: 500 }
    );
  }
}

2. 修正前端响应处理 #

Form.tsx中alert(content.data.tableResponse)的字段不存在，会导致报错，改成有效字段：

// 替换原alert行
alert(`数据已写入：${content.data.updates.updatedRange}`);

3. 验证环境变量与权限 #

• 确认GOOGLE_SHEET_ID是正确的工作表ID（从URL中提取纯ID字符串，不要带路径）
• 确认服务账号（GOOGLE_CLIENT_EMAIL）已被添加为该工作表的编辑者
• 检查GOOGLE_PRIVATE_KEY的格式是否正确，确保转义后的\\n已替换为真实换行符

内容的提问来源于stack exchange，提问作者sundaydriveguy