1. 소개
Google Sheets 기반 앱 스크립트 Codelab의 4부에 오신 것을 환영합니다.
이 Codelab을 완료하면 Apps Script에서 스프레드시트 데이터의 형식을 지정하고 공개 API에서 가져온 서식이 지정된 데이터로 구성된 구조화된 스프레드시트를 만드는 방법을 배울 수 있습니다.
학습할 내용
- Apps Script에서 다양한 Google Sheets 형식 지정 작업을 적용하는 방법
- JSON 객체 및 속성 목록을 Apps Script를 사용하여 구성된 데이터 시트로 변환하는 방법
시작하기 전에
Google Sheets 기초와 앱 스크립트 기초 과정의 네 번째 Codelab입니다. 이 Codelab을 시작하기 전에 이전 Codelab을 완료해야 합니다.
필요한 항목
- 이 재생목록의 이전 Codelab에서 살펴본 기본 Apps Script 주제에 대한 이해입니다.
- Apps Script 편집기에 관한 기본 지식
- Google Sheets에 관한 기본 지식
- 스프레드시트 A1 표기법 읽기 기능
- 자바스크립트 및
String클래스 관련 기본 지식
2. 설정
계속하려면 일부 데이터가 포함된 스프레드시트가 필요합니다. 이전과 마찬가지로 이 연습을 위해 복사할 수 있는 데이터 시트를 제공했습니다. 다음 단계를 따르세요.
- 이 링크를 클릭하여 데이터 시트를 복사한 다음 사본 만들기를 클릭합니다. 새 스프레드시트는 Google Drive 폴더에 저장되며 '데이터 서식 사본'으로 이름이 지정됩니다.
- 스프레드시트 제목을 클릭하고 "Copy of Data Format"에서 "Data format"로 변경하세요. 시트는 다음과 같이 표시되며, 첫 3가지 스타워즈 영화에 관한 몇 가지 기본 정보를 포함합니다.
- 확장 프로그램 > Apps Script를 선택하여 스크립트 편집기를 엽니다.
- Apps Script 프로젝트 제목을 클릭하고 "제목 없는 프로젝트"에서 "데이터 형식'으로 변경합니다. 이름 바꾸기를 클릭하여 제목 변경사항을 저장합니다.
이 스프레드시트와 프로젝트를 사용하여 Codelab을 시작할 준비가 되었습니다. 다음 섹션으로 이동하여 Apps Script의 기본 형식 지정에 대해 자세히 알아보세요.
3. 맞춤 메뉴 만들기
Apps Script의 여러 기본 서식 방법을 스프레드시트에 적용할 수 있습니다. 다음 연습에서는 데이터 형식을 지정하는 몇 가지 방법을 보여줍니다. 형식 지정 작업을 더 쉽게 관리할 수 있도록 필요한 항목이 포함된 맞춤 메뉴를 만드세요. 맞춤 메뉴를 만드는 프로세스는 데이터 사용하기 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를 선택하고 Run을 클릭합니다. 이 명령어는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)는 범위 셀 주변에 검은색 단색 테두리를 배치합니다.
마지막 메서드에는 여러 매개변수가 있으므로 각 매개변수가 어떻게 작동하는지 살펴보겠습니다. 여기서 처음 4개의 매개변수 (모두 true로 설정됨)는 Apps Script에 위, 아래, 범위의 왼쪽과 오른쪽에 테두리가 추가되어야 한다고 알립니다. 다섯 번째 및 여섯 번째 매개변수 (null 및 null)는 선택된 범위 내의 테두리 선을 변경하지 않도록 Apps Script에 전달합니다. 7번째 매개변수 (null)는 테두리의 색상이 기본적으로 검은색이어야 함을 나타냅니다. 마지막으로 마지막 매개변수는 SpreadsheetApp.BorderStyle에서 제공하는 옵션에서 사용할 테두리 스타일 유형을 지정합니다.
결과
다음을 수행하여 포맷 함수가 작동하는 것을 확인할 수 있습니다.
- 스크립트 프로젝트를 아직 저장하지 않았다면 Apps Script 편집기에 저장하세요.
- 빠른 형식 > 행 헤더 형식 지정 메뉴 항목을 클릭합니다.
다음과 같은 결과가 표시됩니다.
이제 서식 작업이 자동화되었습니다. 다음 섹션에서는 동일한 기법을 적용하여 열 헤더의 다른 형식 스타일을 만듭니다.
5. 열 헤더 형식 지정
맞춤설정된 행 헤더를 만들 수 있는 경우 열 헤더를 만들 수도 있습니다. 열 헤더는 특정 데이터 세트의 가독성을 높입니다. 예를 들어 이 스프레드시트의 titles 열은 다음 형식을 선택하여 향상시킬 수 있습니다.
- 텍스트 굵게 표시
- 텍스트 기울임꼴로 표시
- 셀 테두리 추가
- 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에 계산되어 저장됩니다. 이 코드는 행 수를 빼서 행 수가 열(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)
이 도우미 함수는 시트의 첫 번째 행에서 특정 이름을 검색하는 간단한 유틸리티 함수일 뿐입니다. 처음 3행은 앞서 본 메서드를 사용하여 스프레드시트의 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()
이 함수는 이미 구현했던 이전 형식 함수와 비슷한 패턴을 따릅니다. 먼저 활성 시트 (시트)와 데이터 범위 (fullDataRange)에 대한 참조를 보유하는 변수를 가져옵니다.
둘째, Range.offset(rowOffset, columnOffset, numRows, numColumns) 메서드를 사용하여 열과 행 헤더를 제외한 시트의 모든 데이터를 다루는 범위 (noHeadersRange)를 만듭니다. 그런 다음 코드는 이 새로운 범위에 기존 밴딩이 있는지 확인합니다 (Range.getBandings() 사용). 새로운 밴딩이 있는 경우 Apps Script에서 오류를 발생시키므로 필요합니다. 밴딩이 없는 경우 함수는 Range.applyRowBanding(bandingTheme, showHeader, showFooter)를 사용하여 연한 회색 밴딩을 추가합니다. 그 외의 경우에는 함수가 계속 진행됩니다.
다음 단계에서는 formatDates_(colIndex) 도우미 함수를 호출하여 release_date와 #39;라는 열에 있는 열에 날짜 형식을 지정합니다(아래 설명 참고). 열은 이전에 구현한 columnIndexOf_(colName) 도우미 함수를 사용하여 지정됩니다.
마지막으로, 이전과 마찬가지로 다른 테두리를 추가하여 서식을 지정하고 Sheet.autoResizeColumns(columnPosition) 및 Sheet.autoResizeColumns(columnPosition) 메서드를 사용하여 포함된 데이터에 맞게 모든 열과 행의 크기를 자동으로 조절합니다.
formatDates_(colIndex)
이 도우미 함수는 제공된 열 색인을 사용하여 특정 날짜 형식을 열에 적용합니다. 특히 날짜 값의 형식은 "월 일, 연도 (요일)'로 표시됩니다.
먼저 제공된 열 색인이 유효한지 (0 이상) 확인합니다. 그렇지 않은 경우 아무 작업 없이 반환됩니다. 이 검사는 시트에 'release_date' 열이 없는 경우 발생할 수 있는 오류를 방지합니다.
열 색인이 확인되면 함수는 해당 열 (헤더 행 제외)을 포괄하는 범위를 가져오고 Range.setNumberFormat(numberFormat)를 사용하여 형식을 적용합니다.
결과
다음을 수행하여 포맷 함수가 작동하는 것을 확인할 수 있습니다.
- 스크립트 프로젝트를 아직 저장하지 않았다면 Apps Script 편집기에 저장하세요.
- 빠른 형식 > 데이터 세트 형식 지정 메뉴 항목을 클릭합니다.
다음과 같은 결과가 표시됩니다.
여러분은 아직 자동화되어 있는 또 다른 서식 지정 작업입니다. 이제 이러한 형식 명령을 사용할 수 있으므로 이를 적용할 데이터를 더 추가해 보겠습니다.
7. API 데이터 가져오기 및 형식 지정
지금까지 이 Codelab에서는 스프레드시트의 형식을 지정하는 또 다른 수단으로 Apps Script를 사용하는 방법을 살펴봤습니다. 다음으로 공개 API에서 데이터를 가져와 스프레드시트에 삽입하고 읽을 수 있도록 코드를 작성합니다.
이전 Codelab에서는 API에서 데이터를 가져오는 방법을 알아봤습니다. 여기에서 동일한 기술을 사용합니다. 이 연습에서는 공개 Star Wars API (SWAPI)를 사용하여 스프레드시트를 채웁니다. 특히 이 API를 사용하여 오리지널 스타워즈 3편 영화에 나오는 주요 캐릭터에 대한 정보를 얻을 수 있습니다.
이 코드는 API를 호출하여 대량의 JSON 데이터를 가져오고, 응답을 파싱하고, 데이터를 새 시트에 배치한 다음 시트의 서식을 지정합니다.
구현
이 섹션에서는 몇 가지 메뉴 항목을 추가합니다. 각 메뉴 항목은 항목별 변수를 기본 함수 (createResourceSheet_())에 전달하는 래퍼 스크립트를 호출합니다. 이 함수를 구현하고 추가 도우미 함수 3개를 구현합니다. 이전과 마찬가지로, 도우미 함수는 작업의 논리적으로 구분된 부분을 격리하고 코드의 가독성을 유지하는 데 도움이 됩니다.
다음 작업을 수행합니다.
- 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를 선택하고 Run을 클릭합니다. 그러면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) 메서드에서는 메뉴에서 호출할 때 매개변수를 전달할 수 없습니다. 그렇다면 같은 코드를 3회 작성하지 않으려면 어떻게 해야 할까요?
바로 래퍼 함수를 사용하면 됩니다. 이러한 함수는 특정 매개변수를 설정한 상태에서 다른 함수를 즉시 호출할 수 있는 간단한 함수입니다.
여기서 코드는 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' 키는 영화의 캐릭터 이름에 매핑됩니다. 각 리소스 데이터 객체의 키는 모두 동일하다고 가정합니다. 이는 공통 객체 구조를 사용하기 위한 것입니다. 나중에 키 목록이 필요하므로 코드는 자바스크립트 Object.keys() 메서드를 사용하여 resourceObjectKeys에 키 목록을 저장합니다.
다음으로 빌더 함수는 createNewSheet_(name) 도우미 함수를 호출하여 새 데이터가 배치될 시트를 만듭니다. 이 도우미 함수를 호출하면 새 시트도 활성화됩니다.
시트가 생성되면 도우미 함수 fillSheetWithData_(resourceSheet, objectKeys, resourceDataList)가 호출되어 모든 API 데이터를 스프레드시트에 추가합니다.
마지막으로, 이전에 빌드한 모든 서식 함수를 호출하여 동일한 형식 지정 규칙을 새 데이터에 적용합니다. 새 시트가 활성 상태이기 때문에 코드를 수정하지 않아도 이러한 함수를 재사용할 수 있습니다.
fetchApiResourceObject_(url)
이 도우미 함수는 이전 Codelab에서 데이터 작업에서 사용한 fetchBookData_(ISBN) 도우미 함수와 유사합니다. 주어진 URL을 사용하며 UrlFetchApp.fetch(url, params) 메서드를 사용하여 응답을 가져옵니다. 그러면 응답은 HTTPResponse.getContextText() 및 자바스크립트 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 메뉴 항목을 클릭합니다.
다음과 같은 결과가 표시됩니다.
이제 데이터를 스프레드시트로 가져와서 자동으로 서식을 지정하는 코드를 작성했습니다.
8. 결론
이 Codelab을 완료하신 것을 축하드립니다. Apps Script 프로젝트에 포함할 수 있는 Sheets 형식 옵션을 살펴보고 대규모 API 데이터 세트를 가져와 형식을 지정하는 인상적인 애플리케이션을 빌드했습니다.
이 Codelab이 도움이 되었나요?
학습한 내용
- Apps Script를 사용하여 다양한 Sheets 형식 지정 작업을 적용하는 방법 알아보기
onOpen()함수를 사용하여 하위 메뉴를 만드는 방법- Apps Script를 사용하여 JSON 객체의 가져온 목록을 새 데이터 시트로 포맷하는 방법
다음 단계
이 재생목록의 다음 Codelab에서는 Apps Script를 사용하여 차트에서 데이터를 시각화하고 차트를 Google Slides 프레젠테이션으로 내보내는 방법을 보여줍니다.
Slides에서 데이터 차트 및 발표하기에서 다음 Codelab을 찾습니다.