Connect Google Sheets to SQL Server: 2 Easy Methods

on Data Integration, ETL, Tutorials • August 27th, 2020 • Write for Hevo

Does your organization use Google Sheets heavily to store data, and now you are planning to move the entire datasets to SQL Server for better storage, computational powers, analysis of features, and transformation of data? If yes, then this blog post will help you to migrate your data from Google Sheets to SQL Server smoothly.

Table of Contents

Introduction to Google Sheets

Google Sheets Logo
Image Source

Google sheet is a product of the Google platform that provides the capability of Spreadsheet over the cloud. Google sheet is similar to the Spreadsheet app but on Steroid. Due to its cloud behavior, it offers much more functionality than a standard spreadsheet.

Key Features of Google Sheet

  • Google Sheets allow you to access it anywhere over the internet, and hence it provides you with high availability of data.
  • Google Sheet has a separate app that is available on all the operating systems that allow you to view, update and visualize your data.
  • Google Sheets comes bundled with a Gmail account, and you can access it via the Apps section.
  • It uses Google Drive as storage and allows you to access it over apps.
  • Google Spreadsheet has a feature similar to Microsoft Excel. So, if you have used MS Excel, then using Google sheet will be very simple for you.
  • Google Sheet allows you to download the plug-in, add-ons, and custom code to analyze data in spreadsheets effectively.

Introduction to SQL Server

SQL Server Logo
Image Source

SQL Server is a relational database owned by Microsoft to store structured data. It is commonly known as MSSQL and is built on top of Structured Query Language (SQL) to query and perform analytics on the database object. SQL Server has extensive support for ANSI SQL and PLSQL to create stored procedures, derived variables, etc. 

SQL Server was initially available for Windows, but now it supports the Linux operating system as well.

Key Features of SQL Server

  1. Scalable: SQL server is an on-premise database, and you can add more nodes to accommodate the increasing volume of the data.
  2. Performance: SQL Server provides exceptional performance in terms of transformation and analysis of the data. It has excellent support for Windows and Linux.
  3. Secure: Being an on-premise installation, you can have your private network to secure the data. It also supports various authentication methods like Kerberos for safe access to data. 
  4. Analytics Support: MS SQL has excellent support for data analytical functions and machine learning models. You can use Python or R to connect to SQL Server and carry out modeling. 
  5. Multi-Threading: With MS SQL, you can achieve parallelism and multi-threading to process the data.

Moving Data from Google Sheets to SQL Server

There are various methods for transferring data from Google Analytics to SQL Server. In this post, you will peruse through the following approaches:

Method 1: Using the Apps Script to connect Google Sheets to SQL Server

This is a simple 4-step process that starts with whitelisting the IP and creating a Google Sheet. It involves creating a database, user, and table before loading the data into SQL Server. This process has a few limitations that are covered later in this article.

Method 2: Using Hevo to connect Google Sheets to SQL Server

Hevo Data is a No-code Data Pipeline. It is a cloud-based ETL tool. It can automate the process of configuration. It also offers a set of features to overcome the drawbacks of running a script. Hevo is compatible with 100+ Data Sources. Hevo helps you to transfer data from Google Sheets to SQL Server conveniently.

Get Started with Hevo for Free

Its strong integration with various sources such as databases, files, analytics engines, etc gives users the flexibility to bring in data of all different kinds in a way that’s as smooth as possible, without having to write a single line of code.

Transfer Data from sources like Google Sheets to your target destination for Free!

Understanding the Methods to Connect Google Sheets to SQL Server

These are the methods you can use to establish a Google Sheets SQL Server connection in a seamless fashion:

Method 1: Using the Apps Script to connect Google Sheets to SQL Server

To load data from Google Sheets to SQL Server, you need to follow these steps:

Prerequisites

  • Have access to Google Cloud and Google Sheets.
  • Basic understanding of Spreadsheet functionalities.
  • A working instance of SQL Server, with necessary privileges.
  • Basic knowledge about programming and concepts.

