[Google App Script] 구글 설문지 작성 알림 자동화

 

 

 

 

일잘러의 비밀 구글 스프레드시트 제대로 파헤치기:구글 스프레드시트로 엑셀 밟고 칼퇴하자

"이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다."

 

 

 

 

🚀 Google 설문지 작성 알림 자동화

    🔽  Google App Script

        📦 개요

Google App script는 Google Work Space Platform에서 가벼운 Application 개발을 위해 Google이 개발한 Script Platform이에요.

Google App Script는 JavaScript 1.6에 기반을 두고 있으나, ECMA Script 5 API의 하위 집합 및 1.7, 1.8의 일부 기능도 포함하고 있다고 해요.

App Script Project는 Google Infra의 Server Side로 실행된다고 합니다.

Google이 말하길 Google Script는 Google 제품과 Third Party Servie 간 Task를 자동하기 용이한 방법을 제공하고 있다고 한답니다.

또한, App Script는 Google Drive용 추가 기능을 제공하는 유용한 도구이기도 해요.

 

 

 

    🔽  시작하기

        📦 초기구성

주니하랑은 좋은 개발자가 되기 위해 멋진 개발자가 되기 위해 Side Project를 하고 있고, Crew 들이 더 많은 것을 경험하고, 공유하길 바라는 것이에요.

그래서 Sub Project를 준비하고 있고, 이를 통해 고객에게 설문지를 받는다라는 가정을 하고, 이를 Service 하기 위한 Model을 준비하기 위해 Google 설문지를 이용해서 고객에게 어떤 문의나, 요청을 받게 되고, 이를 통해 Google 스프레드시트에 값이 저장이 되면 이를 Slack으로 알려주려고 해요.

구글 설문지

최초 주니하랑은 위와 같이 Google 설문지를 만들어 주었어요.

해당 설문지가 작성이 되면 자동으로 스프레드 시트에 입력값을 받기 위해 위와 같이 설정을 해주어야 합니다.

구글 스프레드 시트

위와 같이 자동으로 스프레드 시트가 만들어졌고, 각각의 값을 열에 받을 수 있도록 첫번째 행에 준비가 된 것을 볼 수 있어요.

 

 

 

        📦 초기 구성

주니하랑은 위와 같이 값이 입력이 되면 Slack으로 알림을 받고 싶은 것이에요.

이런 것들이 가능하다니 정말 개발은 너무 재밌는 것이에요.

최초 위와 같이 확장 프로그램을 선택한 뒤 Apps Script를 선택해 줍니다.

그럼 위와 같이 JavaScript 문법을 통해 Coding 할 수 있는 창이 나올거에요.

 

        📦 Trigger 구성

Initalize()

최초 주니하랑은 스프레드 시트에 값이 입력되면 자동으로
Trigger가 작동하여 지정된 Function이 작동할 수 있도록 작성해 주었어요.

Initalize()라는 함수를 만들어 주고, ProjectTriggers 함수를 동작 시켜 그 값을 triggers 변수에 담도록 하였습니다.

이 전에 작동하고 있는 Trigger가 있을 수 있기 때문에 이 Trigger를 지워주어야 해요.
4 ~ 6번째 반복문이 그 작업을 해주는 것이에요.

본격적으로 스프레드 시트에 값이 입력이 되면 어떤 함수를 작동 시켜주어야 해요.
그러기 위해 8번째 줄에서 새로운 Trigger를 만드는데, 이 때 소괄호 안에 작동 시킬 함수 이름을 넣어주면 되요.

이것은 Google App Script의 Class SpreadsheetTriggerBuilder를 이용하는 것이고,
스프레드 시트 트리거를 위한 빌더에요.

주요 함수는 아래와 같아요.

Function Name	Return Type	Contants
create()	Trigger	트리거를 만들고 반환
onChange()	SpreadsheetTirggerBuilder	스프레드 시트의 내용 또는 구조가 변경될 때 실행되는 트리거 지정
onEdit()	SpreadsheetTirggerBuilder	스프레드 시트를 수정할 때 실행할 트리거 지정
onFormSubmit()	SpreadsheetTirggerBuilder	스프레드 시트에 양식이 제출되면 실행되는 트리거 지정
onOpen()	SpreadsheetTirggerBuilder	스프레드 시트를 열 때 실행할 트리거 지정

