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.
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');
});
I believe your goal is as follows.
In this case, how about the following modification?
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.