Update RecordThrough Google sheet to Mysql

 

// Replace the variables in this block with real values.

  var address = 'xxxxx';

  var user = 'xxxx';

  var userPwd = 'xxxx';

  var db = 'xxxxx';

 

 

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

 

  // C O N N E C T

function getDBConnection_(data)

{

 data = data || getMySqlConnectionData_();

 // getCloudSqlConnection

 var conn = Jdbc.getConnection('jdbc:mysql://' + data.address + ':3306/' + data.db + '?characterEncoding=UTF-8', data.user, data.userPwd);  

                            //  dbName?characterEncoding=UTF-8

                            //  jdbc:mysql://yoursqlserver.example.com:3306/database_name

 return conn;  

}

  // Read up to 1000 rows of data from the table and log them.

 function readFromTable() {

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

    var start = new Date();

    var stmt = conn.createStatement();

    var results = stmt.executeQuery('SELECT * FROM diaken_company');

    var sheet = SpreadsheetApp.getActiveSpreadsheet();

    var cell = sheet.getRange('A1');

    var numCols = results.getMetaData().getColumnCount();

    var row =0;

    while (results.next()) {

      var rowString = '';

      for (var col = 0; col < numCols; col++) {

        rowString += results.getString(col + 1) + '\t';

        cell.offset(row, col).setValue(results.getString(col +1 ));

      }

      row++

     Logger.log(rowString)

    }

    results.close();

    stmt.close();

    conn.close();

    var end = new Date();

    Logger.log('Time elapsed: %sms', end - start);

  }

// U P D A T E

function test_update()

{

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

 // samle update request

  var sheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1ru3Mwu6svgsr_euTP_XUq0AlU_GOAMMnFn58T4wTR8Y/edit#gid=0");

  var data = sheet.getDataRange().getValues();   

  

      var sql = "update diaken_company set outlet_04  = '"+ data[0][1]+"',outlet_05  = '"+ data[0][2]+"',outlet_06  = '"+ data[0][3]+"',outlet_07  = '"+ data[0][4]+"',outlet_08  = '"+ data[0][5]+"',outlet_09  = '"+ data[0][6]+"' where id = "+data[0][0];  

 runCustomUpdate_(sql);  

  

  

}

function runCustomUpdate_(sql)

{

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

 if (sql === '') { return -1; } // nothing to update  

 var stmt = conn.createStatement();

 Logger.log(sql)

 var results = stmt.executeUpdate(sql);

 conn.close();

 return results;

}

// custom menu function

function onOpen() {

  var ui = SpreadsheetApp.getUi();

  ui.createMenu('Record Update')

      .addItem('Save Data','saveData')

      .addToUi();

}

// function to save data

function saveData() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1ru3Mwu6svgsr_euTP_XUq0AlU_GOAMMnFn58T4wTR8Y/edit#gid=0");

  var data = sheet.getDataRange().getValues();   

  

      var sql = "update diaken_company set outlet_04  = '"+ data[0][1]+"',outlet_05  = '"+ data[0][2]+"',outlet_06  = '"+ data[0][3]+"',outlet_07  = '"+ data[0][4]+"',outlet_08  = '"+ data[0][5]+"',outlet_09  = '"+ data[0][6]+"' where id = "+data[0][0];  

 runCustomUpdate_(sql);  

  

}