CODEWITHSUNDEEP
mysqlgoogle-apps-scriptgoogle-sheets
dblack
dblack

Reputation: 113

Insert data range from Google Sheets to MySQL

I'm trying to create a function that takes a data range/specified range in my Google spreadsheet and inserts every row that isn't blank into a MySQL table. I want to take column range A:V in "Sheet One" and insert them to the "mysql_table" table. I'm new to using JDBC google scripts and below is what I've come up with so far, but I can't get it to work with my current knowledge.

function putData() {

   var conn = Jdbc.getConnection(dbUrl, user, userPwd);
  conn.setAutoCommit(false);

   var ss = SpreadsheetApp.getActiveSpreadsheet(),
      sheet = ss.getSheetByName("Sheet One"),
       range = sheet.getDataRange(),
      values = range.getValues(),
      row;

  var start = new Date();
 function writeManyRecords() { 
  for (var r=1; r<values.length; r++) {
    if( values[r].join('') === '' ) return 'Blank row encountered'; 

    row = values[r]; 
    var i = 0;
  var stmt = conn.prepareStatement('INSERT INTO mysql_table '
      + '(field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field11, field12, field13, field14, field15, field16, field17, field18, field19, field20, field21, field22) values (' + start + row[i] + ')');
   stmt.addBatch();



  var batch = stmt.executeBatch();
  conn.commit();
  conn.close();  


  var end = new Date();
  }
 }
}

Anyone have any ideas on how I can achieve this?

Upvotes: 2

Views: 6524

Answers (1)

dblack
dblack

Reputation: 113

I came up with a solution and I'm posting it here for anyone else looking for a way of writing to MySQL from Google Sheets. Here's a simplified version:

function putData() {
  var conn = Jdbc.getConnection(dbUrl, user, userPwd);
  var stmt = conn.createStatement();
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();

  for (var i = 0; i < data.length; i++) {
  var sql = "INSERT INTO test (test, num) VALUES ('" + data[i][0] + "'," + data[i][1] + ")";
  var count = stmt.executeUpdate(sql,1)
  }

  stmt.close();
  conn.close();
}

Upvotes: 9

Related Questions