mongoSheet, MongoDB to Google Spreadsheet

CRUD Operations

Now let's see how the create, read, update and delete operations work in the addon. For better understanding let's first look at how the read works.
For this experiment, let's consider a MongoDB collection named families and the document schema is as below.

      
                  
  {
	  father: {
		  name: ** // string
		  age: ** // number
	  },
	  mother: {
		  name: ** // string
		  age: ** // number
	  },
	  phone: ** // number
	  kids: [
		  {
			  name: ** // string
			  dob: ** // date
			  height: ** // number
			  weight: ** // number
		  }
	  ]
  }

In the above schema please note that most keys are the type of string or number, except kids dob is a date

Reading from database

Let's first read the father and mother from the database. To read, just add keys to row 1 of the spreadsheet. And then click on read from the sidebar. The data will be populated in the spreadsheet.

But you can see that both father and mother are objects. You can get father.name and father.age in a separate column easily by populating headers as shown below.

But now you can see that age is not correctly populated. This can be easily solved by type conversion. Please see the picture below. age is a number, so we can convert the populated data to number by giving the header as 'father.age:number'. The other type conversion are oid, date, string and number. There is also array and object. But its used for a different purpose. Details about it are below.

Dot notation method is great for reading the data. But don't use it while creating or updating a document. Because mongoDB doesn't supports dot notation. The prefered method is to Map the data to objects or arrays from sheet. Details about it is coming next.

Let's look at a more robust way of querying data. Let's first query all the keys, father mother kids, and phone

Since father and mother are objects, kids, are arrays we can separate those keys into different sheets. To do that first create three sheets for father, mother, and kids respectively.

father and mother is of type object we need to give the type information in the header as 'father:object' and 'mother:object'. And for kids, since its an array we need to give the type information in the header as 'kids:array'. Please refer the picture below. Now click on each column and click on 'Write'. This action will write the data from each columns to seperate sheets. This will also add a new reference key as $father $mother and $kidsto the sheet. These keys are used by mongoSheet to populate the data from different sheets while writing back to the database. Please also note that these keys will be an internal key for mongoSheet and will be never updated onto the MongoDB collection

If you look at the $ keys of father, mother, and kids you will see that in sheets father and mother the $ keys are unique, it's not repeating. But in the kids sheet the $ keys are repeating. Its because kids is an array and other two are just an object. When we link these items back to our original document then mongoSheet uses this key for linking.

Now we have queried the document, let's see how the update works. Before update, mongoSheet populates the data if there is anything. So we need to tell mongoSheet that father, mother and kids are object and array and need to be populated before sending for update. Which means that we need to link the data in multiple sheets. This linking is made using the unique keys the column $father, $mother and $kids

To make the populate possible, select the entire column father and then click on Populate from the sidebar. from the sidebar. And do the same for mother and kids. Since we have given the datatype of $father and $mother as object and kids as array mongoSheet will populate the data in the required format before calling the update.

We have now looked at how the Read and Update works. Create works very similar to Update. In Create you don't have to give _id in column A, the same will be populated once the when the In Create operation is completed. To Delete, specify the document id in column A and click on Delete from the sidebar. All documents specified in column A will be removed from the collection.
This was all about CRUD operation and now all you have to do is to try yourself, fail and learn.

If you are facing issues, then please send us an email to hello@mongosheet.com