구글 시트에 Apps Script를 붙여서 자동화를 하다 보면 첫 줄은 거의 늘 같습니다. "어느 파일의 어느 시트에서 작업할지"부터 정해야 하니까요. 엑셀 VBA로 치면 Worksheets("Daily report") 를 변수에 담아 두는 것과 같은 일입니다.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName("Daily report");

두 줄이 하는 일

SpreadsheetApp.getActiveSpreadsheet() 는 지금 스크립트가 붙어 있는 스프레드시트 파일 전체를 돌려줍니다. 시트 메뉴의 확장 프로그램에서 Apps Script를 열어 만든 스크립트(파일에 묶인 스크립트)라면 별다른 설정 없이 바로 그 파일이 잡힙니다.

광고

그 파일 안에서 탭 이름이 "Daily report" 인 시트를 꺼내는 게 getSheetByName 입니다. 이후 s.getRange("A1"), s.getLastRow() 처럼 시트 단위 작업은 모두 이 변수 s 를 통해 합니다.

getActiveSheet() 로 현재 보고 있는 시트를 잡는 방법도 있지만, 그건 사용자가 어떤 탭을 열어 두었느냐에 따라 대상이 바뀝니다. 트리거로 자동 실행하거나 다른 탭을 보고 있는 상태에서 메뉴를 눌렀을 때 엉뚱한 시트에 값이 써지는 일이 생기죠. 대상이 정해져 있는 작업이라면 이름으로 지정하는 쪽이 안전합니다.

시트가 없으면 null

이름이 틀리거나 누가 탭 이름을 바꿔 버리면 getSheetByName 은 오류를 내지 않고 null 을 돌려줍니다. 그 상태로 다음 줄에서 s.getRange(...) 를 부르면 그제서야 "null의 속성을 읽을 수 없다"는 식의 오류가 나서 원인을 찾기 헷갈립니다. 탭 이름 앞뒤 공백, 대소문자, 띄어쓰기 하나 차이로도 못 찾으니, 이름으로 찾은 뒤에는 바로 확인하는 습관을 들이는 게 좋습니다.

function getReportSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getSheetByName("Daily report");
  if (s == null) {
    // 없으면 새로 만든다
    s = ss.insertSheet("Daily report");
  }
  return s;
}

위처럼 없을 때 insertSheet 로 만들어 버리거나, 반대로 throw new Error("Daily report 시트가 없습니다") 로 바로 멈추게 하면 나중에 디버깅하기 훨씬 편합니다.

다른 파일의 시트를 잡을 때

getActiveSpreadsheet() 는 파일에 묶인 스크립트에서만 의미가 있습니다. 독립 실행 스크립트이거나 다른 파일의 시트를 읽어야 한다면 SpreadsheetApp.openById("파일ID") 로 파일을 연 다음, 똑같이 getSheetByName 을 이어 쓰면 됩니다. 파일 ID는 시트 주소의 /d/ 와 /edit 사이에 있는 긴 문자열입니다.

이름이 자주 바뀌는 시트라면 gid로

탭 이름은 누구나 바꿀 수 있어서, 여러 사람이 같이 쓰는 파일이라면 이름 대신 시트 고유 번호로 찾는 방법도 있습니다. 시트를 열었을 때 주소 끝의 #gid=숫자 가 그 번호이고, 탭 이름을 바꿔도 이 값은 그대로 유지됩니다.

function getSheetByGid(gid) {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].getSheetId() == gid) return sheets[i];
  }
  return null;
}

대신 코드만 봐서는 어느 시트인지 알기 어려우니, 주석으로 탭 이름을 같이 적어 두는 게 좋습니다. 시트를 지웠다가 같은 이름으로 다시 만들면 번호가 달라진다는 점도 기억해 두세요.

시트를 잡은 다음에 느려지는 지점

시트 변수를 구했다면 셀 값을 하나씩 getRange().getValue() 로 읽기보다 s.getDataRange().getValues() 로 한 번에 2차원 배열로 받아 오는 편이 훨씬 빠릅니다. Apps Script는 시트를 읽고 쓰는 호출 하나하나가 비싸서, 반복문 안에서 셀 단위로 읽으면 몇 백 행만 돼도 실행 시간이 눈에 띄게 늘어납니다. 쓸 때도 마찬가지로 배열을 만들어 setValues 로 한 번에 넣습니다. getSheetByName 자체도 반복문 안에서 매번 부르지 말고 함수 맨 위에서 한 번만 불러 변수에 담아 쓰면 됩니다.