How to Connect Microsoft SQL Server to Google Sheets

Do you want to know how to connect Microsoft SQL Server to Google Sheets? Read this blog for step-by-step walkthroughs on the top methods.

Microsoft SQL Server is a Relational Database Management System (RDMS) that supports business intelligence, analytics applications, and transaction processing, usually in corporate IT environments. MS SQL Server is built on a SQL-based architecture, and enables users to navigate, search, and analyze data stored in tables using SQL queries.

But SalesOps and RevOps team members often don’t have the SQL skills needed to leverage Microsoft SQL Server. They prefer to handle data in Google Sheets, due to its familiar interface and flexibility. That’s why many of our customers import data from MS SQL Server into Google Sheets.

Here are the top ways to connect Microsoft SQL Server to Google Sheets, including how to set up each option. 

TL;DR: Coefficient is the easiest way to connect Microsoft SQL Server to Google Sheets

Spoiler alert: Coefficient is the quickest and simplest way to connect Microsoft SQL Server to Google Sheets.   

Coefficient connects MS SQL Server to Google Sheets in a single click. The app automatically pulls real-time data from MS SQL Server into Google Sheets. And the data stays synced with MS SQL Server, so it’s always up-to-date. 

Non-technical team members can easily import tables and datasets from SQL Server into Google Sheets with Coefficient’s user-friendly, intuitive data inline previewer.

Team members can apply filters and set limits to data imports with a point-and-click interface. They can also pull data from multiple databases into one spreadsheet, allowing them to blend data from multiple sources. 

Coefficient empowers your technical and non-technical teams to expand and accelerate analysis of MS SQL Server data inside Sheets.

Step-by-Step Walkthroughs: Top Three Methods for Connecting MS SQL Server to Google Sheets

Coefficient

Step 1 

Click Extensions on the top menu of Google Sheets. Then select Add-ons > Get add-ons to open Google Workspace Marketplace.  

Find the app by typing in ‘Coefficient’ into the search bar. Follow the prompts for installation.  

Select the Google account you want to associate with Coefficient. Click Allow to give Coefficient the required permissions. 

After the installation, go back to Extensions on the Google Sheets menu. You’ll see Coefficient as an add-on in the dropdown. Click Launch, and Coefficient will appear on the sidebar.  

Step 2 

Click Import from… on the Coefficient app sidebar.  

Find MS SQL Server from the list of data sources and click the Connect button. 

Connect to your Microsoft SQL Server account by providing your connection details, including: 

• Host
• Database name 
• Username, 
• Password

Then click Connect when you’re done.  

Step 3 

Use Coefficient’s data inline previewer to choose the tables and columns you want to import from your SQL Server database into Google Sheets.  

With this point-and-click GUI, you won’t have to use specific table names and column identifiers to pull the data you want from the database. 

Apply your desired filters and add row limits to the data you want to import. You can also pivot or group your MS SQL Server data in the cloud, allowing you to import only the data you need instead of massive volumes of raw data. 

Step 4 

Click Import your MS SQL Server data will auto-populate in your spreadsheet in seconds. 

Step 5 

Set an auto-refresh schedule to allow Coefficient to automatically update your MS SQL Server data in your spreadsheet. Configure Coefficient to auto-refresh your data on an hourly, weekly, or monthly basis.   

You can also update SQL Server Google Sheets data instantly by clicking the Refresh button shown at the top of your imported tables. 

In addition, you can keep team members in the loop by configuring email and Slack automated alerts that notify them about important KPIs, reports, and data updates.

Overall, Coefficient makes it simple to import data from MS SQL Server into Google Sheets, sync changes, and automate work for your teams. And set up only requires a single click to set up.

Google Apps Script

Technical users with coding knowledge can harness Google Apps Script to connect Microsoft SQL Server to Google Sheets. Apps Script works well for standard SQL queries. The triggers make scheduling data pulls manageable, and you won’t need to interface with APIs to do so. 

Whitelist the IP addresses on this list to connect MS SQL Server to Google Sheets using Google Apps Script. Google JDBC service can only create a database connection with whitelisted IP addresses.  

Next, open a new spreadsheet and click Extensions > Apps Script.

Copy and paste the code below into the Script Editor. 

Over 100K pros building reports use Coefficient to automate business systems data into their Google Sheets

