Sheet class의 createTextFinder()

요약
Sheet 클래스에서 createTextFinder() 사용하기

1. 요약

이 포스트에서는 createTextFinder() 메소드를 사용하여 Sheet 클래스에서 특정 텍스트를 찾는 방법을 소개합니다. Spreadsheet 클래스에서 createTextFinder()메소드를 사용하는 방법을 소개하는 이전 포스트로부터 내용이 이어집니다.

Spreadsheet class의 createTextFinder()

2. Sheet 클래스에서 createTextFinder() 사용하기

지난 포스트에서 살펴보았듯이 구글 앱스 스크립트의 ‘createTextFinder()’ 는 구글 스프레드시트에서 텍스트를 검색할 때 사용할 수 있는 메소드로, 스프레드시트 범위에서 텍스트를 찾는 기능을 수행합니다. createTextFinder()를 Sheet 클래스에서 사용하면 특정 시트를 대상으로 텍스트를 검색할 수 있습니다.

기본 사용 방법

function searchBySheet() {

const SS = SpreadsheetApp.getActiveSpreadsheet();

let sheet = SS.getSheets()[0];

let finder = sheet.createTextFinder(‘텍스트 또는 변수’);

}

이 코드는 getSheets 를 사용하여 특정 시트를 인덱스로 불러옵니다. getSheets()[인덱스] 형식으로 사용합니다. 여기서 createTextFinder 메소드는 Sheet 클래스의 메소드로 사용하고 있지요. 따라서 미리 설정한 시트 안으로 검색 범위가 한정됩니다.

시트의 이름을 직접 지정하려면 getSheetByName() 메소드를 사용할 수 있습니다. getSheetByName() 메소드 사용 방법은 다음과 같습니다.

function searchBySheet() {

const SS = SpreadsheetApp.getActiveSpreadsheet();

const sheet = SS.getSheetByName('시트 이름');

let finder = sheet.createTextFinder('텍스트 또는 변수');

}

구글에서 제공하는 공식 레퍼런스는 다음 웹사이트를 참고해주세요.

활용 예시

아래의 예시는 ‘고객 목록’, ’예약’, ’방문 내역’, ’비활동고객’ 시트로 구성되어 있습니다. ‘신규 고객 추가’ 시트에서 신규 고객의 가입일과 이름, 연락처와 지역을 설정한 뒤에 ‘입력하기’ 버튼을 누르면 ‘고객 목록’ 시트에 저장되는 로직입니다.

정보 입력 후 ‘**입력하기**’ 를 클릭하면 ‘**고객 목록**’ 시트에 데이터가 삽입됩니다.

이때, 이미 등록된 고객의 정보가 중복되어 저장되어서는 안되겠죠. 따라서 정보를 등록하기 전에 중복된 데이터를 검색 후 중복되는 연락처가 없다면 고객 목록에 정보를 등록하고, 중복되는 연락처가 있다면 중복된 값이 존재하는 셀의 위치를 알려주는 알림 창을 표시하는 기능을 넣어보겠습니다.

💡 코드를 작성할 때 주의할 점은, 고객의 연락처를 ‘고객 목록’ 이외의 시트에서는 검색해서는 안된다는 것입니다. 위 예시에서는 고객 연락처를 검색하는 범위를 ‘고객 목록’ 시트로 한정해야 정확한 데이터를 찾아야 합니다.

‘신규 고객 추가’ 시트에 있는 ‘입력하기’ 버튼에 할당된 스크립트는 아래와 같습니다. 예시에서는 고객 목록 탭을 지정하기 위해 getSheets() 메소드를 활용하였습니다.

코드

