]]>Let’s talk about the mighty Google sheets. Mighty? Yes! It is one the best spreadsheet apps on par with Microsoft Excel. Actually, Google sheets is better than Excel in few cases because of its features like Apps script, Timed triggers, Form triggers, Google sheets API, importXML etc. Let’s talk about different advanced features of Google sheets, its limitations, alternate solutions and how to use those features and how to use Google sheets as database!

Apps script platform for developers

Don’t even get me started on the number of integrations available with Google sheets App script such as MailApp, DriveApp, DocumentApp, SlidesApp, CalendarApp etc!

There are so many integrations available to explore within google sheets but most of the features are unused because most users and developers don’t even know that there’s so much to explore in Google Apps Script! Check out the Apps script documentation here – Google Apps script documentation

Usage and limitations of Apps script platform

As mentioned earlier, Apps script has a lot of integrations available within the Google ecosystem. Apps Script is really good when you want to share and modify data between Google’s products. Let me give you a few examples

Generate a PDF invoice and send it to your tenant every month(DriveApp, MailApp, and Timed trigger)

Sync events between Google sheets and Google Calendar

Shorten URLs using URL Shortener Service

Import Google and Youtube analytics to Spreadsheet

These are just a few examples. The possibilities are endless! It’s all good as long as the data is within Google products. Once you want to read or write data outside of Google, however, the problems start.

Let’s say you have a list of employees and their metrics in a google sheet and you want to display it on your company website. How to use Google sheets as a database? This is when Google sheets API comes into the picture!

Google Sheets API

Google Spreadsheet API can be used to read and write data to google sheets. Sheets API can be consumed to use google sheets as database!

But It is so damn complex! From Authentication to API endpoints to API response structure, Its a pain in the ass

Problems with Google Sheets API

Needs OAuth for accessing data in private sheets (Generally It is not a problem but In this context, It is!)

If you are a developer then you surely know that this API response is not really useful!

So what are the alternatives to Sheets API? I’ve tried a few libraries which simplify the API response and provide some useful methods but most of them are deprecated and support only public sheets and also don’t support writing or updating data in google sheets.

Finally, I found this platform called Sheetsu. One platform that simplifies google sheets API to a great extent and helps to use google sheets as database.

Introducing Sheetsu, A sophisticated platform built on Google sheets

Sheetsu is by far the best platform I’ve seen that leverages most of the capabilities of google sheets. Let’s see what features does Sheetsu offer and how we can use them!

Sheetsu is a platform for both developers and non-developers. Some of the features can be used by anyone without coding experience and some advanced features like JSON API can be used by developers to convert google sheets as database.

Important features of Sheetsu :

Google sheets to HTML table

HTML form to Spreadsheet integration – Of course, This feature is already built-in with Google forms.

Now Go to your google sheet and add your data which can be read using the JSON API. Sample data shown below

That’s It! It is as simple as that in just 4 steps. You’ve converted your google sheet into a Database with Full CRUD (Create, Read, Update, Delete) support API

Understanding Sheetsu JSON API

Let’s understand how Sheetsu’s JSON API can be consumed. This API supports GET, POST, PUT, PATCH and DELETE methods. Data can be filtered using search queries, set the limit and offset for pagination support. It is as good as an actual API with a full-fledged database.

API requests can be secured using API key and can also disable methods if required. Let’s say you want to disable updating and deleting of data then you can disable those methods and you are good to go!

Reading and filtering data from google sheets database

Reading data is just making a simple GET request to the API. Click on the link below to see the response.

Practical example – Learn by building a blood donor app

Beginners who don’t have experience working with APIs might be wondering how to actually use these in a web app or mobile app or website. So I made a simple practical example to help the beginners understand better.

It’s a simple web app where users can see the list of people who are ready to donate blood and they can also submit their own details for donating blood.

Users can search based on blood group and city. All the data is stored in a google spreadsheet and all new submissions create a new row in the spreadsheet. All this data is consumed using Sheetsu API

Here’s how the spreadsheet looks

So There’s Name, city, phone, group, and address columns.

I’ve used Sheetsu’s Javascript web client library to simplify the process of calling APIs. All this library does is make calls to the JSON API as explained in the previous section but it helps a lot by providing simple methods to read, write and query data with as little code as possible.

All you need to do to include this library is add a script tag to your HTML file

When to use Google sheets as database

Google sheets is not a full-fledged database so when should you use google sheets as database?

Small to medium scale hobby projects which don’t require authentication or user management

