Spreadsheet class의 createTextFinder()
1. 요약
이 포스트에서는 createTextFinder() 메소드를 사용하여 Spreadsheet 클래스에서 특정 텍스트를 찾는 방법을 소개합니다. 이 포스트는 Sheet, Range 클래스에서 createTextFinder() 메소드를 사용하는 방법을 소개하는 포스트로 내용이 이어집니다.
Sheet class의 createTextFinder()
Range class의 createTextFinder&TextFinder class의 메소드 소개
2. createTextFinder()란?
구글 시트에서 앱스 스크립트로 작업을 하다보면 특정 텍스트를 검색해야 할 때가 많습니다. 예를 들어, 다음의 이미지처럼 고객 정보를 관리하는 구글 시트에 앱스 스크립트로 신규 고객을 추가하는 기능을 사용한다고 가정해볼까요? 고객 정보를 입력한 뒤에 ‘입력하기’ 버튼을 누르면, 그 다음 이미지처럼 고객들의 목록으로 저장됩니다.
고객 정보 관리에 소홀해지면 중복되는 데이터의 수가 많아질 수 있습니다. 이 때, 앱스 스크립트를 이용하면 새로운 고객 정보가 입력될 때마다 해당 고객이 이미 등록된 고객인지 확인하고 알림을 표시할 수 있습니다.
구글 앱스 스크립트의 ‘createTextFinder()’ 는 구글 스프레드시트에서 텍스트를 검색할 때 사용할 수 있는 메소드입니다. 특정 스프레드시트 범위에서 특정 텍스트를 찾고, 해당 텍스트가 포함된 모든 셀이나 문서 요소를 반환합니다. 메소드의 괄호 안에는 텍스트를 넣어도 되고, 변수를 넣어도 됩니다.
3. Spreadsheet 클래스에서 createTextFinder() 사용하기
Spreadsheet 클래스의 createTextFinder()는 구글 시트 전체를 대상으로 텍스트를 검색합니다.
실무에서는 훨씬 더 다양하고 복잡한 정보들을 다루겠지만, 이번 포스트는 앱스 스크립트의 메소드를 소개하기 위한 목적이기 때문에, 앞에서 보았던 간단한 이미지 속 예시를 사용하겠습니다. 고객의 고유한 정보인 연락처가 중복되는지 확인하여 연락처가 존재하지 않는다면 고객 목록에 저장하고, 연락처가 이미 존재한다면 고객 목록에 저장하지 않고 알림 창을 띄워보겠습니다.
기본 사용 방법
기본적인 사용 방법은 다음과 같습니다. createTextFinder 메소드는 특정 문자열 또는 정규식을 사용하여 스프레드시트의 셀을 검색하는 데 사용됩니다. 변수를 넣어서 검색할 수도 있습니다. 이 메소드는 ‘TextFinder’ 객체를 반환합니다.
|
1 2 3 4 |
function searchBySpreadsheet() { const SS = SpreadsheetApp.getActiveSpreadsheet(); let finder = SS.createTextFinder('텍스트 또는 변수'); } |
구글에서 제공하는 공식 레퍼런스는 다음 웹사이트를 참고해주세요.
활용 예시
다음의 스프레드시트를 함께 보시죠. 신규 고객의 정보를 입력하는 화면입니다. 신규 고객의 가입일과 이름, 연락처와 지역을 설정한 뒤에 ‘입력하기’ 버튼을 누르면, 입력한 지역에 해당하는 시트에 고객 정보가 기입됩니다. 고객 정보가 지역 분류에 따라 여러 개의 시트에 저장되는 것입니다.
‘서울’ 지역의 고객을 입력 후 ‘입력하기’ 버튼을 클릭하면 아래처럼 ‘서울’ 시트에 회원이 추가됩니다.
‘신규 고객 추가’ 시트에 있는 ‘입력하기’ 버튼에 할당된 스크립트는 아래와 같습니다.
코드
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
function setNewCustomerBySpreadsheet() { const SS = SpreadsheetApp.getActiveSpreadsheet(); const ADD_CUSTOMER = SS.getSheetByName(‘신규 고객 추가’); // 입력 데이터를 배열로 가져오기
let [joinDate, name, hp, location] = ADD_CUSTOMER.getRange(‘C3:C6’).getValues().map(row => row[0]); // 중복 데이터 찾기 const hpFinder = SS.createTextFinder(hp).matchEntireCell(true).findAll(); // 중복 데이터가 있는지 확인 if (hpFinder.length > 1) { hpFinder.forEach(occurrence => { let sheetName = occurrence.getSheet().getName(); let row = occurrence.getRow(); if (sheetName !== ADD_CUSTOMER.getName()) { Logger.log( SpreadsheetApp.getUi().alert( } }); } else { // 중복 데이터가 없으면 해당 지역의 시트에 고객 정보 입력 let targetSheet = SS.getSheetByName(location); const lastRow = targetSheet.getLastRow(); targetSheet.getRange(lastRow + 1, 1, 1, 4).setValues([[joinDate, name, hp, location]]); SpreadsheetApp.getUi().alert(‘신규 고객 정보가 추가되었습니다.’); } } |
코드 설명
이 코드는 사용자가 입력한 지역을 확인하고, 해당되는 시트에 새로운 고객 데이터를 추가하는 작업을 수행합니다.
SpreadsheetApp.getActiveSpreadsheet()를 사용하여 현재 활성화된 스프레드시트를 가져옵니다.getSheetByName메소드를 사용하여 ‘신규 고객 추가‘ 시트를 가져옵니다.- ‘신규 고객 추가’ 시트에서 고객의 정보를 가져오기 위해
getRange('C3:C6')을 사용하여 해당 범위의 값을 가져옵니다. - 고객 정보가 포함된 배열에서 필요한 정보(가입일, 이름, 전화번호, 위치)를 추출해서 변수로 할당합니다.
createTextFinder()메소드를 사용하여 중복된 연락처를 찾습니다. SS뒤에 메소드를 이어적어서 Spreadsheet 클래스에서 사용합니다. 이 때,matchEntireCell(true)를 사용하여 연락처가 셀 전체와 정확히 일치하는지 확인합니다.- 중복된 연락처가 있는지 확인합니다. 만약 중복된 연락처가 있을 경우, 로그를 남기고, 알림창으로 해당 위치를 표시합니다.
💡 여기서 forEach 문은 createTextFinder() 메소드로 얻은 결과를 순회하면서 정보를 확인하는 역할을 합니다. hpFinder를 그대로 Logger.log에 전달하면 단순히 객체의 식별자와 형식만을 표시하기 때문에 forEach문을 사용하여 각 중복되는 값들의 위치 정보를 추출하는 작업을 추가하였습니다.
- 중복된 연락처가 없을 경우, 고객 정보를 해당 지역 시트의 마지막 행에 추가합니다.
getLastRow()는 데이터가 있는 마지막 행을 가져옵니다. 마지막 행의 다음 행에 데이터를 추가하기 위해 1을 더해주었습니다. - 고객 정보가 추가되었다는 알림창을 표시합니다.
위 코드에서 아래의 조건문에 유의해보겠습니다.
|
1 2 3 4 5 6 7 8 9 10 |
if (hpFinder.length > 1) { hpFinder.forEach(occurrence => { let sheetName = occurrence.getSheet().getName(); let row = occurrence.getRow(); if (sheetName !== ADD_CUSTOMER.getName()) { Logger.log( SpreadsheetApp.getUi().alert( } }); } |
데이터의 갯수가 1개를 초과할 경우에 이미 가입된 회원이라는 알림창을 띄우라는 조건문입니다. 왜 0개가 아닌 1개를 초과할 경우에 알림창을 띄우도록 설정했을까요? Spreadsheet 클래스에서 createTextFinder를 사용하면 전체 시트에서 검색을 하기 때문입니다. 아래의 ‘신규 고객 추가’ 입력창에 입력한 값도 이미 중복된 값으로 처리하는 것이지요. 그래서 ‘sheetName’ 이 ‘신규 고객 추가’ 시트인 경우에는 alert를 노출시키지 않도록 처리하고 있습니다.
이제 ‘입력하기’ 버튼에 스크립트를 할당해보겠습니다. ‘입력하기’버튼의 우 클릭을 하면 상단에 점 3개 버튼이 노출됩니다. 해당 버튼을 누르고 ‘스크립트 할당’ 버튼을 클릭합니다.
다음 창에서 함수의 이름을 입력 후 확인 버튼을 클릭하면 ‘입력하기’ 버튼에 스크립트 할당이 완료됩니다.
스크립트 할당을 완료했으면 한번 테스트해보겠습니다. 위의 조건문을 통해서 중복되는 데이터가 없다면 신규 고객 정보를 삽입하고, 중복되는 데이터가 있다면 추가하지 않고 알림창을 띄울 것입니다. 실행해볼까요?
스크립트가 정상적으로 실행되었습니다. 데이터가 제대로 저장되었는지도 확인해보겠습니다.
‘서울’ 시트에 정확히 데이터가 삽입되었습니다. 위에서 작성한 코드는, 신규 고객을 등록할 때에 중복되는 연락처가 있는지 지역을 구분하지 않고 전체 시트에서 검색하도록 작성되었습니다. 다음 사진처럼 동일한 데이터를 지역만 ‘경기도’로 바꾸어 ‘입력하기’버튼을 누르면 어떻게 될까요?
의도대로 작동된다면 중복데이터로 처리가 되어 데이터 저장이 되지 않고 알림창이 띄워질 것입니다.
로그에도 동일한 내용이 기록되었습니다. 입력하고자 하는 시트는 ‘경기도’ 시트였지만 ‘서울’ 시트에서 중복 데이터가 있다는 알림을 띄우고, 데이터는 실제로 입력되지는 않았습니다. 의도한 기능을 잘 수행하고 있네요.
이제, createTextFinder를 Sheet 클래스에서 사용하는 방법을 다음의 포스팅으로 이어서 살펴보겠습니다.
