[구글시트] 실시간 추가되는 줄에 자동 수식, 셀 서식 반영 -2- (Apps Script)

[지난 포스팅]  셀 서식까지 반영할 필요 없이 수식만 반영

[구글시트] 실시간 추가되는 줄에 자동 수식 반영 -1- (ArrayFormula 함수)

 

 

내가 하고 싶었던 것

: 구글 폼을 통해 구글 시트에 값이 들어갔을 때, 해당 값을 이용하여 적용되는 수식 만들기

 

예시를 보는게 더 빠르겠죠?

공구 블로그에서 금액 얼마 이상을 구매한 사람한테 서비스 금액이나 상품을 제공하는 이벤트를 한다고 가정해봅시다.

사람들이 구글 폼으로 이렇게 월별 구매금을 적고 이벤트 신청 동의까지 체크해서 제출하는 겁니다.

 

이 때, 1~3월까지의 구매금이 총 15만원이 넘어가는지, 이벤트 신청 동의를 했는지 여부에 따라 서비스 여부를 정하고,

실시간으로 체크하여 며칠 내로 서비스를 보내주게 되어 있다면?

 

G열과 H열에는 설문지가 제출될 때마다 수식이 실시간으로 적용이 되는 편이 좋을 겁니다.

 

 

적용하고자 하는 수식

G열에는 1월+2월+3월을 해야하니 C열+D열+E열을 해야겠죠?

그러니 G2셀에는 C2+D2+E2,

G3셀에는 C3+D3+E3를 넣고 싶습니다.

 

H열에는 조건부로 G의 가격이 15만원이 넘어가는지, 이벤트 개인정보 수집에 동의했는지를 알기 위해서

H2셀에는 IF(AND(G2>=150000, F2="예"),"O","X")를 넣고

H3셀에는 IF(AND(G3>=150000, F3="예"),"O","X")를 넣고 싶습니다.

 

그리고 추가로 G열에서는 금액이 15만원이 넘어가면 초록색 색상을 표시하고,

H열에서는 "O"표시가 된 셀에만 노란색 색상을 넣고 싶습니다.

 

사전 준비

먼저 가장 윗셀인 G2와 H2 셀에 수식과 조건부 서식을 삽입합니다.

 

이 때 드래그를 하면 어떻게 되는지 볼게요?

위와 같이 수식과 함께 셀 서식도 반영되는 것을 볼 수 있습니다.

 

하지만 ArrayFormula 함수를 쓰면 데이터에 대한 부분만 적용이 되기 때문에 아래와 같이 셀 서식은 적용되지 않습니다.

 

 

방법 2 (셀의 꾸미기 서식도 반영)

구글 시트의 확장 프로그램인 'Apps Script'를 사용하면 셀을 드래그하여 긁어온 것처럼

바로 윗셀에 적용했던 서식 그대로 새로 추가된 행에도 수식과 셀 서식을 가져올 수 있습니다.

 

해당 확장프로그램은 기본적으로 코드를 이용하여 코딩을 하시는 분께서는 더 수월하게 이용하실 수 있습니다.

하지만 따라하다보면 그리 어렵지 않을 거라 생각합니다.

상단 메뉴 → 확장 프로그램 → Apps Script 를 누르면 확장프로그램이 시작됩니다.

 

들어가면 아래와 같은 화면이 뜹니다.

저기서 '제목 없는 프로젝트' 부분을 원하시는 이름으로 변경해주시고,

'myFunction'부분도 원하는 영문으로 적어주시면 됩니다.

myFunction부분을 편의상 '명령 코드'라고 하겠습니다.

 

저는 명령 코드의 이름을 autoDrag라고 지어줬고 안쪽에 아래와 같이 내용을 적었습니다.

function autoDrag() {
  var sheet = SpreadsheetApp.getActiveSheet(); // 현재 활성화 된 시트의 탭
  var data= sheet.getDataRange().getValues(); // 활성화 된 셀 데이터
  var i=data.length - 1; // 현재 시트의 행 길이 - 1
  for(let i=2; i<=l; i++) {
    var word = String("G" + i + ":H" + i);
    sheet.getRange(word).autoFillToNeighbor(SpreadsheetApp.AutoFillSeries);  
  }
}

위 내용은 G2와 H2에서부터 바로 아래 셀까지 드래그를 하는 것을 반복하는 것입니다.

let i=2에서 시작 행을 적고,

String("G" + i + ":H" + i)에서 무슨 열(여기서는 G, H)을 드래그할지 선택할 수 있습니다.

코드에 대한 이해를 하시는 분들은 주석으로 대충 이해를 하실 수 있을 거라 생각합니다

 

명령 코드의 내용까지 다 쓰고 실행을 누르면 승인 신청 알림창이 뜹니다.

'권한 검토'를 눌러 승인을 해주고 명령 코드 실행을 해보면 아까 드래그 했던 것과 똑같이 적용된 것을 확인하실 수 있습니다.

 

 

여기까지 했으면 Apps Script에서 좌측 메뉴 중 '트리거'(시계 모양)을 클릭하여

'새 트리거를 만듭니다.'를 누릅니다.

여기서 다른 건 다 그대로 두고, 하단의 이벤트 유형 선택에서 '양식 제출 시'를 선택하면,

구글 설문지를 제출할 때마다 위의 명령 코드가 자동으로 실행되어 실시간으로 내용과 셀 서식이 채워지게 됩니다.

 

 

 

주의할 점!! or 꿀팁?!

 

1) 구글 폼을 사용하는 경우, 기존의 셀에 데이터가 들어가는 것이 아닌 새로운 행이 추가되므로 제목 줄을 제외한 셀은 전부 지우고 설문을 받아주세요.

2) 기존 내용을 채울 필요 없이 새로 추가된 행 바로 윗 셀을 드래그하는 효과만 내고 싶다면 명령 코드 내용을 아래와 같이 조금 바꿔도 좋습니다.

function autoDrag() {
  var sheet = SpreadsheetApp.getActiveSheet(); // 현재 활성화 된 시트의 탭
  var data= sheet.getDataRange().getValues(); // 활성화 된 셀 데이터
  var i=data.length - 1; // 현재 시트의 행 길이 - 1
  var word = String("G" + i + ":H" + i);
  sheet.getRange(word).autoFillToNeighbor(SpreadsheetApp.AutoFillSeries);
}

 

 

이 글이 도움이 되었길 바랍니다:3