How to Use Google Sheets to Insert Data Into MySQL
Google Sheets is a spreadsheet application that can help you create spreadsheets on the go, which can then be synced across all the devices your Google account is logged into. I recently worked on a project where thousands of rows of data had to be collected in a single place. A good solution I found was to use Sheets because it is a completely online application and automatically syncs the data across all the devices.
But the tricky part is to insert the data into the database, say MySQL. Databases are similar these days, so the process is nearly the same for any database you use. In my case, it was MySQL, so I will be describing the steps based on it.
In order to use Google Sheets, all you need to have is a Google account. Log in to your account and head over to Sheets. You will be shown a home screen similar to what you see in the picture below.
You will be taken to a screen similar to the picture below. What I like about Sheets is its clean and simple user interface. But, its simplicity doesn’t compromise the stock of features it offers. I found it as sophisticated as Microsoft Excel in terms of the features which is a lot to have in an online application.
You are free to use a wide number of formulas that have quite similar structures to that of Microsoft Excel. Google Sheets has some unique features, too. For instance, you can view the responses to a form created by Google Form in Sheets. That way, you wouldn’t have to worry about collecting the data of a survey, organizing and publishing them. Using Google Sheets and Forms, you can easily conduct mass surveys and research.
Saving the spreadsheet (or not!)
Let’s get back to the topic. After you add some rows, it’s time to save your spreadsheet. The fun part here is that you don’t have to save your spreadsheet at all! Each input you make is automatically saved and synced everywhere. So, you need not worry about data loss or any kind of accident resulting to data loss.
Inserting to the database
From here onward, there are numerous ways to doing the inserting part. I will specifically mention how I managed to insert data into MySQL using a popular tool called phpMyAdmin. It is a GUI version for the command-line MySQL with a lot of features. One of the many features is exporting and importing data. You can import data in several formats. Below you can see the import screen where the format selected is ODS (OpenDocument Spreadsheet).
Google Sheets lets you download the spreadsheet in different formats. One of the formats is ODS. Therefore, the process becomes a lot simpler due to the formats available on both parties. After this, the database is populated itself.
The screenshot shows data being imported to the database, but you can directly import the data into a specific table. Keep in mind that the column names must be identical on both the table and the Google Sheets spreadsheet for the import process to work as expected.
If all this seems a little manual, or you feel you could use some coding to achieve the task, it’s fairly easy too. The Google Sheets API is a rich API with detailed documentation for different programming languages. Here’s the link.
The reason Sheets is so popular is because of its robustness and versatility. It can be used in so many ways that someone is coming up with a new add-on or feature to integrate to it everyday. You can visit the add-on store which has many useful add-ons that simplify tasks on Sheets. Here’s the link. One add-on I found useful in my case is the Export Sheet Data add-on that exports your spreadsheet in JSON or XML format, also allowing you to customize the overall output.