[Google Apps Script] onOpen 이벤트로 시트 이동 메뉴 만들기

시트가 수십 장 되는 구글 스프레드시트를 쓰다 보면 아래 탭을 좌우로 넘기며 찾는 게 꽤 번거롭습니다. Apps Script의 onOpen 은 파일을 열 때마다 자동으로 실행되는 함수라서, 여기에 사용자 메뉴를 붙여 두면 메뉴 하나로 원하는 기능을 바로 부를 수 있습니다.

메뉴 추가만 하는 최소 형태

function onOpen(event) {
  var ss = event.source;
  var menuEntries = [];
  menuEntries.push({name: "Go to sheet...", functionName: "showGoToSheet"});
  ss.addMenu("Tasks", menuEntries);
}

onOpen 이라는 이름 자체가 약속이라 따로 트리거를 등록하지 않아도 됩니다. event.source 가 지금 열린 스프레드시트이고, addMenu 에 메뉴 이름과 항목 배열을 넘기면 상단 메뉴줄에 "Tasks" 가 생깁니다. 항목의 functionName 에는 클릭했을 때 실행할 함수 이름을 문자열로 적습니다.

스크립트를 저장한 직후에는 메뉴가 안 보이는데, 파일을 새로 고침해야 onOpen 이 다시 실행되면서 나타납니다. 처음 만들었을 때 메뉴가 안 생긴다고 헤매는 경우가 대부분 이 때문입니다.

시트 목록 창에서 골라 이동하기

아래는 메뉴를 누르면 시트 이름 목록을 창으로 띄우고, 이름을 클릭하면 그 시트로 이동하는 전체 예제입니다. 맨 아래에는 그림이나 버튼에 연결해 특정 시트("Sheet5")로 바로 가는 함수도 들어 있습니다.

// Google 스프레드 시트의 특정 시트에 연결

function onOpen(event) {
  var ss = event.source;
  var menuEntries = [];
  menuEntries.push({name: "Go to sheet...", functionName: "showGoToSheet"});
  ss.addMenu("Tasks", menuEntries);
}

function showGoToSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var allsheets = ss.getSheets();
  var app = UiApp.createApplication();
  app.setTitle("Go to sheet...").setWidth(800).setHeight(600);
  var table = app.createFlexTable();
  table.setCellPadding(5).setCellSpacing(0);
  var goToSheetClick = app.createServerHandler('handleGoToSheetClick');
  var widgets = [];
  for (var i = 0; i < allsheets.length; i++) {
    var sheet_name = allsheets[i].getName();
    widgets[i] = app.createHTML(sheet_name).setId(sheet_name).addClickHandler(goToSheetClick);
    table.setWidget(i, 1, widgets[i])
  }
  var panel = app.createSimplePanel();
  panel.add(table);
  app.add(panel);
  ss.show(app);
}

function handleGoToSheetClick(e) {
  var sheet_name = e.parameter.source;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheet_name);
  sheet.activate();
  var app = UiApp.getActiveApplication();
  app.close();
  return app;
}

// 버튼을 클릭하고 "Sheet5"로 이동
function showSheetByName(Name) {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheetByName(Name);
   SpreadsheetApp.setActiveSheet(sheet);
}

function showSheet5() {
   showSheetByName("Sheet5");
}

showGoToSheet 는 모든 시트를 돌면서 이름을 표에 한 줄씩 넣고, 각 이름에 같은 클릭 핸들러를 붙입니다. 위젯 ID를 시트 이름으로 지정해 두었기 때문에, 핸들러에서는 e.parameter.source 로 어느 이름을 눌렀는지 알아내고 그 시트를 활성화한 뒤 창을 닫습니다.

UiApp 부분은 지금 쓸 수 없다

창을 만드는 데 쓴 UiApp 은 구글이 지원을 끝낸 서비스라서, 지금 이 코드를 그대로 넣으면 showGoToSheet 를 실행할 때 오류가 납니다. 같은 기능을 만들려면 HtmlService 로 HTML 대화상자를 띄우고, 클릭 시 google.script.run 으로 서버 함수를 호출해 시트를 활성화하는 구조로 바꿔야 합니다. 메뉴를 붙이는 쪽도 요즘은 SpreadsheetApp.getUi().createMenu("Tasks").addItem(...).addToUi() 형태를 많이 씁니다.

HtmlService로 바꾼 시트 이동 창

위 설명대로 옮기면 파일이 두 개가 됩니다. 스크립트 편집기에서 기존 onOpen, showGoToSheet, handleGoToSheetClick 을 지우고 아래 코드를 넣은 뒤, "파일 추가 → HTML" 로 GoToSheet 라는 이름의 HTML 파일을 하나 만듭니다.

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Tasks')
    .addItem('Go to sheet...', 'showGoToSheet')
    .addToUi();
}

function showGoToSheet() {
  var names = SpreadsheetApp.getActiveSpreadsheet().getSheets().map(function (s) {
    return s.getName();
  });
  var t = HtmlService.createTemplateFromFile('GoToSheet');
  t.names = names;
  SpreadsheetApp.getUi().showModalDialog(
    t.evaluate().setWidth(300).setHeight(400), 'Go to sheet...');
}

function goToSheet(name) {
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName(name).activate();
}

<!DOCTYPE html>
<html>
  <body>
    <? for (var i = 0; i < names.length; i++) { ?>
      <div><a href="#" onclick="go(this.textContent); return false;"><?= names[i] ?></a></div>
    <? } ?>
    <script>
      function go(name) {
        google.script.run.withSuccessHandler(function () {
          google.script.host.close();
        }).goToSheet(name);
      }
    </script>
  </body>
</html>

HTML 쪽의 <? ?> 는 템플릿 문법이라, 서버에서 넘긴 names 배열을 돌면서 시트 이름을 링크로 하나씩 찍어 줍니다. 이름을 누르면 google.script.run 이 서버의 goToSheet 를 부르고, 시트가 활성화되면 google.script.host.close() 로 창을 닫습니다. 예전 코드에서 위젯 ID로 시트 이름을 넘기던 부분이 여기서는 링크 글자 자체를 넘기는 방식으로 바뀐 셈입니다.

처음 메뉴를 누르면 권한 승인 창이 뜹니다. 스크립트가 시트를 읽고 대화상자를 띄우려면 한 번은 승인이 필요하니 계정을 골라 허용하면 되고, 그 뒤로는 바로 열립니다. 그리고 onOpen 같은 단순 트리거는 그 파일을 편집할 수 있는 사람이 열 때 실행됩니다. 보기 권한만 있는 사람에게는 메뉴가 안 보일 수 있으니, 여럿이 같이 쓰는 파일이라면 이 점을 미리 알려 두세요.

반면 맨 아래 showSheetByName / showSheet5 는 지금도 그대로 동작합니다. 시트에 도형이나 그림을 넣고 "스크립트 할당"에서 showSheet5 를 지정하면, 그 그림을 누를 때마다 Sheet5로 이동합니다. 목차 시트를 하나 만들어 시트별 버튼을 늘어놓으면 간단한 내비게이션이 됩니다. 시트 이름을 바꾸면 함수 안의 문자열도 같이 고쳐야 한다는 점만 기억해 두세요.