How to use Google Spreadsheets to input data into Looker

(Chris Billet) #1

##Where and When to Use##
Here at our Looker Santa Cruz office, we love to track surfing sessions and different attributes about those sessions. Many of our surfers would like to be able to analyze this data in Looker with out going through a complicated ETL process that would require them to understand the ins and outs of databases. What we can do instead is write a simple script to pull information from a Googlesheet and push the data to a desired database of our choosing!

Use Cases:

Do you have non-technical folks who want to upload data to your database? Say sales people who want to upload goals for their current quota or forecasts up coming quarters. How would a non-technical person go about doing something like this. This article might serve as a potential solution.

##See it In Action##

Below you can see a Googlesheet logging surf session information:

With the push of a button users can push this information to a database, in this case MySQL:

Now in Looker we can build dashboards to report on this information:

###Background Info###

  1. In the example script provided we will be pushing data to a MySQL database. The commands in the script can be changed to pipe data to the database of your choosing. This will be a Google Apps Script which is based on Javascript.
  2. The script will show the functionality of updating the tables with the new values in the spreadsheet with the single click of a button.
  3. The script WILL NOT create the database, tables, or columns. That will need to be manually done on the database server prior to running the script.
  4. To find the script editor follow these instructions:

###Example Script

# Database credential variables
var address = '<your_host_and_port>';
var user = '<your_user>';
var userPwd = '<your_password>';
var db = '<your_db_name>';


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

# Main function to map values in the sheet to the appropriate columns in the database 
function writeManyRecords() {
# Variables for the sheet information
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();
  var conn = Jdbc.getConnection(dbUrl, user, userPwd);
  conn.setAutoCommit(false);

  var start = new Date();
# Prepare the database to load in the new data by truncating the tables that we will be loading into.
  var boom = conn.prepareCall('TRUNCATE TABLE sessions');
# Execute the boom variable.
  boom.execute();
# Function that will contain INSERT Statement for the database.
  var stmt = conn.prepareStatement('INSERT INTO sessions (surfer, session_date, time_in, time_out, break, board, wetsuit, primary_swell_height, primary_swell_period, primary_swell_direction, primary_swell_degree, secondary_swell_height, secondary_swell_period, secondary_swell_direction, secondary_swell_degree, wind_speed_knots, wind_direction, wind_degree, tide_start, tide_movement, wave_height_min, wave_height_max, observed_wind, approximate_crowd, notes) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);');
# For loop to map the values in the spreadsheet to the values variable
  for (var i = 3; i < numRows; i++) {
    stmt.setString(1, values[i][0]);
    stmt.setString(2, values[i][1]);
    stmt.setString(3, values[i][2]);
    stmt.setString(4, values[i][3]);
    stmt.setString(5, values[i][4]);
    stmt.setString(6, values[i][5]);
    stmt.setString(7, values[i][6]);
    stmt.setString(8, values[i][7]);
    stmt.setString(9, values[i][8]);
    stmt.setString(10, values[i][9]);
    stmt.setString(11, values[i][10]);
    stmt.setString(12, values[i][11]);
    stmt.setString(13, values[i][12]);
    stmt.setString(14, values[i][13]);
    stmt.setString(15, values[i][14]);
    stmt.setString(16, values[i][15]);
    stmt.setString(17, values[i][16]);
    stmt.setString(18, values[i][17]);
    stmt.setString(19, values[i][18]);
    stmt.setString(20, values[i][19]);
    stmt.setString(21, values[i][20]);
    stmt.setString(22, values[i][21]);
    stmt.setString(23, values[i][22]);
    stmt.setString(24, values[i][23]);
    stmt.setString(25, values[i][24]);
    stmt.addBatch();
  }
  var batch = stmt.executeBatch();
  conn.commit();
  conn.close();

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

function readRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();
  var values = rows.getValues();

  for (var i = 3; i <= numRows - 1; i++) {
    var row = '\"'+values[i][19]+'\",\"'+values[i][1]+'\",\"'+values[i][2]+'\",\"'+values[i][3]+'\",\"'+values[i][4]+'\",\"'+values[i][5]+'\",\"'+values[i][6]+'\",\"'+values[i][7]+'\",\"'+values[i][8]+'\",\"'+values[i][9]+'\",\"'+values[i][10]+'\",\"'+values[i][11]+'\",\"'+values[i][12]+'\",\"'+values[i][13]+'\",\"'+values[i][14]+'\",\"'+values[i][15]+'\",\"'+values[i][16]+'\",\"'+values[i][17]+'\",\"'+values[i][18]+'\",\"'+values[i][19]+'\",\"'+values[i][20]+'\",\"'+values[i][21]+'\",\"'+values[i][22]+'\",\"'+values[i][23]+'\",\"'+values[i][24]+'\",\"'+values[i][25]+'\",\"'+values[i][26]+'\"';
    Logger.log(row);
  }
};

# Creates a button to run the script from the sheet
function onOpen() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Push to MySQL",
    functionName : "writeManyRecords"
  }];
  spreadsheet.addMenu("FRIENDSHIP AND MAGIC", entries);
};
7 Likes

Is there any way to import excel data into looker
Import from flat file
(Dillon Morrison) #2

this is awesome @Chris_Billet!! Great way to get a spreadsheet into Looker.

0 Likes

(Josh Andrews) #3

Interesting ‘push’ approach, I can see this being helpful in many situations.

We took a “pull” approach where we figured out OAuth with Google in Python (that was a bit tricky) but can launch a pull from a given spreadsheet whenever we want. This is a bit more comfortable from a security perspective in that it doesn’t require us to embed a password in a Google Script where many people can see it.

It’s also helpful in allowing us to control when the table load happens as part of a larger ETL workflow.

1 Like

Get latitude/longitude for any location through Google Sheets and plot these in Looker
(Dirty Looker) #4

@tuple,

That’s a great alternative! We didn’t have a spare box laying around to set something like that up, I think that your approach is much more secure. I wonder if this could be something easy to achieve now with AWS Lamda.

Thanks for sharing that approach.

0 Likes