1. 소개
Google Sheets를 사용한 Apps Script 기본사항 Codelab 재생목록의 네 번째 파트에 오신 것을 환영합니다.
이 Codelab을 완료하면 Apps Script에서 스프레드시트 데이터를 포맷하는 방법과 공개 API에서 가져온 포맷된 데이터로 가득한 체계적인 스프레드시트를 만드는 함수를 작성하는 방법을 알 수 있습니다.
학습할 내용
- Apps Script에서 다양한 Google Sheets 서식 작업을 적용하는 방법
- Apps Script를 사용하여 JSON 객체 목록과 속성을 체계적인 데이터 시트로 변환하는 방법
시작하기 전에
이 Codelab은 Google Sheets를 사용한 Apps Script 기본사항 재생목록의 네 번째 Codelab입니다. 이 Codelab을 시작하기 전에 이전 Codelab을 완료해야 합니다.
필요한 항목
- 이 재생목록의 이전 Codelab에서 살펴본 기본 Apps Script 주제를 이해해야 합니다.
- Apps Script 편집기에 대한 기본적인 지식
- Google Sheets에 관한 기본 지식
- Sheets A1 표기법을 읽을 수 있음
- JavaScript 및 해당
String클래스에 관한 기본 지식
2. 설정
계속하기 전에 데이터가 포함된 스프레드시트가 필요합니다. 이전과 마찬가지로 이러한 연습에 사용할 수 있는 데이터 시트가 제공됩니다. 다음 단계를 따릅니다.
- 이 링크를 클릭하여 데이터 시트를 복사한 다음 사본 만들기를 클릭합니다. 새 스프레드시트가 Google Drive 폴더에 배치되고 '데이터 형식 지정의 사본'이라는 이름이 지정됩니다.
- 스프레드시트 제목을 클릭하고 '데이터 형식 지정 사본'에서 '데이터 형식 지정'으로 변경합니다. 시트가 다음과 같이 표시되어야 합니다. 첫 3편의 스타워즈 영화에 관한 기본 정보가 포함되어 있습니다.
- 확장 프로그램> Apps Script를 선택하여 스크립트 편집기를 엽니다.
- Apps Script 프로젝트 제목을 클릭하고 '제목 없는 프로젝트'에서 '데이터 형식 지정'으로 변경합니다. 이름 바꾸기를 클릭하여 제목 변경사항을 저장합니다.
이 스프레드시트와 프로젝트를 사용하면 Codelab을 시작할 수 있습니다. 다음 섹션으로 이동하여 Apps Script의 기본 서식에 대해 알아보세요.
3. 맞춤 메뉴 만들기
Apps Script에서 여러 기본 서식 지정 방법을 Sheets에 적용할 수 있습니다. 다음 연습에서는 데이터를 형식화하는 몇 가지 방법을 보여줍니다. 서식 지정 작업을 관리할 수 있도록 필요한 항목이 포함된 맞춤 메뉴를 만들어 보겠습니다. 맞춤 메뉴를 만드는 프로세스는 데이터 작업 Codelab에 설명되어 있지만 여기에서 다시 요약하겠습니다.
구현
맞춤 메뉴를 만들어 보겠습니다.
- Apps Script 편집기에서 스크립트 프로젝트의 코드를 다음으로 바꿉니다.
/**
* A special function that runs when the spreadsheet is opened
* or reloaded, used to add a custom menu to the spreadsheet.
*/
function onOpen() {
// Get the spreadsheet's user-interface object.
var ui = SpreadsheetApp.getUi();
// Create and add a named menu and its items to the menu bar.
ui.createMenu('Quick formats')
.addItem('Format row header', 'formatRowHeader')
.addItem('Format column header', 'formatColumnHeader')
.addItem('Format dataset', 'formatDataset')
.addToUi();
}
- 스크립트 프로젝트를 저장합니다.
- 스크립트 편집기에서 함수 목록에서
onOpen를 선택하고 실행을 클릭합니다. 이렇게 하면onOpen()가 실행되어 스프레드시트 메뉴가 다시 빌드되므로 스프레드시트를 새로고침하지 않아도 됩니다.
코드 검토
이 코드를 검토하여 작동 방식을 알아보겠습니다. onOpen()에서 첫 번째 줄은 getUi() 메서드를 사용하여 이 스크립트가 바인드된 활성 스프레드시트의 사용자 인터페이스를 나타내는 Ui 객체를 획득합니다.
다음 줄은 메뉴 (Quick formats)를 만들고, 메뉴에 메뉴 항목 (Format row header, Format column header, Format dataset)을 추가한 다음, 스프레드시트의 인터페이스에 메뉴를 추가합니다. 이 작업은 각각 createMenu(caption), addItem(caption, functionName), addToUi() 메서드를 통해 실행됩니다.
addItem(caption, functionName) 메서드는 메뉴 항목 라벨과 메뉴 항목이 선택될 때 실행되는 Apps Script 함수 간의 연결을 만듭니다. 예를 들어 Format row header 메뉴 항목을 선택하면 Sheets에서 아직 존재하지 않는 formatRowHeader() 함수를 실행하려고 합니다.
결과
스프레드시트에서 Quick formats 메뉴를 클릭하여 새 메뉴 항목을 확인합니다.
해당 함수를 구현하지 않았으므로 이러한 항목을 클릭하면 오류가 발생합니다. 다음 단계에서 이를 구현해 보겠습니다.
4. 헤더 행 서식 지정
스프레드시트의 데이터 세트에는 각 열의 데이터를 식별하는 헤더 행이 있는 경우가 많습니다. 헤더 행을 서식 지정하여 스프레드시트의 나머지 데이터와 시각적으로 구분하는 것이 좋습니다.
첫 번째 Codelab에서는 헤더용 매크로를 빌드하고 코드를 조정했습니다. 여기에서는 Apps Script를 사용하여 처음부터 헤더 행의 형식을 지정합니다. 만들 헤더 행은 헤더 텍스트를 굵게 표시하고, 배경을 어두운 청록색으로 지정하고, 텍스트를 흰색으로 지정하고, 실선 테두리를 추가합니다.
구현
서식 지정 작업을 구현하려면 이전에 사용한 것과 동일한 스프레드시트 서비스 메서드를 사용하지만 이제 서비스의 서식 지정 메서드도 사용합니다. 다음 단계를 따릅니다.
- Apps Script 편집기에서 스크립트 프로젝트 끝에 다음 함수를 추가합니다.
/**
* Formats top row of sheet using our header row style.
*/
function formatRowHeader() {
// Get the current active sheet and the top row's range.
var sheet = SpreadsheetApp.getActiveSheet();
var headerRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
// Apply each format to the top row: bold white text,
// blue-green background, and a solid black border
// around the cells.
headerRange
.setFontWeight('bold')
.setFontColor('#ffffff')
.setBackground('#007272')
.setBorder(
true, true, true, true, null, null,
null,
SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
}
- 스크립트 프로젝트를 저장합니다.
코드 검토
많은 서식 지정 작업과 마찬가지로 이를 구현하는 Apps Script 코드는 간단합니다. 처음 두 줄은 이전에 본 메서드를 사용하여 현재 활성 시트 (sheet)와 시트의 첫 번째 행 (headerRange))에 대한 참조를 가져옵니다. Sheet.getRange(row, column, numRows, numColumns) 메서드는 데이터가 있는 열만 포함하여 첫 번째 행을 지정합니다. Sheet.getLastColumn() 메서드는 시트에서 데이터가 포함된 마지막 열의 열 색인을 반환합니다. 이 예에서는 E열 (url)입니다.
나머지 코드는 다양한 Range 메서드를 호출하여 headerRange의 모든 셀에 서식 선택사항을 적용합니다. 코드를 쉽게 읽을 수 있도록 메서드 체이닝을 사용하여 각 형식 지정 메서드를 차례로 호출합니다.
Range.setFontWeight(fontWeight)는 글꼴 두께를 굵게로 설정하는 데 사용됩니다.Range.setFontColor(color)는 글꼴 색상을 흰색으로 설정하는 데 사용됩니다.Range.setBackground(color)는 배경색을 어두운 청록색으로 설정하는 데 사용됩니다.setBorder(top, left, bottom, right, vertical, horizontal, color, style)는 범위의 셀 주위에 검은색 실선 테두리를 배치합니다.
마지막 메서드에는 여러 매개변수가 있으므로 각 매개변수가 하는 일을 살펴보겠습니다. 여기에서 처음 네 개의 매개변수 (모두 true로 설정됨)는 Apps Script에 범위의 위, 아래, 왼쪽, 오른쪽에 테두리를 추가해야 한다고 알려줍니다. 다섯 번째 및 여섯 번째 매개변수 (null 및 null)는 선택한 범위 내에서 테두리 선이 변경되지 않도록 Apps Script에 지시합니다. 일곱 번째 매개변수 (null)는 테두리 색상이 기본적으로 검은색이어야 함을 나타냅니다. 마지막으로 마지막 매개변수는 SpreadsheetApp.BorderStyle에서 제공하는 옵션에서 가져온 사용할 테두리 스타일의 유형을 지정합니다.
결과
다음과 같이 서식 지정 함수가 작동하는 것을 확인할 수 있습니다.
- 아직 저장하지 않았다면 Apps Script 편집기에서 스크립트 프로젝트를 저장합니다.
- 빠른 형식 > 행 헤더 서식 메뉴 항목을 클릭합니다.
다음과 같은 결과가 표시됩니다.
이제 서식 지정 작업을 자동화했습니다. 다음 섹션에서는 동일한 기법을 적용하여 열 헤더에 다른 형식 스타일을 만듭니다.
5. 열 헤더 형식 지정
맞춤 행 헤더를 만들 수 있다면 열 헤더도 만들 수 있습니다. 열 헤더는 특정 데이터 세트의 가독성을 높입니다. 예를 들어 이 스프레드시트의 제목 열은 다음 형식 선택사항으로 개선할 수 있습니다.
- 텍스트를 굵게 표시
- 텍스트를 기울임꼴로 표시
- 셀 테두리 추가
- url 열 콘텐츠를 사용하여 하이퍼링크를 삽입합니다. 이러한 하이퍼링크를 추가한 후에는 url 열을 삭제하여 시트를 정리할 수 있습니다.
다음으로 formatColumnHeader() 함수를 구현하여 이러한 변경사항을 시트의 첫 번째 열에 적용합니다. 코드를 좀 더 쉽게 읽을 수 있도록 두 개의 도우미 함수도 구현합니다.
구현
이전과 마찬가지로 열 헤더 서식 지정을 자동화하는 함수를 추가해야 합니다. 다음 단계를 따릅니다.
- Apps Script 편집기에서 스크립트 프로젝트 끝에 다음
formatColumnHeader()함수를 추가합니다.
/**
* Formats the column header of the active sheet.
*/
function formatColumnHeader() {
var sheet = SpreadsheetApp.getActiveSheet();
// Get total number of rows in data range, not including
// the header row.
var numRows = sheet.getDataRange().getLastRow() - 1;
// Get the range of the column header.
var columnHeaderRange = sheet.getRange(2, 1, numRows, 1);
// Apply text formatting and add borders.
columnHeaderRange
.setFontWeight('bold')
.setFontStyle('italic')
.setBorder(
true, true, true, true, null, null,
null,
SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
// Call helper method to hyperlink the first column contents
// to the url column contents.
hyperlinkColumnHeaders_(columnHeaderRange, numRows);
}
formatColumnHeader()함수 뒤 스크립트 프로젝트 끝에 다음 도우미 함수를 추가합니다.
/**
* Helper function that hyperlinks the column header with the
* 'url' column contents. The function then removes the column.
*
* @param {object} headerRange The range of the column header
* to update.
* @param {number} numRows The size of the column header.
*/
function hyperlinkColumnHeaders_(headerRange, numRows) {
// Get header and url column indices.
var headerColIndex = 1;
var urlColIndex = columnIndexOf_('url');
// Exit if the url column is missing.
if(urlColIndex == -1)
return;
// Get header and url cell values.
var urlRange =
headerRange.offset(0, urlColIndex - headerColIndex);
var headerValues = headerRange.getValues();
var urlValues = urlRange.getValues();
// Updates header values to the hyperlinked header values.
for(var row = 0; row < numRows; row++){
headerValues[row][0] = '=HYPERLINK("' + urlValues[row]
+ '","' + headerValues[row] + '")';
}
headerRange.setValues(headerValues);
// Delete the url column to clean up the sheet.
SpreadsheetApp.getActiveSheet().deleteColumn(urlColIndex);
}
/**
* Helper function that goes through the headers of all columns
* and returns the index of the column with the specified name
* in row 1. If a column with that name does not exist,
* this function returns -1. If multiple columns have the same
* name in row 1, the index of the first one discovered is
* returned.
*
* @param {string} colName The name to find in the column
* headers.
* @return The index of that column in the active sheet,
* or -1 if the name isn't found.
*/
function columnIndexOf_(colName) {
// Get the current column names.
var sheet = SpreadsheetApp.getActiveSheet();
var columnHeaders =
sheet.getRange(1, 1, 1, sheet.getLastColumn());
var columnNames = columnHeaders.getValues();
// Loops through every column and returns the column index
// if the row 1 value of that column matches colName.
for(var col = 1; col <= columnNames[0].length; col++)
{
if(columnNames[0][col-1] === colName)
return col;
}
// Returns -1 if a column named colName does not exist.
return -1;
}
- 스크립트 프로젝트를 저장합니다.
코드 검토
이 세 함수의 코드를 각각 살펴보겠습니다.
formatColumnHeader()
예상하셨겠지만 이 함수의 처음 몇 줄은 관심 있는 시트와 범위를 참조하는 변수를 설정합니다.
- 활성 시트는
sheet에 저장됩니다. - 열 헤더의 행 수가 계산되어
numRows에 저장됩니다. 여기서 코드는 열 헤더를 행 수에 포함하지 않도록 1을 뺍니다(title). - 열 헤더를 포함하는 범위는
columnHeaderRange에 저장됩니다.
그런 다음 코드는 formatRowHeader()에서와 마찬가지로 열 헤더 범위에 테두리와 굵은 글꼴을 적용합니다. 여기서는 Range.setFontStyle(fontStyle)를 사용하여 텍스트를 기울임꼴로 만듭니다.
헤더 열에 하이퍼링크를 추가하는 것은 더 복잡하므로 formatColumnHeader()가 hyperlinkColumnHeaders_(headerRange, numRows)를 호출하여 작업을 처리합니다. 이렇게 하면 코드를 깔끔하고 읽기 쉽게 유지할 수 있습니다.
hyperlinkColumnHeaders_(headerRange, numRows)
이 도우미 함수는 먼저 헤더 (색인 1로 가정)와 url 열의 열 색인을 식별합니다. columnIndexOf_('url')을 호출하여 URL 열 색인을 가져옵니다. url 열이 없으면 메서드는 데이터를 수정하지 않고 종료됩니다.
이 함수는 헤더 열 행에 해당하는 URL을 포함하는 새 범위 (urlRange)를 가져옵니다. 이는 두 범위의 크기가 동일하도록 보장하는 Range.offset(rowOffset, columnOffset) 메서드를 사용하여 실행됩니다. 그런 다음 headerColumn 및 url 열의 값이 모두 검색됩니다 (headerValues 및 urlValues).
그런 다음 함수는 각 열 헤더 셀 값을 반복하고 헤더와 url 열 콘텐츠로 구성된 =HYPERLINK() Sheets 수식으로 바꿉니다. 그런 다음 수정된 헤더 값이 Range.setValues(values)를 사용하여 시트에 삽입됩니다.
마지막으로 시트를 정리하고 중복 정보를 삭제하기 위해 Sheet.deleteColumn(columnPosition)이 호출되어 url 열을 삭제합니다.
columnIndexOf_(colName)
이 도우미 함수는 시트의 첫 번째 행에서 특정 이름을 검색하는 간단한 유틸리티 함수입니다. 처음 세 줄은 이미 본 적이 있는 메서드를 사용하여 스프레드시트의 1행에서 열 헤더 이름 목록을 가져옵니다. 이러한 이름은 columnNames 변수에 저장됩니다.
그런 다음 함수는 각 이름을 순서대로 검토합니다. 검색 중인 이름과 일치하는 열을 찾으면 중지하고 열의 색인을 반환합니다. 이름 목록의 끝에 도달했는데 이름을 찾지 못한 경우 이름이 발견되지 않았음을 알리기 위해 -1을 반환합니다.
결과
다음과 같이 서식 지정 함수가 작동하는 것을 확인할 수 있습니다.
- 아직 저장하지 않았다면 Apps Script 편집기에서 스크립트 프로젝트를 저장합니다.
- 빠른 형식 > 열 헤더 형식 지정 메뉴 항목을 클릭합니다.
다음과 같은 결과가 표시됩니다.
이제 또 다른 서식 지정 작업을 자동화했습니다. 열 및 행 머리글의 형식을 지정했으므로 다음 섹션에서는 데이터의 형식을 지정하는 방법을 보여줍니다.
6. 데이터 세트 형식 지정
이제 헤더가 있으므로 시트의 나머지 데이터 형식을 지정하는 함수를 만들어 보겠습니다. 다음 서식 옵션을 사용합니다.
- 교차 행 배경 색상 (밴딩이라고도 함)
- 날짜 형식 변경
- 테두리 적용
- 모든 열과 행의 크기 자동 조정
이제 이러한 형식을 시트 데이터에 적용하는 함수 formatDataset()와 추가 도우미 메서드를 만듭니다.
구현
이전과 마찬가지로 데이터 형식을 자동화하는 함수를 추가합니다. 다음 단계를 따릅니다.
- Apps Script 편집기에서 스크립트 프로젝트 끝에 다음
formatDataset()함수를 추가합니다.
/**
* Formats the sheet data, excluding the header row and column.
* Applies the border and banding, formats the 'release_date'
* column, and autosizes the columns and rows.
*/
function formatDataset() {
// Get the active sheet and data range.
var sheet = SpreadsheetApp.getActiveSheet();
var fullDataRange = sheet.getDataRange();
// Apply row banding to the data, excluding the header
// row and column. Only apply the banding if the range
// doesn't already have banding set.
var noHeadersRange = fullDataRange.offset(
1, 1,
fullDataRange.getNumRows() - 1,
fullDataRange.getNumColumns() - 1);
if (! noHeadersRange.getBandings()[0]) {
// The range doesn't already have banding, so it's
// safe to apply it.
noHeadersRange.applyRowBanding(
SpreadsheetApp.BandingTheme.LIGHT_GREY,
false, false);
}
// Call a helper function to apply date formatting
// to the column labeled 'release_date'.
formatDates_( columnIndexOf_('release_date') );
// Set a border around all the data, and resize the
// columns and rows to fit.
fullDataRange.setBorder(
true, true, true, true, null, null,
null,
SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
sheet.autoResizeColumns(1, fullDataRange.getNumColumns());
sheet.autoResizeRows(1, fullDataRange.getNumRows());
}
- 스크립트 프로젝트의 끝에
formatDataset()함수 뒤에 다음 도우미 함수를 추가합니다.
/**
* Helper method that applies a
* "Month Day, Year (Day of Week)" date format to the
* indicated column in the active sheet.
*
* @param {number} colIndex The index of the column
* to format.
*/
function formatDates_(colIndex) {
// Exit if the given column index is -1, indicating
// the column to format isn't present in the sheet.
if (colIndex < 0)
return;
// Set the date format for the date column, excluding
// the header row.
var sheet = SpreadsheetApp.getActiveSheet();
sheet.getRange(2, colIndex, sheet.getLastRow() - 1, 1)
.setNumberFormat("mmmm dd, yyyy (dddd)");
}
- 스크립트 프로젝트를 저장합니다.
코드 검토
이 두 함수의 코드를 각각 검토해 보겠습니다.
formatDataset()
이 함수는 이미 구현한 이전 형식 함수와 유사한 패턴을 따릅니다. 먼저 활성 시트 (sheet)와 데이터 범위 (fullDataRange)에 대한 참조를 보유할 변수를 가져옵니다.
두 번째로 Range.offset(rowOffset, columnOffset, numRows, numColumns) 메서드를 사용하여 열 및 행 헤더를 제외한 시트의 모든 데이터를 포함하는 범위 (noHeadersRange)를 만듭니다. 그런 다음 코드는 이 새 범위에 기존 밴딩이 있는지 확인합니다 (Range.getBandings() 사용). 기존 밴딩이 있는 곳에 새 밴딩을 적용하려고 하면 Apps Script에서 오류가 발생하므로 이 단계가 필요합니다. 밴딩이 없으면 함수는 Range.applyRowBanding(bandingTheme, showHeader, showFooter)를 사용하여 밝은 회색 밴딩을 추가합니다. 그렇지 않으면 함수가 계속 진행됩니다.
다음 단계에서는 formatDates_(colIndex) 도우미 함수를 호출하여 'release_date' 라벨이 지정된 열의 날짜를 형식화합니다(아래 설명 참고). 열은 앞에서 구현한 columnIndexOf_(colName) 도우미 함수를 사용하여 지정됩니다.
마지막으로 이전과 같이 테두리를 추가하여 서식을 지정하고 Sheet.autoResizeColumns(columnPosition) 및 Sheet.autoResizeColumns(columnPosition) 메서드를 사용하여 포함된 데이터에 맞게 모든 열과 행의 크기를 자동으로 조정합니다.
formatDates_(colIndex)
이 도우미 함수는 제공된 열 색인을 사용하여 열에 특정 날짜 형식을 적용합니다. 특히 날짜 값을 'Month Day, Year (Day of Week)'로 서식을 지정합니다.
먼저 함수는 제공된 열 색인이 유효한지 (0 이상) 확인합니다. 그렇지 않으면 아무 작업도 실행하지 않고 반환됩니다. 이 검사를 통해 시트에'release_date' 열이 없는 경우 발생할 수 있는 오류를 방지할 수 있습니다.
열 색인이 검증되면 함수는 해당 열을 포함하는 범위 (헤더 행 제외)를 가져오고 Range.setNumberFormat(numberFormat)를 사용하여 서식을 적용합니다.
결과
다음과 같이 서식 지정 함수가 작동하는 것을 확인할 수 있습니다.
- 아직 저장하지 않았다면 Apps Script 편집기에서 스크립트 프로젝트를 저장합니다.
- 빠른 형식 > 데이터 세트 서식 지정 메뉴 항목을 클릭합니다.
다음과 같은 결과가 표시됩니다.
또 다른 서식 지정 작업을 자동화했습니다. 이제 이러한 서식 지정 명령어를 사용할 수 있으므로 이를 적용할 데이터를 추가해 보겠습니다.
7. API 데이터 가져오기 및 형식 지정
이 Codelab에서는 지금까지 Apps Script를 사용하여 스프레드시트를 서식 지정하는 방법을 알아봤습니다. 다음으로 공개 API에서 데이터를 가져와 스프레드시트에 삽입하고 읽을 수 있도록 형식을 지정하는 코드를 작성합니다.
이전 Codelab에서는 API에서 데이터를 가져오는 방법을 알아봤습니다. 여기에서도 동일한 기법을 사용합니다. 이 연습에서는 공개 스타워즈 API (SWAPI)를 사용하여 스프레드시트를 채웁니다. 특히 API를 사용하여 오리지널 스타워즈 영화 3편에 등장하는 주요 캐릭터에 관한 정보를 가져옵니다.
코드는 API를 호출하여 많은 양의 JSON 데이터를 가져오고, 응답을 파싱하고, 데이터를 새 시트에 배치한 다음 시트의 형식을 지정합니다.
구현
이 섹션에서는 메뉴 항목을 추가합니다. 각 메뉴 항목은 항목별 변수를 기본 함수 (createResourceSheet_())에 전달하는 래퍼 스크립트를 호출합니다. 이 함수와 세 개의 추가 도우미 함수를 구현합니다. 이전과 마찬가지로 도우미 함수는 작업의 논리적으로 구분된 부분을 격리하고 코드를 읽기 쉽게 유지하는 데 도움이 됩니다.
다음 작업을 수행합니다.
- Apps Script 편집기에서 스크립트 프로젝트의
onOpen()함수를 다음과 일치하도록 업데이트합니다.
/**
* A special function that runs when the spreadsheet is opened
* or reloaded, used to add a custom menu to the spreadsheet.
*/
function onOpen() {
// Get the Ui object.
var ui = SpreadsheetApp.getUi();
// Create and add a named menu and its items to the menu bar.
ui.createMenu('Quick formats')
.addItem('Format row header', 'formatRowHeader')
.addItem('Format column header', 'formatColumnHeader')
.addItem('Format dataset', 'formatDataset')
.addSeparator()
.addSubMenu(ui.createMenu('Create character sheet')
.addItem('Episode IV', 'createPeopleSheetIV')
.addItem('Episode V', 'createPeopleSheetV')
.addItem('Episode VI', 'createPeopleSheetVI')
)
.addToUi();
}
- 스크립트 프로젝트를 저장합니다.
- 스크립트 편집기에서 함수 목록에서
onOpen를 선택하고 실행을 클릭합니다. 그러면onOpen()이 실행되어 추가한 새 옵션으로 스프레드시트 메뉴가 다시 빌드됩니다. - Apps Script 파일을 만들려면 파일 옆에 있는 파일 추가
> 스크립트를 클릭합니다. - 새 스크립트의 이름을 'API'로 지정하고 Enter 키를 누릅니다. (Apps Script는 스크립트 파일 이름에
.gs확장자를 자동으로 추가합니다.) - 새 API.gs 파일의 코드를 다음으로 바꿉니다.
/**
* Wrapper function that passes arguments to create a
* resource sheet describing the characters from Episode IV.
*/
function createPeopleSheetIV() {
createResourceSheet_('characters', 1, "IV");
}
/**
* Wrapper function that passes arguments to create a
* resource sheet describing the characters from Episode V.
*/
function createPeopleSheetV() {
createResourceSheet_('characters', 2, "V");
}
/**
* Wrapper function that passes arguments to create a
* resource sheet describing the characters from Episode VI.
*/
function createPeopleSheetVI() {
createResourceSheet_('characters', 3, "VI");
}
/**
* Creates a formatted sheet filled with user-specified
* information from the Star Wars API. If the sheet with
* this data exists, the sheet is overwritten with the API
* information.
*
* @param {string} resourceType The type of resource.
* @param {number} idNumber The identification number of the film.
* @param {number} episodeNumber The Star Wars film episode number.
* This is only used in the sheet name.
*/
function createResourceSheet_(
resourceType, idNumber, episodeNumber) {
// Fetch the basic film data from the API.
var filmData = fetchApiResourceObject_(
"https://swapi.dev/api/films/" + idNumber);
// Extract the API URLs for each resource so the code can
// call the API to get more data about each individually.
var resourceUrls = filmData[resourceType];
// Fetch each resource from the API individually and push
// them into a new object list.
var resourceDataList = [];
for(var i = 0; i < resourceUrls.length; i++){
resourceDataList.push(
fetchApiResourceObject_(resourceUrls[i])
);
}
// Get the keys used to reference each part of data within
// the resources. The keys are assumed to be identical for
// each object since they're all the same resource type.
var resourceObjectKeys = Object.keys(resourceDataList[0]);
// Create the sheet with the appropriate name. It
// automatically becomes the active sheet when it's created.
var resourceSheet = createNewSheet_(
"Episode " + episodeNumber + " " + resourceType);
// Add the API data to the new sheet, using each object
// key as a column header.
fillSheetWithData_(resourceSheet, resourceObjectKeys, resourceDataList);
// Format the new sheet using the same styles the
// 'Quick Formats' menu items apply. These methods all
// act on the active sheet, which is the one just created.
formatRowHeader();
formatColumnHeader();
formatDataset();
}
- 다음 도우미 함수를 API.gs 스크립트 프로젝트 파일의 끝에 추가합니다.
/**
* Helper function that retrieves a JSON object containing a
* response from a public API.
*
* @param {string} url The URL of the API object being fetched.
* @return {object} resourceObject The JSON object fetched
* from the URL request to the API.
*/
function fetchApiResourceObject_(url) {
// Make request to API and get response.
var response =
UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
// Parse and return the response as a JSON object.
var json = response.getContentText();
var responseObject = JSON.parse(json);
return responseObject;
}
/**
* Helper function that creates a sheet or returns an existing
* sheet with the same name.
*
* @param {string} name The name of the sheet.
* @return {object} The created or existing sheet
* of the same name. This sheet becomes active.
*/
function createNewSheet_(name) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Returns an existing sheet if it has the specified
// name. Activates the sheet before returning.
var sheet = ss.getSheetByName(name);
if (sheet) {
return sheet.activate();
}
// Otherwise it makes a sheet, set its name, and returns it.
// New sheets created this way automatically become the active
// sheet.
sheet = ss.insertSheet(name);
return sheet;
}
/**
* Helper function that adds API data to the sheet.
* Each object key is used as a column header in the new sheet.
*
* @param {object} resourceSheet The sheet object being modified.
* @param {object} objectKeys The list of keys for the resources.
* @param {object} resourceDataList The list of API
* resource objects containing data to add to the sheet.
*/
function fillSheetWithData_(
resourceSheet, objectKeys, resourceDataList) {
// Set the dimensions of the data range being added to the sheet.
var numRows = resourceDataList.length;
var numColumns = objectKeys.length;
// Get the resource range and associated values array. Add an
// extra row for the column headers.
var resourceRange =
resourceSheet.getRange(1, 1, numRows + 1, numColumns);
var resourceValues = resourceRange.getValues();
// Loop over each key value and resource, extracting data to
// place in the 2D resourceValues array.
for (var column = 0; column < numColumns; column++) {
// Set the column header.
var columnHeader = objectKeys[column];
resourceValues[0][column] = columnHeader;
// Read and set each row in this column.
for (var row = 1; row < numRows + 1; row++) {
var resource = resourceDataList[row - 1];
var value = resource[columnHeader];
resourceValues[row][column] = value;
}
}
// Remove any existing data in the sheet and set the new values.
resourceSheet.clear()
resourceRange.setValues(resourceValues);
}
- 스크립트 프로젝트를 저장합니다.
코드 검토
코드를 많이 추가했습니다. 각 함수를 개별적으로 살펴보면서 작동 방식을 이해해 보겠습니다.
onOpen()
여기서는 Quick formats 메뉴에 몇 가지 메뉴 항목을 추가했습니다. 구분선이 설정되었으며 Menu.addSubMenu(menu) 메서드를 사용하여 세 개의 새 항목이 있는 중첩 메뉴 구조가 생성되었습니다. 새 항목은 Menu.addItem(caption, functionName) 메서드를 사용하여 추가됩니다.
래퍼 함수
추가된 메뉴 항목은 모두 비슷한 작업을 실행합니다. SWAPI에서 가져온 데이터로 시트를 만들려고 합니다. 유일한 차이점은 각자 다른 영화에 집중하고 있다는 점입니다.
시트를 만드는 단일 함수를 작성하고 함수가 사용할 영화를 결정하는 매개변수를 허용하면 편리합니다. 하지만 메뉴에서 호출할 때는 Menu.addItem(caption, functionName) 메서드에 매개변수를 전달할 수 없습니다. 그렇다면 동일한 코드를 세 번 작성하지 않으려면 어떻게 해야 할까요?
정답은 래퍼 함수입니다. 이는 특정 매개변수가 설정된 다른 함수를 즉시 호출하는 경량 함수입니다.
여기서 코드는 래퍼 함수 createPeopleSheetIV(), createPeopleSheetV(), createPeopleSheetVI()를 사용합니다. 메뉴 항목은 이러한 함수에 연결됩니다. 메뉴 항목을 클릭하면 래퍼 함수가 실행되고 즉시 기본 시트 빌더 함수 createResourceSheet_(resourceType, idNumber, episodeNumber)를 호출하여 메뉴 항목에 적합한 매개변수를 전달합니다. 이 경우 시트 빌더 함수에 스타워즈 영화 중 하나의 주요 캐릭터 데이터로 채워진 시트를 만들어 달라고 요청하는 것을 의미합니다.
createResourceSheet_(resourceType, idNumber, episodeNumber)
이 연습의 기본 시트 빌더 함수입니다. 일부 도우미 함수의 도움을 받아 API 데이터를 가져오고, 파싱하고, 시트를 만들고, 시트에 API 데이터를 쓴 다음, 이전 섹션에서 구성한 함수를 사용하여 시트의 형식을 지정합니다. 세부정보를 검토해 보겠습니다.
먼저 함수는 fetchApiResourceObject_(url)를 사용하여 API에 기본 영화 정보를 가져오도록 요청합니다. API 응답에는 코드가 영화에서 특정 인물 (여기서는 리소스라고 함)에 관한 자세한 내용을 가져오는 데 사용할 수 있는 URL 모음이 포함됩니다. 코드는 이 모든 것을 resourceUrls 배열에 수집합니다.
그런 다음 코드는 fetchApiResourceObject_(url)을 반복적으로 사용하여 resourceUrls의 모든 리소스 URL에 대해 API를 호출합니다. 결과는 resourceDataList 배열에 저장됩니다. 이 배열의 각 요소는 영화의 서로 다른 캐릭터를 설명하는 객체입니다.
리소스 데이터 객체에는 해당 캐릭터에 관한 정보에 매핑되는 여러 공통 키가 있습니다. 예를 들어 'name' 키는 영화 속 캐릭터의 이름에 매핑됩니다. 각 리소스 데이터 객체의 키는 공통 객체 구조를 사용하기 위한 것이므로 모두 동일하다고 가정합니다. 키 목록은 나중에 필요하므로 코드는 JavaScript Object.keys() 메서드를 사용하여 resourceObjectKeys에 키 목록을 저장합니다.
그런 다음 빌더 함수는 createNewSheet_(name) 도우미 함수를 호출하여 새 데이터가 배치될 시트를 만듭니다. 이 도우미 함수를 호출하면 새 시트도 활성화됩니다.
시트가 생성되면 헬퍼 함수 fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)가 호출되어 모든 API 데이터를 시트에 추가합니다.
마지막으로 이전에 빌드한 모든 형식 지정 함수가 호출되어 새 데이터에 동일한 형식 지정 규칙을 적용합니다. 새 시트가 활성 상태이므로 코드는 수정 없이 이러한 함수를 재사용할 수 있습니다.
fetchApiResourceObject_(url)
이 도우미 함수는 이전 Codelab 데이터 작업에서 사용된 fetchBookData_(ISBN) 도우미 함수와 유사합니다. 지정된 URL을 가져와 UrlFetchApp.fetch(url, params) 메서드를 사용하여 응답을 가져옵니다. 그런 다음 응답은 HTTPResponse.getContextText() 및 JavaScript JSON.parse(json) 메서드를 사용하여 JSON 객체로 파싱됩니다. 결과 JSON 객체가 반환됩니다.
createNewSheet_(name)
이 도우미 함수는 매우 간단합니다. 먼저 지정된 이름의 시트가 스프레드시트에 있는지 확인합니다. 그렇다면 함수는 시트를 활성화하고 반환합니다.
시트가 없으면 함수가 Spreadsheet.insertSheet(sheetName)로 시트를 만들고 활성화한 후 새 시트를 반환합니다.
fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)
이 도우미 함수는 API 데이터로 새 시트를 채우는 역할을 합니다. 새 시트, 객체 키 목록, API 리소스 객체 목록을 매개변수로 사용합니다. 각 객체 키는 새 시트의 열을 나타내고 각 리소스 객체는 행을 나타냅니다.
먼저 함수는 새 API 데이터를 표시하는 데 필요한 행과 열의 수를 계산합니다. 각각 리소스 및 키 목록의 크기입니다. 그런 다음 함수는 데이터가 배치될 출력 범위 (resourceRange)를 정의하고 열 헤더를 보관할 추가 행을 추가합니다. resourceValues 변수는 resourceRange에서 추출된 2D 값 배열을 보유합니다.
그런 다음 함수는 objectKeys 목록의 모든 객체 키를 반복합니다. 키는 열 헤더로 설정되고 두 번째 루프는 모든 리소스 객체를 통과합니다. 각 (행, 열) 쌍에 대해 해당 API 정보가 resourceValues[row][column] 요소에 복사됩니다.
resourceValues가 채워지면 이전 메뉴 항목 클릭의 데이터가 포함된 경우 대상 시트가 Sheet.clear()를 사용하여 지워집니다. 마지막으로 새 값이 시트에 기록됩니다.
결과
다음과 같이 작업 결과를 확인할 수 있습니다.
- 아직 저장하지 않았다면 Apps Script 편집기에서 스크립트 프로젝트를 저장합니다.
- 빠른 형식 > 캐릭터 시트 만들기 > 에피소드 IV 메뉴 항목을 클릭합니다.
다음과 같은 결과가 표시됩니다.
이제 Sheets로 데이터를 가져오고 자동으로 형식을 지정하는 코드를 작성했습니다.
8. 결론
이 Codelab을 완료하셨습니다. Apps Script 프로젝트에 포함할 수 있는 일부 Sheets 서식 옵션을 살펴보고 대규모 API 데이터 세트를 가져와 서식을 지정하는 인상적인 애플리케이션을 빌드했습니다.
이 Codelab이 도움이 되었나요?
학습한 내용
- Apps Script로 다양한 Sheets 서식 작업을 적용하는 방법
onOpen()함수로 하위 메뉴를 만드는 방법- 가져온 JSON 객체 목록을 Apps Script를 사용하여 새 데이터 시트로 포맷하는 방법
다음 단계
이 재생목록의 다음 Codelab에서는 Apps Script를 사용하여 차트에서 데이터를 시각화하고 차트를 Google Slides 프레젠테이션으로 내보내는 방법을 보여줍니다.
Slides에서 데이터 차트 작성 및 발표에서 다음 Codelab을 확인하세요.