node.js - Firestore to Google Sheets with a Cloud Function

Question

I am building an appointments app and I would like to keep track of Firestore updates in a google sheet.

Basically, what the function does, is to listen for updates in firestore, then loop through every object in the document and compare date (doc id) and time values with a concatenation of date and time columns from the spreadsheet to paste object data in the corresponding row.

For some reason when a doc is updated, my cloud function is not updating the spreadsheet

This is the Cloud function:

exports.writeToSheetTrigger = functions.firestore
    .document("citas/{date}")
    .onUpdate(async (change, context) => {
        var values = change.after.data();

        const date = context.params.date;

        try {
            const spreadsheet = functions.config().googleapis.sheet;
            
            google.auth
                .getClient({
                    
                    scopes: ["https://www.googleapis.com/auth/spreadsheets"],
                })
                .then(async (auth) => {
                    const api = google.sheets({ version: "v4", auth });
                    const getSheets = api.spreadsheets;
                    const sheetVals = await getSheets.values.get({
                        spreadsheetId: "SOME ID",
                        range: `Hoja 1!C2:D1000`,
                    });

                    // create a concatenated column with date and time
                    let dataArray = sheetVals.data.values;

                    let concat = dataArray.map((r) => {
                        r.push(r[0] + " " + r[1]);
                        return r;
                    });

                    for (time of values) {
                        const rowData = [
                            [
                                values[time].ocuppied,
                                values[time].service,
                                values[time].name,
                                values[time].phone,
                                values[time].email,
                            ],
                        ];

                        let rowToReplace;

                        for (let i = 0; i < concat.length; i++) {
                            if (concat[i][2] === `${date} ${values[time].hour}`) {
                                rowToReplace = i + 2;
                            }
                        }

                        const theRange = `Hoja 1!E${rowToReplace}:I${rowToReplace}`;

                        const payload = {
                            spreadsheetId: "SOME ID",
                            range: theRange, //e.g. 'Sheet1!A2:D',
                            valueInputOption: "USER_ENTERED",
                            resource: {
                                values: rowData,
                            },
                        };

                        let res = [];

                        res.push(getSheets.values.update(payload));
                    }

                    return await Promise.all(res);
                })

                .catch((err) => {
                    console.warn("error writing to spreadsheet", err);
                    return err;
                });
        } catch (err) {
            console.error(err);
            await err;
        }
    }); 

This is how the database and spreadsheet look like :

spreadsheet database

Many thanks!!

Answer 1

等待大神答复