Looker will not be updating this content, nor guarantees that everything is up-to-date.
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.
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.
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:
# 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[0]);
stmt.setString(2, values[1]);
stmt.setString(3, values[2]);
stmt.setString(4, values[3]);
stmt.setString(5, values[4]);
stmt.setString(6, values[5]);
stmt.setString(7, values[6]);
stmt.setString(8, values[7]);
stmt.setString(9, values[8]);
stmt.setString(10, values[9]);
stmt.setString(11, values[10]);
stmt.setString(12, values[11]);
stmt.setString(13, values[12]);
stmt.setString(14, values[13]);
stmt.setString(15, values[14]);
stmt.setString(16, values[15]);
stmt.setString(17, values[16]);
stmt.setString(18, values[17]);
stmt.setString(19, values[18]);
stmt.setString(20, values[19]);
stmt.setString(21, values[20]);
stmt.setString(22, values[21]);
stmt.setString(23, values[22]);
stmt.setString(24, values[23]);
stmt.setString(25, values[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[19]+'\",\"'+values[1]+'\",\"'+values[2]+'\",\"'+values[3]+'\",\"'+values[4]+'\",\"'+values[5]+'\",\"'+values[6]+'\",\"'+values[7]+'\",\"'+values[8]+'\",\"'+values[9]+'\",\"'+values[10]+'\",\"'+values[11]+'\",\"'+values[12]+'\",\"'+values[13]+'\",\"'+values[14]+'\",\"'+values[15]+'\",\"'+values[16]+'\",\"'+values[17]+'\",\"'+values[18]+'\",\"'+values[19]+'\",\"'+values[20]+'\",\"'+values[21]+'\",\"'+values[22]+'\",\"'+values[23]+'\",\"'+values[24]+'\",\"'+values[25]+'\",\"'+values[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);
};