Improve application execution Google script
I am using the below code to write data from google spreadsheet to mySQL database table and this script was used when about 4000 records worked, now it has over 8000 records and is very slow. Is there a work around to read all google spreadsheet data in memory and then write it to 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();
}
source to share
As far as I know when you use
var data = sheet.getDataRange().getValues();
it reads all the data from your sheet1 in one block into memory, so the way you search and read the data looks as fast as it can be. Note, however, that getDataRange () reads a rectangle containing text in memory, so you can optimize it if you can make assumptions about your data, like how many columns you have - in which case you can only read certain parts in memory using getRange (row, column, numRows, numColumns).
I think it might slow down your for loop on the transformation part, but I'm not sure, so you can track down the source of the performance issues by using this code to measure time performance:
var start = new Date().getTime();
// Insert test code here
var end = new Date().getTime();
Logger.log(end - start);
source to share