9번째 줄에서 forSpreadsheet는 지정된 스프레드시트 즉, 소괄호 안에 입력된 스프레드 시트에 연결된 SpreadsheetTriggerBuilder를 만들고 반환하는 역할을해요.

출처 : https://developers.google.com/apps-script/reference/script/trigger-builder#forSpreadsheet(Spreadsheet)

그리고, SpreadsheetApp.getActiveSpreadsheet()는 현재 사용 중인 스프레드 시트를 반환하거나,
없을 경우 null을 반환해주는 함수에요. 

스프레드 시트의 Context에서 실행되는 함수는 이 함수를 호출하여
해당 Spreadsheet 객체에 대한 참조를 가져올 수 있도록 도와 준답니다.

출처 : https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#getactivespreadsheet

즉, 주니하랑은 스프레드 시트에 어떤 값이 일력이 되면 자동으로 트리거가 작동 되도록 만들어 준 것이에요.

 

본격적으로 Trigger 처리를 해볼게요.

트리거 추가를 눌러줄게요.

최초 Trigger가 작동하면 호출할 함수를 선택해 주어야 해요.

주니하랑은 여러 함수를 나눠 놓았고, 첫 시작 함수를 mainFunction()으로 했기 때문에 위와 같이 선택을 했어요.

그리고, 이벤트 유형은 양식 제출 시로 해야 설문지가 작성이 되면 Trigger가 작동될 것이에요.

Trigger가 실패하면 즉시 알림을 받는게 좋겠어요!

 

 

        📦 mainFunction()

본격적으로 Google App Script를 가지고 놀아 볼게요.

반응형

최초 변수들을 선언해 주었어요.

15번째 줄에 getActiveSheet()를 사용합니다.
이 친구는 스프레드 시트에서 활성 시트를 가져오는 함수로 스프레드 시트의 활성 시트는 스프레드 시트 UI에 표시되고 있는 시트를 말해요.

출처 : https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#getactivesheet

그런 뒤 16번째 줄에 getDataRange()를 사용합니다.
이 친구는 Range Data가 있는 차원에 해당 값을 반환하는데, Sheet.getLastColumn(), Sheet.getLastRow()로
지정된 Range를 만드는 것과 동일한 내용 이에요.

출처 : https://developers.google.com/apps-script/reference/spreadsheet/sheet#getDataRange()

 

17번째 줄부터 19번째 줄은 스프레드 시트에 각 값을 편하게 가져오기 위해
마지막 행, 마지막 열, 첫번째 열을 계산하여 변수에 담도록 하였어요.

주니하랑은 이를 통해 Slack에 알림을 받고 싶기 때문에 차후에 Slack Web Hook URL을 받아 입력도 해줄거에요.

 

주니하랑은 해당 Program Version을 관리하고 싶어서 Version 변수를 만들어 주었고,

Slack이나, Email 전송 중 문제가 생기면 그것 또한 Slack에 알림을 받고 싶어서 각각의 문제 내용을 담을 responseValue[] 를 선언해 주었어요.

그리고, Slcak에 보낼 첫 문장을 미리 slackMessage 변수에 담아 주었어요.


스프레드 시트는 행과 열로 이뤄져 있고, 이 안의 값을 처리하려면 반복문이 필요한 것이에요.

해당 내용을 처리할 spreadsheetLoop()를 만들어 주었고,
그 안에 activeSheet, firstCol 등등의 변수들을 전달해 주었어요.

해당 처리가 끝났을 때, 어떤 문제가 발생했으면 그것을 확인하는 Logic인 errorCheck()도 만들어 주었습니다.

 

 

 

 

        📦 spreadSheetsLoop()

728x90

앞에서 이야기 했던 반복문 처리를 위한 함수에요.

1행 내용

반복문 처리를 위해 최초 1행은 각각의 내용에 제목이 있기 때문에 2행 부터 시작하게 하기 위해 row 변수를 2로 주었어요.

그런 뒤 마지막 행보다 반복문의 행 값이 작을 때까지 반복하게 만들어 주었어요.

timeStamp라는 변수는 구글 스프레드 시트의 타임스템프 즉, 해당 설문지 작성한 일시를 처리하기 위한 변수에요.

45번재 줄 if문은 해당 타임스탬프 값이 없다면 문제가 있는 내용이기 때문에 반복문을 멈추게 해 준 장치에요.

49번째 줄에 timeStamp 변수의 값을 GMT+9 (대한민국 표준시)에 맞추어
yyyy년MM월dd일 HH(24시간제)MM분 형식으로 받기 위해 만들어 준 것이에요.

