[ChatGPT] 챗GPT와 함께 앱스 스크립트 조건부 서식 만들기 | 구글 시트, 스프레드시트

이 글은 [이게 되네? 업무 자동화 미친 활용 앱스 스크립트 with 챗GPT]에서 발췌했습니다.
글 서휘승 / 골든래빗 출판사

보통 스프레드시트의 조건부 서식 기능으로 처리합니다. 조건부 서식은 앱스 스크립트에서도 할 수 있습니다. 한 번에 여러가지 조건부 서식을 적용하고 싶을 때 유용하게 쓰일 겁니다. 교통사고 시트로 돌아가 이 작업을 다시 해봅시다.

조건부 서식 만들기

조건부 서식은 newConditionalFormatRule( ) 메서드를 이용하면 됩니다. 이때 조건부 서식은 규칙을 생성하는 코드와, 규칙을 적용하는 코드로 나눠 실행해야 합니다. 무슨 말인지는 다음 기본형 코드를 보며 이야기합시다.

// ➊ 조건부 서식 규칙 생성
const rule = SpreadsheetApp.newConditionalFormatRule()
  .whenTextEqualTo(비교할 텍스트)
  .setBackground(음영 색상) // 음영 색상 설정
  .setBold(true) // 폰트 굵기 설정
  .setUnderline(true) // 폰트 밑줄 설정
  .setRanges([applyRange]) // 적용할 범위 설정
  .build();
// ➋ 조건부 서식 규칙 적용
rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules);

① newConditionalFormatRule( ) 메서드부터 체이닝 방식으로 옵션 메서드들이 쭉 이어져 있습니다. 이어진 메서드들 중 whenTextEqualTo( ) 메서드만 조건과 관련된 것입니다. 나머지는 모두 서식 옵션과 관련된 것입니다. whenTextEqualTo( ) 메서드 외에 자주 사용하는 조건 메서드는 다음과 같습니다.

• whenTextEqualTo(text) : text와 일치하는 경우
• whenTextContains(text) : text가 포함되어 있을 경우
• whenNumberBetween(start, end) : start와 end 사이에 값이 있거나 일치할 경우
• whenNumberEqualTo(number) : 숫자가 number와 같을 경우
• whenNumberGreaterThan(number) : 숫자가 number보다 크거나 같을 경우
• whenNumberLessThan(number) : 숫자가 number보다 작거나 같을 경우

이렇게 조건부 서식을 설정하면 셀에 바로 반영되지 않습니다. ②와 같이 규칙을 불러온 다음 규칙을 푸시하고 setConditionalFormantRules( ) 메서드를 적용해야 합니다. 그럼 이대로 코드를 작성해봅시다. 여기서는 ‘차대차’ 항목만 강조하는 조건부 서식을 적용해봅니다. 텍스트가 일치해야 하므로 whenTextEqualTo( ) 메서드를 사용합니다.

// 조건부 서식 적용
function setConditionalFormattingEqualText() {
  const sheet =
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('교통사고');
  // ➊ 기존에 존재하는 조건부 서식 제거
  sheet.clearConditionalFormatRules();
  // ➋ 조건부 서식을 생성할 범위를 선택
  const applyRange = sheet.getRange('A4:A' + sheet.getLastRow());
  // ➌ 조건부 서식 규칙 생성
  const rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo('차대차')
    .setBackground('yellow')
    .setBold(true)
    .setUnderline(true)
    .setRanges([applyRange])
    .build();
  // ➍ 시트에 조건부 서식을 적용
  var rules = sheet.getConditionalFormatRules();
  rules.push(rule);
  sheet.setConditionalFormatRules(rules);
}

① ~ ② 기존 조건부 서식을 제거하고 새 조건부 서식을 지정할 범위를 지정합니다.

③ newConditionalFormatRule( ) 메서드로 조건부 서식 규칙을 생성합니다. ‘차대차’라는 텍스트와 일치하는 셀을 찾아 나머지 체이닝 메서드로 옵션을 지정합니다.

④ 실질적으로 조건부 서식은 여기서 반영됩니다. getConditionalFormatRules( ) 메서드로 해당 시트에 기록된 조건부 서식 규칙을 가져와 push( ) 메서드로 추가하고, setConditionalFormatRules( ) 메서드로 조건부 서식을 적용합니다.

사고유형중분류에서 ‘충돌’이 포함된 것만 조건부 규칙을 적용하려면?

다른 규칙을 적용하는 조건부 서식을 만들고 싶다면 챗GPT에게 요청하면 됩니다. setConditionalFormattingEqualText( ) 함수를 보여주고 응용해보겠습니다.

[프롬프트]

이런 앱스 스크립트가 있는데 B2:B21, C2:C21에 있는 값이 ‘충돌’을 포함하고 있으면 빨간색 음영, 볼드로 표시할 수 있도록 개선해줄래?

[응답]

아래 코드는 주어진 범위 (B2:B21, C2:C21) 내에 ‘충돌’을 포함하는 값이 있는지 확인하고, 해당 셀을 빨간색 음영과 볼드로 표시합니다.

function setConditionalFormattingEqualText() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('교통사고');
  // 기존 조건부 서식 제거
  sheet.clearConditionalFormatRules();
 // 조건부 서식을 생성할 범위를 선택
  const applyRange = sheet.getRange('B2:C21');
  // 조건부 서식 규칙 생성
  const rule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextContains('충돌') // ➊
    .setBackground('red')
    .setBold(true)
    .setRanges([applyRange])
    .build();
  // 시트에 조건부 서식을 적용
  var rules = sheet.getConditionalFormatRules();
  rules.push(rule);
  sheet.setConditionalFormatRules(rules);
}

챗GPT가 앞에서 배운 메서드인 ① whenTextContains( )를 사용하여 ‘충돌’이 포함된 텍스트에 대하여 조건부 서식을 적용했습니다. 이렇게 메서드 이름을 몰라도 기존 코드를 이용하여 개선하는 방식의 질문을 하면 다른 조건부 서식도 쉽게 적용할 수 있을 겁니다.

더 많은 업무 자동화 방법은 신간 <이게 되네? 업무 자동화 미친 활용 앱스 스크립트 with 챗GPT>를 참고하세요!