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();
}