ComputersConsumer ElectronicsCell PhonesHome Theater & AudioGraphic Design & Video EditingInternetIndustrial Technology

How to Use Google Sheets to Insert Data Into MySQL

Updated on September 15, 2017
ronny2005 profile image

Android developer with more than four years of experience and also an enthusiastic Engineer.

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.

Google Sheets Home Screen on a browser
Google Sheets Home Screen on a browser

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.

Spreadsheet screen
Spreadsheet screen

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).

phpMyAdmin import screen
phpMyAdmin import screen

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.

More approaches

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.

One more important feature I stumbled upon is the Scripts editor. You can insert scripts (JavaScript) into the spreadsheet and use the functions of the scripts in the spreadsheets like formulas. I hope Google Sheets becomes of great help in your case, too.

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.