1. 소개
Google 스프레드시트 Codelab 기반 앱 스크립트 기초 과정의 세 번째 부분입니다.
이 Codelab을 완료하면 Apps Script에서 데이터 조작, 맞춤 메뉴, 공개 API 데이터 검색을 사용하여 Sheets 환경을 개선하는 방법을 배울 수 있습니다. 이 재생목록의 이전 Codelab에서 소개한 SpreadsheetApp, Spreadsheet, Sheet, Range 클래스와 함께 계속 작업합니다.
학습할 내용
- Drive의 개인 또는 공유 스프레드시트에서 데이터를 가져오는 방법
onOpen()함수를 사용하여 맞춤 메뉴를 만드는 방법- Google 시트 셀에서 문자열 데이터 값을 파싱하고 조작하는 방법
- 공개 API 소스에서 JSON 객체 데이터를 가져와서 조작하는 방법
시작하기 전에
Google Sheets 기초가 된 Apps Fundamentals 기초 과정의 세 번째 Codelab입니다. 이 Codelab을 시작하기 전에 이전 Codelab을 완료해야 합니다.
필요한 항목
- 이 재생목록의 이전 Codelab에서 살펴본 기본 Apps Script 주제에 대한 이해입니다.
- Apps Script 편집기에 관한 기본 지식
- Google Sheets에 관한 기본 지식
- 스프레드시트 A1 표기법 읽기 기능
- 자바스크립트 및
String클래스 관련 기본 지식
2. 설정
이 Codelab의 연습에는 작업할 스프레드시트가 필요합니다. 실습에 사용할 스프레드시트를 만들려면 다음 단계를 따르세요.
- Google Drive에서 스프레드시트를 만듭니다. Drive 인터페이스에서 New > Google Sheets를 선택하면 됩니다. 이렇게 하면 새 스프레드시트가 만들어지고 열립니다. 파일은 Drive 폴더에 저장됩니다.
- 스프레드시트 제목을 클릭하여 '제목 없는 스프레드시트'에서 '데이터 조작 및 맞춤 메뉴'로 변경합니다. 시트는 다음과 같이 표시됩니다.
- 스크립트 에디터를 열려면 Extensions> Apps Script를 클릭합니다.
- Apps Script 프로젝트 제목을 클릭하고 "제목 없는 프로젝트"에서 데이터 조작 및 맞춤 메뉴로 변경합니다. 이름 바꾸기를 클릭하여 제목 변경사항을 저장합니다.
빈 스프레드시트와 프로젝트를 사용하면 실습을 시작할 준비가 된 것입니다. 다음 섹션으로 이동하여 맞춤 메뉴에 관해 알아보세요.
3. 개요: 맞춤 메뉴 항목으로 데이터 가져오기
Apps Script를 사용하면 Google Sheets에 표시될 수 있는 맞춤 메뉴를 정의할 수 있습니다. Google 문서, Google 프레젠테이션, Google 설문지의 맞춤 메뉴를 사용할 수도 있습니다. 맞춤 메뉴 항목을 정의하면 텍스트 라벨을 만들어 스크립트 프로젝트의 Apps Script 함수에 연결합니다. 그런 다음 메뉴를 Google Sheets에 추가하여 UI에 추가할 수 있습니다.
사용자가 맞춤 메뉴 항목을 클릭하면 연결된 Apps Script 함수가 실행됩니다. 이는 스크립트 편집기를 열지 않고도 Apps Script 함수를 빠르게 실행할 수 있는 방법입니다. 또한 스프레드시트의 다른 사용자는 코드나 Apps Script의 작동 방식에 대해 몰라도 코드를 실행할 수 있습니다. 자녀는 또 다른 메뉴 항목을 선택할 수 있습니다.
맞춤 메뉴 항목은 다음 섹션에서 자세히 알아볼 onOpen() 간단한 트리거 함수에 정의되어 있습니다.
4. onOpen() 함수
Apps Script의 간단한 트리거는 특정 조건이나 이벤트에 대한 응답으로 특정 Apps Script 코드를 실행하는 방법을 제공합니다. 트리거를 만들 때 트리거가 실행되는 이벤트를 정의하고 해당 이벤트에 대해 실행되는 Apps Script 함수를 제공합니다.
onOpen()는 간단한 트리거의 예입니다. 설정이 간단합니다. onOpen()라는 Apps Script 함수를 작성하면 Apps Script에서 연결된 스프레드시트를 열거나 새로고침할 때마다 스크립트를 실행하면 됩니다.
/**
* A special function that runs when the spreadsheet is first
* opened or reloaded. onOpen() is used to add custom menu
* items to the spreadsheet.
*/
function onOpen() {
/* ... */
}
구현
맞춤 메뉴를 만들어 보겠습니다.
- 스크립트 프로젝트의 코드를 다음으로 바꿉니다.
/**
* A special function that runs when the spreadsheet is first
* opened or reloaded. onOpen() is used to add custom menu
* items to the spreadsheet.
*/
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Book-list')
.addItem('Load Book-list', 'loadBookList')
.addToUi();
}
- 스크립트 프로젝트를 저장합니다.
코드 검토
이 코드를 검토하여 작동 원리를 알아보겠습니다. onOpen()의 첫 번째 줄은 getUi() 메서드를 사용하여 이 스크립트가 바인딩된 활성 스프레드시트의 사용자 인터페이스를 나타내는 Ui 객체를 가져옵니다.
다음 세 줄은 메뉴 (Book-list)를 만들고, 이 메뉴에 메뉴 항목 (Load Book-list)을 추가한 다음, 스프레드시트의 인터페이스에 이 메뉴를 추가합니다. 이 작업은 createMenu(caption), addItem(caption, functionName), addToUi() 메서드에서 각각 진행합니다.
addItem(caption, functionName) 메서드는 메뉴 항목 라벨과 메뉴 항목을 선택할 때 실행되는 Apps Script 함수 간에 연결을 만듭니다. 이 경우 Load Book-list 메뉴 항목을 선택하면 Sheets에서 loadBookList() 함수를 실행하려고 합니다 (아직 존재하지 않음).
결과
지금 이 함수를 실행하여 작동하는지 확인합니다.
- Google Sheets에서 스프레드시트를 새로고침합니다. 참고: 일반적으로 스크립트 편집기의 탭이 닫힙니다.
- Tools > Script Editor를 선택하여 스크립트 편집기를 다시 엽니다.
스프레드시트가 새로고침되면 새 Book-list 메뉴가 메뉴 바에 표시됩니다.
도서 목록을 클릭하면 표시되는 메뉴를 볼 수 있습니다.
다음 섹션에서는 loadBookList() 함수의 코드를 만들고 Apps Script의 데이터와 상호작용할 수 있는 한 가지 방법, 즉 다른 스프레드시트를 읽습니다.
5. 스프레드시트 데이터 가져오기
이제 맞춤 메뉴를 만들었으므로 메뉴 항목을 클릭하여 실행할 수 있는 함수를 만들 수 있습니다.
현재 맞춤 메뉴 Book-list에 메뉴 항목이 하나 있습니다. Load Book-list. Load Book-list 항목을 선택하면 호출되는 함수가 loadBookList(),에 스크립트에 없습니다. 따라서 도서 목록 > 도서 목록 로드를 선택하면 오류가 발생합니다.
loadBookList() 함수를 구현하여 이 오류를 해결할 수 있습니다.
구현
새 메뉴 항목이 작업할 데이터로 스프레드시트를 채우고자 하므로 loadBookList()를 구현하여 다른 스프레드시트에서 책 데이터를 읽고 이 시트에 복사합니다.
onOpen()의 스크립트에 다음 코드를 추가합니다.
/**
* Creates a template book list based on the
* provided 'codelab-book-list' sheet.
*/
function loadBookList(){
// Gets the active sheet.
var sheet = SpreadsheetApp.getActiveSheet();
// Gets a different spreadsheet from Drive using
// the spreadsheet's ID.
var bookSS = SpreadsheetApp.openById(
"1c0GvbVUDeBmhTpq_A3vJh2xsebtLuwGwpBYqcOBqGvo"
);
// Gets the sheet, data range, and values of the
// spreadsheet stored in bookSS.
var bookSheet = bookSS.getSheetByName("codelab-book-list");
var bookRange = bookSheet.getDataRange();
var bookListValues = bookRange.getValues();
// Add those values to the active sheet in the current
// spreadsheet. This overwrites any values already there.
sheet.getRange(1, 1, bookRange.getHeight(), bookRange.getWidth())
.setValues(bookListValues);
// Rename the destination sheet and resize the data
// columns for easier reading.
sheet.setName("Book-list");
sheet.autoResizeColumns(1, 3);
}
- 스크립트 프로젝트를 저장합니다.
코드 검토
이 함수는 어떻게 작동하나요? loadBookList() 함수는 주로 이전 Codelab에서 도입된 Spreadsheet, Sheet, Range 클래스의 메서드를 사용합니다. 이러한 개념을 고려하여 loadBookList() 코드를 다음 4가지 섹션으로 나눌 수 있습니다.
1: 대상 시트 식별하기
첫 번째 줄은 SpreadsheetApp.getActiveSheet()를 사용하여 현재 시트 객체의 참조를 가져와 sheet 변수에 저장합니다. 데이터가 복사될 시트입니다.
2: 소스 데이터 식별
다음 몇 줄은 검색 중인 소스 데이터를 참조하는 4가지 변수를 설정합니다.
bookSS는 코드가 데이터를 읽는 스프레드시트에 대한 참조를 저장합니다. 이 코드는 스프레드시트 ID를 기준으로 스프레드시트를 찾습니다. 이 예시에서는 읽을 소스 스프레드시트의 ID를 제공하고SpreadsheetApp.openById(id)메서드를 사용하여 스프레드시트를 엽니다.bookSheet는bookSS내에서 원하는 데이터를 포함하는 시트 참조를 저장합니다. 이 코드는codelab-book-list이름으로 읽을 시트를 식별합니다.bookRange는 다양한 범위의 데이터 참조를bookSheet에 저장합니다.Sheet.getDataRange()메서드는 시트에서 비어 있지 않은 모든 셀을 포함하는 범위를 반환합니다. 따라서 빈 행과 열을 포함하지 않고 시트의 모든 데이터를 커버할 수 있습니다.bookListValues은bookRange의 셀에서 가져온 모든 값을 포함하는 2D 배열입니다.Range.getValues()메서드는 소스 시트에서 데이터를 읽어 이 배열을 생성합니다.
3: 소스에서 대상으로 데이터 복사
다음 코드 섹션에서는 bookListValues 데이터를 sheet에 복사한 다음 시트의 이름도 바꿉니다.
Sheet.getRange(row, column, numRows, numColumns)는sheet에서 데이터를 복사할 위치를 식별하는 데 사용됩니다.Range.getHeight()및Range.getWidth()메서드는 데이터 크기를 측정하고 동일한 크기의 대상 범위를 정의하는 데 사용됩니다.Range.setValues(values)는bookListValues의 2D 배열을 대상 범위로 복사하여 이미 있는 데이터를 덮어씁니다.
4: 대상 시트 서식 지정하기
Sheet.setName(name)은 대상 시트 이름을 Book-list로 변경하는 데 사용됩니다. 함수의 마지막 줄은 Sheet.autoResizeColumns(startColumn, numColumns)를 사용하여 대상 시트의 처음 세 열 크기를 조절하므로 새 데이터를 더 쉽게 읽을 수 있습니다.
결과
이 함수가 작동하는 모습을 볼 수 있습니다. Google 스프레드시트에서 Book-list > Load Book-list를 선택하여 스프레드시트를 채우는 함수를 실행합니다.
이제 도서 제목, 저자 목록, 13자리 ISBN 번호가 포함된 시트가 생겼습니다. 다음 섹션에서는 문자열 조작과 맞춤 메뉴를 사용하여 이 도서 목록의 데이터를 수정하고 업데이트하는 방법을 알아봅니다.
6. 개요: 스프레드시트 데이터 정리하기
이제 시트에 책 정보가 표시됩니다. 각 행은 특정 도서를 참조하며 개별 열에 제목, 저자, ISBN 번호를 나열합니다. 그러나 이 원시 데이터에서 몇 가지 문제를 확인할 수도 있습니다.
- 일부 행의 경우 제목 및 저자가 제목 열에 함께 쉼표나 " 및 " 문자열로 연결됩니다.
- 일부 행에 책의 제목이나 저자가 누락되어 있습니다.
다음 섹션에서는 데이터를 정리하여 이러한 문제를 해결해 보겠습니다. 첫 번째 문제에서는 제목 열을 읽고 쉼표 또는 '구분자' 구분자가 발견될 때마다 텍스트를 분할하는 함수를 만들어 해당하는 작성자와 제목 하위 문자열을 올바른 열에 배치합니다. 두 번째 문제에서는 외부 API를 사용하여 누락된 책 정보를 자동으로 검색하고 이 정보를 스프레드시트에 추가하는 코드를 작성합니다.
7. 메뉴 항목 추가
구현하려는 데이터 정리 작업을 제어할 메뉴 항목 3개를 만들고자 합니다.
구현
필요한 추가 메뉴 항목을 포함하도록 onOpen()을(를) 업데이트하겠습니다. 다음 단계를 따르세요.
- 스크립트 프로젝트에서 다음과 일치하도록
onOpen()코드를 업데이트합니다.
/**
* A special function that runs when the spreadsheet is first
* opened or reloaded. onOpen() is used to add custom menu
* items to the spreadsheet.
*/
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Book-list')
.addItem('Load Book-list', 'loadBookList')
.addSeparator()
.addItem(
'Separate title/author at first comma', 'splitAtFirstComma')
.addItem(
'Separate title/author at last "by"', 'splitAtLastBy')
.addSeparator()
.addItem(
'Fill in blank titles and author cells', 'fillInTheBlanks')
.addToUi();
}
- 스크립트 프로젝트를 저장합니다.
- 스크립트 에디터의 함수 목록에서
onOpen를 선택하고 Run을 클릭합니다. 그러면onOpen()가 실행되어 스프레드시트 메뉴를 다시 빌드하므로 스프레드시트를 새로고침하지 않아도 됩니다.
이 새 코드에서 Menu.addSeparator() 메서드는 관련 메뉴 항목 그룹을 시각적으로 정리하기 위해 메뉴에 가로 구분선을 만듭니다. 그러면 새 메뉴 항목이 아래에 추가되며 Separate title/author at first comma, Separate title/author at last "by", Fill in blank titles and author cells 라벨이 표시됩니다.
결과
스프레드시트에서 Book-list 메뉴를 클릭하여 새 메뉴 항목을 확인합니다.
이러한 새 항목을 클릭하면 오류가 발생하므로 오류가 발생합니다. 다음 단계에서 다시 실행해 보세요.
8. 쉼표 구분 기호로 텍스트 분할
스프레드시트로 가져온 데이터 세트에는 셀이 쉼표를 사용하여 한 셀에 잘못 결합된 몇 개의 셀이 있습니다.
텍스트 문자열을 별도의 열로 분할하는 것은 일반적인 스프레드시트 작업입니다. Google 스프레드시트는 문자열을 열로 나누는 SPLIT() 함수를 제공합니다. 하지만 데이터 세트에는 내장 함수로 쉽게 해결할 수 없는 문제가 있는 경우가 많습니다. 이 경우 Apps Script 코드를 작성하여 데이터를 정리하고 정리하는 데 필요한 복잡한 작업을 수행할 수 있습니다.
먼저 쉼표가 있는 경우 저자와 제목을 각 셀로 나누는 splitAtFirstComma()라는 함수를 구현하여 데이터 정리를 시작합니다.
splitAtFirstComma() 함수는 다음 단계를 실행해야 합니다.
- 현재 선택된 셀을 나타내는 범위를 가져옵니다.
- 범위 내 셀에 쉼표가 있는지 확인합니다.
- 쉼표가 있는 경우 첫 번째 쉼표의 위치에 문자열을 두 개의 하위 문자열로 분할합니다. 작업을 더 간단하게 하기 위해 쉼표(&[]], [제목]" 문자열 패턴을 표시할 수 있습니다. 셀에 여러 쉼표가 있다면 이를 문자열의 첫 번째 쉼표로 분할하는 것도 적절하다고 가정할 수 있습니다.
- 하위 문자열을 각 제목과 저자 셀의 새 콘텐츠로 설정합니다.
구현
이 단계를 구현하려면 이전에 사용한 것과 동일한 스프레드시트 서비스 메서드를 사용하지만 문자열 데이터를 조작하려면 자바스크립트도 사용해야 합니다. 다음 단계를 따르세요.
- Apps Script 편집기에서 스크립트 프로젝트 끝에 다음 함수를 추가합니다.
/**
* Reformats title and author columns by splitting the title column
* at the first comma, if present.
*/
function splitAtFirstComma(){
// Get the active (currently highlighted) range.
var activeRange = SpreadsheetApp.getActiveRange();
var titleAuthorRange = activeRange.offset(
0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);
// Get the current values of the selected title column cells.
// This is a 2D array.
var titleAuthorValues = titleAuthorRange.getValues();
// Update values where commas are found. Assumes the presence
// of a comma indicates an "authors, title" pattern.
for (var row = 0; row < titleAuthorValues.length; row++){
var indexOfFirstComma =
titleAuthorValues[row][0].indexOf(", ");
if(indexOfFirstComma >= 0){
// Found a comma, so split and update the values in
// the values array.
var titlesAndAuthors = titleAuthorValues[row][0];
// Update the title value in the array.
titleAuthorValues[row][0] =
titlesAndAuthors.slice(indexOfFirstComma + 2);
// Update the author value in the array.
titleAuthorValues[row][1] =
titlesAndAuthors.slice(0, indexOfFirstComma);
}
}
// Put the updated values back into the spreadsheet.
titleAuthorRange.setValues(titleAuthorValues);
}
- 스크립트 프로젝트를 저장합니다.
코드 검토
세 가지 주요 섹션으로 구성된 새 코드를 검토하겠습니다.
1: 강조표시된 제목 값을 가져옵니다
처음 세 줄은 시트의 현재 데이터를 참조하는 세 가지 변수를 설정합니다.
activeRange는splitAtFirstComma()함수가 호출될 때 사용자가 현재 강조표시한 범위를 나타냅니다. 이 연습을 단순화하기 위해 A열의 셀을 강조표시할 때만 사용자가 이 작업을 수행한다고 가정할 수 있습니다.titleAuthorRange는activeRange와 같은 셀을 다루지만 오른쪽에도 열이 하나 더 포함된 새 범위를 나타냅니다.titleAuthorRange는Range.offset(rowOffset, columnOffset, numRows, numColumns)메서드를 사용하여 생성됩니다. 코드의 확장된 작성자는 제목 열에 있는 모든 저자를 배치할 장소가 필요하므로 코드에 이 확장된 범위가 필요합니다.titleAuthorValues는Range.getValues()를 사용하여titleAuthorRange에서 추출한 2D 데이터 배열입니다.
2: 각 제목을 검사하여 첫 번째 쉼표 구분자를 찾았습니다.
다음 섹션에서는 titleAuthorValues의 값을 검토하여 쉼표를 찾습니다. Loop for JavaScript는 titleAuthorValues의 첫 번째 열에 있는 모든 값을 검사하는 데 사용됩니다. JavaScript String indexOf() 메서드를 사용하여 쉼표 하위 문자열이 발견되면(", ") 코드는 다음 작업을 실행합니다.
- 셀 문자열 값이
titlesAndAuthors변수에 복사됩니다. - 쉼표 위치는 자바스크립트 문자열 indexOf() 메서드를 사용하여 결정됩니다.
- 자바스크립트 문자열 slice() 메서드는 쉼표 구분자 앞에 하위 문자열을 가져오고 구분 기호 뒤에 하위 문자열을 가져오기 위해 두 번 호출됩니다.
- 하위 문자열은 titleAuthorValues 2D 배열에 다시 복사되어 해당 위치의 기존 값을 덮어씁니다. '[authors], [title]" 패턴'을 가정했기 때문에 첫 번째 열에 두 번째 하위 문자열을 배치하고 두 번째 열에는 저자를 배치합니다.
참고: 코드에서 쉼표를 찾지 않으면 행의 데이터가 변경되지 않고 그대로 유지됩니다.
3: 새 값을 시트에 다시 복사
모든 제목 셀 값을 검사하면 업데이트된 titleAuthorValues 2D 배열은 Range.setValues(values) 메서드를 사용하여 스프레드시트로 다시 복사됩니다.
결과
이제 splitAtFirstComma() 함수의 효과를 직접 확인할 수 있습니다. 다음을 선택한 후 첫 번째 쉼표에서 제목/작성자 분리 메뉴 항목을 선택하여 실행해 보세요.
...셀 1개:
여러 셀
이제 Sheets 데이터를 처리하는 Apps Script 함수를 빌드했습니다. 그런 다음 두 번째 분할 함수를 구현합니다.
9. '구분자' 구분자 텍스트 분할
원본 데이터를 살펴보면 다른 문제를 볼 수 있습니다. 일부 데이터가 "[authors], [title]"와 같이 단일 셀의 제목 및 저자와 마찬가지로 다른 셀 작성자의 제목과 제목 형식은 "[title] 별 [authors]"입니다.
구현
이전 섹션에서 사용한 동일한 기법으로 splitAtLastBy()이라는 함수를 만들어 이 문제를 해결할 수 있습니다. 이 함수는 splitAtFirstComma()와 비슷한 작업을 합니다. 유일한 차이점은 차이점은 약간 다른 텍스트 패턴을 검색하는 것입니다. 다음을 실행하여 이 함수를 구현합니다.
- Apps Script 편집기에서 스크립트 프로젝트 끝에 다음 함수를 추가합니다.
/**
* Reformats title and author columns by splitting the title column
* at the last instance of the string " by ", if present.
*/
function splitAtLastBy(){
// Get the active (currently highlighted) range.
var activeRange = SpreadsheetApp.getActiveRange();
var titleAuthorRange = activeRange.offset(
0, 0, activeRange.getHeight(), activeRange.getWidth() + 1);
// Get the current values of the selected title column cells.
// This is a 2D array.
var titleAuthorValues = titleAuthorRange.getValues();
// Update values where " by " substrings are found. Assumes
// the presence of a " by " indicates a "title by authors"
// pattern.
for(var row = 0; row < titleAuthorValues.length; row++){
var indexOfLastBy =
titleAuthorValues[row][0].lastIndexOf(" by ");
if(indexOfLastBy >= 0){
// Found a " by ", so split and update the values in
// the values array.
var titlesAndAuthors = titleAuthorValues[row][0];
// Update the title value in the array.
titleAuthorValues[row][0] =
titlesAndAuthors.slice(0, indexOfLastBy);
// Update the author value in the array.
titleAuthorValues[row][1] =
titlesAndAuthors.slice(indexOfLastBy + 4);
}
}
// Put the updated values back into the spreadsheet.
titleAuthorRange.setValues(titleAuthorValues);
}
- 스크립트 프로젝트를 저장합니다.
코드 검토
이 코드와 splitAtFirstComma()에는 몇 가지 주요 차이점이 있습니다.
- 하위 문자열 "
by"는 "," 대신 문자열 구분 기호로 사용됩니다. - 여기서
String.indexOf(substring)대신 자바스크립트String.lastIndexOf(substring)메서드가 사용됩니다. 즉, 초기 문자열에by" 하위 문자열이 여러 개 있고 마지막 \'br&='by"를 제외한 모든 제목이 제목에 포함된다고 가정합니다. - 문자열을 분할하면 첫 번째 하위 문자열은 제목으로, 두 번째 하위 문자열은 작성자로 설정됩니다 (
splitAtFirstComma()의 반대 순서).
결과
이제 splitAtLastBy() 함수의 효과를 직접 확인할 수 있습니다. 옵션을 선택한 후 마지막 제목/작성자 '작성자'별 메뉴 항목을 선택하여 실행해 보세요.
...셀 1개:
여러 셀
Codelab의 이 섹션을 완료했습니다. 이제 Apps Script를 사용하여 시트의 문자열 데이터를 읽고 수정할 수 있으며 맞춤 메뉴를 사용하여 다양한 Apps Script 명령어를 실행할 수 있습니다.
다음 섹션에서는 공개 API에서 가져온 데이터를 빈 셀로 채워 이 데이터 세트를 개선하는 방법을 알아봅니다.
10. 개요: 공개 API에서 데이터 가져오기
지금까지 데이터 세트를 수정해 일부 제목과 저자 형식 문제를 수정했지만 데이터 세트에 여전히 일부 정보가 누락되어 있고 아래 셀에 강조표시되어 있습니다.
현재 보유한 데이터에 문자열 작업을 사용하여 누락된 데이터를 가져올 수 있습니다. 대신 다른 소스에서 누락된 데이터를 가져와야 합니다. Apps Script에서 추가 데이터를 제공할 수 있는 외부 API의 정보를 요청하면 됩니다.
API는 애플리케이션 프로그래밍 인터페이스입니다. 일반적인 용어이지만 기본적으로 프로그램 및 스크립트가 정보를 요청하거나 특정 작업을 실행하기 위해 호출할 수 있는 서비스입니다. 이 섹션에서는 시트의 빈 셀에 삽입할 수 있는 도서 정보를 요청하기 위해 공개적으로 사용 가능한 API를 호출합니다.
이 섹션에서는 다음을 수행하는 방법을 설명합니다.
- 외부 API 소스의 도서 데이터를 요청합니다.
- 반환된 데이터에서 제목 및 저자 정보를 추출하여 스프레드시트에 작성합니다.
11. UrlFetch로 외부 데이터 가져오기
스프레드시트에서 직접 작동하는 코드를 자세히 살펴보기 전에 공개 Open Library API에 도서 정보를 요청하는 도우미 함수를 만들면 Apps Script에서 외부 API를 사용하는 방법을 배울 수 있습니다.
Google의 도우미 함수 fetchBookData_(ISBN)은 13자리 도서의 ISBN 번호를 매개변수로 사용하여 해당 책에 대한 데이터를 반환합니다. Open Library API에 연결되고 정보를 검색한 다음 반환된 JSON 객체를 파싱합니다.
구현
다음을 실행하여 이 도우미 함수를 구현합니다.
- Apps Script 편집기에서 스크립트 끝에 다음 코드를 추가합니다.
/**
* Helper function to retrieve book data from the Open Library
* public API.
*
* @param {number} ISBN - The ISBN number of the book to find.
* @return {object} The book's data, in JSON format.
*/
function fetchBookData_(ISBN){
// Connect to the public API.
var url = "https://openlibrary.org/api/books?bibkeys=ISBN:"
+ ISBN + "&jscmd=details&format=json";
var response = UrlFetchApp.fetch(
url, {'muteHttpExceptions': true});
// Make request to API and get response before this point.
var json = response.getContentText();
var bookData = JSON.parse(json);
// Return only the data we're interested in.
return bookData['ISBN:' + ISBN];
}
- 스크립트 프로젝트를 저장합니다.
코드 검토
이 코드는 두 가지 기본 섹션으로 나뉘어 있습니다.
1: API 요청
처음 두 줄은 fetchBookData_(ISBN)를 API의 URL 엔드포인트 및 Apps Script의 URL 가져오기 서비스를 사용하여 공개 Open Library API에 연결합니다.
url 변수는 웹 주소와 같이 URL 문자열입니다. 공개 라이브러리 서버의 위치를 가리킵니다. 또한 오픈 라이브러리 서버에 요청하는 정보 및 응답 구성 방법을 알려주는 매개변수 3개(bibkeys, jscmd, format)가 포함되어 있습니다. 이 경우 도서의 ISBN 번호를 입력하고 자세한 정보를 JSON 형식으로 반환하도록 요청합니다.
URL 문자열을 빌드하면 코드가 해당 위치에 요청을 보내고 응답을 받습니다. UrlFetchApp.fetch(url, params) 메서드를 사용하면 됩니다. 이 요청을 통해 제공된 외부 URL에 정보 요청이 전송되고 결과 응답이 response 변수에 저장됩니다. 이 코드는 URL 외에도 선택적 매개변수 muteHttpExceptions를 true로 설정합니다. 이 설정은 요청에 의해 API 오류가 발생하는 경우 코드가 중단된다는 의미입니다. 대신 오류 응답이 반환됩니다.
이 요청은 response 변수에 저장된 HTTPResponse 객체를 반환합니다. HTTP 응답에는 응답 코드, HTTP 헤더, 기본 응답 콘텐츠가 포함됩니다. 여기서 중요한 정보는 기본 JSON 콘텐츠이므로, 코드는 추출한 다음 JSON을 파싱하여 원하는 정보를 찾아서 반환해야 합니다.
2: API 응답을 파싱하고 관심 정보 반환
코드의 마지막 세 줄에서 HTTPResponse.getContentText() 메서드는 응답의 기본 콘텐츠를 문자열로 반환합니다. 이 문자열은 JSON 형식이지만 Open Library API는 정확한 콘텐츠와 형식을 정의합니다. JSON.parse(jsonString) 메서드는 JSON 문자열을 자바스크립트 객체로 변환하여 데이터의 여러 부분을 손쉽게 추출할 수 있도록 합니다. 마지막으로 이 함수는 도서의 ISBN 번호에 해당하는 데이터를 반환합니다.
결과
이제 fetchBookData_(ISBN)를 구현했으므로 코드의 다른 함수에서 ISBN 번호를 사용하여 도서의 정보를 찾을 수 있습니다. 이 함수를 사용하여 스프레드시트의 셀을 채울 수 있습니다.
12. 스프레드시트에 API 데이터 쓰기
이제 다음을 실행하는 fillInTheBlanks() 함수를 구현할 수 있습니다.
- 활성 데이터 범위 내의 누락된 제목 및 작성자 데이터를 확인합니다.
fetchBookData_(ISBN)도우미 메서드를 사용하여 Open Library API를 호출하여 누락된 특정 데이터를 검색합니다.- 각 셀에서 누락된 제목 또는 저자 값을 업데이트합니다.
구현
다음을 실행하여 이 새 함수를 구현합니다.
- Apps Script 편집기에서 스크립트 프로젝트 끝에 다음 코드를 추가합니다.
/**
* Fills in missing title and author data using Open Library API
* calls.
*/
function fillInTheBlanks(){
// Constants that identify the index of the title, author,
// and ISBN columns (in the 2D bookValues array below).
var TITLE_COLUMN = 0;
var AUTHOR_COLUMN = 1;
var ISBN_COLUMN = 2;
// Get the existing book information in the active sheet. The data
// is placed into a 2D array.
var dataRange = SpreadsheetApp.getActiveSpreadsheet()
.getDataRange();
var bookValues = dataRange.getValues();
// Examine each row of the data (excluding the header row).
// If an ISBN is present, and a title or author is missing,
// use the fetchBookData_(isbn) method to retrieve the
// missing data from the Open Library API. Fill in the
// missing titles or authors when they're found.
for(var row = 1; row < bookValues.length; row++){
var isbn = bookValues[row][ISBN_COLUMN];
var title = bookValues[row][TITLE_COLUMN];
var author = bookValues[row][AUTHOR_COLUMN];
if(isbn != "" && (title === "" || author === "") ){
// Only call the API if you have an ISBN number and
// either the title or author is missing.
var bookData = fetchBookData_(isbn);
// Sometimes the API doesn't return the information needed.
// In those cases, don't attempt to update the row.
if (!bookData || !bookData.details) {
continue;
}
// The API might not return a title, so only fill it in
// if the response has one and if the title is blank in
// the sheet.
if(title === "" && bookData.details.title){
bookValues[row][TITLE_COLUMN] = bookData.details.title;
}
// The API might not return an author name, so only fill it in
// if the response has one and if the author is blank in
// the sheet.
if(author === "" && bookData.details.authors
&& bookData.details.authors[0].name){
bookValues[row][AUTHOR_COLUMN] =
bookData.details.authors[0].name;
}
}
}
// Insert the updated book data values into the spreadsheet.
dataRange.setValues(bookValues);
}
- 스크립트 프로젝트를 저장합니다.
코드 검토
이 코드는 다음 3가지 섹션으로 구분됩니다.
1: 기존 책 정보를 읽습니다.
함수의 처음 세 줄은 코드를 더 쉽게 읽을 수 있도록 상수를 정의합니다. 다음 두 줄에서는, bookValues 변수가 시트의 로컬 정보 사본을 유지하는 데 사용됩니다. 이 코드는 bookValues에서 정보를 읽고 API를 사용하여 누락된 정보를 입력한 다음 스프레드시트에 이러한 값을 다시 작성합니다.
2: 도우미 함수를 사용하여 누락된 정보 가져오기
이 코드는 bookValues의 각 행에 반복하여 누락된 제목이나 저자를 찾습니다. 효율성을 개선하면서 API 호출 수를 줄이기 위해 이 코드는 다음 조건이 충족되는 경우에만 API를 호출합니다.
- 행의 ISBN 열에 값이 있습니다.
- 행의 제목 또는 작성자 셀이 비어 있습니다.
조건이 true이면 코드에서 이전에 구현한 fetchBookData_(isbn) 도우미 함수를 사용하여 API를 호출하고 결과를 bookData 변수에 저장합니다. 이제 스프레드시트에 삽입하고자 하는 누락된 정보가 있을 것입니다.
남은 작업은 bookData 정보를 스프레드시트에 추가하는 것입니다. 다만, 주의해야 할 점이 있습니다. 안타깝게도 Open Library Book API와 같은 공개 API에서 요청한 정보가 없거나 정보를 제공할 수 없는 다른 문제가 있는 경우가 있습니다. 모든 API 요청이 성공한다고 가정하면 코드가 예기치 않은 오류를 처리할 만큼 안정적이지 않습니다.
코드가 API 오류를 처리할 수 있는지 확인하려면 코드에서 API 응답이 유효한지 확인해야 합니다. 코드에 bookData가 있으면 간단한 검사를 수행하여 읽기 전에 bookData 및 bookData.details가 있는지 확인합니다. 둘 중 하나가 누락되었다면 API에 원하는 데이터가 없는 것입니다. 이 경우 continue 명령어는 해당 행을 건너뛰도록 코드에 지시합니다. 이를 통해 누락된 셀을 채울 수 있지만 적어도 스크립트가 비정상 종료되지는 않습니다.
3: 업데이트된 정보를 시트에 다시 작성합니다.
코드의 마지막 부분에는 API에서 반환된 제목과 저자 정보를 확인하는 검사가 유사합니다. 이 코드는 원본 제목이나 저자 셀이 비어 있고 API가 여기에 배치할 수 있는 값을 반환했을 때만 bookValues 배열을 업데이트합니다.
루프의 모든 행은 시트에서 검사된 후 종료됩니다. 마지막 단계는 Range.setValues(values)를 사용하여 지금 업데이트된 bookValues 배열을 스프레드시트에 다시 작성하는 것입니다.
결과
이제 책 데이터 정리를 완료할 수 있습니다. 다음 단계를 따르세요.
- 아직 사용하지 않았다면 시트에서 A2:A15 범위를 강조표시하고 도서 목록 > 첫 번째 쉼표에서 제목/작성자 분리를 선택하여 쉼표 문제를 해결합니다.
- 아직 사용하지 않았다면 시트에서 A2:A15 범위를 강조표시하고 Book-list > Split title/author at last "by"를 선택하여 &;
- 나머지 셀을 모두 채우려면 도서 목록 > 빈 제목 및 저자 셀 입력을 선택합니다.
13. 결론
이 Codelab을 완료하신 것을 축하드립니다. 앱 스크립트 코드의 여러 부분을 활성화하는 맞춤 메뉴를 만드는 방법도 배웠습니다. 또한 Apps Script 서비스와 공개 API를 사용하여 Google Sheets로 데이터를 가져오는 방법도 확인했습니다. 이는 스프레드시트 처리의 일반적인 작업이며 Apps Script를 사용하면 다양한 소스에서 데이터를 가져올 수 있습니다. 마지막으로 Apps Script 서비스와 자바스크립트를 사용하여 스프레드시트 데이터를 읽고 처리하고 삽입하는 방법을 알아보았습니다.
이 Codelab이 도움이 되었나요?
학습한 내용
- Google 스프레드시트에서 데이터를 가져오는 방법
onOpen()함수에서 맞춤 메뉴를 만드는 방법- 문자열 데이터 값을 파싱하고 조작하는 방법
- URL 가져오기 서비스를 사용하여 공개 API를 호출하는 방법
- 공개 API 소스에서 가져온 JSON 객체 데이터를 파싱하는 방법
다음 단계
이 재생목록의 다음 Codelab에서는 스프레드시트 내에서 데이터 형식을 지정하는 방법에 대해 더 자세히 알아봅니다.
데이터 형식에서 다음 Codelab을 찾습니다.