Loading the data from Google Sheets to SQL Server is not a tedious task; however, it requires a script that will connect to SQL Server to access the objects. Google App Script provides a JDBC service to connect to SQL Server. Below is the step-by-step approach to load data from Google Sheets to SQL Server.

Step 1: Whitelisting the IP 

Google JDBC service requires you to whitelist specific IPs to create a database connection using the JDBC service. In your MS SQL database settings, add the below IPs to the whitelist.

64.18.0.0 - 64.18.15.255
64.233.160.0 - 64.233.191.255
66.102.0.0 - 66.102.15.255
66.249.80.0 - 66.249.95.255
72.14.192.0 - 72.14.255.255
74.125.0.0 - 74.125.255.255
173.194.0.0 - 173.194.255.255
207.126.144.0 - 207.126.159.255
209.85.128.0 - 209.85.255.255
216.239.32.0 - 216.239.63.255

Step 2: Create a Google Sheet

  • Login to your Google Account and from the Apps section go to Google Drive.
  • Once you log in to Drive, from the option New, select Google Sheets.
  • A new Sheet will open in the New Tab.
  • Provide a name to the SpreadSheet and add some data.
Google Sheets to SQL Server:  Google sheet data
Image Source
  • Launch the Script Editor from, Tools>Script Editor. This editor will be used to write the scripts that will connect to SQL Server. 
  • The default page will look like below:
Google Sheets to SQL Server: Execute Script
Image Source

Step 3: Create a Database, Table, and User

Once you have created the spreadsheet and populate it with some data, now you have to create the database, tables, and users in the SQL Server to access the data from Google Sheet. It is possible to create databases, users, tables by using the SQL Server command line or from the workbench, and in this case, you will do the same with the Apps Script. Go to the Script editor and write the below lines of code:

Step 1. Create Connection Variables

Update the variable assignment with the actual values.

var connectionName = 'Instance_connection_name';
var rootPwd = 'root_password';
var user = 'user_name';
var userPwd = 'user_password';
var db = 'database_name';

var root = 'root';
var instanceUrl = 'jdbc:google:mysql://' + connectionName;
var dbUrl = instanceUrl + '/' + db;
Step 2: Create a New Database

Use the following command to create a new database.

function createDb() {
  var con = Jdbc.getConnection(instanceUrl, root, rootPwd);
  con.createStatement().execute('CREATE DATABASE ' + db);
}
Step 3. Create a New User with the Necessary Privileges

Create a new user in the database with the necessary privilege.

function createUser() {
  var conn = Jdbc.getConnection(dbUrl, root, rootPwd);

  var stmt = conn.prepareStatement('CREATE USER ? IDENTIFIED BY ?');
  stmt.setString(1, user);
  stmt.setString(2, userPwd);
  stmt.execute();

  conn.createStatement().execute('GRANT ALL ON `%`.* TO ' + user);
}
Step 4: Create a New Table

Use the following command to create a new table:

function createTable() {
  var conn = Jdbc.getCloudSqlConnection(dbUrl, user, userPwd);
  conn.createStatement().execute('CREATE TABLE employee'
      + '(emp_id INT NOT NULL, emp_name VARCHAR(255), emp_dept VARCHAR(255); ');
}

Step 4: Writing to Database

Now that you have created the database, tables, and user, you will now create the function to write the data from Google Sheets to SQL Server. Use the following code to write in SQL Server using batch mode. 

Step 1: Create a Connection Variable

Update the variable assignment with actual values.

var connectionName = 'Instance_connection_name';
var user = 'user_name';
var userPwd = 'user_password';
var db = 'database_name';
var dbUrl = 'jdbc:google:mysql://' + connectionName + '/' + db;
Step 2: Write Data to SQL Server

Write data to a table in a single batch.

