개요
업무를 진행하면서 생각보다 구글 스프레드시트를 사용하는 일이 빈번하다.
Apps Script를 사용하면 구글 스프레드시트에서 자동화를 구축할 수 있을 뿐만 아니라 구글에서 제공하는 Drive, Docs 등에 접근하여 업무를 처리할 수 있다.
(물론 여러 권한 등의 문제로 제한이 많긴 하다,,)
필자 역시 하나씩 업무를 처리해야 하는 상황에서 효율적으로 자동화할 수 있도록 부서 내에 Apps Script를 도입했다.
Apps Script는 JavaScript와 문법이 동일하기 때문에 기존 JavaScript를 알고 있는 사람이라면 쉽게 이해하여 업무 자동화에 기여할 수 있을 것이다.
구현 모습
Apps Script로 스프레드 시트 내에서 데이터를 다뤄 원하는 값을 뽑아낼 수도 있겠지만, 이번 글에서는 다음과 같이 이메일을 전송할 예정이다.
또한, 구글 드라이브에 접근해 이미지를 추출한 후 링크에 삽입할 예정이다.
위와 같은 예제는 수료생들 마다 각기 다른 이름과 첨부파일을 다뤄야 하지만 이 부분도 Apps Script로 쉽게 해결이 가능하다.
구현 방법
Apps Script 사용 방법
우선, 구글 스프레드 시트에 접속한 이후
확장 프로그램 > Apps Script에 접속하면 아래 이미지와 같이 에디터가 나오는 것을 확인할 수 있다.
에디터엔 자동으로 myFunction() 이라는 함수가 적혀 있을 것이고 중괄호의 실행문에 원하는 코드를 작성하면 된다.
간단히 아래와 같이 console.log('Hello world') 를 작성한 후 실행 버튼을 눌러보자.
(참고로 ES6도 지원하기 때문에 화살표 함수도 가능하다.)
구글 스프레드 시트 접근
DOM API을 통해 HTML 요소에 접근하듯이 Apps Script로 구글 스프레드 시트에 접근할 수 있다.
우선 다음과 myFunction() 함수 실행문에 다음과 같이 작성하면 원하는 범위에 접근할 수 있다.
const myFunction = () => {
// 구글 스프레드시트에 접근
const ss = SpreadsheetApp.getActiveSpreadsheet();
// 해당 스프레드시트의 탭에 접근
const sheet = ss.getActiveSheet();
// 해당 탭의 A1에 접근
const data = sheet.getRange("A1").getValue();이제 A1에 임의의 데이터를 넣고 console.log(data)로 출력해보면 다음 이미지와 같이 권한에 관련된 모달창이 뜰 것이다.
그럼 계정에 로그인한 뒤,
고급 > 제목 없는 프로젝트(Apps Script 에디터 이름)로 이동 > 허용
을 누르면 권한을 부여하고 원하는 작업을 수행할 수 있다.
메일 발송
Apps Script MailApp 를 보면Apps Script를 통해 Gamil을 발송하는 예제가 나와있다.
다음은 Apps Script를 통해 Gmail을 보내는 코드 예제이다.
MailApp.sendEmail({
to: "recipient@example.com",
subject: "Logos",
htmlBody: "inline Google Logo<img src='cid:googleLogo'> images! <br>" +
"inline YouTube Logo <img src='cid:youtubeLogo'>"
});to는 메일 주소, subject는 메일 제목, htmlBody는 메일 본문이다.
이때 htmlBody는 꼭 html 마크업 언어로 작성해서 보내야 한다.
그럼 이제 다음 이미지와 같이 스프레드시트에 주소, 제목, 본문을 작성해서 메일을 보낼 수가 있다.
const myFunction = () => {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();
const subject = sheet.getRange("A1").getValue();
const main = sheet.getRange("A2").getValue();
const address = sheet.getRange("A3").getValue();
MailApp.sendEmail({
to: address,
subject: subject,
htmlBody: main,
});
}
}개개인 메일 발송
이제 반복문을 통해서 메일 발송할 주소와 이름만 수정해주면 구현 모습의 이미지에서 봤던 것처럼 개개인에게 메일을 발송할 수가 있다.
제목과 본문에서 이름 자리를 비워둔 채, 개개인의 이름을 넣어 주었다.
특히 A2인 본문의 첫번째 줄에는 block 요소인 p 태그를 없애주면서 한 줄에 표시될 수 있도록 했다.
아래 이미지와 같이 보낼 이름과 주소를 나열해 준 뒤, 다음과 같이 코드를 적을 수 있겠다.
추가로 try ~ catch 문을 작성해서 메일 전송 여부를 판단했다.
const myFunction = () => {
try {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();
const subject = sheet.getRange("A1").getValue();
const main = sheet.getRange("A2").getValue();
for (let i = 6; i <= 9; i++) {
try {
name = sheet.getRange("C"+i).getValue();
address = sheet.getRange("D"+i).getValue();
MailApp.sendEmail({
to: address,
subject: name+subject,
htmlBody: name+main,
});
sheet.getRange("E"+i).setValue("O");
} catch (err) {
sheet.getRange("E"+i).setValue("X");
}
}
console.log("완료")
} catch (err) {
console.log("실패")
}
}
실행 버튼을 눌렀을 때 Email에 접근하는 것이기 때문에 역시 권한 부여에 관해 알림창이 뜨지만 위와 같은 방법으로 권한을 부여해주면 된다.
중간중간 br 태그만 조금 빼주면 될 듯하다.
다음 글에선 App Script를 통해 구글 드라이브에 접근해서 원하는 이미지를 메일에 보내는 방법을 작성하겠다.
