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

 

그럼, 데이터를 조회해서 바로 Google Sheets에 바로 뿌려줄 수 있는 기능을 구현해본다.

초기에 작성한 부분이라, 모든 함수를 호출 할 때

SheetUrl, SheetName 을 계속 호출하는데

excuteA(sheetUrl, sheetName);
excuteB(sheetUrl, sheetName);
excuteC(sheetUrl, sheetName);

앞으로 개발 할 때는 sheet 를 먼저 선언하고, sheet를 넘겨 매번 sheet를 호출하는 작업을 없애야함

var sheet = SpreadsheetApp.openByUrl(sheetUrl).getSheetByName(sheetName);

excuteA(sheet);
excuteB(sheet);
excuteC(sheet);

데이터 조회하는 부분

function getDataFromBigquery() {
	// Query setting
    const datasetId = global_datasetId;
    const tableId = "standard_long";
    const columns = "BillingDocument, BillingQuantityUnit, NetAmount, TaxAmount, BillingDocumentItemText"
    const queryString = 'SELECT  ' + columns + ' FROM `'
   					  +  GLOBAL_PROJECT_ID + "." + datasetId + "." + tableId + '` WHERE TransactionCurrency = "KRW" AND BillingDocumentItem = "82"; '

    // Sheets Informations
    const sheetUrl = SpreadsheetApp.getActiveSpreadsheet().getUrl();
    const sheetName = "Sheet2"

    // Call common function
    const dataRange = queryAndBinding(sheetUrl, sheetName, queryString, 2, 1, "N", null, "Y");

    if ( dataRange != null ) {
        // Set Table Border
        setTableBorder(sheetUrl, sheetName, dataRange, true);
    }
}

공통 영역

/**
 * 데이터를 Bigquery에서 조회해서 스프레드 시트에 표출
 * 동일한 function 을 동일한 template의 sheet에서 실행하도록 구현하면
 * 라이브러로 생성된 현재 function만 수정하면 모두 반영가능 
 * @param {string} sheetUrl     = 작업할 sheet URL
 * @param {string} sheetName    = 작업할 sheet name
 * @param {string} queryString  = Query
 * @param {string} startRow     = startRow
 * @param {string} startColumn  = startColumn
 * @param {string} mergeYn      = 셀병합이 되어있으면(Y) delete Rows, 셀병합이 되어있지 않으면(N) clear Rows
 * @param {string} mergeArea    = 셀병합할 영역 - 예시  첫번째, 세번째 셀을 병합 시  "[1, 3]"
 * @param {string} headerYn     = 헤더(컬럼정보) 보여줄지 여부
 */
