구글시트, 업무 자동화 특별 스터디 (5강) | 구글 드라이브 & G메일 자동화 실습⚡

📌 Apps Script 자동화 스터디 (5일차) - 예제 파일 및 코드

/* ------------------ 실시간 인기검색어 + 매 시간 자동 Trigger ------------------------ */

/**
 * 실시간 인기검색어 Top10 목록을 시트에 출력합니다.
 */
function printRealtimeSearch() {
 
  /* 
  구글스프레드시트 기본 변수 선언 
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var activeSheet = ss.getSheetByName('시트이름');
  */

  /* 현재 활성화된 시트 마지막 행 찾기 */
 
  /* 
  현재 시간을 지정한 날짜 서식으로 반환합니다. (2회차 강의 참고) 
	var displayTime = Utilities.formatDate(new Date, Session.getTimeZone(), "y-M-d H:m:s")
  */
 
  /* 실시간 인기 검색어 목록 받아온 후, 현재 시간과 함께 시트에 출력하기*/

}

/**
 * 시그널 홈페이지에서 실시간 인기검색어 Top 10 목록을 반환합니다.
 * 구글시트 자동화 특강 4일차 강의 참고
 * @customfunction
 */
function getRealtimeSearch() {
 
  var url = "https://api.signal.bz/news/realtime/"
  var response = UrlFetchApp.fetch(url);
  var webContent = response.getContentText();
  var jsonData = JSON.parse(webContent); //JSON일 경우 데이터 변환

  var top10 = jsonData.top10;
  var result = [];

  for (var i = 0; i <= top10.length - 1; i++) {
    result.push([top10[i].keyword]);
  }

  return result;
}

/**
 * 특정 열의 마지막 데이터가 입력된 행 번호를 반환합니다.
 */
function getEndRow(colNo) {

  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();
 
  var maxRow = activeSheet.getMaxRows();
  var maxRange = activeSheet.getRange(maxRow,colNo);
  var lastRange = maxRange.getNextDataCell(SpreadsheetApp.Direction.UP);
  var endRow = lastRange.getRow();

  return endRow;

}

/* ------------------ 이메일 보내기 자동화 실습 ------------------------ */
function sendEmail() {

  /**
   * G메일 MailApp에 대한 자세한 설명은 아래 링크를 참고하세요.
   * https://developers.google.com/apps-script/reference/mail/mail-app
   * 주요 확인사항
   * 1. 일일 발송량 : 100개 (무료) / 1,500개 (WorkSpace)
   * 2. 이메일 본문크기 : 200kb (무료) / 400kb (WorkSpace)
   * */ 

  /*
  메일을 보내기 위한 수신자 목록 확인 
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var activeSheet = ss.getSheetByName('시트이름');
  var endRow = getEndRow(1);
  */

  /* 
  ■ 이메일 발송
  옵션 1: 배열로 설정하여 보내는 방법 (다양한 설정 가능하지만 최초 입력시 번거로움)
  MailApp.sendEmail({to: "수신인", bcc: "숨은참조", body: "본문", subject: "제목" 등..})
  
  옵션 2 : 수신인, 제목, 본문 + 옵션으로 보내는 방법 (1번보다 쉽게 설정 가능, 필요시 추가 옵션 지정)
  MailApp.sendEmail(수신인,제목,본문,[옵션])
  */

  /*
  1. for문으로 각 이메일을 하나씩 돌아가며, 이메일주소/이름/등급 받아오기
  2. 받아온 값으로 메일에 들어갈 본문만들기
  3. MailApp 으로 이메일 발송하기
  4. PDF blob 데이터 생성 후, 첨부파일 추가하기
  5. 잔여 이메일 발송량 확인하기 (하루 100개 제한)
  */

}

/**
 * PDF 형식의 blob 데이터를 생성합니다.
 */
function convertToBlob(contents,filename) {
  var blob = Utilities.newBlob(contents,MimeType.HTML,filename);
  var pdf = blob.getAs(MimeType.PDF);
  return pdf;
}

✅ Apps Script 자동화 스터디 (5일차) - 강의 완성 코드

/* ------------------ 실시간 인기검색어 + 매 시간 자동 Trigger ------------------------ */
 
/**
 * 실시간 인기검색어 Top10 목록을 시트에 출력합니다.
 */
function printRealtimeSearch() {
 
  /* 
  구글스프레드시트 기본 변수 선언 
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var activeSheet = ss.getSheetByName('시트이름');
  */

  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var activeSheet = ss.getSheetByName('실시간인기검색어');
  
  /* 현재 활성화된 시트 마지막 행 찾기 */
  var endRow = getEndRow(1);
  // var endRow = activeSheet.getLastRow();

  /* 
  현재 시간을 지정한 날짜 서식으로 반환합니다. (2회차 강의 참고) 
	var displayTime = Utilities.formatDate(new Date, Session.getTimeZone(), "y-M-d H:m:s")
  */
  var displayTime = Utilities.formatDate(new Date, Session.getTimeZone(), "y-M-d H:m:s");

  /* 실시간 인기 검색어 목록 받아온 후, 현재 시간과 함께 시트에 출력하기*/
  var keywords = getRealtimeSearch();
  var count = keywords.length;
  var sequence = [[1],[2],[3],[4],[5],[6],[7],[8],[9],[10]];
  activeSheet.getRange(endRow+1,2,count).setValues(keywords);
  activeSheet.getRange(endRow+1,1,count).setValue(displayTime);
  activeSheet.getRange(endRow+1,3,count).setValues(sequence);
}
 
