Connect SQL Server to Google Sheets Simplified 101

on Activate, Google sheets, Hevo Activate, Microsoft SQL Server, Reverse ETL, SQL Server, Tutorials • March 23rd, 2022 • Write for Hevo

Connect SQL Server to Google Sheets_FI

SQL Servers are the open-source Relational Database Management System preferred by most developers to deploy web-based software applications. Also, the solution helps in creating and managing RDBMS systems. On the other hand, Google Sheets helps make your data pop with colourful graphs and charts. With features like built-in formulas, conditional formatting options, and pivot tables, you can save time and simplify common spreadsheet tasks for free. It also allows you to get a headstart with a vast variety of schedules, budgets, and other pre-built spreadsheets. Google Sheets is great for data handling, but importing raw data into Google Sheets from databases is a difficult job for a non-programmer.

In this article, you will gain information about how to connect SQL Server to Google Sheets. You will also gain a holistic understanding of SQL Server, Google Sheets, their key features, and the steps involved on how to connect SQL Server to Google Sheets. Read along to find out in-depth information about how to connect SQL Server to Google Sheets.

Table of Contents

Prerequisites

  • Basic knowledge of databases & tables in SQL Server
  • Basic Knowledge of Google Sheets
  • Required privileges to SQL Server

What is SQL Server?

Connect SQL Server to Google Sheets - MS SQL Server Logo
Image Source

SQL Server is a Relational Database Management System designed exclusively for Windows environments by Microsoft. It has a Client-Server architecture and supports ANSI SQL. The purpose to introduce SQL service was to enable different users to create, manage, and implement RDBMS systems simultaneously. The application software is often used at the backend to store and process all system data.

Also, with the proper administration of SQL servers, users can recover, optimize, and maintain server performance. It is easy to install and configure Microsoft SQL Server compared to other database management software. Also, it has built-in transparent data compression features and other exclusive features that make it a top choice among the rest.

Key Features of SQL Server

Connect SQL Server to Google Sheets: SQL Server architecture
Image Source

The main features of SQL Server that make it highly usable are:

1) Cloud Database Support

Microsoft SQL Server offers editions that can be integrated with Microsoft cloud or Azure SQL with built-in security and manageability. Cloud database support makes SQL Server highly available, fast failover with minimum uptime.

2) Ease of Management

Microsoft SQL Servers can easily be used with Windows and Linux systems. SQL Server deployment and its management is easy with Kubernetes.

3) High Security

Data in SQL Server is encrypted and highly secured. Schemas and Tables can be password protected easily and can be accessed with permissions only.

4) End-to-End Business Data Solutions

SQL Server mainly focuses on commercial needs so it provides end-to-end business data solutions. Microsoft SQL Server offers tools for Data Administration,  ETL solutions, Online Analytical Processing(OLAP), and Data Mining purposes. It additionally provides Option Reporting, Interactive Analysis, and Visual Data Exploration using SQL Server BI tools.

What is Google Sheets?

Connect SQL Server to Google Sheets - Google Sheets Logo
Image Source

Google Sheets is a Web-based free Spreadsheet tool launched by Google in 2012. The web mode allows multiple users to work simultaneously on the same sheet and collaborate effectively. Google Sheets is a SaaS (Software-as-a-Service) application that means it can be accessed remotely from anywhere anytime. 

Google Sheets also offers a revision history of the sheet to keep track of which user made what changes and can restore to any of the previous versions of the sheet. It gives you the option to choose from a variety of schedules, budgets, and other pre-made spreadsheets that are designed to make your work that much better and your life easier. A single sheet can store data in up to 5 million cells with 15 GB max as a free user and can scale up with different G-Suite plans.

For further information about Google Sheets, you can follow the Official Documentation.

Key Features of Google Sheets

Google Sheets is a widely used tool by companies and individuals that can be shared with anyone within or outside the organization. A few features of Google Sheets are listed below:

1) Offline Mode

Google Sheets not only works online but when the internet is not available, users can work on the sheet in offline mode, and the changes will be updated later on the sheets once the sheet gets online.

2) Explore Panel

