Google Apps Script running very slow

Question

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

Use `setValues()` instead of `appendRow()`. Also try `console.time()` to figure out the part of the script that causes more time. — TheMaster, Nov 18 '21 at 07:31

Cooper · Accepted Answer · 2021-11-18T17:44:36.930

This provide a bit of a speed up all though a greater of performance is achievable if you can write code on both ends of the stream and access them via ssl.

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 = [];//this is where you store all of the data
  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);//this is where you save all of your data into the spreadsheet thus saving you all of the individual writes to the rows.
  results.close();
  stmt.close();
}

Class Range setValues() Method

If odbc had a way to get all of the data out as a 2d array that would be a great way to speed up the process immensely. Personally, I have written some routines on my database server that do that for me and I access them through fetches or in the case of google apps script through UrlFetchApp. And they can really speed things up. I prefer not going through all of that here because it involves code on both sides and all I can say is that when you're ready for it you will know. And then you can write it for yourself or higher some one to do it for you based upon what your time is worth. — Cooper, Nov 18 '21 at 17:39

Google Apps Script running very slow

1 Answers1