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. 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 non-technical teams like SalesOps and RevOps 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 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. 

Top 3 ways to connect MS SQL Server to Google Sheets

1. Coefficient Data Connector – Easy and out of the box 1-click solution 
2. Google Apps Script – Complex setup and requires coding knowledge
3. Hevo Data – Tech team dependent & requires technical know-how

Let’s take a look at them in detail.

Video Walkthrough: How to Connect SQL Server to Google Sheets

1. Coefficient

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.

While the other methods like Hevo Data and Google Apps Script work, you’d still have to rely on data teams and developers or you need to possess the coding skills to make it work.

Advantages of using Coefficient to connect MS SQL Server to Google Sheets –

1. Access to fresh and accurate data (always) – Pull real-time data automatically from MS SQL Server into Google Sheets. And the data stays synced with MS SQL Server, so it’s always up-to-date. Team members can apply filters and set limits to data imports with a point-and-click interface
2. Connect Multiple Databases – Users can pull data from multiple databases into one spreadsheet, allowing them to blend data from multiple sources. 
3. No coding needed – Coefficient connects MS SQL Server to Google Sheets in a single click without any reliance on technical teams.

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

Step-by-Step Walkthrough: Connecting MS SQL Server to Google Sheets

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.

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

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. 

Limitations of using Google Apps Script –

1. Not for non-technical users – You’ll need to have a firm coding background to use Google App Script. This method is only feasible for technical users.
2. Time spent on Debugging – Even technical users have to tackle debugging and Apps Script maintenance which are annoyingly time-consuming. 

That’s why pre-built google sheets connectors, such as those offered by Coefficient, are preferred even by technical users.

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

Challenges of using HevoData –

• Tedious setup and access limitations – HevoData is often used by Data teams who might not entertain providing tool access beyond their function. 
• Requires technical know-how – You also require the technical know-how and have to rely on data teams to get the connector working.
• User experience – Hevo Data takes you out of Google Sheets, creating a more fractured and disconnected experience. Coefficient allows you to stay in Google Sheets, allowing for more efficiencies in the processes of your team.

Most non-technical teams like SalesOps and RevOps 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

Pushing data from Google Sheets to MS SQL Server –

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. Try Coefficient now, no credit card required, to unleash the full potential of your MS SQL Server data in Sheets.