Google Spreadsheets are widely used to build databases for side projects. Therefore, we are going to create a spreadsheet API proxy to make integration with web apps easier.

1. You need Google Service Account Key. Go here https://console.cloud.google.com/, API & Services, Enable APIs and Services, Search for Google Sheets API, Enable, Create Credentials, and in that select Application data, after that select the Service Account that we just created, Go to Keys → Create new key. Now you will have JSON private file in download folder and Now save that json file in a folder in app/files.

2. Important part You have to add the service account email to the spreadsheet for which you want to enable the API. The service account email will look like this: abc@xyz.iam.gserviceaccount.com.

3. Install the Google APIs SDK package.

terminal
Copy
npm install googleapis

3. Open server.js file and add two lines in this file.

JavaScript
Copy
// server.js
const googleProxyRoutes = require('./app/routes/googleProxyRoutes');

app.use('/google/proxy', googleProxyRoutes);

4. Create a route file in the app/routes directory named googleProxyRoutes.js

JavaScript
Copy
// app/routes/googleProxyRoutes.js
const express = require('express');
const router = express.Router();
const googleController = require('../controllers/googleController');

router.get('/rows/:sheetId', googleController.getSheetRows);
router.post('/rows/:sheetId', googleController.addNewRows);
router.put('/rows/:sheetId', googleController.updateRow);

module.exports = router;

5. Create a controller file in the app/controllers directory named googleController.js

JavaScript
Copy
// app/controllers/googleController.js
const { responseList } = require('../errors/responseList');
const { google } = require('googleapis');
const serviceAccount = require('../files/buildapi-app-ef9c8899093c.json');

// Authenticate using the service account JSON file
const auth = new google.auth.GoogleAuth({
    credentials: serviceAccount,
    scopes: ['https://www.googleapis.com/auth/spreadsheets'],
});

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

// List firebase auth users
exports.getSheetRows = async (req, res) => {
    try {
        const { sheetId } = req.params;
        const { tabId } = req.query;
        if (tabId === undefined) return res.status(400).send(responseList(400, 'tabId is required'));

        const range = `${tabId}!A1:ZZ`; // Change this to the desired range
        const response = await sheets.spreadsheets.values.get({
            spreadsheetId: sheetId,
            range,
        });

        const values = response.data.values;
        if (!values || !values.length) {
            return res.status(400).send(responseList(400, 'No data found'));
        }

        // Extract headers (keys) from the first row
        const headers = values[0];

        // Convert values array into array of objects with keys from headers
        const rows = values.slice(1).map((row, index) => {
            const rowData = {};
            row.forEach((value, columnIndex) => {
                rowData[headers[columnIndex]] = value;
            });
            // Add rowIndex to each object
            rowData.rowIndex = index + 2;
            return rowData;
        });
        res.status(200).send(rows);
    } catch (error) {
        res.status(500).send(responseList(500, error.message));
    }
};

exports.addNewRows = async (req, res) => {
    try {
        const { sheetId } = req.params;
        const { tabId } = req.query;
        const rowData = req.body;

        if (tabId === undefined) return res.status(400).send(responseList(400, 'tabId is required'));
        if (Object.keys(rowData).length === 0) return res.status(400).send(responseList(400, 'Body data is null'));

        const response = await sheets.spreadsheets.values.get({
            spreadsheetId: sheetId,
            range: `${tabId}!1:1`,
        });

        const headers = response.data.values[0];
        const values = rowData.map((item) => headers.map((header) => item[header]));

        // Append the new rows to the spreadsheet
        await sheets.spreadsheets.values.append({
            spreadsheetId: sheetId,
            range: `${tabId}!A:A`,
            valueInputOption: 'USER_ENTERED',
            requestBody: {
                values,
            },
        });

        res.status(200).send(responseList(200, 'Row(s) added successfully'));
    } catch (error) {
        res.status(500).send(responseList(500, error.message));
    }
};

exports.updateRow = async (req, res) => {
    try {
        const { sheetId } = req.params;
        const { tabId } = req.query;
        const rowData = req.body;

        if (tabId === undefined) return res.status(400).send(responseList(400, 'tabId is required'));
        if (Object.keys(rowData).length === 0) return res.status(400).send(responseList(400, 'Body data is null'));

        const { rowIndex } = rowData;
        if (rowIndex === undefined) return res.status(400).send(responseList(400, 'rowIndex is missing in the body'));

        const response = await sheets.spreadsheets.values.batchGet({
            spreadsheetId: sheetId,
            ranges: [`${tabId}!A1:ZZZ`, `${tabId}!A${rowIndex}:ZZZ${rowIndex}`],
        });

        const headerKeys = response.data.valueRanges[0].values[0];
        const targetRow = response.data.valueRanges[1].values[0];

        const updatedRowData = headerKeys.reduce((acc, header, index) => {
            acc[header] = targetRow[index]; // Use the existing value if not updated
            if (rowData[header] !== undefined) {
                acc[header] = rowData[header]; // Update with new value if provided
            }
            return acc;
        }, {});

        // Append the new rows to the spreadsheet
        await sheets.spreadsheets.values.update({
            spreadsheetId: sheetId,
            range: `${tabId}!A${rowIndex}:ZZZ${rowIndex}`,
            valueInputOption: 'USER_ENTERED',
            requestBody: {
                values: [Object.values(updatedRowData)],
            },
        });

        res.status(200).send(responseList(200, 'Row updated successfully'));
    } catch (error) {
        res.status(500).send(responseList(500, error.message));
    }
};