NodeJS Express GoogleSheets CRUD

index.js

/*
Special thanks to https://github.com/RajKKapadia/Youtube-GoogleSheet-NodeJS
*/

const fs = require("fs");
const express = require("express");
const { GoogleSpreadsheet } = require("google-spreadsheet");

const app = express();
app.use(express.urlencoded({ extended: true }));

app.get("/", async (req, res) => {
  let response = {};
  try {
    response = await getRows();
  } catch (error) {
    console.log(`error`, error);
  }
  res.send(response);
});

app.post("/", async (req, res) => {
  res.send("Successfully submitted! Thank you!");
});

app.listen(1337, (req, res) => console.log("running on 1337"));

const initConfig = async () => {
  // spreadsheet key is the long id in the sheets URL
  const RESPONSES_SHEET_ID = "1FTKEkidY6s1VsT3GzlruMVB7E7OQVvISG33S3Chwb";

  // Create a new document
  const doc = new GoogleSpreadsheet(RESPONSES_SHEET_ID);

  // Credentials for the service account
  const CREDENTIALS = JSON.parse(fs.readFileSync("credentials.json"));
  // use service account creds
  await doc.useServiceAccountAuth({
    client_email: CREDENTIALS.client_email,
    private_key: CREDENTIALS.private_key,
  });

  // load the documents info
  await doc.loadInfo();
  return doc;
};

const getRows = async () => {
  let response = [];

  const doc = await initConfig();

  // Index of the sheet
  let sheet = doc.sheetsByIndex[0];

  // Get all the rows
  let rows = await sheet.getRows();

  for (let index = 0; index < rows.length; index++) {
    const obj = {};
    const row = rows[index];
    let keys = Object.keys(row);
    keys = keys.filter((x) => !/_/.test(x));
    for (const key of keys) obj[key] = row[key];
    response.push(obj);
  }
  return response;
};

const addRow = async (rows) => {
  const doc = await initConfig();

  // Index of the sheet
  let sheet = doc.sheetsByIndex[0];

  for (let index = 0; index < rows.length; index++) {
    const row = rows[index];
    await sheet.addRow(row);
  }
};

let rows = [
  {
    email: "[email protected]",
    user_name: "ramesh",
    password: "abcd@1234",
  },
  {
    email: "[email protected]",
    user_name: "dilip",
    password: "abcd@1234",
  },
];

// addRow(rows);

const updateRow = async (keyValue, oldValue, newValue) => {
  const doc = await initConfig();

  // Index of the sheet
  let sheet = doc.sheetsByIndex[0];

  let rows = await sheet.getRows();

  for (let index = 0; index < rows.length; index++) {
    const row = rows[index];
    if (row[keyValue] === oldValue) {
      rows[index][keyValue] = newValue;
      await rows[index].save();
      break;
    }
  }
};

// updateRow('email', '[email protected]', '[email protected]')

const deleteRow = async (keyValue, thisValue) => {
  const doc = await initConfig();

  // Index of the sheet
  let sheet = doc.sheetsByIndex[0];

  let rows = await sheet.getRows();

  for (let index = 0; index < rows.length; index++) {
    const row = rows[index];
    if (row[keyValue] === thisValue) {
      await rows[index].delete();
      break;
    }
  }
};

// deleteRow("email", "[email protected]");