구글 엑셀시트 자동입력 (Apps Script로 동기화)

엑셀로 데이터 정리를 하다 보면 이런 경우가 자주 생긴다.
같은 시트 안에 두 개의 표가 있고, 특정 컬럼 값이 같을 때 다른 컬럼의 값을 가져와야 하는 상황.

 

 

그런데 이때 VLOOKUP함수로 값을 가져올 수도 있지만 연동되는 값이 자동으로 입력되면 좋을 것이다. 

예를 들어, 아래와 같이 하고 싶을 경우

"표1에서 작업자 칼럼에 내 이름이 입력될 때만, 표2에 자동으로 반영되면 좋겠다."

 

 

이건 함수만으로는 완전히 해결하기 어렵고 자동화 프로그램을 만들어야 한다. 
이때, 구글 스프레드에서 사용할 수 있는 것이 Google Apps Script 기능이다.

 

Apps Script로 자동화

구글 시트 메뉴에서 확장프로그램 → Apps Script(앱스 스크립트)에서 구글엑셀시트와 관련된 프로그램을 만들 수 있다. 아마 자바스크립트만 허용되는 것 같다. 

 

 

 

 

 

아래 이미지처럼 "+"버튼 눌러서 파일 생성후 코드를 작성하면된다. 지금과 같은 경우 이벤트 트리거라 "실행"을 따로 하지 않아도 되지만 다른 코드일 경우 "실행"버튼 눌러서 실행하고 로그도 볼 수 있다. 

 

 

 

 

 

 

이벤트 트리거 코드

이제 나의 케이스로 들어와서 내가 만들려는 이벤트 트리거는 다음과 같다.

 

1. 표1의 "논문연도"에 "번호"가 입력되면 매칭되는 표2의 해당 "논문연도"의 끝에도 해당번호가 자동으로 입력(동기화)되는 것. 

 

 

function onEdit(e) {
  if (!e) return; // 실행 버튼 누르면 에러 방지

  var sheet = e.range.getSheet();
  
  // 시트 이름 맞춰주세요 (예: "시트1")
  if (sheet.getName() === "시트1") {
    var editedRange = e.range;
    
    // M열(13번째 열, 번호)이 수정됐을 때만 실행
    if (editedRange.getColumn() == 13) {  
      var row = editedRange.getRow();
      var paperValue = sheet.getRange(row, 12).getValue(); // L열(논문·연도)
      var numberValue = editedRange.getValue();            // M열(번호)
      
      if (paperValue && numberValue) {
        var lastRow = sheet.getLastRow();
        var range = sheet.getRange(4, 5, lastRow-3, 1).getValues(); // E열(논문·연도) 범위
        
        for (var i=0; i<range.length; i++) {
          if (String(range[i][0]).trim().toLowerCase() == String(paperValue).trim().toLowerCase()) {
            sheet.getRange(i+4, 9).setValue(numberValue); // I열에 번호 입력
          }
        }
      }
    }
  }
}

 

동작 방식

1. 사용자가 표1의 M열(번호)에 값을 입력
2. 같은 행의 L열(논문·연도)을 읽어옴
3. 표2의 E열 전체를 스캔해서 같은 값을 찾음
4. 발견되면 I열에 번호를 써 넣음

실제로 써보면 꽤 편하다.
표1에 값만 넣으면 표2가 자동으로 동기화되니까.