Quick prototyping and testing

Let a sales team or HR team use google sheets as database and quickly display some data on the company website.
(Checkout Sheetsu’s Table feature for this)

Wrapping up

This is a long blog post that I’ve written after a long time I tried to put out as much information as possible with regard to using Google sheets as database. If I’ve missed something or if you have any questions or feedback then do let me know in the comments below!

]]>https://codingislove.com/google-sheets-database/feed/02399Animated SVG Avatar in login form using GSAPhttps://codingislove.com/animated-svg-avatar-login-form-using-gsap/
https://codingislove.com/animated-svg-avatar-login-form-using-gsap/#respondMon, 26 Feb 2018 10:20:51 +0000https://codingislove.com/?p=2385This excellent SVG Avatar animation is made using Greensock Tweenmax and some trigonometry. Kudos and credits to Darin Senneff Check out the live demo below!

]]>https://codingislove.com/animated-svg-avatar-login-form-using-gsap/feed/02385Exploring the Future of AI, ML and Robotics at IBM Code Day 2018https://codingislove.com/ibm-code-day-2018/
https://codingislove.com/ibm-code-day-2018/#commentsSat, 17 Feb 2018 21:05:41 +0000https://codingislove.com/?p=2306Tech conferences are exciting for developers! We always want to know more about the new technologies, their practical use cases and try them out! What’s…

]]>Tech conferences are exciting for developers! We always want to know more about the new technologies, their practical use cases and try them out!

What’s more exciting than learning them from the Industry experts itself! There comes IBM Code Day on February 14th 2018, A day of tech learning across various technologies like Machine learning, AI, Robotics, Quantum computing, Blockchain and many more!

Seema Kumar, Country Leader, Developer Ecosystems, IBM kickstarted the IBM Code day by talking about Why It’s the best time to be a developer in India. It made me think about a recent post that I read “Are Indian developers good at programming?”. The answers were mixed opinions. It’s a big controversial topic overall.

The truth is that “There are a lot of below average developers” But “There are many more exceptional developers in India” You just have to pick them right. Why else would Google and Microsoft have Indian CEOs!

14-Year-old talks about Quantum computing

Next talk was from this 14-year-old Tanmay Bakshi. I had never touched a computer when I was 14 and this guy is talking about Quantum computing at this age A Real Genius!

It was a quick Intro to Quantum computing with IBM Q and Training IBM visual recognition using Machine learning along with live demos. It was interesting to see how easily auto-tagging can be utilized from IBM platform for video recognition just by manually tagging a few frames. I’ll drop demo videos on this topic soon!

Building for a billion – India Stack

This talk by Nikhil Kumar was one of my favorite talks on IBM Code Day. It was interesting, had some fun element, and detailed information about India stack.

I was aware that Indian Government is providing APIs for Aadhar Auth, E-KYC and UPI but never knew that It is this sophisticated. I’ve been seeing that more and more platforms trying to integrate Aadhar and E-KYC for Identity verification. I’m definitely going to try this out.

Fun fact from this talk: T-Series (An Indian music channel) is the most viewed channel on Youtube

Data science and AI in business Analytics

A Quick Q & A session with Manthan CTO, Atul Batra on using AI and data science in Business Models. This session was all about how effective business decisions can be made using AI in analyzing the data.

There wasn’t any practical demo. It would’ve been great if a quick demo was shown on how Manthan works!

IBM Code Challenge

IBM Code challenge was running for past 1 month. I was a participant too! And guess what I got the 2nd prize The challenge had 3 mini challenges – Riddle challenge, Trivia challenge and Build a chatbot challenge. I built a simple Pizza ordering chatbot using IBM Watson Conversation API.

Building a Chatbot using Watson conversation API is a breeze. You can train your chatbot to a highly sophisticated chatbot if you spend a decent amount of time training Watson with appropriate intents and dialogs.

If you want to build a chatbot then Watson conversation service should be your first priority.

What was the prize I got in IBM Code challenge? It was a Fitbit Charge 2 It definitely Adds some value in tracking my fitness goals!

Data science and AI in robotics

One important point from this session is that We are using Robots extensively in doing repetitive actions which are “Thing faced” but we aren’t using Robots in “Human faced” actions which have a social element or emotional element or direct interaction with users.

There are many reasons why we aren’t using Robots in “Human faced” actions, It could be about how secure it can be or the fact that Robots aren’t sophisticated enough to understand human emotions.

