How to import Google Sheets Data to MongoDB Atlas Realm

Question

I am trying to import the data I have from my Google sheets doc to MongoDB Atlas. I have written the app script as well as the function in the function editor. When I run my code in the function editor in MongoDB I get this ERROR "mongodb insert: argument must be an object". Here is what I have so far:

Google Sheet APP Script

function exportSheetsToMongo () {

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("US");
  var headersRows = 1;
  var range = sheet.getDataRange();
  var numsRows = range.getNumRows;
  var data = range.getValues(); 

  for (var i = headersRows; i < numsRows; i++) {
    var usZipIdCell = range.getCell( i +1, columns.zip+1) // Error possible here
    // var description = data[i][columns.description];
    
    var formData = {
      // 'country': data[i][columns.description],
      'zip': data[i][columns.zip],
      'city': data[i][columns.city],
      'state': data[i][columns.state],
      'state_code': data[i][columns.state_code],
      'county': data[i][columns.county],
      'county_code': data[i][columns.county_code],
      'lattitude': data[i][columns.lattitude],
      'longitude': data[i][columns.longitude],
      'accuracy': data[i][columns.accurcay]

    };
    var options = {
      'method':'post',
      'payload': formData
    };
    if(description) {
      var insertID  = UrlFetchApp.fetch("https://us-east-1.aws.data.mongodb-api.com/app/mmc-backend-mzbhi/endpoint", options);
      usZipIdCell.setValue(insertID)
    }
  } 
}

MongoDB Realm Function Editor

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

Partial Google Sheet Data

Data Base

I have to apologize for my poor English skill. I cannot understand the relationship between "Google Sheet APP Script" and "MongoDB Realm Function Editor". Can I ask you about the detail of it? — Tanaike, Jan 28 '22 at 01:31
The Google Sheets App script is a function on how to import the data and the MongoDB Realm Function is to insert that data in JSON format. I hope this helps, sorry I am new using the google app script and the mongo realm. I was trying to follow this tutorial https://www.mongodb.com/blog/post/stitching-sheets-using-mongodb-stitch-to-create-an-api-for-data-in-google-sheets but it looks like 3rd party services have been depreciated. — Kblack4290, Jan 28 '22 at 15:57
Thank you for replying. In your current issue, "Google Sheet APP Script" works fine. But "MongoDB Realm Function Editor" dowsn't work as you expected. Is my understanding correct? — Tanaike, Jan 29 '22 at 00:03

score 0 · Answer 1 · answered Sep 02 '22 at 14:55

The issue may be with the data. You need to stringify it and set the application type. You are also accessing payload.query instead of payload.body. So something like this should work:

    var formData = {
      ...
    };
    var options = {
      'contentType': 'application/json', // JSON application body type
      'method':'post',
      'payload': JSON.stringify(formData) // Stringify the data
    };
    if(description) {
      var insertID  = UrlFetchApp.fetch("https://us-east-1.aws.data.mongodb-api.com/app/mmc-backend-mzbhi/endpoint", options);
      usZipIdCell.setValue(insertID)
    }

exports = async function(payload) {
  
  const mongodb = context.services.get("mongodb-atlas");
  const eventsdb = mongodb.db("us_zip");
  const eventscoll = eventsdb.collection("zip");

  const body = JSON.parse(payload.body.text()); // Parse the body of the 'request'

  const result = await eventscoll.insertOne(body);
  var id = result.insertedId.toString();
  if(result) {
    return JSON.stringify(id,false,false);
  }
  return {text: 'Error saving'}
};

I'd also recommend using eventscoll.insertMany() instead of eventscoll.insertOne() as this is going to make a url call for every entry in your sheet.

How to import Google Sheets Data to MongoDB Atlas Realm

1 Answers1