Google Sheets also allows you to get instant insights with its Explore panel. The Explore panel also provides a list of suggested graphs that are representative of the data entered in the Spreadsheet. You can pick from Pie Charts, Pivot Tables, and Bar Charts.

3) Security

Google Sheets offers user access security control that lets the owner of the sheet decide who can view, edit, copy or download the sheet.

To know more about Google Sheets, you can visit the Official Documentation.

Ways to Connect SQL Server to Google Sheets

Method 1: Manually Connect BigQuery to Google Sheets

This method allows you to manually connect SQL Server to Google Sheets.

Method 2: Using Hevo Activate to connect Google BigQuery to Google Sheets

Hevo Activate helps you directly transfer data from Snowflake, Amazon Redshift Google BigQuery, etc., and various other sources to Google Sheets (Coming Soon!), CRMs such as Salesforce, HubSpot, various SaaS applications, and a lot more, in a completely hassle-free & automated manner. Hevo Activate is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

Hevo Activate takes care of all your data preprocessing needs and lets you focus on key business activities and draw a much more powerful insight on how to generate more leads, retain customers, and take your business to new heights of profitability. It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination.

Get Started with Hevo for Free

Check out what makes Hevo Activate amazing:

  • Real-time Data Transfer: Hevo Activate, with its strong Integration with various sources, allows you to transfer data quickly & efficiently. This ensures efficient utilization of bandwidth on both ends.
  • Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer. 
  • Secure: Hevo Activate has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
  • Tremendous Connector Availability: Hevo Activate houses a large variety of connectors and lets you bring in data from numerous Data Warehouses and load it into Marketing & SaaS applications, such as Salesforce, HubSpot, Zendesk, Intercom, etc. in an integrated and analysis-ready form.
  • Simplicity: Using Hevo Activate is easy and intuitive, ensuring that your data is exported in just a few clicks. 
  • Completely Managed Platform: Hevo Activate is fully managed. You need not invest time and effort to maintain or monitor the infrastructure involved in executing codes.
  • Live Support: The Hevo Activate team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.

Users can leverage Hevo Activate to perform the following operations:

  • Create User Segments: Creating user segments allows the Marketing and Sales teams to understand how resources should be utilized for different kinds of users. This allows teams to focus on channels that convert better and maximize their Return On Investment (ROI).
  • Build 360 View of Customers: This can be used to understand each customer better and plan strategies accordingly to ensure maximum revenue. This information can also be leveraged to help Support teams prioritize Enterprise customers. Businesses can seamlessly sync all customer data into their support software and respond quicker with a holistic customer background.
  • Sync Product Data into Sales CRM: Hevo Activate can be leveraged to get all product data in the CRM tool of choice, allowing businesses to track user activity easily. Users can be segmented based on their activity, and that information can be used to improve product adoption and prevent churns.
Sign up here for a 14-Day Free Trial!

How to Connect SQL Server to Google Sheets?

Let’s dive deep into the process to connect SQL Server to Google Sheets. You will learn to read data from your MS SQL Server database and write it into Google Sheets. Google Sheets come with a built-in app development platform called Apps Script. You can connect your database be it  MySQL, Microsoft SQL Server, or Oracle using Apps Script’s JDBC service.

The 2 methods for connecting SQL Server to Google Sheets are as follows:

Method 1: Connect SQL Server to Google Sheets Manually

The steps followed to connect SQL Server to Google Sheets manually are as follows:

Step 1: Whitelist IP Addresses

To create a database connection to MS SQL Server using the JDBC service, you must whitelist certain IP ranges in your database settings so that Apps Script can access it. Note that the JDBC service can only connect to ports 1025 and above so you have to make sure that your database service is not running on a lower port. When the IPs are whitelisted, the database connection can be created using one of the Jdbc.getConnection(URL) methods and the database’s URL that you want to connect to.

If you are using windows, you can follow the following steps to whitelist the IP addresses.

  • Step 1: Go to the Start Menu.
  • Step 2: Navigate to the “Windows Firewall and Advanced Security” section.
  • Step 3: Click on the “Inbound Rules” option.
  • Step 4: Click on the “MS SQL Server Rule” option.
  • Step 5: Switch to the “Scope” tab.
  • Step 6: Under Remote IP Addresses add the IP Addresses provided by Google in the JDBC documentation.
  • Step 7: Click on the “Apply” button.
