챗GPT로 '스프레드시트' 자동화 하기 (앱스크립트)

1. Apps Script를 사용하여 구글 스프레드시트를 자동으로 제어하도록 할 수 있습니다.
2. 사람이 직접 클릭해서 하는 행동들에 이름이 붙어있는데, 그 행동들을 일련의 작업들로 변환한 것이 코드입니다.
3. 이 코드는 AI이전에 직접 작성해야했지만, 챗GPT는 수많은 코드와 그 역할을 알고있고, 수행하길 원하는 역할만 자연어로 제공하면 챗GPT가 대신 코드를 작성해줍니다!

Apps Script를 활용해서 구글 스프레드시트를 제어할 수 있는 것들은 다음과 같습니다.

셀 크기 조정: Apps Script를 사용하여 특정 열이나 행의 높이와 너비를 조정할 수 있습니다. 예를 들어, 특정 열의 너비를 설정하거나 특정 행의 높이를 조절할 수 있습니다.
자동 데이터 입력: 스크립트를 사용하여 스프레드시트에 데이터를 자동으로 입력하거나 수정할 수 있습니다. 예를 들어, 사용자 입력을 받아 스프레드시트에 데이터를 추가하거나, 외부 데이터 소스에서 정보를 가져와 스프레드시트에 채울 수 있습니다.
셀 배경색 변경: 특정 조건에 따라 셀의 배경색을 변경하여 시각적으로 데이터를 구분할 수 있습니다. 예를 들어, 값의 범위에 따라 색상을 다르게 하여 데이터를 쉽게 구별할 수 있습니다.
텍스트 색상 변경: 마찬가지로 셀의 텍스트 색상을 변경하여 정보를 강조하거나 구분할 수 있습니다. 특정 조건을 충족하는 데이터에 대해 텍스트 색상을 변경하여 사용자의 주의를 끌 수 있습니다.
셀 형식 지정: 숫자, 날짜, 시간 형식 등 다양한 셀 형식을 지정하여 데이터를 보다 명확하게 표시할 수 있습니다. 이는 데이터의 가독성을 높이는 데 도움이 됩니다.
조건부 서식: 특정 조건을 만족하는 셀에 자동으로 서식을 적용할 수 있습니다. 예를 들어, 특정 값 이상이거나 이하인 셀에 대해 서로 다른 배경색이나 텍스트 스타일을 적용할 수 있습니다.
시트 및 셀 보호: 특정 셀이나 시트를 보호하여 무단 변경을 방지할 수 있습니다. 이는 공동 작업 시 데이터의 무결성을 유지하는 데 유용합니다.

이 밖에도 우리가 수동으로 하는 작업이라면, 모든 작업을 코드로 표현할 수 있고

코드로 표현할 수 있다면, 자동화할 수 있습니다.

이 글에서는 위 목록 중, '조건부 서식' 규칙을 Apps Script로 자동 적용하는 것을 시도해보겠습니다.

조건부 서식 기능 활용하기

여기 하나의 스프레드시트가 있습니다.

위 시트를 더 보기편하게 만들기 위해 다음과 같은 '조건부 서식'을 적용할 수 있습니다.

만약, 부장님이 김대리에게 아래와 같은 작업을 지시했다고 가정해볼게요.

이유는 모르겠지만, 위에서 시켰으니 해야합니다.

직업이 개발자, 건축가, 조종사이면 배경을 회색으로
직업이 기자, 사서, 회계사이면 배경을 초록색으로
직업이 간호사, 의사, 치과 의사이면 배경을 파란색으로

경력이 1년이상 3년 이하이면 배경을 노란색으로
경력이 4년 이상 7년 이하이면 배경을 주황색으로
경력이 8년 이상 12년 이하이면 배경을 파란색으로
경력이 13년 이상 20년 이하이면 배경을 초록색으로

취미가 낚시, 클라이밍, 스키, 자전거 타기 => 배경을 파란색으로
취미가 원예, 공예, 그림, 요리 => 배경을 하늘색으로
취미가 독서, 사진, 음악, 게임 => 배경을 보라색으로
취미가 명상, 요가 => 배경을 초록색으로

AI 활용하지 않을 때 (3개의 조건부 서식을 적용하는데에 50초 소요)

