Google script exceeded maximum execution time work around

Question

connecting to a sql server database via jdbc from a google sheets doc. This table has some 30k odd records and hit "exceeded maximum execution time" error. Is there a way to improve performance or a work around to the 6 minute execution time.

function myFunction() {
  var conn = Jdbc.getConnection("jdbc:sqlserver://dbURL", "username", "password");
  var stmt = conn.createStatement();
  stmt.setMaxRows(3000);
  var start = new Date();
  var rs = stmt.executeQuery('SELECT * FROM dbo.myTable');
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var cell = doc.getRange('a1');
  var row = 0;
  var data = [];
  while (rs.next()) {
    var rowData = [];
    for (var col = 0; col < rs.getMetaData().getColumnCount(); col++) {
      rowData[col] = (rs.getString(col + 1));
    }
    data[row] = rowData;
    row++;
  }
  rs.close();
  stmt.close();
  conn.close();
  var end = new Date();
  Logger.log('Time elapsed: ' + (end.getTime() - start.getTime()));
}

Thank you.

Modified the code to use Continuous Batch Library, even though I don't get Exceeded execution time error looks like its getting into an infinite loop and writing not one records to the sheets.

var FUNCTION_NAME = "test";
var EMAIL_RECIPIENT = "";

function test() {
  
  ContinuousBatchLibrary.startOrResumeContinousExecutionInstance(FUNCTION_NAME)
  
  var conn = Jdbc.getConnection("jdbc:sqlserver://HOST:PORT/DBNAME","user","password");
  var stmt = conn.createStatement();
  stmt.setMaxRows(100);
  var rs = stmt.executeQuery('SELECT * FROM myTable');
  var SS;
  var Sheet;
  var SheetRange;
  
  // OPEN EXISTING SPREADSHEET
  SS = SpreadsheetApp.openById(SpreadsheetApp.getActiveSpreadsheet().getId());
  
  // SET SPREADSHEET ACTIVE
  SpreadsheetApp.setActiveSpreadsheet(SS);  
  Sheet = SpreadsheetApp.setActiveSheet(SS.getSheetByName(SpreadsheetApp.getActiveSpreadsheet().getSheetName()));  
  
  // GET NUMBER OF COLUMNS
  var ColCount = rs.getMetaData().getColumnCount(); 
  
  // SET COLUMN HEADERS
    for (var col = 1; col <= ColCount; col++) {
      GSheet.getRange(1, col).setValue(rs.getMetaData().getColumnName(col));
    }
  
  // SET RANGE TO FIRST ROW AND BOLD
  GSheetRange = GSheet.getRange(1,1,1,GSheet.getLastColumn());  
  GSheetRange.setFontWeight("bold");  

  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var cell = doc.getRange('a2');
  var row = 0;
  var data = [];
  var i = ContinuousBatchLibrary.getBatchKey(FUNCTION_NAME) || 0;

  for (; i < row.length; i++) {
  while (rs.next()) {
    var rowData = [];
    for (var col = 0; col < rs.getMetaData().getColumnCount(); col++) {
      rowData[col] = (rs.getString(col + 1));
    }
    data[row] = rowData;
    row++;
  }
    if (ContinuousBatchLibrary.isTimeRunningOut(FUNCTION_NAME)) {
        ContinuousBatchLibrary.setBatchKey(FUNCTION_NAME, i)
        break;
      }
    }
  
  rs.close();
  stmt.close();
  conn.close();

    if (i === row.length) {
    ContinuousBatchLibrary.endContinuousExecutionInstance(FUNCTION_NAME, EMAIL_RECIPIENT, "task complete")
  } 
}

Answer 1

This answer is probably still the best work around for the 6 min timer.

The cumulative trigger run time limit is one hour a day so if you are getting <10% of the way thru your data before the timeout you will run into that as well.