Connect SQL Server to Google Sheets - SQL Server Properties
Image Source

Step 2: Create Apps Script project

  • Step 1: In your SpreadSheet, from the top menu click on the “Extensions” option.
  • Step 2: Then click on the “Apps Script” option. Note that this will create a new Apps Script project.
Connect SQL Server to Google Sheets - Connect to SQL Server
Image Source

Step 3: Create connection to MS SQL Server database

In order to create a connection to the MS SQL Server database, you need the following.

  • Server name or server’s public IP address.
  • Username for the database.
  • Password for the database.
  • Database URL.
  • JDBC Connection Statement.
var serverName = 'name/ip-address';
var username = 'db-username';
var password = 'db-password';
var db = 'db-name';
 
var databaseUrl = 'jdbc:sqlserver://' + server + ':1433;dbName=' + db;
 
var connection = Jdbc.getConnection(dbUrl, username, password);

Step 4: Read Data from MS SQL Server database

After creating a connection to the database, you can read data by performing the following steps.

  • Step 1: Create a statement through the connection object.
  • Step 2: Execute the query.
  • Step 3: Return MetaData from the query output.
  • Step 4: Return the number of Columns from MetaData.
  • Step 5: Initialize a blank multidimensional array.
  • Step 6: Save the column name returned by the query in an array and push that array to the output array.
  • Step 7: Save data of all the rows for each column in an array and push that array to the output array.
var statement = connection.createStatement(); //Create Statement
 
var execute_query = statement.executeQuery(query); //Execute Query
 
var metaData = execute_query.getMetaData(); //MetaData from the query output
 
var columnCount = metaData.getColumnCount(); //Number of Columns from MetaData
 
var data = []; //Save query output to multidimensional array
 
for (var column = 0; column < columnCount; column++) { //First save Column Names in Header
  header = [];
  header.push(metaData.getColumnName(column + 1));
};
 
data.push(header); //Push header row to the data array
 
while (execute_query.next()) { //Then save data of each column in row_data
  row_data = [];
  for (var column = 0; column < columnCount; column++) {
    row_data.push(execute_query.getString(column + 1));
  };
 
  data.push(row_data); //Push row_data to data array
 
};
execute_query.close();

Step 5: Write Data to Google Sheets from MS SQL Server database

After getting the results from the query and saving it in an array, now it’s time to write the data to Google Sheets.

The steps carried out to write the data to Google Sheets are as follows:

  • Step 1: Find out and navigate to the active spreadsheet.
  • Step 2: Set the sheet state to active by providing its name.
  • Step 3: Find the last row in the sheet that contains data as there is a possibility that the sheet is prefilled.
  • Step 4: Extract the sheet range that is equal to the size of the data array.
  • Step 5: Now, populate the sheet with data in the array.
  • Step 6: Now click on the “Run” button and you are all set to load data and connect SQL Server to Google Sheets.
var activeSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();  //Get Active Spreadsheet
var activeSheet = SpreadsheetApp.setActiveSheet(activeSpreadSheet.getSheetByName(name)); //Set Active Sheet
var lastRow = activeSheet.getLastRow(); //Get first empty row
 
activeSheet.getRange(lastRow + 1, 1, data.length, data[0].length).setValues(data); //populate data

Method 2: Using Hevo Activate to connect Google BigQuery to Google Sheets

Connect SQL Server to Google Sheets - hevo logo
Image Source

Hevo Activate helps you directly transfer data from SQL Server (coming soon!), Google BigQuery, Snowflake,etc. to CRMs such as Salesforce, HubSpot, various SaaS applications like Google Sheets, Data Warehouses, and a lot more, in a completely hassle-free & automated manner.

Hevo Activate is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

Hevo Activate takes care of all your data preprocessing needs and lets you focus on key business activities and draw a much more powerful insight on how to generate more leads, retain customers, and take your business to new heights of profitability. It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. 

