In this article i will explain you step by step how you can save data to google spreadsheet in your node js application

Step 1:Create google spreadsheet credentials

In order to read and write data to google spreadheet you need to authenticate with google for that we need to create Google service account key file

• Go to https://console.developers.google.com/

• Select a project or create a new project

• Go to credentials and click on manage service account

• Click on create service account

• Fill the detail and click on done

• Click on manage keys from dropdown

• Click on Add Key and from Popup select JSON and click on create

• Copy the private key id and client email from your downloaded json file add to your env file or use it in next step

Step 2: Enable Google spread sheet api

• Click on Enable APIs & Service from left panel

• Click on ENABLE APIS AND SERVICE from the top header

• Search Google Sheet API

• Click on Google Sheet API from the search result and click on Enable

Step 3: Create Google Spreadsheet

Create google spreadhsheet and copy spreadhsheet id and sheet id ans save it somewhere safe or paste it in your env file because we are going to use this

Step 4: Share the google spreadsheet with the private email from downloaded json file

Step 5: Add data to google spread sheet

• Install google-spreadsheet to your nodejs application

npm install --save google-spreadsheet

• Copy paste following code under route app.post(“/savedata”) in your node.js project

import express from "express";
import path from "path";
import "dotenv/config";
import { GoogleSpreadsheet } from "google-spreadsheet";

const app = express();
const port = 3000;
const __dirname = path.resolve();
const doc = new GoogleSpreadsheet(process.env.SPREADSHEET_ID);

app.use(express.json());

app.post("/savedata", async (req, res, next) => {
  await doc.useServiceAccountAuth({
    client_email: process.env.GOOGLE_SERVICE_ACCOUNT_EMAIL,
    private_key: process.env.GOOGLE_PRIVATE_KEY,
  });

  await doc.loadInfo();
  const sheet = doc.sheetsById[process.env.SHEET_ID];
  const result = await sheet.addRow({
    "First Name": req.body.first_name,
    "Last Name": req.body.last_name,
    "Email Address": req.body.email,
    Password: req.body.password,
  });
  res.json(result);
});

app.get("/readdata", (req, res, next) => {});

app.listen(port, () => {
  console.log(`Demo app listening on port ${port}`);
});