Google Sheets API only working locally

Question

Hi all,I'm creating a custom webhook using NextJS's api route to save data from a form to a Google Sheets file.I've seen several posts with errors being thrown, but in my case, I get no error although the data isn't being appended to the Google Sheet.The following code works as expected locally./api/webhook.jsimport { SaveData } from "@/lib/googlesheetsapi";

const GoogleSheetID = process.env.GOOGLE_SHEET_ID;

export default function handler(req, res) {
  if (req.method === "POST") {
    try {
      SaveData(GoogleSheetID, req.body);
      return res.status(200).send("OK");
    } catch (error) {
      return res.status(500).send({ message: error.message });
    }
  } else {
    return res.status(400).send({ warning: "Only POST methods are allowed" });
  }
}/lib/googlesheetsapiimport { google } from "googleapis";

export async function SaveData(googleSheetID, params) {
  const newValues = JSON.parse(JSON.stringify(params));

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, "
")
      },
      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: googleSheetID,
      range: "A1:C1",
      valueInputOption: "USER_ENTERED",
      requestBody: {
        values: [[newValues.Name, newValues.Email, newValues.Message]],
      },
    });
  } catch (error) {
    console.log("Error saving to file: ", error.message);
  }
}This is running on vercel and all logs are either 200 or 400 based on wether it is a POST or not.Thanks allot

Blood cockle · Accepted Answer

Solved.It had a couple of issues. Even if the SaveDatafunction had errors they were not being returned so that the response could show them and the environment variables set on vercel, especifically the private_key, was not being sent correctly thus not saving the values.

Google Sheets API only working locally

1 Reply