CODEWITHSUNDEEP
mysqlgoogle-apps-scriptgoogle-sheets
Stuart Conroy
Stuart Conroy

Reputation: 97

Google Script timing out when grabbing rows from Database (MYSQL)

function myFunction() {

  var ServerIP = 'xxx.xxx.xxx.xxx';
  var SQL_Port = '3306';
  var SQL_Usr = 'GDriveUser';
  var SQL_Pwd = '**********';
  var SQL_DB = 'wordpress_10';
  var connectorInstance = 'jdbc:mysql://' + ServerIP+':'+SQL_Port;
  var ConnectString = connectorInstance+'/'+SQL_DB;
  
     var conn = Jdbc.getConnection(ConnectString, SQL_Usr, SQL_Pwd);
     conn.setAutoCommit(false)
  
  var SQLstatement = conn.createStatement();
  var result = SQLstatement.executeQuery("SELECT * FROM `TABLE35` WHERE Team in ( 'atl', 'bkn', 'bos', 'cha', 'chi', 'cle', 'dal', 'den', 'det', 'gsw') and Date > 20201201 ORDER BY Date ASC");
  var ss = SpreadsheetApp.openById("1QTv67oSTYnwsD0kwd0sDubzAor19pfAVngubjePqtzJg"); 
  
 var sheet = ss.getSheetByName("Imported");
  var cell = sheet.getRange('A2');
  // loop through result object, setting cell values to database data
  var row = 0;
  while(result.next()) {
    for(var i=0; i<23; i++) {     // four fields per record
       cell.offset(row, i).setValue(result.getString(i+1));
  }
    row++;
 }
 result.close();
 SQLstatement.close();

 conn.close();
}

This Google Script is only putting about 150 rows into my Google Sheet and then it times out.

Can someone help me find a fix for this?

Upvotes: 0

Views: 39

Answers (1)

Cooper
Cooper

Reputation: 64062

Something like this:

function myFunction() {

  var ServerIP = 'xxx.xxx.xxx.xxx';
  var SQL_Port = '3306';
  var SQL_Usr = 'GDriveUser';
  var SQL_Pwd = '**********';
  var SQL_DB = 'wordpress_10';
  var connectorInstance = 'jdbc:mysql://' + ServerIP+':'+SQL_Port;
  var ConnectString = connectorInstance+'/'+SQL_DB;
  
     var conn = Jdbc.getConnection(ConnectString, SQL_Usr, SQL_Pwd);
     conn.setAutoCommit(false)
  
  var SQLstatement = conn.createStatement();
  var result = SQLstatement.executeQuery("SELECT * FROM `TABLE35` WHERE Team in ( 'atl', 'bkn', 'bos', 'cha', 'chi', 'cle', 'dal', 'den', 'det', 'gsw') and Date > 20201201 ORDER BY Date ASC");
  var ss = SpreadsheetApp.openById("1QTv67oSTYnwsD0kwd0sDubzAor19pfAVngubjePqtzJg"); 
  
 var sheet = ss.getSheetByName("Imported");
  var cell = sheet.getRange('A2');
  // loop through result object, setting cell values to database data
  var row = 0;
  let oA=[];
  sheet.getRange(2,1,sheet.getLastRow()-1,sheet.getLastColumn()).clearContent();//clear content before writing
  while(result.next()) {
    let rA=[];
    for(var i=0; i<23; i++) {     
       rA.push(result.getString(i+1));
    }
    oA.push(rA);
 }
 sheet.getRange(2,1,oA.length,oA[0].length).setValues(oA);//starts at row 2 every time
 result.close();
 SQLstatement.close();

 conn.close();
}

Upvotes: 1

Related Questions