Re: Sync data from Google spreadsheet to Cloud SQL using Google Apps Script

Hi Matt,

Please try out this code. It worked for me. Sorry I could not document it well, in case you find it difficult to understand, please drop me a note and I will explain that snippet.

function writeToDB() {
  var connString ="jdbc:google:rdbms://"; //add your instance name after '//'
  var databaseName = "use testdb";
  var conn = Jdbc.getCloudSqlConnection(connString);
  var stmt = conn.createStatement();
  var rs = stmt.execute(databaseName);
 
  rs = stmt.executeQuery("SELECT column_name FROM information_schema.columns WHERE  table_name = 'test_table'");
 
  var columnCount = rs.getMetaData().getColumnCount();
  var dbColsNameArray = new Array();
  while(rs.next()) {
    for( var i = 1; i <= columnCount; i++ ){
      dbColsNameArray.push(rs.getString(i));
      columnName = rs.getMetaData().getColumnName(i);
    }
  }
 
  var colNameStr = "";
  var finalColNameStr = "";
  var valueStr = "";
  var finalValueStr ="";
  var arraylen = dbColsNameArray.length;
  Logger.log("before len::"+arraylen);
  //Logic that will dynamically create the prepared statement with column names
  for(i = 1;i< arraylen; i++)
  {
    colNameStr =   dbColsNameArray[i];
    valueStr = "?,";
   
    if(i==(arraylen-1))
    {
      colNameStr = colNameStr;
      valueStr = "?";
    }
    else
    {
      colNameStr = colNameStr+",";
     
    }
   
    if("".equals(finalColNameStr)){
      finalColNameStr = colNameStr;
    }
    else{
      finalColNameStr = finalColNameStr+colNameStr;
    }
    finalValueStr  = finalValueStr+valueStr;
    Logger.log("\n finalValueStr::"+finalValueStr);
  }
 
  conn.setAutoCommit(false);
  var insertQry = "insert into test_table (";
  insertQry = insertQry+finalColNameStr+") values (" +finalValueStr+")";
  Logger.log("insertQry::"+insertQry);
 
  stmt = conn.prepareStatement(insertQry);
 
  var noOfCols = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getDataRange().getLastColumn();
  var noOfrows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getDataRange().getLastRow();
  var values = SpreadsheetApp.getActiveSheet().getRange(2,1,noOfrows,noOfCols).getValues();
  var counter = 0;
 
  Logger.log("noOfCols::"+noOfCols+ "noOfrows::"+noOfrows);
  var rowsleng= noOfrows-1;
  Logger.log("\nrowsleng::::::"+rowsleng);
  var colValue = "";
  var tempDate = "";
 
  try{
    for (var i = 0; i < rowsleng; i++)
    {
      for(var j = 0; j< noOfCols; j++)
      {
        counter = j+1;
        colValue = values[i][j];
       
        stmt.setObject(counter,colValue.toString());
      }
      stmt.addBatch();
    }
    var res = stmt.executeBatch();
    conn.commit();
  }catch(exception){
    Logger.log(exception);
  }
  conn.close();
}

Regards,
Sonal

On Wednesday, April 17, 2013 11:47:03 PM UTC+5:30, Matt C wrote:

Hi Sonal,

I have the example JDBC code running successfully, but the example does not show how to read data FROM a spreadsheet INTO Cloud SQL, only how to generate auto-incremented integers within the script and send them to SQL. However, it does show how to copy data from SQL into a spreadsheet.

Basically, I don't know how to modify the example code to extract a range from my spreadsheet.

The example code I'm using is from this page - https://developers.google.com/apps-script/jdbc. You will see that getActiveSpreadsheet only appears on the second example, not the first.

Many thanks,

Matt

On 17 April 2013 07:15, SonalB <sonal...@accenture.com> wrote:

Hi Matt,

Yes. I have written the functionality to read and write data from Cloud SQL to Google spreadsheet. The logic reads data dynamically and writes the output to Google Spreadsheet.

Please share what you are trying to accomplish or issues encountered. This would help me in suggesting how you can proceed further.

Regards,
Sonal

On Tuesday, April 16, 2013 11:39:29 PM UTC+5:30, Matt C wrote:

Hi Sonal,

How far have you got with your project? I want to do exactly the same thing. I have been experimenting with Google's example JDBC code and understand the general idea, but have now hit the limit of my abilities. I would be extremely grateful if you could offer some assistance.

Many thanks,

Matt

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.