만약 시간을 12시간제로 받고 있다면 HH를 hh로 바꾸어 주면 됩니다.

이렇게 하는 이유는 timeStamp는 아래와 같이 값이 들어오기 때문이에요.

Sat Nov 05 2022 00:00:00 GMT+0900 (Korean Standard Time)Sat Dec 30 1899 04:28:32 GMT+0827 (Korean Standard Time)

만약 위와 같이 처리 하지 않으면 나중에 Slack에 아래와 같이 값이 들어 올거에요.

다음 각각의 변수들은 반복문을 돌 때, 각각의 행에서 각 열의 값을 가져와 변수로 담기 위해 만들어 준 변수들이에요.

이 중 For 문을 이용하면 변수를 줄일수는 있겠으나, 시간 복잡도가 올라가기 때문에 위와 같이 처리해 주었어요.

emailTest라는 변수는 차 후 Email 발송 Test 때, 신청 고객에게 메일이 발송되면 안되기 때문에
그런 것을 방지하기 위해 만들어 준 변수에요.

이제 반복문을 돌면서 각 행에서 가져온 값들을 Slack으로 보낼 Message 변수에 담아 줍니다.

 

94번째 줄에서 위에서 받은 Slack Message를 Slack으로 보내기 위한 함수 sendSlack을 호출하고, slack Web Hooks URL과 보낼 Message, 스프레드 시트 URL, Program Version 정보를 담은 변수를 전달해 주었어요.

그런 뒤 반환값을 slackSendReturnValue 변수에 담아 주었습니다.

이 변수는 Slack에 Message를 보내면서 문제가 생겼는지 확인을 하기 위해 선언해 준 것이에요.

95번째 변수는 신청 고객에게 Mail을 보내는 Logic에서 문제가 발생한 내용을 담아 주기 위한 변수에요.

97번째 부터 101번째까지 if문은 스프레드 시트에 Email 발송 여부 값이 undefined 이거나, 미발송 이라는 값이 있다면 Mail 발송을 하기 위해 조건문을 준 것이에요.

위와 같이 스프레드 시트에 Email 발송 여부를 처리할 수 있도록 처리해 주었어요.

만약 위의 조건문에 맞게 된다면 Mail 발송 처리를 담당하는 함수가 호출되고,
반환값을 emailSendReturnValue 변수에 담아 문제 여부를 확인 하려고 해요.

103번째 배열을 하나 만들어 주는데, 이는 Slack에서 문제 내용과 Mail 발송 시에 나온 문제들을 담아
한번에 처리하기 위해 만들어 준 것이에요.

그런 뒤 105 ~ 106번째 줄에 각각의 문제 내용을 해당 배열에 받도록 하고, 그 배열을 반환하게 해 주었어요.

 

 

        📦 Slack 구성하기

Slack의 Web Hook 설정하는 방법이에요.

최초 위와 같이 Web Hook을 연동할 채널을 우클릭 한 뒤 채널 세부 정보 보기를 선택해 줄게요.

위와 같이 통합에서 앱 추가를 눌러줄게요.

 

위와 같이 incoming WebHooks를 찾고, 설치를 눌러 줍니다.

Slack에 추가를 눌러줄게요.

채널 선택에서 알림을 받을 채널을 선택해 주세요.
그런 뒤 수신 웹후크 통합 앱 추가를 눌러줍니다.

주니하랑은 위와 같이 설정을 해주려고 해요.

Web Hook URL을 잘 정리해 두어야 해요.

설정 저장을 눌러줄게요.

💡 참고 사항
Slack Incoming Web Hooks

 

 

 

위와 같이 처리를 한 뒤 본격적으로 Slack에 Message를 담당할 함수를 분석해 볼게요.

117번째 줄에 Slack 처리 시에 문제가 있다면 해당 내용을 담을 변수를 선언해 주었어요.

그런 뒤 payload 객체 안에 text라는 Key로 Value는 slackMessage를 넣어 주었어요.

option이라는 객체 안에는 Slack에 내용을 보낼 것이기 때문에 Http Method를 POST로 지정해 주고, contentType을 json Type으로 보낼 수 있도록 처리 해 준 뒤 실제 Payload 값을 JSON으로 바꾸어 저장해 주었어요.

129번째 줄부터 133번째 줄까지 실제로 Slack에 Message를 보내는 Logic이에요.
이 곳에서 문제가 생길 수 있기 때문에 try - catch로 묶어 주었어요.

