고급 스프레드시트 서비스

고급 Sheets 서비스를 사용하면 Apps Script를 사용하여 Sheets API에 액세스할 수 있습니다. Apps Script의 내장 Google Sheets API 서비스와 마찬가지로 이 API를 사용하면 스크립트가 Google Sheets에서 데이터를 읽고, 수정하고, 형식을 지정하고, 표시할 수 있습니다. 대부분의 경우 내장 서비스를 사용하는 것이 더 쉽지만 이 고급 서비스는 몇 가지 추가 기능을 제공합니다.

이 서비스는 사용 전에 사용 설정해야 하는 고급 서비스입니다.

참조

이 서비스에 관한 자세한 내용은 Sheets API의 참조 문서를 참고하세요. Apps Script의 모든 고급 서비스와 마찬가지로 고급 Sheets 서비스는 공개 API와 동일한 객체, 메서드, 매개변수를 사용합니다. 자세한 내용은 메서드 서명이 결정되는 방식을 참고하세요.

문제를 신고하고 기타 지원을 받으려면 Sheets 지원 가이드를 참고하세요.

샘플 코드

아래 샘플 코드에서는 API의 버전 4를 사용합니다. 현재 Apps Script에서 고급 서비스로 사용할 수 있는 Sheets API 버전은 이 버전뿐입니다.

범위에서 값 읽기

다음 예에서는 Sheets 고급 서비스를 사용하여 시트의 지정된 범위에서 데이터 값을 읽는 방법을 보여줍니다. 단일 범위 읽기 레시피 샘플과 동일합니다.

advanced/sheets.gs

GitHub에서 보기

/**
 * Read a range (A1:D5) of data values. Logs the values.
 * @param {string} spreadsheetId The spreadsheet ID to read from.
 * @see https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get
 */
function readRange(spreadsheetId = yourspreadsheetId) {
  try {
    const response = Sheets.Spreadsheets.Values.get(spreadsheetId, 'Sheet1!A1:D5');
    if (response.values) {
      console.log(response.values);
      return;
    }
    console.log('Failed to get range of values from spreadsheet');
  } catch (e) {
    // TODO (developer) - Handle exception
    console.log('Failed with error %s', e.message);
  }
}

여러 범위에 값 쓰기

다음 예에서는 하나의 요청으로 시트의 서로 다른 분리된 범위에 데이터를 쓰는 방법을 보여줍니다. 여러 범위에 쓰기 레시피 샘플과 동일합니다.

advanced/sheets.gs

GitHub에서 보기

/**
 * Write to multiple, disjoint data ranges.
 * @param {string} spreadsheetId The spreadsheet ID to write to.
 * @see https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate
 */
function writeToMultipleRanges(spreadsheetId = yourspreadsheetId) {
  // Specify some values to write to the sheet.
  const columnAValues = [
    ['Item', 'Wheel', 'Door', 'Engine']
  ];
  const rowValues = [
    ['Cost', 'Stocked', 'Ship Date'],
    ['$20.50', '4', '3/1/2016']
  ];

  const request = {
    'valueInputOption': 'USER_ENTERED',
    'data': [
      {
        'range': 'Sheet1!A1:A4',
        'majorDimension': 'COLUMNS',
        'values': columnAValues
      },
      {
        'range': 'Sheet1!B1:D2',
        'majorDimension': 'ROWS',
        'values': rowValues
      }
    ]
  };
  try {
    const response = Sheets.Spreadsheets.Values.batchUpdate(request, spreadsheetId);
    if (response) {
      console.log(response);
      return;
    }
    console.log('response null');
  } catch (e) {
    // TODO (developer) - Handle  exception
    console.log('Failed with error %s', e.message);
  }
}

새 시트 추가

다음 예에서는 특정 크기와 탭 색상으로 새 시트를 만드는 방법을 보여줍니다. 시트 추가 레시피 샘플과 동일합니다.

advanced/sheets.gs

GitHub에서 보기

/**
 * Add a new sheet with some properties.
 * @param {string} spreadsheetId The spreadsheet ID.
 * @see https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate
 */
function addSheet(spreadsheetId = yourspreadsheetId) {
  const requests = [{
    'addSheet': {
      'properties': {
        'title': 'Deposits',
        'gridProperties': {
          'rowCount': 20,
          'columnCount': 12
        },
        'tabColor': {
          'red': 1.0,
          'green': 0.3,
          'blue': 0.4
        }
      }
    }
  }];
  try {
    const response =
      Sheets.Spreadsheets.batchUpdate({'requests': requests}, spreadsheetId);
    console.log('Created sheet with ID: ' +
      response.replies[0].addSheet.properties.sheetId);
  } catch (e) {
    // TODO (developer) - Handle exception
    console.log('Failed with error %s', e.message);
  }
}

피벗 테이블 만들기

다음 예에서는 소스 데이터에서 피벗 테이블을 만드는 방법을 보여줍니다. 피벗 테이블 추가 레시피 샘플과 동일합니다.

advanced/sheets.gs

GitHub에서 보기

/**
 * Add a pivot table.
 * @param {string} spreadsheetId The spreadsheet ID to add the pivot table to.
 * @param {string} pivotSourceDataSheetId The sheet ID to get the data from.
 * @param {string} destinationSheetId The sheet ID to add the pivot table to.
 * @see https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate
 */
function addPivotTable(
    spreadsheetId = yourspreadsheetId,
    pivotSourceDataSheetId= yourpivotSourceDataSheetId,
    destinationSheetId= yourdestinationSheetId) {
  const requests = [{
    'updateCells': {
      'rows': {
        'values': [
          {
            'pivotTable': {
              'source': {
                'sheetId': pivotSourceDataSheetId,
                'startRowIndex': 0,
                'startColumnIndex': 0,
                'endRowIndex': 20,
                'endColumnIndex': 7
              },
              'rows': [
                {
                  'sourceColumnOffset': 0,
                  'showTotals': true,
                  'sortOrder': 'ASCENDING',
                  'valueBucket': {
                    'buckets': [
                      {
                        'stringValue': 'West'
                      }
                    ]
                  }
                },
                {
                  'sourceColumnOffset': 1,
                  'showTotals': true,
                  'sortOrder': 'DESCENDING',
                  'valueBucket': {}
                }
              ],
              'columns': [
                {
                  'sourceColumnOffset': 4,
                  'sortOrder': 'ASCENDING',
                  'showTotals': true,
                  'valueBucket': {}
                }
              ],
              'values': [
                {
                  'summarizeFunction': 'SUM',
                  'sourceColumnOffset': 3
                }
              ],
              'valueLayout': 'HORIZONTAL'
            }
          }
        ]
      },
      'start': {
        'sheetId': destinationSheetId,
        'rowIndex': 49,
        'columnIndex': 0
      },
      'fields': 'pivotTable'
    }
  }];
  try {
    const response = Sheets.Spreadsheets.batchUpdate({'requests': requests}, spreadsheetId);
    // The Pivot table will appear anchored to cell A50 of the destination sheet.
  } catch (e) {
    // TODO (developer) - Handle exception
    console.log('Failed with error %s', e.message);
  }
}