Introduction to Google Sheets as a Database
As the saying goes, "There is no-one-Database for everyone," the needs of each user may require a different solution with different functionalities to fulfill those needs. There could be a user that may prioritize speed over expandability, while others may prioritize maintainability over some other vital aspect and so on. There are many prominent options out into the market that cater to these varying needs of the customers, such as MySQL, Oracle, PostgreSQL, Microsoft SQL Server, MongoDB, Redis, Elasticsearch, Cassandra so on. Each one provides some functionality that the other may lack.
Using NoSQL databases, developers do not need to convert in-memory structure to relational structure. Click to explore about, NoSQL
Why it is important?
Using google sheets as a Database against other options has a number of pros and cons. Let us look at some of the most important ones below :
Pros
- Connectivity: Using it as a Database has the advantage of storing data Securely on Google cloud. As it is a Web Application, it is thus available online anywhere and anytime.
- Access Control: Using it gives you access to Google's access control system. With only a few clicks, this enables the users to provide view and edit permissions to your data or revoke existing permissions.
- Price: Among many other services, Google sheet is also one of those that are free for unlimited usage. While other alternatives such as MS Excel require a license or a subscription to be first bought. This provides one less layer of cost for development.
- Learn and adapt: For any user, whether they have already used spreadsheet software or not. Anyone can learn the format and utilize its full potential in a very short period. The adaptability curve and learning time needed are very small.
- Visualization: Due to the vast support and usage of it, there are many features built-in to the software that enable users to manipulate data appearance and structure quickly. This can then further be visualized and analyzed in no time.
Cons
- Scalability: Scalability is one of the main factors that Google sheets lack. There are data limits for storage and also, as the data grows in size, so does the latency in response. Thus making it not the best option in this term.
- Query Options: It surely provides query mechanisms, but these are limited compared to a DBMS. A DBMS would provide advanced query mechanisms that can easily handle large amounts of data while Sheets will not.
- Shareability: This factor is a major drawback for Google sheets when DBMS are built to allow multiple users to access and modify data simultaneously. This is not the case with it, inconsistencies can frequently occur when two or more users are working with the same data set simultaneously.
A fully managed database service helps to set up, manage, and administer your database in the cloud and manage and also provide services for hardware provisioning and Backup. Click to explore about, Benefits and Use Cases
Data Manipulation on Google Sheets
The use case for Google sheet as a DataBase are numerous, but let us take a specific example of small businesses. Let's say you own a restaurant and keep logs of all your clients and purchases. Each interaction by the client, such as a purchase, complaint, order, etc., is recorded for each client. Each of these record additions is called a "Create" operation in terms of it and means that we added a new record to the it.
Now, on occasion, you want to find the breakfast order for a specific customer. This is called a "Read" operation. When you modify an existing entry for a customer, we call it an "Update" operation. Finally, when you want to remove a record entry from the database, it's called a "Delete" operation.
These operations are commonly known as the CRUD operations and form the core of the actions that someone would perform on a Database. These should be supported by any tool trying to manipulate it. These can be done either manually or by using automation on code level on a Google Sheet. Manual manipulation of it includes the user actively inputting the newly acquired details of the customer. This process is viable for cases where the updation of it is not frequent and thus can be performed when the data is available.
Automatic manipulation of Google Sheets can be done using the Google Sheets API. This option comes into the picture for cases when we have an application developed or developed for Data management. You can connect this application to Google Sheets via the Sheets API and thus automate the import/export of data. Google provides various in-built libraries for languages, including :
- Go
- C#
- Java
- Browser Javascript
- PHP
- Python
- Ruby
- Node.js
This allows you to perform the CRUD operations on the Sheets.
The basic difference between SQL vs. NoSQL vs. NewSQL. These are the types of databases. Click to explore about, SQL vs NoSQL vs NewSQL
Procedure to Set up Sheets as Database
Google Sheet APIs come with various functionalities and interaction options for its interface. It can let you:
- Data Read/Write
- Formating data
- Charting
- Apply formulas on data
- Table Pivots
- And so on.
But to use these functionalities in your application, we need first to get the app authenticated by google and then set up the API configurations for accessing the features. The main steps needed to be completed to finalize authentication and app enablement include the following :
- Enabling its API on GCP Account.
- Go to the Google APIs Console
- Click "Select a project" in the top-right corner
- Click the "New Project" option
- Give your project a different identity and press the "Create" button.
- Navigate to the APIs dashboard.
- Look for "Google Sheets API" and then click on it.
- Wait for the API to be enabled by clicking "Enable."
Making a Service Account.
- Navigate to the Credentials page once the API has been enabled.
- Select " Service Account " from the available "Create Credentials" drop-down box, select "Service Account."
- After that, click "Create" and give the service account a name.
- Select "Done" after clicking "Select a role" => "Project" => "Editor."
Adding a Service Key to the GCP Account's credential section.
- Click on the name of your service account on the Credentials page.
Go to "Keys." - Select "Add Key" => "Generate new key"
- Leave the JSON option selected and press the "Create" button.
- This operation will retrieve a JSON file, which should be renamed client secret.json.
Share your spreadsheet with the service account's client email.
- In the top-right corner of your spreadsheet, click "Share".
- Paste the client email in the field and give Editor rights.
- Click "Send".
After the steps are completed, the app is authenticated, and you also get a client_secret.json file. This file can be used to Read and Write data from/to the Google Sheet.
For reading data: Authenticate -> Connect to it -> Query the data based on sheet ID and range.
For Writing data: Connect to it -> Write data in specific cells.
While writing data, we need to make sure that the selected range (rows and columns) matches the number of data to be written.
PaaS helps one focus on developing the application as it provides the rest of the requirements needed for the development, including middleware, servers, and storage. Click to explore about, PaaS - An Efficient Cloud Computing Service
How to use it for HTML Pages?
You can find all the necessary resources with Google's App Script required to create HTML pages and have dynamic interaction with its content using Google Sheets only.
Below are the basic steps required for creating a simple HTML page using Apps Script and Google Sheets as a Database:
- Make a new spreadsheet in Google Sheets.
- Rename the sheet to something more understandable and trackable, such as "QuickStart Data."
- To access the Script Editor, go to Tools -> Script Editor.
- The project should now be called "Demo HTML Page. "
- Select "HTML" from the " + " icon next to "Files" on the left side.
"Homepage" is the name of the new HTML file. - Update the HTML code snippet provided below to the new HTML page.
- You'll need to change the code in the "code.gs" file with the code provided below.
- Then, click the Deploy button in the top-right corner and select New deployment.
- Select "Web App" from the list by clicking the gear icon next to "select type."
- Deploy after providing an appropriate description.
- For the first time, you will be required to provide authorization. The app will after being released.
Time to move away from Google sheets as Database
We have so far discussed the pros/cons of it as a Database, How to switch to Sheets and the proper set of a sequence for enabling the switch. But it is very much possible that you have been using Sheets for your use case and now want to move to another option. This can be due to many reasons ranging from More diverse automation options, scalability, speed, etc.
Some functional limitations may be hindering your application from growing at the pace it actually can, such as :
Data Limitations
It limits storage to 5 million cells. Thus, for businesses whose data grows rapidly and has the potential to expand a lot, the Limit of Google Sheets may be hit. This will require users to switch to a more expandable option instead, which can be quite tedious.
Consistency
Although its API is great for simple usage and can also manage common requests quite well, it is not always consistent. There might be many cases where requests fail or time out for no viable definitive reason. Most of the Common DBMS have incorporated "fault tolerance" functionalities that help solve this kind of problem. In contrast, in Google Sheets, these handlers need to be created by the users themselves.
Security
It can be considered a very prominent tool for storing simple data formats and structures, but from the security point of view, some other option may prove to be a better fit. For example, if you want a its to store some sensitive user information and/or passwords that may need to be encrypted on the go, you may want to use a its system that provides more advanced tools for this kind of data.
In general, the most optimal option for anyone to switch from Google sheets to another its option is by mapping a CSV, tsv, or similar accepted file format and transferring the data accordingly.
Conclusion
It is important to remember that Google sheet is a fantastic option for cases with small and vocational data dependencies. It can prove to be a great time saver for cases like Proof of Concepts, demos, small prototypes, and so on. But if you feel like your use case has outgrown the bounds of Google Sheets, you should look for a more suitable option and switch.
- Explore about Database Testing Types and its Tools
- Read more about Database-as-a-Service
