How to Use Google Sheets to Insert Data Into MySQL

Updated on May 24, 2018
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.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, turbofuture.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://turbofuture.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)