How to Connect Google Sheets to SQL Server Using 2 Methods?

Google Sheets and SQL Server can make a powerful data management combination. The cloud-based Sheets provide 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 platforms. This opens up possibilities like building collaborative business intelligence dashboards on top of your Sheets data from the SQL Server database.

This guide outlines 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.

What are Google Sheets?

Google Sheets is a cloud-based spreadsheet application offering a flexible and user-friendly platform for data organization, analysis, and collaboration. Users can access it from any device connected to the Internet, enabling real-time processing and many other useful features.

Key Features:

• Integration and Automation: It offers a variety of add-ons to extend functionality, including third-party integrations.
• Real-Time Collaboration: It allows many users to work on the same spreadsheet, and all changes are saved automatically. The changes show up in real-time.
• Accessibility and Convenience: It is accessible through any web browser or mobile Google Sheets app. It also supports offline editing and automatically synchronizes changes once back online.
• Data Management and Analysis: It offers extensive support for functions and formulas used in calculations and data analysis, together with tools for creating various charts and graphs that make it easy for its users to analyze data.

Overview of SQL Server

SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is designed to store, manage, and retrieve data in response to requests from other software applications. SQL Server is built on the SQL (Structured Query Language) standard, which is used to query and manage relational databases.

Key Features of SQL Server:

• TSQL: Robust Transact-SQL in complex queries and stored procedures.
• Scalability: Supports huge databases and multiple concurrent users.
• High Availability: SQL server high availability has features including Always On and Failover clustering.
• Security: Tight security through solid encryption, auditing, and row-level security.
• Integration: Integrates very well with other Microsoft services and Third-Party Tools
• Data Tools: In-depth tools for ETL, reporting, and data analysis. 
• Cloud Integration: Comparatively much easier to integrate with Azure services

What are the ways to load Google Sheets data to SQL Server?

Method 1: Using Hevo

Step 1: Configure Google Sheets as Your Source

Step 2: Configure SQL Server as Your Destination

With these two steps, you will be able to connect Google Sheets and SQL Server seamlessly.

Method 2: Using the Apps Script

Google App Script provides a JDBC service to connect to SQL Server. Below is the step-by-step approach to load Google Sheets data 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.

• Launch the Script Editor from Extension> App Script. This editor will be used to write the scripts connecting to SQL Server. 
• This is what the default page will look like:

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

Use the following code to write in SQL Server using batch mode. 

Step 4.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 4.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

Migrating your Google Sheets data to the SQL server could have the following benefits:

• A lot of coding is required to move the data from Google Sheets. 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.

Why do you need to migrate Google Sheets Data?

Migrating your Google Sheets data to the SQL server could have the following benefits: 

• Enhanced Data Management: Centralized storage, improved data integrity, and better scalability.
• Improved Data Analysis: Powerful querying capabilities and seamless integration with BI tools.
• Enhanced Collaboration & Security: Controlled access, version control, and improved security.
• Automation & Integration: Efficient ETL/ELT processes and seamless integration with other systems.

Discover how to use Google Sheets as a database with our guide on leveraging its data management and analysis capabilities.

Conclusion

In this blog, you have learned how to connect Google Sheets and SQL servers using two methods. By leveraging SQL Server’s robust features, you can gain better control over your data, perform complex analyses, and improve overall data security and collaboration within your organization. 

One method involves using Apps Script to connect the two manually, while the other method uses Hevo to connect Google Sheets to SQL Server automatically. Sign up for a 14-day free trial and simplify your data integration process. Check out the pricing details to understand which plan fulfills all your business needs.

FAQs