function writeManyRecords() {
  var conn = Jdbc.getConnection(dbUrl, user, userPwd);
  conn.setAutoCommit(false);

  var start = new Date();
 var sheet = SpreadsheetApp.getActiveSheet();
 var data = sheet.getDataRange().getValues();

 var stmt = conn.prepareStatement('INSERT INTO employee ' +  '(emp_id, emp_name, emp_dept) values (?, ?, ?)');

 for (var i = 0; i < data.length; i++) {
  stmt.setString('Emo Id: ' + data[i][0]);
  stmt.setString('Emp Name: ' + data[i][1]);
  stmt.setString('Emp Dept: ' + data[i][2]);
  stmt.addBatch();

}

  var batch = stmt.executeBatch();
  conn.commit();
  conn.close();

  var end = new Date();
  Logger.log('Time elapsed: %s ms for %s rows.', end - start, batch.length);
}

Click on the Run Script to run the Script. Check the SQL Server for the data.

Limitations of using the Apps Script to connect Google Sheets to SQL Server

Limitations of using Apps Script for connecting Google Sheets to SQL Server are listed below:

  • There is a lot of coding required to move the data from Google Sheets to SQL Server. Suppose you’re looking for a more straightforward solution. In that case, we recommend you to try Hevo Data. It can be set up within minutes and without writing any code.
  • Google Sheets with Apps Script has some time limits on script execution. This process is not reliable when you need to move vast volumes of data.
  • Extra coding is required if you want to perform the transformation on the data before moving it to SQL Server.

Method 2: Using Hevo to connect Google Sheets to SQL Server

Hevo Logo
Image Source

Hevo Data, a No-code Data Pipeline, provides an easy-to-use, completely managed cloud-based service that can execute the load operation from 100+ data sources such as Google Sheets to SQL Server in a few clicks. Hevo also helps you transform your data and automatically map schemas in databases. The data migration from Google Sheets to SQL server can be carried out in 2 simple steps:

Sign up here for a 14-Day Free Trial!
  • Step 1: Configure Google Sheets as a source, by entering the Pipeline Name and the spreadsheet you wish to replicate.
Google Sheets Source Config
Image Source
  • Step 2: Configure SQL Server as a destination and start moving data instantly by entering the Destination Name, Database Host, Database Port, Database User, Database Password, Database Name, Database Schema.
SQL Server Destination Config
Image Source

Here are a few benefits of using Hevo:

  • Completely Automated: Hevo platform can be set up in minutes and requires minimal maintenance.
  • Real-Time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
  • Transformations: Hevo provides preload transformations through Python code. It also allows you to run transformation code for each event in the pipelines you set up. You need to edit the event object’s properties received in the transform method as a parameter to carry out the transformation. Hevo also offers drag and drop transformations like Date and Control Functions, JSON, and Event Manipulation to name a few. These can be configured and tested before putting them to use.
  • Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer. 
  • Fault-Tolerant: Hevo is capable of detecting anomalies in the incoming data and informs you instantly. All the affected rows are kept aside for correction so that it doesn’t hamper your workflow.
  • Live Support: Hevo team is available round the clock to extend exceptional support to you through chat, email, and support calls.
  • Schema Management: Hevo takes away the tedious task of schema management and automatically detects the schema of incoming data and maps it to the destination schema.
  • Live Monitoring: Hevo allows you to monitor the data flow, so you can check where your data is at a particular point in time.

Conclusion

In this blog, you have learned how you can move the data from Google Sheets to SQL Server. However, if you’re looking for an easy solution, then, try –  Hevo Data, a No-code Data Pipeline. It helps you transfer data from a source of your choice in a fully automated and secure manner without writing the code repeatedly.

Visit our Website to Explore Hevo

Hevo supports pre-built integrations with 100+ sources and allows you to export, load, transform and enrich your data to make it analysis-ready in a jiffy.

Give Hevo a try by Signing up for a 14-day free trial!. You can now transfer data from sources like Google Sheets to your target destination for Free using Hevo!

Share your experience of connecting Google Sheets to SQL Server in the comment section below.

Free No-Code Data Pipeline for Google Sheets