Apps Script의 trigger 사용기와 활용 예시

💡Apps Script에서 제공하는 trigger를 사용하고 느낀점을 적어보았다.

 

 

목차

1. Apps Script에서 제공하는 Trigger의 종류
2. Apps Script Trigger 사용 시 주의할 사항
3. 속도 이슈
4. 권한 이슈
5. Apps Script Trigger 활용 예시

 

 

 

 

 

1. Apps Script에서 제공하는 Trigger의 종류

Apps Script 에서는 두가지 trigger를 제공한다.

1.1 Simple Triggers

Simple Triggers | Apps Script | Google Developers

간단한 트리거  |  Apps Script  |  Google Developers

1.2 Installable Triggers

Installable Triggers | Apps Script | Google Developers

설치 가능한 트리거  |  Apps Script  |  Google Developers

쉽게 내장 / 외장 trigger로 볼 수 있다.

 

 

Simple Triggers 란?

지정된 함수를 실행하면 자동적으로 trigger가 실행된다.

• onOpen(e)는 사용자에게 수정 권한이 있는 스프레드시트, 문서, 프레젠테이션 또는 양식을 열 때 실행됩니다.
• onInstall(e)은 사용자가 Google Docs, Sheets, Slides 또는 Forms 내에서 편집자 부가 기능을 설치할 때 실행됩니다.
• onEdit(e)는 사용자가 스프레드시트에서 값을 변경할 때 실행됩니다.
• onSelectionChange(e)는 사용자가 스프레드시트에서 선택 항목을 변경할 때 실행됩니다.
• doGet(e)는 사용자가 웹 앱을 방문하거나 프로그램이 웹 앱에 HTTP GET 요청을 보낼 때 실행됩니다.
• doPost(e)는 프로그램이 웹 앱에 HTTP POST 요청을 보낼 때 실행됩니다.

“e” 는 이벤트 객체로, 아래 링크에서 필요한 것들을 사용하면 된다.

Event Objects | Apps Script | Google Developers

이벤트 객체  |  Apps Script  |  Google Developers

 

 

 

 

 

 

Installable Triggers 란?

Apps Script 화면에서 Triggers 메뉴를 통해 들어가, 생성한 함수와 1:1로 트리거를 맵핑하는 방식이다.

[From spreadsheet] 에서 on open, on edit, on change, on form submit trigger를 맵핑 할 수 있고,

 

[Time-driven] 에서 배치 형태로 실행 할 수도 있다.

 

 

 

 

 

 

 

2. Apps Script Trigger 사용 시 주의할 사항

Trigger들의 개념이 정확이 없을때, 개발하면서 오류를 계속 범했던 부분이다.

Simple Trigger 를 선언해두고,

function onEdit(e) {
  alert(e.value);
}

function alert(msg, tit) {
  if ( isNull(tit) ) {
    tit = "알림";
  }
  const ui = SpreadsheetApp.getUi();
  ui.alert(tit, msg, ui.ButtonSet.OK);
}

Installable Trigger 로 Simple Trigger와 같이 맵핑을 하면,

해당 Trigger가 2번 실행된다.

트리가거 2번 실행되서 데이터가 초기화되는 경우가 계속 발생했엇다.

 

SimpleTrigger 이름으로, Trigger를 추가하면 두번 실행된다.

 

 

 

 

 

이제 테스트.

1번 열에 값을 입력하면, 다음 열에 값 변경 이벤트를 줘본다.

 

첫번째 행에 값이 입력되면, 두번째 행에 값을 자동으로 할당해줘본다.

function onEdit(e) {
  const sheetName   = e.source.getActiveSheet().getName();
  const column      = e.range.getColumn();
  const row         = e.range.getRow();
  const activeCell  = e.source.getActiveCell();

  if ( sheetName == "Sheet3" && row > 1 ) {
    if ( column == 1 ) {
      activeCell.offset(0, 1).setValue(`${e.value} 님 환영합니다.`);
    }
  }
}

 

 

 

 

 

3. 속도 이슈

 

개발을 하면서, 속도의 이슈가 있었다.

속도차이를 수치로 확인해보고 싶었다.

 

두개의 trigger에 timestamp를 찍어 속도를 비교해본다.

/**
 * Simple Triggers
 */
function onEdit(e) {
  const time1 = Date.now();
  const sheetName   = e.source.getActiveSheet().getName();
  const column      = e.range.getColumn();
  const row         = e.range.getRow();
  const activeCell  = e.source.getActiveCell();

  if ( sheetName == "Sheet3" && row > 1 ) {
    if ( column == 1 ) {
      activeCell.offset(0, 1).setValue(time1);
      activeCell.offset(0, 2).setValue(Date.now());
    }
  }
}

/**
 * Installable Triggers
 */
function onEdit2(e) {
  const time1 = Date.now();
  const sheetName   = e.source.getActiveSheet().getName();
  const column      = e.range.getColumn();
  const row         = e.range.getRow();
  const activeCell  = e.source.getActiveCell();

  if ( sheetName == "Sheet3" && row > 1 ) {
    if ( column == 5 ) {
      activeCell.offset(0, 1).setValue(time1);
      activeCell.offset(0, 2).setValue(Date.now());
    }
  }
}

