구글 스프레드시트 사용자폼(UserForm)으로 데이터 입력(구글 앱스스크립트)
구글/구글 스프레드시트
반응형
엑셀의 사용자 정의 폼(userform) 데이터 입력 기능을 구글 스프레드시트에서 수행하는 방법을 알아 보겠습니다.
구글 스프레드시트에서의 사용자 정의 폼(Userform)은 구글 앱스 스크립트(Apps Script)에서 HTML 문서로 만들어 사용합니다.
구글 참조: HTML Service
앱스스크립트 가이드에서는 HTML Service를 사용히는 스크립트를 web app로 배포 독립적으로 실행하는 방법과 구글 문서(스프레드시트 포함)에 종속된(container-bound) 스크립트로 실행하는 방법이 나와 있습니다. 본글은 구글 스프레드시트에 종속된 앱스 스크립트에서 사용자 정의 폼을 만들고 스프레드시트 문서에 데이터를 추가하는 과정입니다.
목차
- 예시 시트 문서
- 사용자폼과 앱스 스크립트 작성시 사용 함수
- 사용자폼 HTML 파일 작성
- 사용자폼에 스크립트 추가
- 앱스 스크립트 함수 작성
- 스프레드시트 문서 상 추가 사항
- 데이터 입력 실행
예시 시트 문서
시트 내 데이터 추가 영역
아래는 지출내역을 건별로 작성하는 구글 시트 문서입니다.
표시된 지출 내역 데이터가 실려 있는 영역의 마지막 행에 사용자폼(UserForm)을 사용해 계속해서 지출 내역을 추가하는 과정을 이야기해 보겠습니다.
- 1번 부분은 데이터 추가 영역입니다.
- 2번 부분은 사용자폼(UserForm)통해 지출 내역을 추가하는 스크립트 작업을 호출하는 도형 버튼입니다.
- 3번 부분은 2번 실행 버튼을 클릭하여 시트 문서 사이드바에 출력된 HTML 사용자폼(UserForm)입니다.
- 문서에는 데이터가 추가되는 입력 sheet와 드롭다운 메뉴 항목이 있는 목록 sheet가 있습니다.
사용자 폼 사이드바
아래는 실행 버튼을 클릭하여 사이드바에 로드된 사용자폼(UserForm)입니다.
- 1번 부분은 일자 입력부분은 아래 이미지와 같이 날짜 형식으로 입력될 수 있도록 <input type="date"> 속성을 추가했습니다.
- 2번 부분은 목록 시트의 항목으로 드롭다운 메뉴를 삽입했습니다..
- 3번 입력버튼: 입력 데이터를 앱스 스크립트로 전달합니다.
- 3번 초기화 버튼: 입력 폼을 초기화 합니다.
- 4번 종료 버튼: 입력 작업을 종료하고 사이드바의 입력폼을 unload 합니다.
사용자폼과 앱스 스크립트 작성시 사용 함수
▶ createHtmlOutputFromFile(filename)
- 구글 앱스 스크립트에서 구글 문서 상에 UI용 HTML 문서를 출력하는 함수로서 HtmlOutput 객체를 반환합니다.
HtmlOutput 참조
▶ createHtmlOutputFromFile(filename)
- 구글 앱스 스크립트에서 UI용 HTML 폼을 출력하는 HTML 서비스 함수입니다.
- 앱스 스크립트에서 사용하는 HtmlOutput 객체를 반환합니다.
var output = HtmlService.createHtmlOutputFromFile('myPage');
변수 output에는 HtmlOutput 객체가 반환 됩니다.- 함수 참조
▶ google script run
- HTML 문서로 작성된 사용자 폼에서 앱스 스크립트 함수를 호출하는 API 함수이며
- 앱스 스크립트와 사용자폼간의 상호 데이터 전달을 수행합니다.
- 구글 참조
위의 참조 글에는 매개변수 및 반환 값의 제한 사항이 있는데 몇 가지 데이터 유형은 전달될 수 없다고 나옵니다.(date 객체 포함)
withSuccessHandler(필요시)google.script.run에서 사용하는 콜백 함수로서 호출 함수 성공 시 앱스 스크립트에서 리턴 받은 데이터를 처리할 수 있습니다.withFailureHandler(필요시)google.script.run에서 사용하는 콜백 함수로서 호출 함수 실패 시 에러 코드를 반환 받을 수 있습니다.
▶ google.script.host(필요시)
- 본 글에서는 시트 문서의 사이드바의 HTML 폼을 닫는 기능을 수행합니다.
사용자폼(userform) HTML 파일 작성
- 상단 메뉴>확장프로그램>앱스스크립트(Apps Script)를 선택하여 Apps Script편집기를 엽니다.
- 좌측 탐색창 상단의 파일 탭 +버튼(파일 추가) 클릭 HTML을 선택합니다.
- 좌측 제목 없음 박스에 확장자 없이 파일명 입력(여기서는 Index)
- 코드 창의 기본 내용은 삭제하고 아래 내용에서 예시된 HTML 코드를 삽입합니다.
예시 사용자폼 HTML 문서
아래의 내용은 본 문서 상단에 예시한 사용자폼 HTML 문서입니다. 추후 스크립트를 추가합니다.
<!doctype html>
<html lang="ko">
<head>
<base target="_top">
<!--<meta charset="utf-8">-->
<title>지출 기록</title>
</head>
<body onload="pageLoaded()">
<form id="wform">
<label for="">일 자 : </label>
<input type="date" id="fdate">
<br>
<br>
<label for="fdesc">내 용 : </label>
<input type="text" id="fdesc">
<br>
<br>
<label for="famt">금 액 : </label>
<input type="text" id="famt">
<br><br>
<label for="fcat">구 분 : </label>
<select id="fcat">
</select>
<br><br>
<button type="button" id="addB" onclick="addData()">입력</button>
<button type="reset" form="wform">초기화</button>
<button type="button" onclick="google.script.host.close()" id="closeB">종료</button>
</form>
<script>
//스크립트 영역
</script>
</body>
</html>
- 입력 항목은 FORM 요소의 기능을 사용할 목적으로 FORM 요소 안에 넣었습니다.
- 11행: 일자 입력 필드에는 type="date" 속성을 추가, 날짜 입력 박스 또는 날짜 선택기(date picker)로 입력합니다. <input type="date">
- yyyy-mm-dd 형태로 입력 박스에 입력 또는 우측 date picker 선택 버튼을 클릭하면 달력 형식의 날짜 선택 창이 나옵니다.
- 22행: 상품 항목은 드롭다운 메뉴로 작성했으며 드롭다운 메뉴 항목은 앱스 스크립트에서 가져 오겠습니다.
- 8행: 사용자폼 로드 이벤트 시(
onload="pageLoaded()") 앱스 스크립트 함수를 호출 드롭다운 메뉴 항목을 가져 옵니다. - 25행: 입력 버튼 요소에
type="button"속성을 추가해 FORM 요소의 submmit 기능을 방지합니다. - 26행: 초기화 버튼의 type="reset"으로 입력 실행 후 추가 입력을 위해 입력 항목을 초기화 합니다.
- 27행: 종료 버튼은 사용자폼을 unload 합니다. 여기서는 onclick 이벤트에
google.script.host.close()함수를 사용했습니다. (사용자폼 상단 창닫기(x) 버튼과 같은 기능)
사용자폼에 스크립트 추가
드롭다운 항목 요청
function pageLoaded(){
google.script.run.withSuccessHandler(makeDlist).withFailureHandler(onFailure).dList();
}
- 사용자폼 로드 이벤트 시 앱스 스크립트 함수(dList) 호출 드롭다운 항목을 요청합니다.
- 앱스 스크립트 함수(dList)가 정상 수행되면 withSuccessHandler(makeDlist)로 전달 받은 변수로 콜백 함수 makeDlist(arrDlist)가 실행됩니다.
드롭다운 항목 추가
function makeDlist(arrDlist){
var wcat = document.getElementById("fcat");
//드롭다운 리스트 요소 추가
arrDlist.forEach(function(v){
var option = document.createElement("option");
option.text=v;
wcat.appendChild(option);
});
}
- 전달 받은 항목(arrDlist)으로 <select> 태그 안에 전달 받은 항목에 상응하는 <option> 태그를 추가 사용자폼에 드롭다운 메뉴를 생성합니다.
사용자폼에 입력된 데이터 앱스 스크립트에 전달
function addData(){
//간단한 입력 데이터 검증
if (isNaN(document.forms["wform"][2].value) || document.forms["wform"][2].value == "" ){
alert("금액 숫자 요");
return;
}
var wdate = document.getElementById("fdate");
var wdesc = document.getElementById("fdesc");
var wamt = document.getElementById("famt");
var wcat = document.getElementById("fcat");
//입력된 데이터를 1차원 배열로 전달
var rowData = [wdate.value, wdesc.value, wamt.value, wcat.value];
google.script.run.withSuccessHandler(onSuccess).withFailureHandler(onFailure).addNewRow(rowData);
}
- 사용자폼의 입력 버튼을 클릭하면 실행되는 함수입니다.
- 2~6행은 간단한 입력 데이터 검증 코드입니다.
- 7~10행은 입력 받은 데이터를 변수에 저장합니다.
- 자바스크립트의 date 객체는 위에서 언급한데로 앱스스크립트 함수에 변수로 전달될 수 없습니다.
<input type="date">인 '일 자' 필드에서 선택 또는 입력된 날짜 데이터는 타입이 string이며 입력된 값을 그대로 전달했습니다. - 12~13행:
데이터 변수를 1차원 배열로 묶어 시트 문서에 데이터 행을 추가하는 앱스 스크립트 함수 addNewRow(rowData)를 호출합니다.(google.script.run) - withSuccessHandler(onSuccess)로 addNewRow(rowData) 함수가 성공하면 성공 메시지를 출력하는 콜백 함수 onSuccess가 실행됩니다.
- 콜백 함수 withFailureHandler(onFailure)로 addNewRow(rowData) 함수 실패시 오류 메시지를 받을 수 있습니다.(필요시)
앱스 스크립트 함수 작성
사용자폼 로드
function ufLoad() {
const htmlOutput = HtmlService.createHtmlOutputFromFile('Index')
.setTitle('입력');
const ui = SpreadsheetApp.getUi();
ui.showSidebar(htmlOutput);
}
- 구글 예제에서는 사용자 HTML 폼을 출력할 때
- 사이드바 창
- 다이알로그 대화 창
- 위 두 가지 형식으로 설명하는 데 본 글에서는 사이드바에 출력합니다.
드롭다운 힝목 전달
function dList(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName("목록");
//A열 마지막 행까지의 값 반환
return sh.getRange(1, 1, sh.getLastRow()).getValues();
}
- 사용자폼 함수
pageLoaded()에서 호출한 함수입니다. - 5행: 목록 시트의 A열 목록 데이터를 1행부터 마지막행까지를 반환합니다.
입력 데이터 스프레드시트 추가
function addNewRow(rowData){
var wdate = rowData[0];
//입력 날짜가 공란이면 오늘 날짜로
if (wdate == '') {
rowData[0] = Utilities.formatDate(new Date(), "GMT+9", "yyyy-MM-dd");
} else {
rowData[0] = wdate;
}
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh = ss.getSheetByName("입력");
//등록 시트 데이터 추가 영역 마지막 행
lrow=sh.getRange("b4").getDataRegion().getLastRow();
//setValues 함수의 변수는 2차원 배열
sh.getRange(lrow+1,2,1,4).setValues([rowData]);
sh.setCurrentCell(sh.getRange(lrow+1,2));
}
- 사용자폼
addData()함수에서 호출했습니다. - 2~8행:
변수인 배열 rowData의 첫 번째 항목인 날짜를 값이 공란이면 오늘 날짜를 아니면 입력된 날짜로 날짜 데이터 객체로 변경합니다. - 12행: 입력 데이터가 추가될 b4 셀에 이은 입력 추가 양식 데이터 영역의 마지막 행을 구합니다.
- 14행: 입력값 rowData는 1차원 배열이며
setValues함수는 2차원 배열을 요구합니다. 대괄호로 묶어 [rowData]로 2차원 배열로 만들어 양식 영역 마지막행에 입력된 데이터를 추가합니다. - 15행: 추가된 행의 2번째 칼럼(추가된 데이터의 첫범째 칼럼)을 CurrentCell로 지정합니다.
스프레드시트 문서 상 추가 사항
앱스 스크립트 사용자폼 로드 함수 실행 도형 버튼 삽입
그림 메뉴탭>삽입>그림을 선택하면 아래와 같은 캔버스 창이 나옵니다.
- 상단 리본 메뉴에서 도형을 선택 원하는 도형을 선택하고(여기에서는 입체 테두리 선택)
- 아래 캔버스로 드래그하여 도형을 생성하고 텍스트를 넣고 우측 상단 저장 후 닫기를 클릭하면 시트상 선택돼 있는 셀 부근에 도형이 삽입됩니다.
- 마우스를 (우)클릭하면 편집 상태가 되고 크기 조절 위치 이동이 가능합니다.
스크립트 할당
- 편집 상태에서 우측 세로 점 세개를 클릭합니다.
- 스크립트 할당을 선택합니다.
- 스크립트 할당 창이 나옵니다.
- 사용자폼을 로드하는 앱스 스크립트 함수 ufLoad를 기입하고 확인을 클릭합니다.
인증
시트에 추가한 스크립트 실행 도형 버튼을 클릭하면 스크립트 인증 과정이 진행됩니다.
인증 과정이 끝나면 실행 버튼을 다시 클릭해야 스크립트가 실행됩니다.
데이터 입력 실행
시트 문서의 실행 버튼을 클릭하면 지출 내역 입력 작업이 수행됩니다.
매개변수 및 반환 값의 제한 사항
매개변수 및 반환 값은 Number, Boolean, String, null와 같은 자바스크립트 기본 요소뿐만 아니라 기본 요소, 객체, 배열로 구성된 자바스크립트 객체 및 배열입니다. 페이지 내의 form 요소도 매개변수로 사용할 수 있지만, 함수의 유일한 매개변수여야 하고 반환 값으로 사용할 수 없습니다. form나 Date 또는 Function와 같이 금지된 유형(객체 또는 배열 내부에 금지된 유형 포함)과 함께 금지된 요소를 전달하려고 하면 요청이 실패합니다. 순환 참조를 만드는 객체도 실패하고 배열 내의 정의되지 않은 필드는 null가 됩니다.
구글 참조 발췌
반응형