만약 문제가 발생한다면 위에서 만들었던 slackErrorCheck 변수에 어떤 문제가 생겼는지를 담도록 하였어요.

그리고, 반환값으로 해당 변수를 반환하게 해 주었답니다.

 

 

        📦 자동으로 Mail 보내도록 하기

이번엔 Gmail을 통해 Mail이 갈 수 있도록 처리해 볼게요.

먼저 Email 제목을 넣기 위해 emailSubject 변수에 내용을 넣어 주었어요.

그리고, Email 본문 값을 emailBody에 넣어 주었습니다.

161번째 줄에 변수는 Mail을 보내다가 문제가 생기면 해당 내용을 받기 위한 변수에요.

163 ~ 165번째 줄은 Mail 발송 Test 시에 변수를 확인해서Mail 발송 Test라면
주니하랑에게 Mail이 올 수 있도록 처리해 주는 것이에요.

그런 뒤 Try catch 문을 이용해서 문제에 대한 대비를 해 주고, try 문을 통해 Mail이 발송되는 Logic을 처리해 주었어요.

169번째 줄에는 해당 Mail이 보내지면 스프레드 시트 해당 행 16번째 행에
'발송'이라는 Data가 자동으로 남게 해 주었어요.
그래야 다음 실행 때도 Mail이 동일인에게 발송되는 걸 막을 수 있기 때문이에요.

만약 문제가 생기면 161번째 줄 변수에 어떤 문제가 생겼는지를 담도록 해 주고, 이를 반환해 주도록 처리해 주었어요.

 

        📦 Error Check

Slack과 Mail 발송 시에 어떤 문제가 발생하게 되면 그 문제를 다시 Slack으로 보내기 전 Logic을 처리하기 위한 함수에요.

주니하랑이 해당 Message에 남기고 싶은 내용을 먼저 변수에 담아 주고, 위에서 해당 값들을 받기 위해 선언했던 responseValue 배열에 값을 하나씩 꺼내 처리하기 위해 반복문을 돌려 주었어요.

해당 배열에 idx 값 내용이 빈 값이 아니라면 errorSlackMessage에 Error 내용을 담아주고,
해당 내용을 Slack으로 보내기 위한 함수를 호출하도록 하고 있어요.

        📦 Slack Error Value Send

문제가 생긴 내용을 Slack으로 보내게 하기 위한 Logic이에요.

203번째 줄에 다른 채널에 해당 값을 받기 위해 Web Hooks URL을 새로 받아 주었어요.

그런 뒤 위의 Slack Send Logic과 마찬가지로 값을 보내도록 처리를 하였답니다.

 

 

 

 

    🔽  Test

        📦 정상 작동 여부 확인

이제 잘 작동하는 지 한번 확인해 볼게요.

Test를 위해 최초 작동할 함수를 선택해 주고, 실행 Button을 눌러줍니다.

 

그럼 위와 같이 경고가 발생할텐데, 이는 Email Logic을 처리하기 전 계정 설정을 해주어야 하기 때문이에요.

현재 Login된 계정을 선택합니다.

....(안전하지 않음)을 눌러주세요.

 

위와 같이 나오면 허용을 눌러줍니다.

 

 

 

실행이 정상적으로 되면 위와 같이 Slack이 오는 것을 확인할 수 있어요.

Mail도 정상적으로 온 것을 확인할 수 있어요.

스프레드 시트에도 Mail이 간 뒤 발송이라는 값이 남은 걸 확인할 수 있어요.

Slack을 발송하려다가 문제가 생기는 경우를 가정하기 위해 130번째 fetch 매개변수에 URL 변수명을 조작해 보았어요.

위와 같이 Slack을 보내다가 문제가 발생했다고 Slack으로 오는 것을 확인할 수 있어요.

이번엔 Mail을 보내다가 문제가 발생한 상황을 가정해 볼게요.

최초 Slack은 정상적으로 도착을 했어요.

하지만 Mail 보낼 때 문제가 발생했다고 나오는 것을 확인할 수 있어요.

둘 다 문제가 발생하게 해버리면 이렇게 둘 다 문제가 있다고 알려주는 것도 확인을 하였답니다.

 

 

 

일잘러의 비밀 구글 스프레드시트 제대로 파헤치기:구글 스프레드시트로 엑셀 밟고 칼퇴하자

"이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다."