function queryAndBinding(sheetUrl, sheetName, queryString, startRow, startColumn, mergeYn, mergeArea, headerYn) {
    var request = {
      query: queryString,
      useLegacySql: false
    };

    var queryResults = BigQuery.Jobs.query(request, GLOBAL_PROJECT_ID);
    var jobId = queryResults.jobReference.jobId;

    // Check on status of the Query Job.
    var sleepTimeMs = 10;
    while ( !queryResults.jobComplete ) {
      Utilities.sleep(sleepTimeMs);
      sleepTimeMs *= 2;
      queryResults = BigQuery.Jobs.getQueryResults(GLOBAL_PROJECT_ID, jobId);
    }

    // Get all the rows of results.
    var rows = queryResults.rows;
    while (queryResults.pageToken) {
      queryResults = BigQuery.Jobs.getQueryResults(GLOBAL_PROJECT_ID, jobId, {
        pageToken: queryResults.pageToken
      });
      rows = rows.concat(queryResults.rows);
    }

    // 해당 sheet에 데이터가 있는지 확인
    var checkDeleteRow = checkLastRow(sheetUrl, sheetName);
    
    if ( checkDeleteRow ) {
      if ( mergeYn == "N" ) {
		// 셀병합이 되어 있지 않으면 clear data
        clearAllRow(sheetUrl, sheetName, startRow);
      } else {
		// 셀병합이 되어 있는 경우에는, 셀병합을 푸는거보다 삭제 쉬움
        deleteAllRow(sheetUrl, sheetName, startRow);
      }
    }

    if (rows) {
      var sheet = SpreadsheetApp.openByUrl(sheetUrl).getSheetByName(sheetName);

      // Append the headers.
      var headers = queryResults.schema.fields.map(function(field) {
        return field.name;
      });

	  // header 정보를 뿌려줄 경우
      if ( !isNull(headerYn) && headerYn == "Y" ) {
        console.log("show headers");
        sheet.appendRow(headers);
      }

      // Append the results.
      var data = new Array(rows.length);
      for (var i = 0; i < rows.length; i++) {
        var cols = rows[i].f;
        data[i] = new Array(cols.length);
        for (var j = 0; j < cols.length; j++) {
          data[i][j] = cols[j].v;
        }
      }

      sheet.getRange(startRow, startColumn, rows.length, headers.length).setValues(data);

      // border 설정을 위한 set value 영역
      var rangeObj = new Object();
      rangeObj.startRow = startRow;
      rangeObj.startColumn = startColumn;
      rangeObj.endRow = rows.length;
      rangeObj.endColumn = headers.length;
	  // 셀병합
      //if ( !isNull(mergeArea) ) {
      //  mergeCell(sheetUrl, sheetName, mergeArea);
      //}
    } else {
      alertMessage("⚠️ Data Load Failed!", "존재하는 데이터가 없습니다. \\r\\n");
      Logger.log('No rows returned.');
      return null;
    }
    return rangeObj;
}

/**
 * 해당 sheet에 데이터가 있는지 확인
 * @param {string} sheetUrl   = 작업할 sheet URL
 * @param {string} sheetName  = 작업할 sheet name
  */
function checkLastRow(sheetUrl, sheetName) {
  var sheet = SpreadsheetApp.openByUrl(sheetUrl).getSheetByName(sheetName);
  var lastRow = sheet.getLastRow();
  if ( lastRow > 0 ) { 
    return true; 
  }
  return false;
}

/**
 * 헤더를 제외한 전체 영역의 데이터 삭제
 * @param {string} sheetUrl   = 작업할 sheet URL
 * @param {string} sheetName  = 작업할 sheet name
 * @param {int} startRow      = 삭제를 시작할 row num
 */
function clearAllRow(sheetUrl, sheetName, startRow) {
  var sheet = SpreadsheetApp.openByUrl(sheetUrl).getSheetByName(sheetName);
  // 이거 귀찮아서 대충함
  sheet.getRange('A' + startRow + ':' + 'Z1001').clearContent();
}

/**
 * 행삭제 - 시작부터 끝까지    * 삭제 시 수식/서식이 날아감
 * @param {string} sheetUrl   = 작업할 sheet URL
 * @param {string} sheetName  = 작업할 sheet name
 * @param {int}    startRow   = 삭제시작row
 */
function deleteAllRow(sheetUrl, sheetName, startRow) {
  var sheet = SpreadsheetApp.openByUrl(sheetUrl).getSheetByName(sheetName);
  var lastRow = sheet.getLastRow();
  var checkDeleteRow = checkLastRow(sheetUrl, sheetName);
  if ( checkDeleteRow && (lastRow-startRow>0) ) {
    sheet.deleteRows(startRow, lastRow-startRow);
  }
}

/**
 * 테두리 설정 - 값을 가져와서 값을 가져온 영역의 border 설정
 * @param {string}  sheetUrl    = 작업할 sheet URL
 * @param {string}  sheetName   = 작업할 sheet name
 * @param {obj}     dataRange   = 작업할 sheet range
 * @param {boolean} flag        = set border status
 */
function setTableBorder(sheetUrl, sheetName, dataRange, flag) {
  var sheet = SpreadsheetApp.openByUrl(sheetUrl).getSheetByName(sheetName);
  sheet.getRange(dataRange.startRow, dataRange.startColumn, dataRange.endRow, dataRange.endColumn).setBorder(flag, flag, flag, flag, flag, flag);
}