Connecting GSheets to GCloud

There are few sensations as scintillating as clicking that button…

“New > Spreadsheet” is a command that opens up a whole world of potential. GSheets are a place to sketch just about any project conceivable, and take it to new galaxies of imagination by means of the hugely powerful (and downright delectable) formulae Google provide… ARRAYFORMULA and IMPORTRANGE come to mind, but everything has its place, down to the humble SUMIFS and COUNTA statements that are the lifeblood of this product.

But what happens when your project grows & scales to the point that a spreadsheet is no longer the most appropriate place to store its data?

I had cause in 2018 to implement a solution for one client that makes backup of their spreadsheet data (a spreadsheet is crucial to them as the way in which their team are used to performing their data entry) into Google Cloud Platform’s SQL storage. Such a solution is truly a staple of what GCP envisioned in setting up their GSuite and GCP products to interface as smoothly as they do… and yet, the documentation and existing store of online tutorials & walkthroughs appeared to me to be lacking. Here is my holistic solution that should provide everything you need to take data all the way through from manual entry at spreadsheet level, through to SQL storage that is secure and typed.

var connectionName = '<IP>:3306';
var user = '<USER>'; // advise against using 'root'
var userPwd = '<PASS>';
var db = '<DB>';
var dbUrl = 'jdbc:mysql://' + connectionName + '/' + db;
function writeManyRecords() {
var conn = Jdbc.getConnection(dbUrl, user, userPwd);
conn.setAutoCommit(false);
var list_my_cols = SpreadsheetApp.getActiveSpreadsheet(
).getRangeByName('Schema!E17'
).getValues(
).toString();
var question_mks = SpreadsheetApp.getActiveSpreadsheet(
).getRangeByName('Schema!E18'
).getValues(
).toString();
var totl_my_cols = SpreadsheetApp.getActiveSpreadsheet(
).getRangeByName('Schema!E19'
).getValues(
).toString();
var create_table = SpreadsheetApp.getActiveSpreadsheet(
).getRangeByName('Schema!E2'
).getValues(
).toString();
// CREATE THE TABLE, REPLACING EXISTING ONE
// NOT WORKING SO, FOR NOW, JUST ERASE TABLE DATA
Logger.log(create_table); // use this to check what CREATE command is being sent
// conn.createStatement().execute(create_table);
conn.createStatement().execute('delete from courses table;');
// INSERT THE VALUES, INTO NEW EMPTY TABLE
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Courses');
var headr = 3
var range = SpreadsheetApp.getActiveSpreadsheet().getRangeByName('Courses!A'+headr+':Z');
var numRows = range.getNumRows();
var start = new Date();
var stmt = conn.prepareStatement(
'INSERT INTO courses '
+ '('+list_my_cols+') '
+ 'values ('+question_mks+')'
);
for (var r = headr; r < numRows; r++) {
for (var c = 1; c <= totl_my_cols; c++) {
var colX = sheet.getRange(r, c).getValues();
stmt.setString(c, colX);
}
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);
}

What did you make of these steps? Please feed back below, and let others know any issues you encountered along the way.