[구글 스프레드시트 기초] 구글 스프레드시트 자동화 매크로 만들기

Posted by 넉넉한

2018. 8. 25. 18:02 구글/구글 스프레드시트

[구글 스프레드시트 기초] 구글 스프레드시트 자동화 매크로 만들기

스프레드시트 문서 작성용 도구로는 대다수 MS 엑셀을 사용하고 있습니다. 본 글에서는 구글 문서 중 하나인 구글 스프레드시트를 사용하여 문서 작성 자동화 기능 중 하나인 매크로 작성에 대해서 알아보겠습니다. 매크로는 반복적인 작업을 스프레드쉬트 문서 상에서 자동으로 기록하여 나중에 반복적 작업을 매크로 실행으로 자동화 하는 기능이며 자료 정리 기능으로 많이 사용하고 있습니다.

구글 문서의 장점은 우선 무료이며 클라우드 상에서 이루어 지므로 모바일에서도 작성 가능하고 협업상 강점을 지니고 있습니다. Google 문서 오프라인 확장 프로그램을 설치하면 오프라인에서도 작성할 수 있습니다.

매크로 작성이 완료되면 저장된 매크로명의 자바스크립트 함수(function)가 생성되며 매크로를 실행할 때 본 자바스크립트 함수가 실행됩니다. MS 엑셀에서는 VBA가 작성되지요. 구글 문서는 MS 오피스 문서와 호환된다고 소개하는데 완전히 호환되지는 않습니다. 작성된 매크로 함수는 수동으로도 편집이 가능합니다.

구글 스프레드시트 문서는 sheets.google.com으로 접속하거나 또는 구글앱 메뉴에서 내드라이브 또는 문서에서 작성 수정합니다.

구글 스프레드시트 매크로 작성

매크로 기록하기

매크로 작성 예로 간단한 성적 관리 문서를 열어보겠습니다. 이 문서는 학생별 전월까지의 성적 평균과 금월 성적 금월 까지의 성적 평균을 정리합니다. 금월까지의 성적평균은 단순히 전월과 금월을 2로 나눈 것으로 이러한 문서는 없겠지요. 예시용 문서로 새로 작성하는 문서이다 보니 제목 없는 스프레드시트로 돼있네요.

구글 스프레드시트 문서

상단 문서 메뉴에서 도구>매크로 기록을 선택합니다. 매크로 기록 위의 스크립트 편집기 항목은 Google Apps Script에 대해 알게되면 작성된 매크로에 기능을 추가할 수있고 매크로 작성보다 스크립트 편집기로 복잡한 문서 자동화 기능을 만들 수있습니다.

구글 스프레드시트 매크로 기록 메뉴

문서 하단 중앙 부근에 매크로 기록 창이 나옵니다. 두개의 참조 사용 선택 항목이 나옵니다. 기록된 매크로가 실행되면

절대 참조로 기록된 매크로 : 정확한 영역에서 매크로 실행
상대 참조로 기록된 매크로 : 현재 선택되어 있는 셀 부근의 상대적 영역에서 매크로 실행

본 문서의 내용으로는 정확한 영역에서 매크로가 실행돼야 하므로 절대 참조를 선택하겠습니다.

구글 스프레드시트 메크로 기록 중

금월 평균 범위 복사 : 수정 메뉴>복사 선택 또는 마우스 우클릭하여 복사 선택
전월 평균 범위에 붙혀넣기 : 산식이므로 수정 메뉴>붙혀넣기>값만 붙혀놓기 선택 또는 마우스 우클릭하여 동 항목 선택
금월 성적 범위 설정 수정 매뉴>값 삭제로 금월 성적 값 지우기
저장을 선택하고 나오는 칭에서 매크로명을 입력하고 매크로를 저장하여 작성을 완료합니다.
비어 있는 금월 성적란에 금월 성적을 기입하고 다름 수정 시점에서 매크로를 실행하면 되겠지요.
매크로가 저장된 후 도구>스크립트 편집기 또는 도구>매크로>매크로 관리를 선택하면 저장된 매크로명의 자바스크립트 함수를 볼 수 있습니다.(아래 이미지 및 자바스크립트 함수 표시 박스 참조)
스프레드시트 문서에서 나옵니다. 문서는 자동 저장됩니다.

아래의 내용은 저장된 매크로 내용입니다.

구글 스프레드시트 메크로 자바스크립트 함수

이미지가 작을 것 같아 밑에 텍스트로 표시하겠습니다. 본 자바스크립트 함수는 수동으로 편집할 수있습니다.

/** @OnlyCurrentDoc */

function monthrecord() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('C5:C10').activate();
  spreadsheet.getRange('E5:E10').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('D5:D10').activate();
  spreadsheet.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});
};

매크로 실행하기

저장된 문서를 열고 스프레드시트 문서 메뉴에서 도구>매크로를 선택하면 우측에 기록된 매크로명이 나옵니다.

구글 스프레드시트 메크로 실행 메뉴

매크로 명을 클릭하고 매크로를 실행합니다.

최초 실행시 권한이 필요합니다.라는 인증 요구 창이 나옵니다.
이어 구글 계정 선택인증 창이 나옵니다.
다음 매크로 실행 허용여부를 묻는 창이 나옵니다. 하단의 허용을 클릭합니다.
작성된 매크로가 실행됩니다.(간혹 실행되지 않을 경우도 있습니다. 약간 기다리다가 다시 실행합니다.) 이상이 있을 경우 수졍 메뉴에서 실행 취소를 할 수있습니다.

아래 그림과 같은 매크로 실행 후 화면이 나옵니다.

구글 스프레드시트 메크로 실행