Nao Bot was brought to the session for a demo. Nao bot was looking so cute! I wanted to buy one as soon as I saw this bot. But it is expensive at around 11 Lakhs INR

Anyway, The demo was to use Nao Bot as an interactive Robot for document verification. Let’s say a user walks into a bank for completing his KYC compliance, then a Robot should be able to scan the documents and verify if everything is provided correctly or not and complete the process without any manual intervention. But the demo did not work because of some network issues or lighting issues to scan the document.

I felt that these are the kind of practical challenges that need to be addressed when it comes to AI, Visual recognition or Machine learning. For example, A shadow on an image can screw up visual recognition in apps like Cam Scanner. It is getting better and better every day but there’s still a long way to go!

Controlling a Bot wirelessly using IBM Watson APIs

A simple bot was made to explain how it can be controlled wirelessly using Watson APIs. It had simple functionality like changing colors, Moving, rotating etc. This is not a sophisticated bot. It was made just to show the wireless connectivity with bots. Check out the demo video below.

Visual recognition TJ Bot using Raspberry PI

This is like an extended version of the previous bot. TJ Bot is made of cardboard and the design is open source. Anyone can make this bot on their own. It can scan a person and tell their age based on Watson Vision API. It can answer general questions and say hi with its cute cardboard hand. Check out the demo below!

Code Labs and live demos at IBM Code day

There were many code labs and live demos at IBM Code day like Scalable WordPress implementation using Kubernetes containers, deep learning, scalable data science with containers, serverless cognitive actions, Insurance app using Blockchain etc.

I really liked the Blockchain demo which explains how a blockchain network can be used by an Insurance company. It looks like a perfect use case to explain about Blockchain practically.

In this case, An Insurance company, Automobile company and police department will be on a single Blockchain network. Let’s say a user lost his bike and files a complaint with the police and then claims his bike insurance with the insurance company. The insurance company can immediately cross verify the bike’s purchase details, police complain and confirm his insurance claim. This all happens immediately because they are on the same blockchain network. Read more about this use case here – Insurance app using Blockchain

Wrapping up

IBM Code Day was a great experience learning about latest technologies and interacting with developers and experts. I am going to more conferences like these! But the sessions can be made better by adding some fun element to it. I felt like a college lecture in few sessions but few others were amazing.

Thanks to IBM for organizing such an amazing event for developers! I Hope that an AI Robot with Machine learning skills will invite me at the entrance on next IBM Code Day

]]>https://codingislove.com/ibm-code-day-2018/feed/22306Build your first blockchain with nodeJShttps://codingislove.com/simple-blockchain-javascript/
https://codingislove.com/simple-blockchain-javascript/#commentsSat, 17 Feb 2018 14:23:50 +0000https://codingislove.com/?p=2301Blockchain is a big buzz word which is heard a lot these days. But it’s like the movie interstellar, everyone thinks its cool but no…

Blockchain is a big buzz word which is heard a lot these days. But it’s like the movie interstellar, everyone thinks its cool but no one really gets it. I personally also don’t clearly understand how blockchain is implemented to solve actual problems. But I know how the data structure works and that is what we will deal with in this blog post.

Introduction

According to Wikipedia,

A blockchain is a continuously growing list of records, called blocks, which are linked and secured using cryptography.

So basically, a blockchain is a data structure like linked lists with a growing set of nodes or blocks. Most of us would have learned about linked lists in school. Each node has several attributes including id, data, next node’s address etc. So, the blockchain is a slightly more complex data structure.

In a blockchain, each node is called a “block” and each block has an index, timestamp, hash of itself, hash of the previous block and of course the data. The data can be anything. In a cryptocurrency, the data is the details of the transaction like sender’s username, receiver’s username and amount.

Blockchain was initially implemented in 1991 to timestamp digital documents, to make sure they are not backdated or tampered with. But it didn’t get much attention as timestamping documents is not as cool as bitcoins.

The concept

The special thing about the blockchain is that it cannot easily be tampered with. The hash of each block gives it a unique identity like a fingerprint. And this hash is generated only once when the block is created. It is generated using the values of all the other attributes of the block. So, if any of the values are changed, the hash will no more be valid and hence the entire block will be invalid. So, this way nobody can tamper with the values stored in the block.

