Apps Script로 JDBC를 이용해 Oracle 데이터 가져오기

 

Apps Script에서 JDBC를 이용해 Oracle 에 접근해 데이터를 가져와 본다.

jdbc만 변경하면 mysql, mssql 등도 접근이 가능하다.`

// Replace the variables in this block with real values.
const address = 'ip';
const port    = 'port';
const user    = 'user';
const userPwd = 'password';
const db      = 'db';

// Read up to 1000 rows of data from the table and log them.
function readFromTable() {
  const dbUrl = `jdbc:oracle:thin:@${address}:${port}:${db}`;
  const conn = Jdbc.getConnection(dbUrl, user, userPwd);

  const start = new Date();
  const stmt = conn.createStatement();
  stmt.setMaxRows(1000);
  const results = stmt.executeQuery('SELECT * FROM table WHERE ROWNUM < 10');
  const numCols = results.getMetaData().getColumnCount();

  while (results.next()) {
    var rowString = ''; 
    for (var col = 0; col < numCols; col++) {
      rowString += results.getString(col + 1) + '\\t';
    }
    Logger.log(rowString)
  }

  results.close();
  stmt.close();

  const end = new Date();
  Logger.log('Time elapsed: %sms', end - start);
}

https://whiseung.tistory.com/entry/Apps-Script%EB%A1%9C-BigQuery-%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%A5%BC-Google-Sheets%EC%97%90-%EB%BF%8C%EB%A0%A4%EC%A3%BC%EA%B8%B0?category=1064560 

Apps Script로 BigQuery 데이터를 Google Sheets에 뿌려주기

BigQuery 데이터를 Google Sheets에 뿌려주듯이,

JDBC를 통해 가져온 데이터를 뿌려줄 수도 있다.

 

 

function oracleToSheet() {
  const conn = Jdbc.getConnection(dbUrl, user, userPwd);

  const start = new Date();
  const stmt = conn.createStatement();
  stmt.setMaxRows(1000);
  const results = stmt.executeQuery('SELECT * FROM VC_TM.POC_DATA WHERE ROWNUM < 100');
  const numCols = results.getMetaData().getColumnCount();
  const rows = new Array();

  while (results.next()) {
    var arr1 = new Array(numCols);
    for (var col = 0; col < numCols; col++) {
      arr1[col] = results.getString(col + 1);
    }
    rows.push(arr1);
  }

  results.close();
  stmt.close();

  const sheetUrl  = SpreadsheetApp.getActiveSpreadsheet().getUrl();
  const sheetName = "getOracle"
  const sheet     = SpreadsheetApp.openByUrl(sheetUrl).getSheetByName(sheetName);

  sheet.getRange(1, 1, rows.length, numCols).setValues(rows);
}