function setNewCustomerBySheet() {

const SS = SpreadsheetApp.getActiveSpreadsheet();

const ADD_CUSTOMER = SS.getSheetByName(‘신규 고객 추가’);

const SHEET = SS.getSheets()[1];

// 입력 데이터를 배열로 가져오기

let [joinDate, name, hp, location] = ADD_CUSTOMER.getRange(‘C3:C6’).getValues().map(row => row[0]);

// 중복 데이터 검사

const hpFinder = SHEET.createTextFinder(hp).matchEntireCell(true).findAll();

if (hpFinder.length > 0) {

hpFinder.forEach(occurrence => {

let sheetName = occurrence.getSheet().getName();

let row = occurrence.getRow();

Logger.log(이미 가입된 회원입니다: '${sheetName}' 시트의 ${row}행을 참조하세요.)

SpreadsheetApp.getUi().alert(‘이미 가입된 회원입니다: \\’‘ + sheetName + ‘\\‘ 시트의 ‘ + row + ‘행을 참조하세요.’);

});

} else {

// 배열로 데이터 삽입

const lastRow = SHEET.getLastRow();

SHEET.getRange(lastRow + 1, 1, 1, 4).setValues([[joinDate, name, hp, location]]);

SpreadsheetApp.getUi().alert(‘신규 고객 정보가 추가되었습니다.’);

}

코드 설명

setNewCustomerBySheet 함수를 호출하면 스크립트는 현재 활성화된 스프레드시트를 가져옵니다.
getSheetByName 메소드를 사용하여 ‘신규 고객 추가‘ 시트를 가져옵니다.
getSheets()[1]를 사용하여 스프레드시트의 두 번째 시트를 가져옵니다. ‘고객 목록’ 시트는 2번째로 생성된 시트이기 때문에 배열의 규칙에 따라서 ‘[1]’ 로 표시하여 시트를 불러왔습니다.

💡 getSheets() 메소드는 스프레드 시트에 있는 모든 시트를 배열로 반환합니다. 이 배열의 순서는 시트가 생성된 순서에 의해 정해집니다. 예를 들어, getSheets()[0]은 스프레드시트에서 첫 번째로 만들어진 시트를 의미하고, getSheets()[1]는 두 번째로 만들어진 시트를 의미합니다. 배열의 첫 번째 요소는 인덱스가 0부터 순차적으로 증가하기 때문에 두 번째로 생성된 ‘고객 목록’시트를 [1]로 표기합니다.

‘신규 고객 추가’ 시트에서 고객의 정보를 가져오기 위해 getRange('C3:C6')을 사용하여 해당 범위의 값을 가져옵니다.
고객 정보가 포함된 배열에서 필요한 정보(가입일, 이름, 전화번호, 위치)를 추출해서 변수로 할당합니다.
두 번째 시트에서 중복된 전화번호를 검사하기 위해 createTextFinder 메소드를 사용하여 중복된 전화번호를 찾습니다. 만약 중복된 연락처가 있을 경우, 해당 위치를 로그로 남기고 알림창으로 표시합니다. 이 부분에서도 forEach문을 사용하여 각 중복되는 값들의 위치 정보를 추출하는 작업을 수행합니다.
중복된 연락처가 없을 경우, 고객 정보를 ‘고객 목록’ 시트의 마지막 행에 추가합니다. getLastRow() 는 데이터가 있는 마지막 행을 가져오기 때문에 데이터가 덮어쓰여지는 것을 방지하기 위해 1을 더해주었습니다.
고객 정보가 추가되었다는 알림창을 표시합니다.

이 코드에서 유의할 점은 getSheets()[1] 를 통해서 ‘고객 목록’시트를 불러온다는 것입니다. 이렇게 검색할 시트를 한정해두면 불필요하게 다른 시트에서도 검색하는 일은 없어지게 됩니다. 스크립트를 할당하고 실행해보겠습니다. ‘입력하기’ 버튼을 우 클릭 한 뒤에 점 3개 버튼을 누르고 ‘스크립트 할당’ 버튼을 클릭합니다.

미리 작성한 스크립트의 함수 이름을 삽입 후 ‘확인’ 버튼을 클릭합니다.

스크립트 할당이 완료되었습니다. 이제 스크립트를 한번 실행해볼까요?

고객 정보가 추가되었다는 알림창이 정상적으로 출력되었습니다. ‘고객 목록’ 시트로 이동해서 데이터 삽입이 잘 되었는지 확인해보겠습니다.

‘고객 목록’에 정상적으로 저장되었네요. 이제 검색 조건인 연락처를 제외한 가입일, 이름, 지역을 바꾼 채로 다시 ‘입력하기’ 버튼을 클릭해보겠습니다. 연락처가 중복이 된다면 데이터의 삽입이 이루어져서는 안되며, 중복된 셀의 위치를 알림창을 통해 전달받아야 합니다. 테스트를 위해 다음의 정보를 입력하였습니다.

‘입력하기’ 버튼을 클릭해볼까요?

중복된 연락처를 확인하였고 중복된 셀의 위치를 전달하고 있습니다. 로그도 남아있는 것을 확인할 수 있습니다.

실무에서는 스프레드시트가 더 복잡한 경우가 많습니다. 이 때에 특정 정보를 스프레드시트 전체에서 검색을 하게 되면 의도한 것과는 다른 곳에서 정보가 검색이 될 수도 있고, 검색 시간⏰이 길어지게 되는 경우도 있습니다. 하지만 지금처럼 검색하는 범위를 한정하게 되면 정확한 정보를 빠르게 찾는데 도움이 됩니다. 👍🏻

Sheet 클래스에서 createTextFinder를 사용해보았습니다. 만약 검색의 범위를 더 줄여서, 하나의 시트가 아니라 시트 안의 특정 범위에서 텍스트를 검색해야 한다면 어떨까요?

이 경우에는 텍스트를 찾는 범위를 더 세부적으로 설정을 해야하기 때문에 Range 클래스에서 createTextFinder()를 사용하는 것이 하나의 방법이 될 수 있습니다. 다음 포스트에서는 Range 클래스에서 createTextFinder를 사용해보고, textFinder에서 제공하는 다양한 메소드의 기능을 소개하겠습니다.

Range class의 createTextFinder&TextFinder class의 메소드 소개

1. 요약