Google Sheets and SQL Server can make a powerful data management combination. The cloud-based Sheets provides easy collaboration, while SQL Server offers robust relational database capabilities for storing and querying data.

Connecting your Google Sheets to SQL Server database allows you to synchronize data between these two platforms. This opens up possibilities like building collaborative business intelligence dashboards on top of your Sheets data fed from a SQL Server database.

In this guide, we outline two simple methods for establishing connectivity and syncing data between Google Sheets and SQL Server. By linking these cloud-based spreadsheets and on-premises databases together, you can enable convenient data access and updates for your organization.

Explore These Methods to Connect Google Sheets and SQL Server

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.

This method would be time-consuming and somewhat tedious to implement. Users will have to write custom codes to enable two processes, streaming data from Google Sheets and ingesting data into SQL Server. This method is suitable for users with a technical background.

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

Hevo, an Automated Data Pipeline, provides you with a seamless solution to connect Google Sheets to SQL Server within minutes with an easy-to-use no-code interface. Hevo provides a simple interface to perfect, modify, and enrich the data you want to transfer into an analysis-ready form.

Load Data from Google Sheets to MS SQL Server
Load Data from Google Ads to MS SQL Server
Load Data from Salesforce to MS SQL Server

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:

Download the Ultimate Guide on Database Replication
Download the Ultimate Guide on Database Replication
Download the Ultimate Guide on Database Replication
Learn the 3 ways to replicate databases & which one you should prefer.

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: Self
  • Launch the Script Editor from, Tools>Script Editor. This editor will be used to write the scripts connecting to SQL Server. 
  • This is what the default page will look like:
Google Sheets to SQL Server: Execute Script
Image Source: Self

Step 3: Create a Database, Table, and User

Once you have created the spreadsheet and populated it with some data, now you have to create the database, tables, and users in the SQL Server to access the data from Google Sheets.

It is possible to create databases, users, and 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 3a. 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 3b: 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 3c. 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 3d: 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 4a: 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 4b: 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:

  • A lot of coding is required to move the data from Google Sheets to SQL Server. This method is only feasible for technical users.
  • 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 transform the data before moving it to SQL Server.

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

Hevo, a No-code Data Pipeline, provides an easy-to-use, completely managed cloud-based service that can execute the load operation from 150+ 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:

  • Step 1: Configure Google Sheets as a source by entering the Pipeline Name and the spreadsheet you wish to replicate.
Google Sheets to SQL Server: Source Configuration
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, and Database Schema.
Google Sheets to SQL Server: Destination Configuration
Image Source

You may transfer data from a Google Form to SQL database by completing the above-mentioned steps.

Here are a few benefits of using Hevo:

  • In-built Transformations: Format your data on the fly with Hevo’s preload transformations using either the drag-and-drop interface or our nifty Python interface. Generate analysis-ready data in your warehouse using Hevo’s Postload Transformation.
  • Near Real-Time Replication: Get access to near real-time replication for all database sources with log-based replication. For SaaS applications, near real-time replication is subject to API limits.
  • Auto-Schema Management: Correcting improper schema after the data is loaded into your warehouse is challenging. Hevo automatically maps source schema with the destination warehouse so that you don’t face the pain of schema errors.
  • Security: Discover peace with end-to-end encryption and compliance with all major security certifications including HIPAA, GDPR, and SOC-2.

Reasons to Migrate Data from Google Sheets to SQL Server

Google Sheets allows you to edit, arrange, and analyze various types of data. It enables collaborations, concurrent editing, and formatting by multiple users and tracks all spreadsheet revisions.

SQL Server offers improved performance thanks to its integrated transparent data compression and encryption features. SQL Server provides access controls and effective permission management tools to assist users in protecting sensitive business data.

When integrated together, moving data from Google Sheets to SQL Server could solve some of the biggest data problems for businesses.

Additional Resources on Google Sheets to SQL Server

Conclusion

  • In this blog, you have learned how to import data from Google Sheets to SQL Server.

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

Vishal Agrawal
Technical Content Writer, Hevo Data

Vishal Agarwal is a Data Engineer with 10+ years of experience in the data field. He has designed scalable and efficient data solutions, and his expertise lies in AWS, Azure, Spark, GCP, SQL, Python, and other related technologies. By combining his passion for writing and the knowledge he has acquired over the years, he wishes to help data practitioners solve the day-to-day challenges they face in data engineering. In his article, Vishal applies his analytical thinking and problem-solving approaches to untangle the intricacies of data integration and analysis.

Free No-Code Data Pipeline for Google Sheets