Where and When to Use

At our Santa Cruz Looker office, we love to track surfing sessions and different attributes about those sessions. Many of our surfers would like to analyze this data in Looker, without going through a complicated ETL process that would require them to understand the inside and outside specification of databases. Instead, we write a script that enables users to pull information from a Google Sheet and push the data to a desired database of our choosing! This makes the data easy to model and Explore via the database connection in Looker.

Use Cases

Do you have non-technical users who want to upload data, like goals for their current quota or forecasts for upcoming quarters, to your database? How would a non-technical user go about achieving this without involving a database administrator? This article might serve as a potential solution.

See It in Action

Below is a Google Sheet that is used to log surf session information:

With the click of a button (that appears after adding the script below), users can push this information to a database - in this case, we are pushing to MySQL:

In Looker we can model the data and build dashboards to report on this information:

Implementation

1. In the example script below, we are pushing data to a MySQL database. The commands in the script can be changed to pipe data to the database of your choosing. This is 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. Those will need to be manually created 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. Customize the details for the table you will be inserting into.
  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. Change the button name "FRIENDSHIP AND MAGIC" as desired.
function onOpen() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Push to MySQL",
    functionName : "writeManyRecords"
  }];
  spreadsheet.addMenu("FRIENDSHIP AND MAGIC", entries);
};