How to connect Google Sheets to Database

Question

I'm trying to auto-populate some raw data on a sheet in my google sheets file with a query.

It doesn't look like sheets has any built in functionality to do so like Microsoft Excel does.

Am I missing something? I found one add-on that has since been discontinued and no longer works called data everywhere: https://www.dataeverywhere.com/use-database-sheets

Is there something else that has replaced that?

Answer 1

As referred here, you can use the JDBC services of Google Apps Scripts. You will have to write a script that populates your spreadsheet with data from the JDBC service.

Read from the database

This example demonstrates how to read a large number of records from the database, looping over the result set as necessary.

// Replace the variables in this block with real values.
var address = 'database_IP_address';
var user = 'user_name';
var userPwd = 'user_password';
var db = 'database_name';

var dbUrl = 'jdbc:mysql://' + address + '/' + db;

// Read up to 1000 rows of data from the table and log them.
function readFromTable() {
  var conn = Jdbc.getConnection(dbUrl, user, userPwd);

  var start = new Date();
  var stmt = conn.createStatement();
  stmt.setMaxRows(1000);
  var results = stmt.executeQuery('SELECT * FROM entries');
  var numCols = results.getMetaData().getColumnCount();

  while (results.next()) {
    var rowString = '';
    for (var col = 0; col < numCols; col++) {
      rowString += results.getString(col + 1) + '\t';
    }
    Logger.log(rowString)
  }

  results.close();
  stmt.close();

  var end = new Date();
  Logger.log('Time elapsed: %sms', end - start);
}

Hope this helps!

Answer 2

If you don't want to roll your own solution, check out SeekWell. It allows you to connect to databases and write SQL queries directly in Sheets.

A few other features:

• Quickly view all tables and columns in a database and get summary stats on a column with one click
• Query from the sidebar, a large pop-out window, or from within a cell Results can be sent to a specific cell, scratch sheet or directly to a pivot table
• Your query history is saved and viewable if you need to re-execute an older query
• You can save a set of queries on a “Run Sheet” to update multiple reports at once

Disclaimer: I made this.

Answer 3

This is the sample code to how read data from a SQL Server instance and insert them in Google Sheet. The code creates a menu item to re-load data, and each time clears the content while it keeps the format.

function onOpen() {
    var spreadsheet = SpreadsheetApp.getActive();
    var menuItems = [
        { name: 'Get Data', functionName: 'readData' }
    ];
    spreadsheet.addMenu('My Functions', menuItems);
}

// Replace the variables in this block with real values.
var address = 'ip-address:port'; //ex. '10.1.1.1:1433'
var user = 'db-username';
var userPwd = 'db-password';
var db = 'db-name';

var dbUrl = 'jdbc:sqlserver://' + address + ';databaseName=' + db;

// if your server does not support SSL, write like below
// var dbUrl = 'jdbc:sqlserver://' + address + ';databaseName=' + db + '?useSSL=false';

function readData() {
    var conn = Jdbc.getConnection(dbUrl, user, userPwd);
    var stmt = conn.createStatement();
    var results = stmt.executeQuery('SELECT * FROM [dbo].[User]');
    var metaData = results.getMetaData();
    var numCols = metaData.getColumnCount();
    var spreadsheet = SpreadsheetApp.getActive();
    var sheet = spreadsheet.getSheetByName("Sheet1");
    //you can use the following line to get the active sheet
    //var sheet = SpreadsheetApp.getActiveSheet();
    sheet.clearContents();
    var arr = [];
    for (var col = 0; col < numCols; col++) {
        arr.push(metaData.getColumnName(col + 1));
    }
    sheet.appendRow(arr);
    while (results.next()) {
        arr = [];
        for (var col = 0; col < numCols; col++) {
            arr.push(results.getString(col + 1));
        }
        sheet.appendRow(arr);
    }
    results.close();
    stmt.close();
    sheet.autoResizeColumns(1, numCols + 1);
}

Answer 4

