Monday, 31 March 2014

Database connectivity with Google Sheets

Overview

I happened to be watching some Google I/O videos from 2013 last week and was watching some on apps-script, which I had never really used up until now. They mentioned about having JDBC connector so that you could connect to external data sources (Oracle, MySQL, SQL Server, etc).

It's worth noting, there are 2 types scripts that can be container bound to a spreadsheet:

1. Add ons
2. Script gallery

Script gallery is not supported in the new version of sheets, in favour of add-ons. There is a market place where you can search for and install add-ons, and if your organisation is using google apps, you can even restrict your add-on to your organisation.

Unlike the script gallery where you could view the source, that is not the case for Add-ons (the code is not visible to end users). Excluding if you share the document with the development version of the code attached to the document.

Add-ons are currently in developer preview, so whilst you can develop add-ons, only approved developers can publish to the store.

For more information on apps-script in general, head over to https://developers.google.com/apps-script

Database Connectivity

All the information is on their JDBC guide: https://developers.google.com/apps-script/guides/jdbc, all the same, here's a little demo of connecting to an Oracle database and populating cells. I will be fetching some data from my app_users table.

Open the script editor by opening the sheet and navigating to tools --> script editor. The name you give the project is what will appear in the Add-ons menu, and then any menu entries you create will appear as a sub menu-item.

The first step is to add some menu entries:


function onOpen(e){

  SpreadsheetApp.getUi()
  .createAddonMenu()
  .addItem("Fetch app_users", "loadAppUsers")
  .addItem("Clear sheet", "clearSheet")
  .addToUi();
  
}

function onInstall(e){
 
  onOpen(e);
  
}


The onInstall function is there so the menu is added when the add-on is first installed, with onOpen being called whenever the sheet is opened.

Then a function to connect to the database and populate the spreadsheet:


function getConnection_(server, sid, port, username, password) {
  
  return Jdbc.getConnection('jdbc:oracle:thin:@//' + server + ':' + port + '/' + sid, username, password);
}

function loadAppUsers(){
 
  var conn = getConnection_(
    '[server]'
  , '[sid]'
  , '[port]'
  , '[username]'
  , '[password]'
  );
  
  var stmt = conn.createStatement();
  var rs = stmt.executeQuery('select id, user, first_name, last_name from app_users');
  var document = SpreadsheetApp.getActive();
  
  var cell = document.getRange("a1");
  var row = 0;
  
  var rsMetaData= rs.getMetaData();
  
  clearSheet();
  
  while(rs.next()){
    for(var col=0;col < rsMetaData.getColumnCount(); col++){
      
      if(row==0){
        cell.offset(row, col).setValue(rsMetaData.getColumnName(col+1)); 
      } else {
      
        cell.offset(row, col).setValue(rs.getString(col+1));
      
      }
      
    }
    row++;
  }
  
  stmt.close();
  rs.close();

  
  conn.close();
  
}

The clear function is nothing more than

function clearSheet(){
 
  SpreadsheetApp.getActiveSheet().clear();
  
}


This gives us a sheet with the menu:


And populated spreadsheet:



















Since the code is run from google servers, they need to be able to communicate with your database server, with the IP address range of the google servers being defined on the JDBC guide: https://developers.google.com/apps-script/guides/jdbc#accessing_local_databases

1 comment:

  1. I made this change:

    while(rs.next()){
    for(var col=0;col < rsMetaData.getColumnCount(); col++){

    if(row==0){
    cell.offset(row, col).setValue(rsMetaData.getColumnName(col+1));
    cell.offset(row+1, col).setValue(rs.getString(col+1));
    } else {

    cell.offset(row+1, col).setValue(rs.getString(col+1));

    }

    }
    row++;
    }

    ReplyDelete