Updating a specific value in row with conditions nodejs google sheet

Question

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');
});

Answer 1

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.

References:

• Method: spreadsheets.values.get
• Method: spreadsheets.values.batchUpdate