Google Apps Script running very slow-CodePudding

My Google Sheets Apps Script has been timing out - it does a simple read from a MySQL DB and loads the data in, it queries one single persisted table which hosts only 150 rows so there is no reason it should be timing out.

My Script looks like this:

var server = 'IP';
var port = PORT;
var dbName = 'DB';
var username = 'UN';
var password = 'PW';
var url = 'jdbc:mysql://' server ':' port '/' dbName;

function readData() {
 var conn = Jdbc.getConnection(url, username, password);
 var stmt = conn.createStatement();
 var results = stmt.executeQuery('SELECT * FROM db.table');
 var metaData=results.getMetaData();
 var numCols = metaData.getColumnCount();
 var spreadsheet = SpreadsheetApp.getActive();
 var sheet = spreadsheet.getSheetByName('SheetName');
 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);*/
} 

/*ScriptApp.newTrigger('readData')
.timeBased()
.everyHours(2)
.create();*/

Is there anything that could be done to better handle the import to speed it up, as I said the data is persisted into a table on the DB, the table only has approx 150 rows and 10-15 columns so the data is small and queries instantly from a DB GUI

CodePudding user response：

var server = 'IP';
var port = PORT;
var dbName = 'DB';
var username = 'UN';
var password = 'PW';
var url = 'jdbc:mysql://'   server   ':'   port   '/'   dbName;

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

  sheet.getRange(1, 1, arr.length, arr[0].length).setValues(arr);
  results.close();
  stmt.close();
}