Tips for importing data from Google Sheets to MongoDB Atlas Realm

Question

Tips for importing data from Google Sheets to MongoDB Atlas Realm

I'm attempting to transfer my Google Sheets data to MongoDB Atlas. I've created the app script and function in the editor. However, when running the code in MongoDB's function editor, I encounter the ERROR "mongodb insert: argument must be an object". Here is an overview of my progress:

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

https://i.sstatic.net/FHMe3.png

Data Base

https://i.sstatic.net/ZLEfR.png

javascript mongodb google-apps-script google-sheets mongodb-realm

Answer 1

Answer №1

The problem might lie in the data manipulation process. It is important to convert the data into a string format and specify the application type. Additionally, it seems like you are referencing payload.query instead of payload.body. To address this issue, consider implementing the following code snippet:

    var formData = {
      ...
    };
    var options = {
      'contentType': 'application/json', // Specifying JSON application body type
      'method':'post',
      'payload': JSON.stringify(formData) // Converting the data into a string
    };
    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()); // Parsing 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'}
};

It is also advisable to utilize eventscoll.insertMany() instead of eventscoll.insertOne() to minimize the number of url calls made for each entry in your sheet.

Answer 2

The problem might lie in the data manipulation process. It is important to convert the data into a string format and specify the application type. Additionally, it seems like you are referencing payload.query instead of payload.body. To address this issue, consider implementing the following code snippet:

    var formData = {
      ...
    };
    var options = {
      'contentType': 'application/json', // Specifying JSON application body type
      'method':'post',
      'payload': JSON.stringify(formData) // Converting the data into a string
    };
    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()); // Parsing 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'}
};

It is also advisable to utilize eventscoll.insertMany() instead of eventscoll.insertOne() to minimize the number of url calls made for each entry in your sheet.

Tips for importing data from Google Sheets to MongoDB Atlas Realm

Answer №1

Similar questions

jQuery Dragging Element With Restricted Area Overflow Hidden

Encountering issues with Webpack 2 failing to resolve the entry point specified in the

How can you use Redash to generate a visual representation that displays the frequency of different categories?

Can we create a process that automatically transforms any integer field into a hashed string?

Will the rel attribute work in all web browsers and with all HTML tags?

Locating the index of the initial duplicate in an array using Javascript

Is the HTML5 video backup image misplaced?

When clicking on an item in the wishlist, only the text for that specific item should change,

One of the two buttons on the form must be selected

What is the best way to integrate SVGs into a React project after transitioning from Vanilla JavaScript?

Sending an array via an AJAX POST request using jQuery

Front-end React app paired with a backend Express server, both running through a Nginx server

Issue with retrieving an array of objects in an Angular function

Utilizing Bootstrap 5: Executing JavaScript exclusively upon successful form validation

Attempting to conceal certain elements based on the current route in Angular 5

Implementing a file download feature in Python when clicking on a hyperlink

Identifying the unique parent component of a component within Angular

React: Dealing with null values when making a PUT request using axios

Utilizing the variable's value as a parameter in a jQuery selector

Triggering an event when the cursor enters a specific div/span/a element and again when the cursor exits the element