Performance issue when writing to google docs sheet via apps script-CodePudding

I use apps script to connect to an SQL database and execute a query. It works as intended but writing the result to a docs sheet takes too long. The execution exceeds the timeout long before all rows are written. The query itself is executed relatively fast (<10s) but writing the lines (over 12000 results) happens very slowly. I use the following code:

var server = 'x.x.x.x';
var port = 3306;
var dbName = 'xxx';
var username = 'xxx';
var password = 'xxx';
var url = 'jdbc:mysql://' server ':' port '/' dbName;
 
function readData() {
 var conn = Jdbc.getConnection(url, username, password);
 var stmt = conn.createStatement();
 var results = stmt.executeQuery('insertQueryHere');
 var metaData=results.getMetaData();
 var numCols = metaData.getColumnCount();
 var spreadsheet = SpreadsheetApp.getActive();
 var sheet = spreadsheet.getSheetByName('sheet123');
 sheet.clearContents();
 var arr=[];
 
 for (var col = 0; col < numCols; col  ) {
   arr.push(metaData.getColumnName(col   1));
 }
 
 sheet.appendRow(arr);
 
while (results.next()) {
 arr=[];
 for (var col = 0; col < numCols; col  ) {
   arr.push(results.getString(col   1));
 }
 sheet.appendRow(arr);
}
 
results.close();
stmt.close();
sheet.autoResizeColumns(1, numCols 1);
}

Any idea on how to improve performance?

CodePudding user response：

You are making repeated calls to the server with appendRow(). Instead collect all the new rows in an array and use setValues(). See Best Practices

function readData() {
 var conn = Jdbc.getConnection(url, username, password);
 var stmt = conn.createStatement();
 var results = stmt.executeQuery('insertQueryHere');
 var metaData=results.getMetaData();
 var numCols = metaData.getColumnCount();
 var spreadsheet = SpreadsheetApp.getActive();
 var sheet = spreadsheet.getSheetByName('sheet123');
 sheet.clearContents();
 var arr=[];
 var rows = [];
 
 for (var col = 0; col < numCols; col  ) {
   arr.push(metaData.getColumnName(col   1));
 }
 
 rows.push(arr);
 
while (results.next()) {
 arr=[];
 for (var col = 0; col < numCols; col  ) {
   arr.push(results.getString(col   1));
 }
 rows.push(arr);
}

sheet.getRange(sheet.getLastRow() 1,1,rows.length,numCols).setValues(rows);
SpreadsheetApp.flush(); // just to make sure the data is written
 
results.close();
stmt.close();
sheet.autoResizeColumns(1, numCols 1);
}