Try Now

"What an amazing app! Coefficient makes integrating some of the most complex systems unbelievably simple! Set up your integrations in less than a minute."

Yonatan Schvimer

function readData(db, queryString) {

  //connect to the database

  var server = ‘your-servername-OR-serverPublicIpAddress’; 

  var username = ‘your-sql-username’;

  var password = ‘your-password’;

  var dbUrl = ‘jdbc:sqlserver://’ + server + ‘:1433;databaseName=’ + db;

  var conn = Jdbc.getConnection(dbUrl, username, password ); 

  //query the data

  var stmt = conn.createStatement();

  var exec_query = stmt.executeQuery(queryString);

  var metaData = exec_query.getMetaData(); //get the meta data of the SQL result

  var numCols = metaData.getColumnCount(); //read number of columns of the results

  //save query data to an array

  var result=[]; //initiate a blank array

  //save the column header

  for (var col = 0; col < numCols; col++) {

    header = []; //initiate the header row

    header.push(metaData.getColumnName(col + 1)); //add the name of each column to the header row

  };

  result.push(header);//after the header row is formed, put it to the result array

  //save the data of each row

  while (exec_query.next()) {

    row_data = [];

    for (var col = 0; col < numCols; col++) {

      row_data.push(exec_query.getString(col + 1));//add data of each column to the row data

      //Logger.log(row_data);

    };

    result.push(row_data); // add row data to result

    //Logger.log(result);

  };

  exec_query.close();

  return result

};

The function will connect Google Sheets to MS SQL Server, query the database, and save the SQL query result as a multidimensional array. The function inputs are the name of the MS SQL database and the SQL query that retrieves data from the database. The function output is a multidimensional array equaling the rows returned by the SQL query.

If you encounter this message:

Unexpected error while getting the method or property getConnection on object Jdbc.

Use your database’s public IP address instead.

After this step, you need to write a function to push this MS SQL Server data to Google Sheets. You can find an example below:

This function can append multidimensional arrays to Google Sheets. If you have a one-dimension array, change line 7 in the function to:

sheet.getRange(lastRow+1, 1, 1, data.length).setValues(data)

Finally, you must write a function that calls the readData function and passes the results to the pushDataToGoogleSheet function. This will insert the data into cells within Google Sheets. 

The Google Apps Script method is only feasible for technical users. But even for those users, debugging and maintaining this Apps Script function is annoyingly time-consuming. That’s why pre-built connectors, such as those offered by Coefficient, are preferred even by technical users. 

Hevo Data

Hevo Data is an ETL tool that extracts, transforms, and loads data between different sources. With Hevo Data, technical-oriented users can build a data pipeline between MS SQL Server and Google Sheets. 

The set up is relatively straightforward: first you must configure Google Sheets as a source system. Enter the pipeline name and the spreadsheet you want to replicate. Then configure Microsoft SQL Server as a destination.  

Then enter the Destination number, Database Host, User, Port, Password, Name, and Schema to establish the connection between MS SQL Server and Google Sheets.

With this data pipeline, you can export Google spreadsheets into your MS SQL Server. However, you cannot import data from MS SQL Server into Google Sheets. To do that, you would need a reverse ETL pipeline that can send data from the destination (MS SQL Server) back to the source (Google Sheets).

However, most SalesOps and RevOps users only need to get data into a spreadsheet. They’re rarely pushing spreadsheets back into databases or data warehouses. That’s why the simplicity and ease-of-use of Coefficient makes the most sense for them. 

With Coefficient, Ops users can bypass the complicated coding of Apps Script, and import SQL Server data into Google Sheets in a single click. In the rare case when they need to pushback entire spreadsheets to MS SQL Server, they can use Hevo Data as a supplementary tool.

Microsoft SQL Server to Google Sheets: What Method Works for You?

There are several viable ways to connect MS SQL Server to Google Sheets — from prebuilt, one-click connections to code-based functions. Ultimately, your choice will depend on your team’s priorities, resources, and capabilities. 

However, Coefficient generally offers the easiest way to connect MS SQL Server to Google Sheets. And it solves for the use case that most SalesOps and RevOps teams encounter: importing MS SQL Server data into Google Sheets.

Try Coefficient now, no credit card required, to unleash the full potential of your MS SQL Server data in Sheets.