Updating a specific value in row with conditions nodejs google sheet-CodePudding

I've been trying to update an existing row with the same name, email, etc.. my problem is that I can't figure out how to add a number in the second trial WPM for the same user.

to be more clear, the user submits a form and start the operation in the web app, when the first operation ends, the data is sent to google sheets and the user moves to the second operation. I want to send the data of the second operation to the user with the same name, email.

before after

app.post("/competition" , async (req, res) => {
const {Name, Email, Number, Faculty, University, first_Trial_WPM , first_Trial_CPM} = req.body;
const auth = new google.auth.GoogleAuth({
    keyFile: "credentials.json",

    scopes: "https://www.googleapis.com/auth/spreadsheets",

    });

const client = await auth.getClient();

const googleSheets = google.sheets({version: "v4", auth: client});

const spreadsheetId = "###";

await googleSheets.spreadsheets.values.append({

    auth,
    spreadsheetId,
    range: "Sheet1!A:G",
    valueInputOption: "USER_ENTERED",

    resource : {
        values: [
            [Name, Email, Number, Faculty, University, first_Trial_WPM , first_Trial_CPM]
        ]
    }
})

res.render("competition2");
});

 app.post("/home" , async (req, res) => {

const {Name, Email, Number, Faculty, University, second_Trial_WPM , second_Trial_CPM} = req.body;

    const auth = new google.auth.GoogleAuth({
        keyFile: "credentials.json",

        scopes: "https://www.googleapis.com/auth/spreadsheets",

    });

    const client = await auth.getClient();

    const googleSheets = google.sheets({version: "v4", auth: client});

    const spreadsheetId = "###";

    await googleSheets.spreadsheets.values.update({

        auth,
        spreadsheetId,
        range: "Sheet1!A2:I1000",
        valueInputOption: "USER_ENTERED",

        resource : {
            values: [
                [Name, Email, Number, Faculty, University, second_Trial_WPM , second_Trial_CPM]
            ]
        }
    })

    res.render('home');
});

CodePudding user response：

I believe your goal is as follows.

You want to put the values to the columns "H" and "I" by searching the columns "A" and "B".
You want to achieve this using googleapis for Node.js.

In this case, how about the following modification?

Modified script:

In this modification, please modify your 2nd script for putting the values of second_Trial_WPM and second_Trial_CPM as follows. This modified script uses the variables of Name and Email, second_Trial_WPM, and second_Trial_CPM.

  const spreadsheetId = "###";
  const range = "Sheet1";
  const values = (await googleSheets.spreadsheets.values.get({spreadsheetId, range})).data;
  const rows = values.values.map(([a, b], i) => (a == Name && b == Email ? i   1 : "")).filter(String);
  const data = rows.map((e) => ({values: [[second_Trial_WPM, second_Trial_CPM]], range: `'${range}'!H${e}`}));
  const res = await googleSheets.spreadsheets.values.batchUpdate({spreadsheetId, resource: {valueInputOption: "USER_ENTERED", data}});

In this modification, first, the values are retrieved from "Sheet1". And, retrieve the row numbers for putting values using the values of Name and Email. And then, second_Trial_WPM and second_Trial_CPM are put into the columns "H" and "I" of the searched row.
If the same names are retrieved in columns "A" and "B" of "Sheet1", the values of second_Trial_WPM and second_Trial_CPM are put to those rows.

Modified script:

References: