Apps Script 라이브러리 생성해서 다른 Apps Script에서 사용하기 - 실제 사용 예

💡 Apps Script 를 이용해 실제로 개발했던 내용을 요약했다.

 

 

 

Apps Script 라이브러리 생성해서 다른 Apps Script에서 사용하기

 

 

 

위에서 Apps Script의 구조를 간략히 설명했는데,

사용 예시와 함께 좀 더 디테일하게 작성해 보았다.

 

서울 영업부서 Google Sheet와 연결 된 Apps Script (껍데기)

Google Sheets 메뉴에서 [확장프로그램] - [Apps script] 로 생성된 Apps Script 이다.

 

상단 변수에서,

"내가 서울영업부서의 구글시트 파일이다." 라고 구분지을수 있는,

sheet url과 code를 선언해주고,

accountLib 에 해당 정보들을 전달해준다.

const this_url      = accountLib.currentSheetUrl();
const this_jisaCode = "1000";

/**
 * Generate Top buttons.
 */
function onOpen() {
  SpreadsheetApp.getUi()
        .createMenu('📄 리스트')
        .addItem('🔎 조회', 'getList')
        .addToUi();
}

/**
 * 데이터 조회
 */
function getList() {
  accountLib.getList(this_url, this_jisaCode);
}

 

영업 공통 Apps script

지사별 영업 Google Sheet 템플릿은 동일할 것이다.

그렇기 때문에, 영업공통 apps script 파일에서

sheet 이름과 dataset, table이름을 선언해준다.

그리고 데이터를 조회해서 뿌려줄 정보들을 common apps script의

queryAndBinding 라는 함수로 전달한다.

const listSheetName = "뿌려줄 시트 이름";
const dataTableId   = "가져올 테이블 이름";

/**
 * 실행된 sheet url return
 */
function currentSheetUrl () {
  return common.getSheetUrl();
}

/**
 * 리스트 조회하기
 * @param {string} sheetUrl  = 해당 function을 호출한 sheet의 URL
 * @param {string} jisa_code = 해당 function을 호출한 sheet의 사업장코드
 */
function getA4Data(sheetUrl, jisa_code ) {
    try {
        const sheetName = listSheetName ;
        const datasetId = GLOBAL_DATASET_ID;
        const tableId   = dataTableId;

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

        // 조회조건
        const cellId      = "C4";
        const target_year = common.getCellDataBySheet(sheet, cellId);
        if ( !target_year ) { common.alertMissParam(cellId); return; }

        var columnName = 'grn_class, detail_class, unit, landfill_flag';
        var whereState = 'AND year_date = "' + target_year + '"';
        if ( !common.isNull(jisa_code ) ) {
        	whereState += ' AND jisa_code  = "' + jisa_code  + '" ';
        }
        const orderState = ' ORDER BY CAST(unit AS int64) DESC ; ';

        const queryString = 'SELECT ' + columnName + ' FROM `'
                        +  GLOBAL_PROJECT_ID + "." + datasetId + "." + tableId + '` '
                        + ' WHERE status = "Y" ' + whereState + orderState;

        const dataRange = common.queryAndBinding(sheetUrl, sheetName, queryString, 10, 1, "N", null, "N");

        if ( !common.isNull(dataRange) ) {
        	common.setTableBorderBySheet(sheet, dataRange, true);
        }   
    } catch (error) {
        var msg01 = error.message;
        common.alertMessage("❌ Error", "(getList) Error Message : " + msg01);
        Logger.log(msg01);
    }
}

 

공통 Apps script

모든 정보들을 받아와 정말 실행만 해주는 실행부이다.

/**
 * 데이터를 Bigquery에서 조회해서 스프레드 시트에 표출
 * @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);
    }

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

    if (rows) {
      // Append the headers.
      var headers = queryResults.schema.fields.map(function(field) {
        return field.name;
      });
      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);

      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;
}

/**
 * 단일 cell 데이터 추출
 * @param {obj} sheet     = 작업할 sheet
 * @param {string} cellId = 작업할 cellId
 */
function getCellDataBySheet(sheet, cellId) {
  try {
    const sheet = SpreadsheetApp.openByUrl(sheetUrl).getSheetByName(sheetName);
    const val   = sheet.getRange(cellId).getValue();

    if ( isNull(val == 0 ? "0" : val) ) {
      return false;
    }

    return val;
  } catch (error) {
    const msg01 = error.message;
    alertMessage("❌ Error", "(getCellData) Error Message : " + msg01);
    Logger.log(msg01);
  }
}

/**
 * Null 체크
 * @param {obj}  obj  = Null 체크 대상
 */
function isNull(obj) {
	return (typeof obj != "undefined" && obj != null && obj != "") ? false : true;
}

/**
 * 테두리 설정 - 값을 가져와서 값을 가져온 영역의 border 설정
 * @param {obj}     sheet       = 작업할 sheet
 * @param {string}  sheetName   = 작업할 sheet name
 * @param {obj}     dataRange   = 작업할 sheet range
 * @param {boolean} flag        = set border status
 */
function setTableBorderBySheet(sheet, dataRange, flag) {
  try {
    sheet.getRange(dataRange.startRow, dataRange.startColumn, dataRange.endRow, dataRange.endColumn).setBorder(flag, flag, flag, flag, flag, flag);
  } catch (error) {
    const msg01 = error.message;
    alertMessage("❌ Error", "(setTableBorder) Error Message : " + msg01);
    Logger.log(msg01);
  }
}

/**
 * Alert Message for Google sheet.
 * @param {string} tit = 타이틀
 * @param {string} msg = 메세지
 */
function alertMessage(tit, msg) {
  const ui = SpreadsheetApp.getUi();
  ui.alert(tit, msg, ui.ButtonSet.OK);
}

/**
 * 검색조건 누락
 * @param {string} param = parameter
 */
function alertMissParam(param) {
  const ui = SpreadsheetApp.getUi();
  ui.alert("⛔ Error", param + " 조건이 누락되었습니다.", ui.ButtonSet.OK);
}