/**
 * 시그널 홈페이지에서 실시간 인기검색어 Top 10 목록을 반환합니다.
 * 구글시트 자동화 특강 4일차 강의 참고
 * @customfunction
 */
function getRealtimeSearch() {
 
  var url = "https://api.signal.bz/news/realtime/"
  var response = UrlFetchApp.fetch(url);
  var webContent = response.getContentText();
  var jsonData = JSON.parse(webContent); //JSON일 경우 데이터 변환
 
  var top10 = jsonData.top10;
  var result = [];
 
  for (var i = 0; i <= top10.length - 1; i++) {
    result.push([top10[i].keyword]);
  }
 
  return result;
}
 
/**
 * 특정 열의 마지막 데이터가 입력된 행 번호를 반환합니다.
 */
function getEndRow(colNo) {
 
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();
 
  var maxRow = activeSheet.getMaxRows();
  var maxRange = activeSheet.getRange(maxRow,colNo);
  var lastRange = maxRange.getNextDataCell(SpreadsheetApp.Direction.UP);
  var endRow = lastRange.getRow();
 
  return endRow;
 
}
 
/* ------------------ 이메일 보내기 자동화 실습 ------------------------ */
function sendEmail() {
 
  /**
   * G메일 MailApp에 대한 자세한 설명은 아래 링크를 참고하세요.
   * https://developers.google.com/apps-script/reference/mail/mail-app
   * 주요 확인사항
   * 1. 일일 발송량 :  100개 (무료) / 1,500개 (WorkSpace)
   * 2. 이메일 본문크기 : 200kb (무료) / 400kb (WorkSpace)
   * */ 
 
  /*
  메일을 보내기 위한 수신자 목록 확인 
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var activeSheet = ss.getSheetByName('시트이름');
  var endRow = getEndRow(1);
  */
  var app = SpreadsheetApp;
  var ss = app.getActiveSpreadsheet();
  var activeSheet = ss.getSheetByName('이메일발송');
  var endRow = getEndRow(1);

  /* 
  ■ 이메일 발송
  옵션 1: 배열로 설정하여 보내는 방법 (다양한 설정 가능하지만 최초 입력시 번거로움)
  MailApp.sendEmail({to: "수신인", bcc: "숨은참조", body: "본문", subject: "제목" 등..})
 
  옵션 2 : 수신인, 제목, 본문 + 옵션으로 보내는 방법 (1번보다 쉽게 설정 가능, 필요시 추가 옵션 지정)
  MailApp.sendEmail(수신인,제목,본문,[옵션])
  */
 
  /*
  1. for문으로 각 이메일을 하나씩 돌아가며, 이메일주소/이름/등급 받아오기
  2. 받아온 값으로 메일에 들어갈 본문만들기
  3. MailApp 으로 이메일 발송하기
  4. PDF blob 데이터 생성 후, 첨부파일 추가하기
  5. 잔여 이메일 발송량 확인하기 (하루 100개 제한)
  */

  for (i=2;i<=endRow;i++) {
    var currentEmail = activeSheet.getRange(i,1).getValue();
    var currentName = activeSheet.getRange(i,2).getValue();
    var currentGrade = activeSheet.getRange(i,3).getValue();
    var currentContents = '오빠두엑셀 성적표'+currentName+'님의 성적은 ['+currentGrade+'] 입니다.오빠두엑셀 (인)';
    //Logger.log('이메일주소:'+currentEmail+' 이름:'+currentName+' 성적:'+currentGrade)

    var attachment = convertToBlob(currentContents,'성적표');

    /* 성적표 보내기 예제 */
    MailApp.sendEmail(currentEmail,
                      currentName+'님의 성적표',
                      '안녕하세요. 오빠두엑셀입니다.\n\n'
                      +currentName+' 님의 성적은 ['+currentGrade+'] 등급입니다.\n\n'
                      +'감사합니다. 오빠두엑셀 드림',
                      {attachments: [attachment]});
    
    /*
    실시간 인기 검색어 보내기 예제
    MailApp.sendEmail(currentEmail,
                      '현재시간 인기검색어',
                      getRealtimeSearch().join());
    */
    
  }

  app.getUi().alert('발송완료!\n 잔여 발송량'+MailApp.getRemainingDailyQuota()+'개 입니다.');

}
 
/**
 * PDF 형식의 blob 데이터를 생성합니다.
 */
function convertToBlob(contents,filename) {
  var blob = Utilities.newBlob(contents,MimeType.HTML,filename);
  var pdf = blob.getAs(MimeType.PDF);
  return pdf;
}