The answer to this question depends heavily on what you’re trying to connect to Google Sheets and may require some additional setup if you’re trying to connect Sheets to an internal database instance, which isn’t exposed to the internet.

For services which are exposed to the internet, there’s a handful of extensions which can handle pushing and pulling data from different database services and SAAS applications. Coefficient works well (disclaimer: I'm a software engineer there). It has connectors for a large array of different services and databases, with the relevant ones to your question currently being:

• MySQL
• Redshift
• PostgreSQL
• CSV (In the worst-case scenario, if your platform isn’t supported yet, you could export to CSV and then import the CSV automatically)

We’re constantly adding more connectors as well, and you can mix, match, and manage them all within the same sheet. We also provide a lot of documentation for their product and ideas on how to leverage Sheets reporting for the different platforms they support in their blog.

If you’d like to avoid using 3rd party tools, you can utilize App Scripts to craft a custom function to pull down data for you from an external SQL instance. Just a note: the scripts they provide as examples are for connecting to a google cloud instance. You’ll want to use Jdbc.getConnection(url) instead of Jdbc.getCloudSqlConnection(url) if you’re connecting to a non-Google Cloud database. Just like with the Add-on solution, you’ll need to make sure that any databases you’re hosting and wish to access, that they are accessible from the internet (the linked google documentation has more info on that).

An advantage to this solution is that it’s VERY customizable and can be re-used. It’s also very intuitive for Sheets veterans, as the call can be made just like a regular sheets function.

The caveat is that it’s a solution that requires some development understanding to implement in a way that is reusable and is intuitive for all users. The “Read from the Database” example that Google provides in their documentation takes no parameters, and just dumps out all the data for one table. Creating a new function for each table you want to pull from, each time you want to load a new table probably isn’t on your to-do list, so you’ll likely want to add in parameters for which server to connect to, which port, which database, which columns to return, and any filters to be applied (or maybe what SQL command to run?).

But at that point it’s become much less accessible for non-technical users, and you’re recreating a package that others like Coefficient have already put the time, resources, and understanding to develop.

Answer 5

You have couple options:

1. Build your own connector / addon

You can build your own connector using Google app script (https://developers.google.com/apps-script/guides/sheets). I'd recommend this tutorial for addon development https://youtu.be/6jcc3xm7aRU.

To connect to your database, you can either use the JDBC services (https://developers.google.com/apps-script/guides/jdbc) like @abielita suggested, or use a separate database connectors, like psycopg2 or node-postgres if Apps Script JDBC service does not support your database. Run it on some server, front it with REST API and fetch the data from Google sheet.

2. Use addon

There are many addons in the market that are trying to solve this exact problem. You can find them by searching "connect database to google sheets" on G Suite Marketplace (https://gsuite.google.com/marketplace).

Answer 6

You might want to have a look at this list of detailed article "the best 7 ways to connect MySQL to Google Sheets in 2020. Some of them require no code whatsoever and others are more technical, so that you can make a choice based on your needs and skills.

Here is the list of solutions (you'll find a presentation of each in the article):

1. Google Apps Script (there are 2 main steps here: 1. create a Google Sheets script to import a SQL table ; 2. run a Google Sheets script automatically every minute to retrieve SQL data => I wrote a step-by-step tutorial on how to automatically retrieve data from my SQL database to Google Sheets with a script — with the actual code to make it work)
2. Zapier
3. Blockspring
4. Actiondesk
5. Kloudio
6. SeekWell
7. QueryClips

I hope this thorough presentation will help you find a solution that will simplify your life with MySQL and help make you and your team more efficient and productive!

Answer 7

The answer really depends on the database you are using. I'll mention the solutions not already addressed above:

1. Zapier provides nifty integrations with databases like Typeform, Gmail emails, facebook leads, Trello cards, Slack, Mailchimp, Clickfunnels, Survey Monkey, Asana, Airtable data, Twitter, and many many more etc.

2. If you want to connect to Mongodb, use Mongodb stitch, as mentioned here: https://www.mongodb.com/blog/post/stitching-sheets-using-mongodb-stitch-to-create-an-api-for-data-in-google-sheets

3. Postgreql to Sheets: Zapier integration (https://zapier.com/apps/google-sheets/integrations/postgresql) or actiondesk/kloud integration (https://www.actiondesk.io/blog/4-tools-to-connect-postgresql-to-google-sheets)

4. If it is a custom. DB, you can always develop your own plugin using the APIs mentioned here: https://developers.google.com/sheets/api

Which database do you want to connect to?

Answer 8

This code works well: to connect Azure database and grab data from table

function onOpen() {

var spreadsheet = SpreadsheetApp.getActive();

var menuItems = [
    {name: 'Get Data', functionName: 'readData'}
];
 spreadsheet.addMenu('Report', menuItems);
}


// Replace the variables in this block with your values.
 var hostName = 'SERVER.database.windows.net:1433;'
 var db = 'DBNAME;';
 var user = 'USER@SERVER';
 var userPwd = 'PASSWORD';



 var dbUrl = 'jdbc:sqlserver://'+hostName + 'databaseName='+db; 


function readData() {
 var conn = Jdbc.getConnection(dbUrl, user, userPwd);
 var stmt = conn.createStatement();

  
 // Place your query below
 var results = stmt.executeQuery('SELECT TOP (10) * FROM [dbo].[NAME]');
 var metaData=results.getMetaData();
 var numCols = metaData.getColumnCount();
 var sheet = SpreadsheetApp.getActiveSheet();

 sheet.clearContents();

 var arr=[];

 for (var col = 0; col < numCols; col++) {
  arr.push(metaData.getColumnName(col + 1));
 }

 sheet.appendRow(arr);


 while (results.next()) {
  arr=[];

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

   arr.push(results.getString(col + 1));
  }
 sheet.appendRow(arr);

}

results.close();
stmt.close();

sheet.autoResizeColumns(1, numCols+1);

}

Answer 9

Abielita answer works nice

I'm only add some extra lines in order to write the data into the active sheet.

// Replace the variables in this block with real values.
var address = 'host';
var user = 'user';
var userPwd = 'password';
var db = 'dbname';

var dbUrl = 'jdbc:sqlserver://'+address+";databaseName="+db+";";

// Read up to 1000 rows of data from the table and log them.
function readFromTable() {
  var conn = Jdbc.getConnection(dbUrl, user, userPwd);

  var start = new Date();
  var stmt = conn.createStatement();
  stmt.setMaxRows(1000);
  var results = stmt.executeQuery('select * from TableName');
  var numCols = results.getMetaData().getColumnCount();

   var sheet = SpreadsheetApp.getActiveSpreadsheet();
      var cell = sheet.getRange('A1');
      var numCols = results.getMetaData().getColumnCount();
      var row =0;

      while (results.next()) {
        var rowString = '';
        for (var col = 0; col < numCols; col++) {
          rowString += results.getString(col + 1) + '\t';
          cell.offset(row, col).setValue(results.getString(col +1 ));
        }
        row++
       Logger.log(rowString)
      }

  results.close();
  stmt.close();

  var end = new Date();
  Logger.log('Time elapsed: %sms', end - start);
}

Answer 10

Below is a simple, robust way to import Google Sheet CSV files into Postgres then export back as Google Sheet CSV files. Below are general steps I use on my local Linux desktop that uses Insync to synchronize my local hard drive and Postgres input/output files to my Google Drive:

• Install Postgres database and Insync
• Create CSV files and store locally and synched to Google Drive (e.g. /home/data/googledrv/csvin).
• Use the Postgres copy command to load the above CSV input file(s).
• Run your Postgres CREATE table/view SQL commands.
• Use the Postgres copy command to export your tables/views to your local and synced Google drive (e.g. /home/data/googledrv/csvout)
• FWIW, my CSV input files are pipe delimited "|" for easy reading/updating and my CSV output files are plain CSV formatted to be easily viewed as a Google Sheet.