AI 활용 (11개의 조건부 서식을 적용하는데에 15초 소요)

위와같이 조건부 서식을 적용하는 작업을 Apps Script로 아주 간단하고 빠르게 할 수 있고,

Apps Script를 작성하는 것은 챗GPT가 아주 잘하는 일입니다.

방법

아래와 같이 프롬프트를 작성할 수 있습니다.

내가 가진 스프레드시트는 1행부터 101행까지 데이터가 있고,
A열은 이름, B열은 직업, C열은 경력, D열은 취미 데이터로 구성되어있어.

아래의 조건부 서식을 자동으로 적용하는 Apps Script를 작성해줘
정확하게 일치하지 않더라도 포함되면 해당 조건을 적용해줘

[원하는 조건부 서식 내용(자연어)]

프롬프트를 챗GPT에 입력하면 코드가 작성됩니다.

챗GPT가 작성한 코드를 복사하여 앱스크립트 편집기에 붙여넣기 합니다.

저장 후 실행 버튼을 클릭하면, 원하는 조건부 서식이 적용됩니다.

챗GPT가 작성해준 Apps Script 코드는 다음과 같습니다.

(복잡한 코드의 의미를 몰라도, 활용할 수 있어요!)

function addConditionalFormatting() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange("A1:D101");

  // 직업에 따른 조건부 서식
  var jobsFormatRules = [
    {
      jobKeywords: ["개발자", "건축가", "조종사"],
      color: "#BDBDBD" // 회색
    },
    {
      jobKeywords: ["기자", "사서", "회계사"],
      color: "#90EE90" // 초록색
    },
    {
      jobKeywords: ["간호사", "의사", "치과 의사"],
      color: "#ADD8E6" // 파란색
    }
  ];

  // 경력에 따른 조건부 서식
  var experienceFormatRules = [
    {
      yearsRange: [1, 3],
      color: "#FFFF00" // 노란색
    },
    {
      yearsRange: [4, 7],
      color: "#FFA500" // 주황색
    },
    {
      yearsRange: [8, 12],
      color: "#ADD8E6" // 파란색
    },
    {
      yearsRange: [13, 20],
      color: "#90EE90" // 초록색
    }
  ];

  // 취미에 따른 조건부 서식
  var hobbiesFormatRules = [
    {
      hobbies: ["낚시", "클라이밍", "스키", "자전거 타기"],
      color: "#ADD8E6" // 파란색
    },
    {
      hobbies: ["원예", "공예", "그림", "요리"],
      color: "#87CEFA" // 하늘색
    },
    {
      hobbies: ["독서", "사진", "음악", "게임"],
      color: "#EE82EE" // 보라색
    },
    {
      hobbies: ["명상", "요가"],
      color: "#90EE90" // 초록색
    }
  ];

  var rules = [];

  // 직업 조건 추가
  jobsFormatRules.forEach(function(rule) {
    rule.jobKeywords.forEach(function(keyword) {
      rules.push(SpreadsheetApp.newConditionalFormatRule()
        .whenTextContains(keyword)
        .setBackground(rule.color)
        .setRanges([sheet.getRange("B1:B101")])
        .build());
    });
  });

  // 경력 조건 추가
  experienceFormatRules.forEach(function(rule) {
    var minYears = rule.yearsRange[0];
    var maxYears = rule.yearsRange[1];
    rules.push(SpreadsheetApp.newConditionalFormatRule()
      .whenNumberGreaterThanOrEqualTo(minYears)
      .whenNumberLessThanOrEqualTo(maxYears)
      .setBackground(rule.color)
      .setRanges([sheet.getRange("C1:C101")])
      .build());
  });

  // 취미 조건 추가
  hobbiesFormatRules.forEach(function(rule) {
    rule.hobbies.forEach(function(hobby) {
      rules.push(SpreadsheetApp.newConditionalFormatRule()
        .whenTextContains(hobby)
        .setBackground(rule.color)
        .setRanges([sheet.getRange("D1:D101")])
        .build());
    });
  });

  var existingRules = sheet.getConditionalFormatRules();
  rules = existingRules.concat(rules);
  sheet.setConditionalFormatRules(rules);
}

위 코드를 구글 Apps Script에 붙여넣기 후 저장하고 실행하면 다음과 같이 조건부 서식이 적용됩니다.