[Google Sheets/Apps Script] 구글 시트 테두리 자동화하기

엑셀을 활용할 때에는 행 / 열이 추가될 때마다 테두리를 자동으로 적용할 수 있는데요,

구글 시트에서는 기본적으로 이러한 기능을 지원하고 있지 않습니다.

 

따라서 새로운 행/열이 추가될 때마다 기존에 있던 테두리를 지우고, 새로 만들어 줘야 하는데요

Google Apps Script를 활용하면 간단하게 기존에 있던 테두리를 지우고 새로 만들 수 있습니다.

Google Apps Script 공식 문서는 다음 링크에 있습니다.

https://developers.google.com/apps-script/samples

Apps Script 샘플 개요  |  Google Developers

 

1. Google Apps Script 사용하기

Google Apps Script를 활용하면 본인의 필요에 따라서 함수를 작성하고 또 적용할 수 있습니다.

일반적으로 사용하는 VLOOKUP과 같은, 수식이나 분석 용도의 함수도 만들 수 있지만, 저는 테두리를 그리는 것을 자동화하고자 함수를 정의해보았습니다.

 

먼저 구글 스프레드시트로 엑셀 파일을 열어줍니다.

임의의 값으로 작성한 시트입니다.

가장 상단에 있는 컬럼을 보면 일주일마다 데이터가 추가되는 것을 확인할 수 있습니다.

또한 가장 최신 주에 빨간 색으로 테두리가 쳐져 있고, total을 나타내는 row에도 검은 색으로 테두리가 쳐져 있습니다.

 

그렇다면 다음 주 (=3월 첫 주)에 하나의 컬럼이 추가되었을 때 또다시 테두리를 두 번 쳐야 합니다.

그런데 그렇게 하려면 2/26에 쳤던 테두리를 또 지우고, 다시 그려야 합니다.

이러한 테두리가 위의 샘플처럼 한 번만 나타난다면 그냥 수기로 하고 말지만, 3개가 넘어가게 되면 자동화에 대한 니즈를 느낄 것입니다. 이 때 구글 Apps Script가 도움이 될 수 있습니다.

 

상단 바의 확장 프로그램 > Apps Script를 클릭합니다. 새로운 창이 뜨게 됩니다. 아래와 같은 화면이 뜨면 정상입니다.

 

2. Google Apps Script 콘솔 창에서 코드 입력하기

일반적으로 프로그래밍에 익숙한 사람이라면 공식 문서만 보고서도 쉽게 코드를 작성하실 수 있을 것입니다.

간단하게 설명을 적도록 하겠습니다.

Function

시트에 값을 입력하거나 테두리를 그리거나 색을 채워넣는 등의 작업을 할 수 있는 함수입니다.

특이한 것은, 콘솔 창에 함수를 여러 개 정의할 수 있는데, 이 함수를 호출하는 것은 콘솔 위의 함수 실행 영역에서 버튼을 누르면 된다는 것입니다. 자세한 설명은 아래에서 계속 해보도록 하겠습니다.

 

일단 새로운 컬럼을 오른쪽에 추가하겠다고 가정하겠습니다.

 

먼저 함수를 두 개로 분리해서 작성하겠습니다. 빨간색 테두리, 검은 테두리를 순서대로 그릴 것입니다.

또, 테두리를 어떤 공간에 그릴 것인지 Range를 통해서 정의해줍니다.

우리는 정해진 공간의 가장 오른쪽에 위치한 곳에 테두리를 그려줄 것입니다.

 

그런데 새로운 주의 레코드에 테두리를 그리는 것 외에도, 직전 주의 레코드에 있던 테두리를 지우는 것까지 해야합니다. 따라서 테두리를 지우는 함수도 하나 작성해줍니다.

 

참고로 저희 예시에서는 하나의 색상으로 단 한번만 그리면 되었지만, 만약 여러 영역에 그리고 싶다면

var blackcells = sheet.getRange(10, 1, 2, lastcolnum);

blackcells.setBorder(true, true, true, true, null, null, "black", SpreadsheetApp.BorderStyle.SOLID_THICK);

var blackcells = sheet.getRange(14, 1, 2, lastcolnum);

blackcells.setBorder(true, true, true, true, null, null, "black", SpreadsheetApp.BorderStyle.SOLID_THICK);

 

위 코드처럼 그릴 영역을 한 번 더 정의하고 -> 그리기 명령을 해주면 됩니다.

 

샘플 코드를 첨부합니다.

function remove_line() {
  // 먼저 스프레드시트에 접근
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  // 임의의 row 하나 잡아서, 가장 마지막 컬럼 번호를 찾는다
  var row = sheet.getRange("A1:1");
  var lastcolnum = row.getLastColumn();

  // 테두리를 제거해야 하는 셀 Range 정의 (가장 마지막 컬럼 - 1)
  var removecells = sheet.getRange(1, lastcolnum-1, 12);

  // 테두리를 모두 제거해줍니다.
  removecells.setBorder(false, false, false, false, false, false);
}


function red_line() {
  // 먼저 스프레드시트에 접근
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  // 임의의 row 하나 잡아서, 가장 마지막 컬럼 번호를 찾는다
  var row = sheet.getRange("A1:1");
  var lastcolnum = row.getLastColumn();

  // 빨간 테두리를 그려야 하는 셀 Range 정의 (가장 마지막 컬럼)
  var redcells = sheet.getRange(1, lastcolnum, 12);

  // 테두리를 그려줍니다.
  redcells.setBorder(true, true, true, true, null, null, "red", SpreadsheetApp.BorderStyle.SOLID_THICK);
}

function black_line() {
  // 먼저 스프레드시트에 접근
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  // 임의의 row 하나 잡아서, 가장 마지막 컬럼 번호를 찾는다
  var row = sheet.getRange("A1:1");
  var lastcolnum = row.getLastColumn();

  // 검은 테두리를 그려야 하는 셀 Range 정의 (10번째 row의 1번째 셀부터 2줄, 가장 마지막 컬럼까지)
  var blackcells = sheet.getRange(10, 1, 2, lastcolnum);

  // 테두리를 그려줍니다.
  blackcells.setBorder(true, true, true, true, null, null, "black", SpreadsheetApp.BorderStyle.SOLID_THICK);
}

 

코드를 다 짰으면 저장 버튼을 눌러줍니다. 저장을 누르면 다음과 같이 실행할 함수를 선택하는 드롭다운이 활성화됩니다.

3. 함수 실행하기

이제 함수를 하나씩 클릭해서 실행해줍니다.

가장 먼저 remove_line, 그 뒤로 red, black을 차례대로 실행해줍니다.

이 때 처음으로 실행하는 경우 승인이 필요하다고 뜰 수 있습니다.

잘 해결되지 않는 경우 다음 링크를 따라하세요.

 

https://www.citopes.com/m/entry/%EA%B5%AC%EA%B8%80%EC%95%B1-%EC%8A%A4%ED%81%AC%EB%A6%BD%ED%8A%B8-%EA%B6%8C%ED%95%9C-%ED%97%88%EC%9A%A9%ED%95%98%EA%B8%B0

구글앱 스크립트 권한 허용하기

 

실행 전

remove_line 실행시

red_line 실행시

black_line 실행시

이렇게 원하는 영역에 테두리를 그릴 수 있습니다. 🥳

 

참고로 AppsScript옆의 알람 모양 버튼을 누르면 특정 시간에 / 설문지에 응답이 새로 들어왔을 때 등 트리거를 받아 자동으로 해당 코드가 돌아갈 수 있도록 설정하기도 가능하다고 합니다.