Speed up apps script from MySQL Query Google Sseets

Question

I am facing an issue like many before with regards to a timeout out Google Apps Script, I am reading the data from a indexed/persisted table in a MySQL Database, the table in question has 71 columns and a total of 28000 rows, the sheet in google sheets I am writing to has no calculations etc on it which might slow things down - those happen on other sheets.

Please can you review the below that I am using and propose any changes to assist in avoiding the time out?

var server = 'xx.xx.xx.xxx';
var port = xxxx;
var dbName = 'test';
var username = 'test';
var password = 'xxx';
var url = 'jdbc:mysql://'+server+':'+port+'/'+dbName;

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

Answer 1

Issue:

I don't think the script can be made considerably faster, since potential improvements (e.g. using Sheets API as suggested by Ninca Tirtil) don't affect significatively the bulk of the script (iterating through 28000 rows).

Workaround:

Therefore, instead of trying to speed it up, I'd suggest accomplishing this in multiple executions. To that goal, I'd do the following:

• Check execution time after each iteration. If this time is close to the time limit, end the loop and write current data to the sheet. You can use the Date object for this.
• Create the following time-based trigger at the end of your function: after(durationMilliseconds). Thanks to this, the function will fire automatically after the amount of milliseconds you indicate. After each execution, a trigger will be created to fire the next execution.
• Because you want to split the loop, you have to store the row index somewhere (you could use PropertiesService at the end of each execution, for example) and retrieve it at the beginning of the next, so that in each successive execution, the script resumes the loop where it left it. You can get the row index via getRow(), and then move to that row in the next execution via relative(rows).

Code sample:

var maxTimeDiff = 1000 * 60 * 5; // 5 minutes
const PROPERTY_KEY = "Row index";

function setRowIndex(rowIndex) {
  const scriptProps = PropertiesService.getScriptProperties();
  scriptProps.setProperty(PROPERTY_KEY, rowIndex);
}
function getRowIndex() {
  const scriptProps = PropertiesService.getScriptProperties();
  const rowIndex = scriptProps.getProperty(PROPERTY_KEY);
  return rowIndex;
}

function createTrigger() {
  ScriptApp.newTrigger("readDataPast")
    .timeBased()
    .after(60 * 1000) // Next execution after a minute
    .create();  
}

function readDataPast() {
  var startTime = new Date();
  var conn = Jdbc.getConnection(url, username, password);
  var stmt = conn.createStatement();
  var results = stmt.executeQuery('SELECT * FROM test.test_table');
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getSheetByName('Raw_Data');
  var rowIndex = getRowIndex();
  var arr = [];
  let row = [];
  if (!rowIndex || rowIndex == 0) { // Clear sheet and add metadata if first execution
    sheet.clearContents();
    var metaData = results.getMetaData();
    var numCols = metaData.getColumnCount();
    for (var col = 0; col < numCols; col++) {
      row.push(metaData.getColumnName(col + 1));
    }
    arr.push(row);
  } else {
    results.relative(rowIndex); // Move to current row
  }
  while (results.next()) {
    row = [];
    for (var col = 0; col < numCols; col++) {
      row.push(results.getString(col + 1));
    }
    arr.push(row);
    if (new Date() - startTime > maxTimeDiff) break; // End iteration if long time
  }
  var currentRow = results.getRow(); // 0 if all rows have been iterated
  setRowIndex(currentRow);
  var lastRow = sheet.getLastRow();
  sheet.getRange(lastRow + 1, 1, arr.length, arr[0].length).setValues(arr);
  results.close();
  stmt.close();
  if (currentRow) createTrigger(); // Create trigger if iteration is not finished
}