To search the row in the google sheet if it is there update otherwise add, using google APIS in nodejs

Question

I am using google sheets APIS, I have successfully created the APIS that will get the data and post the data in my google sheet.

My google sheet looks like:

Coin    Price   Volume  Cap
ETH $2,456.90   1459738 $8712BN
BTH $2,900.89   1245983 $24953BN

Now if the coin is already present in the sheet then API should update its value, if the coin is not present then the API should add the coin with all the details.

The API which I am using for appending the value is:

app.post('/save-data', async (req,res) => {
    try{

        const {newCoin, price, volume, cap} = req.body;
        const { sheets } = await authentication();
        
        const postReq = await sheets.spreadsheets.values.append({
            spreadsheetId: sheetId,
            range: "Sheet1",
            valueInputOption: 'USER_ENTERED',
            resource:{
                values: [
                    [newCoin, price, volume, cap]
                ]
            }
        })
        if(postReq.status==200)
        {
            res.status(200).send("The values are saved successfully");
        }else{
            res.status(400).send("Error in saving the values");
        }

    }catch(e){
        console.log("Error while saving the data on the google sheets::", e);
        res.status(400).send();
    }
})

So I want to change the API so that first it searches if it finds the coin then just updates otherwise add a new row. Please help me to achieve this.

Thanks

Answer 1

In your situation, how about the following modification?

Modified script:

From:

const {newCoin, price, volume, cap} = req.body;
const { sheets } = await authentication();

const postReq = await sheets.spreadsheets.values.append({
    spreadsheetId: sheetId,
    range: "Sheet1",
    valueInputOption: 'USER_ENTERED',
    resource:{
        values: [
            [newCoin, price, volume, cap]
        ]
    }
})
if(postReq.status==200)
{
    res.status(200).send("The values are saved successfully");
}else{
    res.status(400).send("Error in saving the values");
}

To:

const {newCoin, price, volume, cap} = req.body;
const { sheets } = await authentication();

// I modified below script.
const [, ...values] = (await sheets.spreadsheets.values.get({ spreadsheetId: sheetId, range: "Sheet1" })).data.values;
const obj = values.reduce((o, [a, ...v], i) => ((o[a] = i + 2), o), {});
let postReq;
if (obj[newCoin]) {
  postReq = await sheets.spreadsheets.values.update({
    spreadsheetId: sheetId,
    range: `'Sheet1'!A${obj[newCoin]}`,
    resource: { values: [[newCoin, price, volume, cap]] },
    valueInputOption: "USER_ENTERED",
  });
} else {
  postReq = await sheets.spreadsheets.values.append({
    spreadsheetId: sheetId,
    range: "Sheet1",
    valueInputOption: "USER_ENTERED",
    resource: { values: [[newCoin, price, volume, cap]] },
  });
}
if (postReq.status == 200) {
  res.status(200).send("The values are saved successfully");
} else {
  res.status(400).send("Error in saving the values");
}

• When this script is run, first, the value of newCoin is searched from the column "A". And, when the value is found, the row is updated. When the value is not found, a new row is appended.

References:

• Method: spreadsheets.values.get
• Method: spreadsheets.values.update