Check out what makes Hevo Activate amazing:

  • Real-time Data Transfer: Hevo Activate, with its strong Integration with 100+ sources, allows you to transfer data quickly & efficiently. This ensures efficient utilization of bandwidth on both ends.
  • Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer. 
  • Secure: Hevo Activate has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
  • Tremendous Connector Availability: Hevo Activate houses a large variety of connectors and lets you bring in data from numerous Marketing & SaaS applications, databases, etc. such as Airflow, HubSpot, Marketo, MongoDB, Oracle, Salesforce, Redshift, etc. in an integrated and analysis-ready form.
  • Simplicity: Using Hevo Activate is easy and intuitive, ensuring that your data is exported in just a few clicks. 
  • Completely Managed Platform: Hevo Activate is fully managed. You need not invest time and effort to maintain or monitor the infrastructure involved in executing codes.
  • Live Support: The Hevo Activate team is available round the clock to extend exceptional support to its customers through chat, email, and support calls
Sign up here for a 14-Day Free Trial!

With Hevo Activate, you can easily connect SQL Sever (coming soon!) to Google Sheets in 2 simple steps:

Step 1: Configure your Source

You can select the source as SQL Server (coming soon!) from where you want to connect to Google Sheets.

Step 2: Configure Google Sheets as your Target Destination

To set Google Sheets as your Target destination, you can follow the simple steps given below:

  • Step 1: Click on the Activate in the Asset Palette.
  • Step 2: Do one of the following:
Connect SQL Server to Google Sheets - Create a new Target
Image Source
Connect SQL Server to Google Sheets - Create an Activation
Image Source
  1. In the Select Warehouse page, select your Activate Warehouse or click + ADD WAREHOUSE to add a new warehouse. Read Activate Warehouses to configure the selected Warehouse type.
  2. In the Select a Target page, click + ADD TARGET.
Connect SQL Server to Google Sheets - Add New Target
Image Source
  • Step 3: In the Select a Target Type page, click on Google Sheets.
Connect SQL Server to Google Sheets - Select a Target Type
Image Source
  • Step 4: In the Configure your Google Sheets account page, select the authentication method for connecting to Google Sheets.
Connect SQL Server to Google Sheets - Select Authentication Method
Image Source
  • Step 5: Do one of the following:
    • Connect using a User Account:
      1. Click + ADD GOOGLE SHEETS ACCOUNT.
      2. Select the Google account associated with your Google Sheets data and click ALLOW to authorize Hevo to access your data.
    • Connect using a Service Account:
      1. Attach the Service Account Key. Read Downloading the key file for steps to create a new key. Note: Hevo supports only JSON format for the key file.
      2. Click CONFIGURE GOOGLE SHEETS ACCOUNT.
  • Step 6: In the Configure your Google Sheets Target page, specify details such as a unique Target Name, your Google Drive folder(optional), and your Google Spreadsheet.
Connect SQL Server to Google Sheets - Configure Google Sheets Target
Image Source
  • Step 7: Click TEST & CONTINUE. You can view the new Target in the Targets List View. If you are creating an Activation, you return to the Select Data to Synchronize page.
Connect SQL Server to Google Sheets - Targets List View
Image Source

Conclusion

In this article, you have learned about how to connect SQL Server to Google Sheets. This article also provided information on SQL Server, Google Sheets, their key features, and the steps involved on how to connect SQL Server to Google Sheets.

Integrating your customer data from a diverse set of sources into your CRM application, BI tools, etc can be challenging and this is where Hevo Activate comes into the picture.

Visit our Website to Explore Hevo

Hevo Activate will automate your data transfer process, hence allowing you to focus on other aspects of your business like analytics, customer management, etc. This platform allows you to transfer data from Cloud-based Data Warehouses like Snowflake, Google BigQuery, Amazon Redshift, etc to various CRMs like Salesforce and SaaS applications. It will provide you with a hassle-free experience and make your work life much easier.

Want to take Hevo for a spin?

Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share your experience of learning about how to connect SQL Server to Google Sheets. Tell us in the comments below!

Empower Customer Analytics Using Hevo Activate