How to connect Google Sheets to Database

问题:

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?

回答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!

回答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.

回答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;


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);


}

回答4:

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);

}

回答5:

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!