MongoDB Integration with Google Sheets to Update documents

MongoDB Atlas App Services & Realm
1

So, I’m using Realm to integrate my Google Sheet data with the MongoDB Database.

I basically want to compare each row of Google Sheet with MongoDB Documents on the basis of ID and if there’s not a match then I want it to insert a document and if the id matches with one of the existing documents then I want it to check and update values.

This is what I’ve done so far (I’ve just done it for TransformX_Vendor_Name for now but I want it to check for all the columns and not just one ):

exports = async function(payload,response) {
 const mongodb = context.services.get("mongodb-atlas");
 const eventsdb = mongodb.db("CPaaS_Decision_Engine");
 const eventscoll = eventsdb.collection("CPaaS_Vendors");
 var body = JSON.parse(payload.body.text());
 var TransformX_Vendor_Name = body.TransformX_Vendor_Name;
 const result= await eventscoll.count({ "TransformX_Vendor_Name": TransformX_Vendor_Name });

    if(result.count > 0 ) {
        response.setBody( JSON.stringify({TransformX_Vendor_Name}) );    
      } else {
  const insert= await eventscoll.insertOne(payload.query);
  var id = result.insertedId.toString();
}

I’m new to MongoDB Realm. Any help would be really helpful, thank you!

2

I would suggest sending the id of the document as a response to your google sheet.

exports = async function(payload) {
   const mongodb = context.services.get("mongodb-atlas");
   const eventsdb = mongodb.db("my_db");
   const eventscoll = eventsdb.collection("my_collection");
   const result= await eventscoll.insertOne(payload.query);
   var id = result.insertedId.toString();
   if(result) {
      return JSON.stringify(id,false,false);   
   }
   return { text: `Error saving` };
}

And in your google sheet script, save the new ID. If your Id is in google sheet, it exists in the database :

var options = {
      'method' : 'post',
      'payload' : formData
    };

var insertID = UrlFetchApp.fetch('your_url', options);
eventIdCell.setValue(insertID); // Insert the new event ID

Also I tried for a few hours to do the verification in Mongo but without success.