Another thing is if the hash is also recalculated, then it will not be detected as tampering. To avoid this, we store the hash of the previous block. This creates a chain of the blocks and if the hash of any block changes, the chain will be broken and the entire thing will be invalid. So, to successfully hack a blockchain, the hacker has to know the location of all blocks and simultaneously change all their hashes and relink the whole blockchain. This is not impossible but extremely tedious as there is a mandatory gap of 10 minutes between each transaction. So it beats the whole purpose of life.

These blockchains are not stored in a centralized server. But the blocks are distributed across the internet and they are connected using a peer-to-peer network. When someone joins the network, they are given the full copy of the network. When they create a new block, the block is sent to all the peers and the validity of the chain is checked. If everything checks out, the block is connected to the chain.

Let’s get to the code

We will be building a simple cryptocurrency called “cilcoin” (coding is love coin). So let’s get started on building possibly your very first blockchain.

Create an empty directory anywhere on your computer. In that, create a new file called main.js.

Create a class called “Block” which will represent a block in the chain. Give it a constructor with arguments as index, data and previous hash. Store the values of the arguments in consequent local variables. Create another attribute called timestamp and store the actual timestamp in it.

Create a function inside the class and name it getHash. We will be using the sha256 algorithm to generate the hash. So import the required files. Inside the function, calculate the hash by using the other attributes as salt. Salts are basically just arbitary string values used for generating hashes. Return the calculated hash. Also, create an attribute in the constructor and store the calculated value by calling our function.

Now, lets create a new class to represent the entire block chain. This class will have a single attribute called “chain” which will hold an array of blocks.

class BlockChain{
constructor(){
this.chain = [];
}
}

Create a function in this class called addBlock. This will be responsible for adding a new block to the chain and it will take the data as it’s argument. Inside the function, calculate the index of the new block by getting the length of the existing chain. Get the hash of the last block in the chain(If the chain is empty, use “0” by default). Finally, create a new object of the “Block” class and plug in the calculated values in the constructor. Push this object into the “chain” array.

Create another method in the same class and name it chainIsValid. In this function, we will be checking if the blockchain is following all the rules. It should have two checks as mentioned above. One, the validity of the hash, which can be done by comparing the stored hash with a newly calculated hash. If they match, then the values haven’t been tampered with. Then two, checking if the previousHash attribute is storing the same value as the hash of the previous block. This has to be performed on all the blocks in the chain.

We are now done with building the blockchain. Now we only have to add new blocks and test it.

Testing it

First lets test if the block chain is working without any errors. So, outside both the classes, create an object of the BlockChain class and name it CILCoin. Then, add a few blocks to it using the addBlock function.

]]>This is a quick tutorial on composing animations with React Native. This tutorial is made for beginners. Just read the React Native’s Animation documentation and Animated API’s documentation once before watching this tutorial.

]]>If you have installed postfix in your Ubuntu server from digital ocean and trying to configure Sendgrid SMTP then here are the instructions – Sendgrip SMTP with postfix

Looks like ports 25, 587 and 465 are blocked by Digital ocean. So make sure to use port 2525. I wasted a whole day trying to figure out what’s wrong. But turns out that 2525 is the only port supported by Sendgrid and allowed by Digital Ocean.

I was actually using PhpMailer and trying to configure SMTP with that but It did not work So I installed postfix to investigate the issue from scratch and found that all I had to do was change the port to 2525.

So If you had an issue configuring Sendgrid SMTP with a Digital Ocean server then use port 2525 and It should work!

]]>https://codingislove.com/unable-configure-sendgrid-postfix-digitalocean-server-solved/feed/02165Real-time search using basic JavaScripthttps://codingislove.com/realtime-search-javascript/
https://codingislove.com/realtime-search-javascript/#commentsMon, 18 Dec 2017 14:44:12 +0000https://codingislove.com/?p=2243Introduction We have all seen a lot of websites which have real-time search filtering. This looks really good and provides a convenient user experience. If…

Introduction

We have all seen a lot of websites which have real-time search filtering. This looks really good and provides a convenient user experience. If you are still wondering what real-time search is, it is a way of displaying the filtered result as the user is typing. This is much better than waiting for the user to finish typing the whole search query and then press a button. Real-time search saves time because the user need not type the whole query as there is a high chance that he/she will see the intended result before completing the query.

The concept behind filtering

The concept is quite simple. To conduct a search filter, we need something to search from. A list of sorts which has a collection of strings from which the user will search. It’s quite understood that we need to use an array for this in JavaScript.

So, now we have an array of strings to search from. Now, we need to make a function that can fetch all the relevant strings from the array based on the user’s search query. And this function needs to be called for every letter the user types. Based on the result of this function, we need to change the view and display the result on the screen. That’s it. That is the concept behind real-time search.

