Improve google apps script execution

Viewed 660

I am using the below code to write data from a google spreadsheet to a mySQL database table and this script used to work fine when it had around 4000 records now it has over 8000 records and runs very slow. Is there a work around to read all the google spreadsheet data into memory and then write it to the MySQL database.

function myfunction() { 
  var colA;
  var colB; 
  var colC; 
  var colD;
  var colE;  

  var mysqldb = Jdbc.getConnection("jdbc:mysql;dbipaddress","user","pa$$word");
  var sql = mysqldb.createStatement();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet1'); 
  var data = sheet.getDataRange().getValues();  

  mysqldb.setAutoCommit(false) 

  var query = "{call [dbo].[sp_copygsheets](?,?,?,?,?)}";
  sql = mysqldb.prepareCall(query);

  for (var i = 1; i < data.length; i++) {
  colA = data[i][0];
  colB = data[i][1]; 
  colC = data[i][2]; 
  colD = data[i][3]; 
  colE = data[i][4];  

  sql.setString(1, colA);
  sql.setString(2, colB); 
  sql.setString(3, colC); 
  sql.setString(4, colD);
  sql.setString(5, colE); 
  sql.addBatch();
  }

  sql.executeBatch();
  mysqldb.commit();

  sql.close();
  mysqldb.close();
}
1 Answers
Related