결과이다.

 

 

 

Simple Trigger가 평균적으로 빠른 반응 속도를 보여줬다. 속도는 제각각이지만…

 

 

 

 

 

 

4. 권한 이슈

그리고 두개는 차이점이 존재한다. 곧 문제점.

 

 

두가지 event trigger 에서 bigquery에 접근하는 기능을 구현한다.

/**
 * Simple Triggers
 */
function onEdit(e) {
  const time1 = Date.now();
  const sheetName   = e.source.getActiveSheet().getName();
  const column      = e.range.getColumn();
  const row         = e.range.getRow();
  const activeCell  = e.source.getActiveCell();

  if ( sheetName == "Sheet3" && row > 1 ) {
    if ( column == 1 ) {
      const result = queryExcute();
      activeCell.offset(0, 1).setValue(`${result.length} 건 조회`);
    }
  }
}
/**
 * Installable Triggers
 */
function onEdit2(e) {
  const time1 = Date.now();
  const sheetName   = e.source.getActiveSheet().getName();
  const column      = e.range.getColumn();
  const row         = e.range.getRow();
  const activeCell  = e.source.getActiveCell();

  if ( sheetName == "Sheet3" && row > 1 ) {
    if ( column == 5 ) {
      const result = queryExcute();
      activeCell.offset(0, 1).setValue(`${result.length} 건 조회`);
    }
  }
}

/**
 * Query 실행 및 결과반환
 * select count / update status
 * @param {string} queryString  = Query
 */
function queryExcute(queryString) {
  queryString = 'SELECT BillingDocument, BillingQuantityUnit, NetAmount, TaxAmount, BillingDocumentItemText FROM `porjectId.datasetId.standard_long`  WHERE TransactionCurrency = "KRW" AND BillingDocumentItem = "82"';
  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 = 50;
  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 resultObj;
 
  if (rows) {
    if ( rows.length < 2 ) {
      resultObj = rows[0];
    } else {
      resultObj = rows;
    }
  } else {
    if ( queryResults.jobComplete ) {
      resultObj = queryResults.jobComplete;
    } else {
      return null;
    }
  }

  return resultObj;
}

 

 

 

 

 

 

Simple Trigger에서는 실행이 되지 않는다.

 

 

try catch 문을 추가해본다.

/**
 * Simple Triggers
 */
function onEdit(e) {
  try {
    const time1 = Date.now();
    const sheetName   = e.source.getActiveSheet().getName();
    const column      = e.range.getColumn();
    const row         = e.range.getRow();
    const activeCell  = e.source.getActiveCell();

    if ( sheetName == "Sheet3" && row > 1 ) {
      if ( column == 1 ) {
        const result = queryExcute();
        activeCell.offset(0, 1).setValue(`${result.length} 건 조회`);
      }
    }
  } catch (error) {
    const msg01 = error.message;
    alert("Error Message : " + msg01);
  }
}

에러메세지를 확인할수 있다. 권한문제다.

 

Error Message : API call to bigquery.jobs.query failed with error: 
Request is missing required authentication credential. 
Expected OAuth 2 access token, login cookie or other valid authentication credential. 
See <https://developers.google.com/identity/sign-in/web/devconsole-project>.

 

Trigger 이벤트로 BigQuery에 접근하려면 Installable trigger를 이용해야 한다.

(BigQuery 뿐만 아니라 다른 권한을 득해야하는 모든 서비스. 다른sheet 데이터를 참조해야할경우도)

 

간단한 트리거  |  Apps Script  |  Google Developers

 

 

 

 

 

 

 

결론적으로,

속도차이는

Simple Triggers >>> Installable Triggers

 

해당 sheet 컨트롤 하는 기능은 Simple Trigger를,

외부에 접근 권한이 필요한 기능은 Installable Triggers 를 이용해 구현해야 한다.

 

 

 

5. Apps Script Trigger 활용 예시

5.1 Google Sheets onEdit

Apps Script로 Google Sheets의 선택된 항목을 sidebar에서 수정하도록 구현 (셀 클릭 이벤트 대체)

Apps Script로 ChatGPT의 openAPI를 Google Sheets에서 호출하기

Apps Script로 Google Sheets의 종속되는 Dropdown 만들기

 

 

 

5.2 Time-driven

 

Apps Script로 장 종료 후 특정 주식의 종가 정보 카카오톡으로 전송하기

Apps Script로 MSSQL 데이터를 분단위 trigger로 sync 유지하기

Apps Script로 공공데이터 포털 openAPI 데이터를 매일 Telegram으로 받기

Apps Script로 Upbit api를 이용해 코인 시세를 매일 아침에 Telegram으로 조간 브리핑 받기

Apps Script로 UPbit API를 이용해 현재 시세를 가져와 카카오톡 메세지로 보내기

Apps Script로 지난 밤 Nasdaq 지수 Telegram으로 아침에 조간 브리핑 받기

Apps Script로 어제자 네이버 증권 시황 뉴스를 cheerio를 이용해 크롤링 해오기

Apps Script로 어제 KOSPI 지수 Telegram으로 아침에 조간 브리핑 받기