Now let’s get coding

Let’s make a simple HTML page with a search box and an unordered list with a few dummy items. Create a new directory and in that create an “index.html” file. In that file, put some basic HTML code with an ‘input’ tag and an ‘ol’ tag with a few dummy ‘li’ tags in it

Now, it’s time for the most important part of the code. The function to filter the array. Let’s break down the required function into small pieces.

Let’s call the function “updateResult”.

All functions generally have an input and an output. In this function, the input will be the what the user types in the search box. This will be the only argument the function takes. It won’t have any output but it will have an action i.e changing the view. So, it need not return anything.

function updateResult(query) {
}

Now, we need to call this function every time the user types something. The easiest way to do this is the “oninput” attribute of the “input” element. So, put an oninput attribute to our input box and pass its value.

Iterate through the array to linearly check each element if the entered words match the array elements. To check if it matches, you can use the Array.indexOf() function. If it returns -1 it doesn’t match. We need to call the indexOf() function for each of the words the user enters. We can do that by using the split() and map() functions. Don’t forget to use String.toLowerCase() to ignore case sensitivity.

This will keep adding items to the list as the function is called. The items will keep stacking up. To avoid this, we need to truncate the list at the begining of the function itself. So, finally your code should look like this.

Open the index.html file in your browser. It will look something like this.

Final thoughts

Wasn’t that easy?

I hope you understood how it works now. In this tutorial, we used simple linear search. This won’t always be feasible. When there are more elements in the array, better search algorithms need to be used. If you know of any good algorithms, please share in the comments below. Also, I will be hosting the code I wrote on GitHub. Feel free to make contributions.

]]>A simple HTML template for displaying site under maintenance. Use this to quickly show a nice message which says “We apologize for the inconvenience, but we’re performing some maintenance.” with a contact email.

]]>https://codingislove.com/site-maintenance-page-template-free-download/feed/22145Convert Json to csv and csv to Json in Excel VBAhttps://codingislove.com/json-csv/
https://codingislove.com/json-csv/#commentsWed, 25 Oct 2017 11:28:58 +0000https://codingislove.com/?p=2006I recently received a query on how to convert JSON to CSV. Usually, If the JSON file is small or has a simple structure then…

]]>I recently received a query on how to convert JSON to CSV. Usually, If the JSON file is small or has a simple structure then I would use any of the online converters to quickly convert it to CSV. But if the JSON is complex or needs more customizations then I would convert it using VBA.

Code Explanation

I saved my JSON file in the same folder as the excel file so I used Application.ActiveWorkbook.Path to get the current path. You can just set it to full file path if required.

FreeFile method is used to get the next free file number which will be used as a temporary memory stack. In the above code, you can just use fileNum = 1 but it might cause some issues when you are working with multiple files simultaneously.

Then open the file using Open For Input method, read the entire file and store it in jsonString

Code Explanation

It’s self-explanatory if you read the first Code explanation. Print method writes to a new line each time it is called.

Write to CSV file in VBA

Now Let’s understand how to write to a CSV file. CSV file is nothing but a text file with rows separated by line and columns separated by a comma.

We can directly export an excel file as CSV but I’m showing this example only to explain the CSV format and how to programmatically generate them so that this knowledge can be used in converting JSON to CSV

Code Explanation

I have set the range to be dynamic so that same code works for 3 rows and also 1000 rows. Range("A10000").End(xlUp) method gets the last used cell in column A. In this case, Range becomes A1:A3

ColumnsNum can also be made dynamic but usually, Columns are not very high in number so I mentioned the number of columns manually here. Change the columnsNum according to your data.

Then we open a file, Loop through each row, Loop through each column in the row and concatenate the columns to one row separated by a comma and print the row data and close the file after the loop is complete. CHR(34) = Double Quote

CSV File will be saved in the same as folder as the excel file in which this code is executed.

Convert JSON to CSV

Now You know how to read a file and write a file. Let’s read a JSON file, parse it and convert it to CSV file. I’m using a library VBA-JSON to parse JSON in VBA. I’ve explained it in detail in Import JSON to Excel post. If you haven’t read that then go have a look before you read this.

Code explanation

I’m using ADODB recordset to read CSV file. We can also use Open as Input method and read line by line getting each row. But csv doesn’t always mean that new line = new row. Sometimes there may be line breaks in the fields. So I’m using ADODB connection.