스프레드시트는 각 시트에 여러 행이 있는 여러 개의 시트를 포함할 수 있습니다.
할 수 있습니다. 셀은
교집합이며 데이터 값을 포함할 수 있습니다. 이
Google Sheets API는
spreadsheets.values
드림
리소스를 사용하여 값 읽기 및 쓰기를 사용 설정합니다.
이 페이지에서는 spreadsheets.values
리소스 사용의 기본사항을 설명합니다. 만약
행을 삽입하거나 서식 및 기타 속성을
시트에 있는 경우
spreadsheets.batchUpdate
드림
스프레드시트 업데이트에 설명된 방법을 사용하세요.
메서드
spreadsheets.values
리소스
는 각각 값을 읽고 쓰는 다음 메서드를 제공합니다.
태스크에 따라 다릅니다
범위 액세스 | 읽기 | 쓰기 |
---|---|---|
단일 범위 | spreadsheets.values.get |
spreadsheets.values.update |
여러 범위 | spreadsheets.values.batchGet |
spreadsheets.values.batchUpdate |
추가 | spreadsheets.values.append |
일반적으로 여러 읽기 또는 업데이트를
batchGet
및 batchUpdate
메서드 (각각 개선됨)
효율적입니다
각 방법에 대한 예는 기본 읽기 및 기본 샘플 페이지를 작성합니다 모든 샘플을 보려면 다음을 참조하세요. 샘플 개요 페이지로 이동합니다.
읽기
시트에서 데이터 값을 읽으려면 스프레드시트 ID와 A1이 필요합니다.
범위 표기법입니다. 시트 ID 없이 범위 지정 (A1:B2
)
스프레드시트의 첫 번째 시트에서 요청이 실행됨을 의미합니다. 자세한 내용은
스프레드시트 ID와 A1 표기법에 대한 자세한 내용은 Google Sheets API를 참조하세요.
개요를 참조하세요.
여러 선택적 쿼리 매개변수가 출력 형식을 제어합니다.
형식 매개변수 | 기본값 |
---|---|
majorDimension |
행 |
valueRenderOption |
FORMATTED_VALUE |
dateTimeRenderOption |
SERIAL_NUMBER |
valueRenderOption
인 경우에만 dateTimeRenderOption
를 사용해야 합니다.
FORMATTED_VALUE
이(가) 아닙니다.
반환되는 데이터 양에는 명시적인 제한이 없습니다. 오류가 데이터를 반환하지 않습니다. 비어있는 후행 행과 열은 생략됩니다.
아래에서는 단일 및 일괄 가져오기 메서드에 대해 설명합니다. 기본 읽기 작업에 대해 자세히 알아보려면 기본 읽기를 참조하세요.
단일 범위 읽기
스프레드시트에서 단일 범위의 값을 읽으려면
spreadsheets.values.get
드림
요청:
Apps Script
/** * Gets the values of the cells in the specified range * @param {string} spreadsheetId id of the spreadsheet * @param {string} range specifying the start and end cells of the range * @returns {*} Values in the range */ Snippets.prototype.getValues = function(spreadsheetId, range) { // This code uses the Sheets Advanced Service, but for most use cases // the built-in method SpreadsheetApp.getActiveSpreadsheet() // .getRange(range).getValues(values) is more appropriate. try { const result = Sheets.Spreadsheets.Values.get(spreadsheetId, range); const numRows = result.values ? result.values.length : 0; return result; } catch (err) { // TODO (developer) - Handle exception console.log('Failed with error %s', err.message); } };
자바
import com.google.api.client.googleapis.json.GoogleJsonError; import com.google.api.client.googleapis.json.GoogleJsonResponseException; import com.google.api.client.http.HttpRequestInitializer; import com.google.api.client.http.javanet.NetHttpTransport; import com.google.api.client.json.gson.GsonFactory; import com.google.api.services.sheets.v4.Sheets; import com.google.api.services.sheets.v4.SheetsScopes; import com.google.api.services.sheets.v4.model.ValueRange; import com.google.auth.http.HttpCredentialsAdapter; import com.google.auth.oauth2.GoogleCredentials; import java.io.IOException; import java.util.Collections; /* Class to demonstrate the use of Spreadsheet Get Values API */ public class GetValues { /** * Returns a range of values from a spreadsheet. * * @param spreadsheetId - Id of the spreadsheet. * @param range - Range of cells of the spreadsheet. * @return Values in the range * @throws IOException - if credentials file not found. */ public static ValueRange getValues(String spreadsheetId, String range) throws IOException { /* Load pre-authorized user credentials from the environment. TODO(developer) - See https://developers.google.com/identity for guides on implementing OAuth2 for your application. */ GoogleCredentials credentials = GoogleCredentials.getApplicationDefault() .createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS)); HttpRequestInitializer requestInitializer = new HttpCredentialsAdapter( credentials); // Create the sheets API client Sheets service = new Sheets.Builder(new NetHttpTransport(), GsonFactory.getDefaultInstance(), requestInitializer) .setApplicationName("Sheets samples") .build(); ValueRange result = null; try { // Gets the values of the cells in the specified range. result = service.spreadsheets().values().get(spreadsheetId, range).execute(); int numRows = result.getValues() != null ? result.getValues().size() : 0; System.out.printf("%d rows retrieved.", numRows); } catch (GoogleJsonResponseException e) { // TODO(developer) - handle error appropriately GoogleJsonError error = e.getDetails(); if (error.getCode() == 404) { System.out.printf("Spreadsheet not found with id '%s'.\n", spreadsheetId); } else { throw e; } } return result; } }
자바스크립트
function getValues(spreadsheetId, range, callback) { try { gapi.client.sheets.spreadsheets.values.get({ spreadsheetId: spreadsheetId, range: range, }).then((response) => { const result = response.result; const numRows = result.values ? result.values.length : 0; console.log(`${numRows} rows retrieved.`); if (callback) callback(response); }); } catch (err) { document.getElementById('content').innerText = err.message; return; } }
Node.js
/** * Gets cell values from a Spreadsheet. * @param {string} spreadsheetId The spreadsheet ID. * @param {string} range The sheet range. * @return {obj} spreadsheet information */ async function getValues(spreadsheetId, range) { const {GoogleAuth} = require('google-auth-library'); const {google} = require('googleapis'); const auth = new GoogleAuth({ scopes: 'https://www.googleapis.com/auth/spreadsheets', }); const service = google.sheets({version: 'v4', auth}); try { const result = await service.spreadsheets.values.get({ spreadsheetId, range, }); const numRows = result.data.values ? result.data.values.length : 0; console.log(`${numRows} rows retrieved.`); return result; } catch (err) { // TODO (developer) - Handle exception throw err; } }
PHP
use Google\Client; use Google\Service\Drive; use Google\Service\Sheets\BatchUpdateSpreadsheetRequest; /** * get values of a particular spreadsheet(by Id and range). */ function getValues($spreadsheetId, $range) { /* Load pre-authorized user credentials from the environment. TODO(developer) - See https://developers.google.com/identity for guides on implementing OAuth2 for your application. */ $client = new Google\Client(); $client->useApplicationDefaultCredentials(); $client->addScope(Google\Service\Drive::DRIVE); $service = new Google_Service_Sheets($client); $result = $service->spreadsheets_values->get($spreadsheetId, $range); try{ $numRows = $result->getValues() != null ? count($result->getValues()) : 0; printf("%d rows retrieved.", $numRows); return $result; } catch(Exception $e) { // TODO(developer) - handle error appropriately echo 'Message: ' .$e->getMessage(); } }
Python
import google.auth from googleapiclient.discovery import build from googleapiclient.errors import HttpError def get_values(spreadsheet_id, range_name): """ Creates the batch_update the user has access to. Load pre-authorized user credentials from the environment. TODO(developer) - See https://developers.google.com/identity for guides on implementing OAuth2 for the application. """ creds, _ = google.auth.default() # pylint: disable=maybe-no-member try: service = build("sheets", "v4", credentials=creds) result = ( service.spreadsheets() .values() .get(spreadsheetId=spreadsheet_id, range=range_name) .execute() ) rows = result.get("values", []) print(f"{len(rows)} rows retrieved") return result except HttpError as error: print(f"An error occurred: {error}") return error if __name__ == "__main__": # Pass: spreadsheet_id, and range_name get_values("1CM29gwKIzeXsAppeNwrc8lbYaVMmUclprLuLYuHog4k", "A1:C2")
Ruby
result = service.get_spreadsheet_values(spreadsheet_id, range_name) num_rows = result.values ? result.values.length : 0 puts "#{num_rows} rows received."
이 요청에 대한 응답은
ValueRange
드림
객체를 지정합니다.
여러 범위 읽기
스프레드시트에서 여러 개의 불연속 값 범위를 읽으려면
spreadsheets.values.batchGet
드림
요청을 통해 검색할 수 있는 여러 범위를 지정할 수 있습니다.
Apps Script
/** * Get the values in the specified ranges * @param {string} spreadsheetId spreadsheet's ID * @param {list<string>} _ranges The span of ranges * @returns {*} spreadsheet information and values */ Snippets.prototype.batchGetValues = (spreadsheetId, _ranges) => { // This code uses the Sheets Advanced Service, but for most use cases // the built-in method SpreadsheetApp.getActiveSpreadsheet() // .getRange(range).getValues(values) is more appropriate. let ranges = [ //Range names ... ]; try { const result = Sheets.Spreadsheets.Values.batchGet(spreadsheetId, {ranges: ranges}); return result; } catch (err) { // TODO (developer) - Handle exception console.log('Failed with error %s', err.message); } };
자바
import com.google.api.client.googleapis.json.GoogleJsonError; import com.google.api.client.googleapis.json.GoogleJsonResponseException; import com.google.api.client.http.HttpRequestInitializer; import com.google.api.client.http.javanet.NetHttpTransport; import com.google.api.client.json.gson.GsonFactory; import com.google.api.services.sheets.v4.Sheets; import com.google.api.services.sheets.v4.SheetsScopes; import com.google.api.services.sheets.v4.model.BatchGetValuesResponse; import com.google.auth.http.HttpCredentialsAdapter; import com.google.auth.oauth2.GoogleCredentials; import java.io.IOException; import java.util.Collections; import java.util.List; /* Class to demonstrate the use of Spreadsheet Batch Get Values API */ public class BatchGetValues { /** * Returns one or more ranges of values from a spreadsheet. * * @param spreadsheetId - Id of the spreadsheet. * @param ranges - Range of cells of the spreadsheet. * @return Values in the range * @throws IOException - if credentials file not found. */ public static BatchGetValuesResponse batchGetValues(String spreadsheetId, List<String> ranges) throws IOException { /* Load pre-authorized user credentials from the environment. TODO(developer) - See https://developers.google.com/identity for guides on implementing OAuth2 for your application. */ GoogleCredentials credentials = GoogleCredentials.getApplicationDefault() .createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS)); HttpRequestInitializer requestInitializer = new HttpCredentialsAdapter( credentials); // Create the sheets API client Sheets service = new Sheets.Builder(new NetHttpTransport(), GsonFactory.getDefaultInstance(), requestInitializer) .setApplicationName("Sheets samples") .build(); BatchGetValuesResponse result = null; try { // Gets the values of the cells in the specified range. result = service.spreadsheets().values().batchGet(spreadsheetId) .setRanges(ranges).execute(); System.out.printf("%d ranges retrieved.", result.getValueRanges().size()); } catch (GoogleJsonResponseException e) { // TODO(developer) - handle error appropriately GoogleJsonError error = e.getDetails(); if (error.getCode() == 404) { System.out.printf("Spreadsheet not found with id '%s'.\n", spreadsheetId); } else { throw e; } } return result; } }
자바스크립트
function batchGetValues(spreadsheetId, _ranges, callback) { let ranges = [ // Range names ... ]; ranges = _ranges; try { gapi.client.sheets.spreadsheets.values.batchGet({ spreadsheetId: spreadsheetId, ranges: ranges, }).then((response) => { const result = response.result; console.log(`${result.valueRanges.length} ranges retrieved.`); if (callback) callback(response); }); } catch (err) { document.getElementById('content').innerText = err.message; return; } }
Node.js
/** * Batch gets cell values from a Spreadsheet. * @param {string} spreadsheetId The spreadsheet ID. * @param {string} _ranges The mock sheet range. * @return {obj} spreadsheet information */ async function batchGetValues(spreadsheetId, _ranges) { const {GoogleAuth} = require('google-auth-library'); const {google} = require('googleapis'); const auth = new GoogleAuth({ scopes: 'https://www.googleapis.com/auth/spreadsheets', }); const service = google.sheets({version: 'v4', auth}); let ranges = [ // Range names ... ]; try { const result = await service.spreadsheets.values.batchGet({ spreadsheetId, ranges, }); console.log(`${result.data.valueRanges.length} ranges retrieved.`); return result; } catch (err) { // TODO (developer) - Handle exception throw err; } }
PHP
use Google\Client; use Google\Service\Drive; use Google\Service\Sheets; /** * method to get a spreadsheet values in batch */ function batchGetValues($spreadsheetId) { /* Load pre-authorized user credentials from the environment. TODO(developer) - See https://developers.google.com/identity for guides on implementing OAuth2 for your application. */ $client = new Google\Client(); $client->useApplicationDefaultCredentials(); $client->addScope(Google\Service\Drive::DRIVE); $service = new Google_Service_Sheets($client); try{ $ranges = 'Sheet1!A1:B2'; $params = array( 'ranges' => $ranges ); //execute the request $result = $service->spreadsheets_values->batchGet($spreadsheetId, $params); printf("%d ranges retrieved.", count($result->getValueRanges())); return $result; } catch(Exception $e) { // TODO(developer) - handle error appropriately echo 'Message: ' .$e->getMessage(); } }
Python
import google.auth from googleapiclient.discovery import build from googleapiclient.errors import HttpError def batch_get_values(spreadsheet_id, _range_names): """ Creates the batch_update the user has access to. Load pre-authorized user credentials from the environment. TODO(developer) - See https://developers.google.com/identity for guides on implementing OAuth2 for the application. """ creds, _ = google.auth.default() # pylint: disable=maybe-no-member try: service = build("sheets", "v4", credentials=creds) range_names = [ # Range names ... ] result = ( service.spreadsheets() .values() .batchGet(spreadsheetId=spreadsheet_id, ranges=range_names) .execute() ) ranges = result.get("valueRanges", []) print(f"{len(ranges)} ranges retrieved") return result except HttpError as error: print(f"An error occurred: {error}") return error if __name__ == "__main__": # Pass: spreadsheet_id, and range_name batch_get_values("1CM29gwKIzeXsAppeNwrc8lbYaVMmUclprLuLYuHog4k", "A1:C2")
Ruby
range_names = [ # Range names ... ] result = service.batch_get_spreadsheet_values(spreadsheet_id, ranges: range_names) puts "#{result.value_ranges.length} ranges retrieved."
이 요청에 대한 응답은
BatchGetValuesResponse
드림
spreadsheetId
및
ValueRange
객체입니다.
쓰기
시트에 쓰려면 A1의 셀 범위인 스프레드시트 ID가 필요합니다. 올바른 요청 본문 내에서 쓰려는 데이터를 객체를 지정합니다. 스프레드시트 ID와 A1 표기법에 대한 자세한 내용은 을(를) 참조하세요. Google Sheets API 개요를 참고하세요.
업데이트하려면 유효한
ValueInputOption
매개변수
단일 업데이트의 경우 필수 쿼리 매개변수입니다. 일괄 업데이트의 경우
요청 본문에 이 매개변수가 필요합니다. ValueInputOption
컨트롤
입력 데이터가 해석되는 방식과 입력 문자열이 파싱되는지 또는
아래 표에서 설명하는 것처럼 사용할 수 없습니다.
ValueInputOption |
설명 |
---|---|
RAW |
입력이 파싱되지 않고 문자열로 삽입됩니다. 예를 들어 '=1+2'를 입력하면 '=1+2'는 공식이 아닌 문자열을 배치합니다. 를 입력합니다. (부울 또는 숫자와 같이 문자열이 아닌 값은 항상 RAW 로 처리됩니다.) |
USER_ENTERED |
입력한 값은 Sheets UI에 입력된 것처럼 정확하게 파싱됩니다. 예: '2016년 3월 1일' 는 날짜가 되고 "=1+2" 공식이 됩니다. 형식도 추론될 수 있으므로 '$100.15'는 가 통화 서식이 지정된 숫자가 됩니다. |
아래에서는 단일 및 일괄 업데이트 메서드에 대해 설명합니다. 기본 쓰기 작업에 관한 내용은 기본 쓰기를 참고하세요.
단일 범위에 쓰기
단일 범위에 데이터를 쓰려면
spreadsheets.values.update
드림
요청:
Apps Script
/** * Updates the values in the specified range * @param {string} spreadsheetId spreadsheet's ID * @param {string} range the range of cells in spreadsheet * @param {string} valueInputOption determines how the input should be interpreted * @see * https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption * @param {list<list<string>>} _values list of string lists to input * @returns {*} spreadsheet with updated values */ Snippets.prototype.updateValues = (spreadsheetId, range, valueInputOption, _values) => { // This code uses the Sheets Advanced Service, but for most use cases // the built-in method SpreadsheetApp.getActiveSpreadsheet() // .getRange(range).setValues(values) is more appropriate. let values = [ [ // Cell values ... ] // Additional rows ... ]; try { let valueRange = Sheets.newValueRange(); valueRange.values = values; const result = Sheets.Spreadsheets.Values.update(valueRange, spreadsheetId, range, {valueInputOption: valueInputOption}); return result; } catch (err) { // TODO (developer) - Handle exception console.log('Failed with error %s', err.message); } };
자바
import com.google.api.client.googleapis.json.GoogleJsonError; import com.google.api.client.googleapis.json.GoogleJsonResponseException; import com.google.api.client.http.HttpRequestInitializer; import com.google.api.client.http.javanet.NetHttpTransport; import com.google.api.client.json.gson.GsonFactory; import com.google.api.services.sheets.v4.Sheets; import com.google.api.services.sheets.v4.SheetsScopes; import com.google.api.services.sheets.v4.model.UpdateValuesResponse; import com.google.api.services.sheets.v4.model.ValueRange; import com.google.auth.http.HttpCredentialsAdapter; import com.google.auth.oauth2.GoogleCredentials; import java.io.IOException; import java.util.Collections; import java.util.List; /* Class to demonstrate the use of Spreadsheet Update Values API */ public class UpdateValues { /** * Sets values in a range of a spreadsheet. * * @param spreadsheetId - Id of the spreadsheet. * @param range - Range of cells of the spreadsheet. * @param valueInputOption - Determines how input data should be interpreted. * @param values - List of rows of values to input. * @return spreadsheet with updated values * @throws IOException - if credentials file not found. */ public static UpdateValuesResponse updateValues(String spreadsheetId, String range, String valueInputOption, List<List<Object>> values) throws IOException { /* Load pre-authorized user credentials from the environment. TODO(developer) - See https://developers.google.com/identity for guides on implementing OAuth2 for your application. */ GoogleCredentials credentials = GoogleCredentials.getApplicationDefault() .createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS)); HttpRequestInitializer requestInitializer = new HttpCredentialsAdapter( credentials); // Create the sheets API client Sheets service = new Sheets.Builder(new NetHttpTransport(), GsonFactory.getDefaultInstance(), requestInitializer) .setApplicationName("Sheets samples") .build(); UpdateValuesResponse result = null; try { // Updates the values in the specified range. ValueRange body = new ValueRange() .setValues(values); result = service.spreadsheets().values().update(spreadsheetId, range, body) .setValueInputOption(valueInputOption) .execute(); System.out.printf("%d cells updated.", result.getUpdatedCells()); } catch (GoogleJsonResponseException e) { // TODO(developer) - handle error appropriately GoogleJsonError error = e.getDetails(); if (error.getCode() == 404) { System.out.printf("Spreadsheet not found with id '%s'.\n", spreadsheetId); } else { throw e; } } return result; } }
자바스크립트
function updateValues(spreadsheetId, range, valueInputOption, _values, callback) { let values = [ [ // Cell values ... ], // Additional rows ... ]; values = _values; const body = { values: values, }; try { gapi.client.sheets.spreadsheets.values.update({ spreadsheetId: spreadsheetId, range: range, valueInputOption: valueInputOption, resource: body, }).then((response) => { const result = response.result; console.log(`${result.updatedCells} cells updated.`); if (callback) callback(response); }); } catch (err) { document.getElementById('content').innerText = err.message; return; } }
Node.js
/** * Updates values in a Spreadsheet. * @param {string} spreadsheetId The spreadsheet ID. * @param {string} range The range of values to update. * @param {object} valueInputOption Value update options. * @param {(string[])[]} _values A 2d array of values to update. * @return {obj} spreadsheet information */ async function updateValues(spreadsheetId, range, valueInputOption, _values) { const {GoogleAuth} = require('google-auth-library'); const {google} = require('googleapis'); const auth = new GoogleAuth({ scopes: 'https://www.googleapis.com/auth/spreadsheets', }); const service = google.sheets({version: 'v4', auth}); let values = [ [ // Cell values ... ], // Additional rows ... ]; const resource = { values, }; try { const result = await service.spreadsheets.values.update({ spreadsheetId, range, valueInputOption, resource, }); console.log('%d cells updated.', result.data.updatedCells); return result; } catch (err) { // TODO (Developer) - Handle exception throw err; } }
PHP
use Google\Client; use Google\Service\Drive; use Google\Service\Sheets\ValueRange; function updateValues($spreadsheetId, $range, $valueInputOption) { /* Load pre-authorized user credentials from the environment. TODO(developer) - See https://developers.google.com/identity for guides on implementing OAuth2 for your application. */ $client = new Google\Client(); $client->useApplicationDefaultCredentials(); $client->addScope(Google\Service\Drive::DRIVE); $service = new Google_Service_Sheets($client); try{ $values = [["sample", 'values']]; $body = new Google_Service_Sheets_ValueRange([ 'values' => $values ]); $params = [ 'valueInputOption' => $valueInputOption ]; //executing the request $result = $service->spreadsheets_values->update($spreadsheetId, $range, $body, $params); printf("%d cells updated.", $result->getUpdatedCells()); return $result; } catch(Exception $e) { // TODO(developer) - handle error appropriately echo 'Message: ' .$e->getMessage(); } }
Python
import google.auth from googleapiclient.discovery import build from googleapiclient.errors import HttpError def update_values(spreadsheet_id, range_name, value_input_option, _values): """ Creates the batch_update the user has access to. Load pre-authorized user credentials from the environment. TODO(developer) - See https://developers.google.com/identity for guides on implementing OAuth2 for the application. """ creds, _ = google.auth.default() # pylint: disable=maybe-no-member try: service = build("sheets", "v4", credentials=creds) values = [ [ # Cell values ... ], # Additional rows ... ] body = {"values": values} result = ( service.spreadsheets() .values() .update( spreadsheetId=spreadsheet_id, range=range_name, valueInputOption=value_input_option, body=body, ) .execute() ) print(f"{result.get('updatedCells')} cells updated.") return result except HttpError as error: print(f"An error occurred: {error}") return error if __name__ == "__main__": # Pass: spreadsheet_id, range_name, value_input_option and _values update_values( "1CM29gwKIzeXsAppeNwrc8lbYaVMmUclprLuLYuHog4k", "A1:C2", "USER_ENTERED", [["A", "B"], ["C", "D"]], )
Ruby
values = [ [ # Cell values ... ] # Additional rows ... ] data = [ { range: range_name, values: values }, # Additional ranges to update ... ] value_range_object = Google::Apis::SheetsV4::ValueRange.new(range: range_name, values: values) result = service.update_spreadsheet_value(spreadsheet_id, range_name, value_range_object, value_input_option: value_input_option) puts "#{result.updated_cells} cells updated."
업데이트 요청의 본문은
ValueRange
드림
객체에 액세스할 수 있지만 유일한 필수 필드는 values
입니다. range
가 지정되면
URL의 범위와 일치해야 합니다. ValueRange
에서
이것의
majorDimension
기본적으로 ROWS
가 사용됩니다. COLUMNS
가 지정되면 각 내부 배열은
행이 아닌 열에 기록됩니다.
업데이트 시 데이터가 없는 값은 건너뜁니다. 데이터를 삭제하려면 빈 문자열("")입니다.
여러 범위 쓰기
불연속적인 범위를 여러 개 쓰려면
spreadsheets.values.batchUpdate
드림
요청:
Apps Script
/** * Updates the values in the specified range * @param {string} spreadsheetId spreadsheet's ID * @param {string} range range of cells of the spreadsheet * @param {string} valueInputOption determines how the input should be interpreted * @see * https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption * @param {list<list<string>>} _values list of string values to input * @returns {*} spreadsheet with updated values */ Snippets.prototype.batchUpdateValues = (spreadsheetId, range, valueInputOption, _values) => { // This code uses the Sheets Advanced Service, but for most use cases // the built-in method SpreadsheetApp.getActiveSpreadsheet() // .getRange(range).setValues(values) is more appropriate. let values = [ [ // Cell values ... ] // Additional rows ... ]; try { let valueRange = Sheets.newValueRange(); valueRange.range = range; valueRange.values = values; let batchUpdateRequest = Sheets.newBatchUpdateValuesRequest(); batchUpdateRequest.data = valueRange; batchUpdateRequest.valueInputOption = valueInputOption; const result = Sheets.Spreadsheets.Values.batchUpdate(batchUpdateRequest, spreadsheetId); return result; } catch (err) { // TODO (developer) - Handle exception console.log('Failed with error %s', err.message); } };
자바
import com.google.api.client.googleapis.json.GoogleJsonError; import com.google.api.client.googleapis.json.GoogleJsonResponseException; import com.google.api.client.http.HttpRequestInitializer; import com.google.api.client.http.javanet.NetHttpTransport; import com.google.api.client.json.gson.GsonFactory; import com.google.api.services.sheets.v4.Sheets; import com.google.api.services.sheets.v4.SheetsScopes; import com.google.api.services.sheets.v4.model.BatchUpdateValuesRequest; import com.google.api.services.sheets.v4.model.BatchUpdateValuesResponse; import com.google.api.services.sheets.v4.model.ValueRange; import com.google.auth.http.HttpCredentialsAdapter; import com.google.auth.oauth2.GoogleCredentials; import java.io.IOException; import java.util.ArrayList; import java.util.Collections; import java.util.List; /* Class to demonstrate the use of Spreadsheet Batch Update Values API */ public class BatchUpdateValues { /** * Set values in one or more ranges of spreadsheet. * * @param spreadsheetId - Id of the spreadsheet. * @param range - Range of cells of the spreadsheet. * @param valueInputOption - Determines how input data should be interpreted. * @param values - list of rows of values to input. * @return spreadsheet with updated values * @throws IOException - if credentials file not found. */ public static BatchUpdateValuesResponse batchUpdateValues(String spreadsheetId, String range, String valueInputOption, List<List<Object>> values) throws IOException { /* Load pre-authorized user credentials from the environment. TODO(developer) - See https://developers.google.com/identity for guides on implementing OAuth2 for your application. */ GoogleCredentials credentials = GoogleCredentials.getApplicationDefault() .createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS)); HttpRequestInitializer requestInitializer = new HttpCredentialsAdapter( credentials); // Create the sheets API client Sheets service = new Sheets.Builder(new NetHttpTransport(), GsonFactory.getDefaultInstance(), requestInitializer) .setApplicationName("Sheets samples") .build(); List<ValueRange> data = new ArrayList<>(); data.add(new ValueRange() .setRange(range) .setValues(values)); BatchUpdateValuesResponse result = null; try { // Updates the values in the specified range. BatchUpdateValuesRequest body = new BatchUpdateValuesRequest() .setValueInputOption(valueInputOption) .setData(data); result = service.spreadsheets().values().batchUpdate(spreadsheetId, body).execute(); System.out.printf("%d cells updated.", result.getTotalUpdatedCells()); } catch (GoogleJsonResponseException e) { // TODO(developer) - handle error appropriately GoogleJsonError error = e.getDetails(); if (error.getCode() == 404) { System.out.printf("Spreadsheet not found with id '%s'.\n", spreadsheetId); } else { throw e; } } return result; } }
자바스크립트
function batchUpdateValues(spreadsheetId, range, valueInputOption, _values, callback) { let values = [ [ // Cell values ... ], // Additional rows ... ]; values = _values; const data = []; data.push({ range: range, values: values, }); // Additional ranges to update. const body = { data: data, valueInputOption: valueInputOption, }; try { gapi.client.sheets.spreadsheets.values.batchUpdate({ spreadsheetId: spreadsheetId, resource: body, }).then((response) => { const result = response.result; console.log(`${result.totalUpdatedCells} cells updated.`); if (callback) callback(response); }); } catch (err) { document.getElementById('content').innerText = err.message; return; } }
Node.js
/** * Batch Updates values in a Spreadsheet. * @param {string} spreadsheetId The spreadsheet ID. * @param {string} range The range of values to update. * @param {object} valueInputOption Value update options. * @param {(string[])[]} _values A 2d array of values to update. * @return {obj} spreadsheet information */ async function batchUpdateValues( spreadsheetId, range, valueInputOption, _values, ) { const {GoogleAuth} = require('google-auth-library'); const {google} = require('googleapis'); const auth = new GoogleAuth({ scopes: 'https://www.googleapis.com/auth/spreadsheets', }); const service = google.sheets({version: 'v4', auth}); let values = [ [ // Cell values ... ], // Additional rows ... ]; const data = [ { range, values, }, ]; // Additional ranges to update ... const resource = { data, valueInputOption, }; try { const result = await service.spreadsheets.values.batchUpdate({ spreadsheetId, resource, }); console.log('%d cells updated.', result.data.totalUpdatedCells); return result; } catch (err) { // TODO (developer) - Handle exception throw err; } }
PHP
/** * to update values in batch for a particular spreadsheet */ use Google\Client; use Google\Service\Drive; use Google\Service\Sheets; function batchUpdateValues($spreadsheetId, $range, $valueInputOption) { /* Load pre-authorized user credentials from the environment. TODO(developer) - See https://developers.google.com/identity for guides on implementing OAuth2 for your application. */ $client = new Google\Client(); $client->useApplicationDefaultCredentials(); $client->addScope(Google\Service\Drive::DRIVE); $service = new Google_Service_Sheets($client); $values = []; try{ $data[] = new Google_Service_Sheets_ValueRange([ 'range' => $range, 'values' => $values ]); $body = new Google_Service_Sheets_BatchUpdateValuesRequest([ 'valueInputOption' => $valueInputOption, 'data' => $data ]); $result = $service->spreadsheets_values->batchUpdate($spreadsheetId, $body); printf("%d cells updated.", $result->getTotalUpdatedCells()); return $result; } catch(Exception $e) { // TODO(developer) - handle error appropriately echo 'Message: ' .$e->getMessage(); } }
Python
import google.auth from googleapiclient.discovery import build from googleapiclient.errors import HttpError def batch_update_values( spreadsheet_id, range_name, value_input_option, _values ): """ Creates the batch_update the user has access to. Load pre-authorized user credentials from the environment. TODO(developer) - See https://developers.google.com/identity for guides on implementing OAuth2 for the application. """ creds, _ = google.auth.default() # pylint: disable=maybe-no-member try: service = build("sheets", "v4", credentials=creds) values = [ [ # Cell values ... ], # Additional rows ] data = [ {"range": range_name, "values": values}, # Additional ranges to update ... ] body = {"valueInputOption": value_input_option, "data": data} result = ( service.spreadsheets() .values() .batchUpdate(spreadsheetId=spreadsheet_id, body=body) .execute() ) print(f"{(result.get('totalUpdatedCells'))} cells updated.") return result except HttpError as error: print(f"An error occurred: {error}") return error if __name__ == "__main__": # Pass: spreadsheet_id, range_name value_input_option and _values) batch_update_values( "1CM29gwKIzeXsAppeNwrc8lbYaVMmUclprLuLYuHog4k", "A1:C2", "USER_ENTERED", [["F", "B"], ["C", "D"]], )
Ruby
values = [ [ # Cell values ... ] # Additional rows ... ] data = [ { range: range_name, values: values }, # Additional ranges to update ... ] batch_update_values = Google::Apis::SheetsV4::BatchUpdateValuesRequest.new( data: data, value_input_option: value_input_option ) result = service.batch_update_values(spreadsheet_id, batch_update_values) puts "#{result.total_updated_cells} cells updated."
일괄 업데이트 요청의 본문은
BatchUpdateValuesRequest
드림
객체에는 ValueInputOption
및
ValueRange
개체 (기록된 범위당 하나씩) 각 ValueRange
객체는 자체
range
, majorDimension
, 입력 데이터
값 추가
시트에서 데이터 테이블 뒤에 데이터를 추가하려면
spreadsheets.values.append
드림
요청:
Apps Script
/** * Appends values to the specified range * @param {string} spreadsheetId spreadsheet's ID * @param {string} range range of cells in the spreadsheet * @param valueInputOption determines how the input should be interpreted * @see * https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption * @param {list<string>} _values list of rows of values to input * @returns {*} spreadsheet with appended values */ Snippets.prototype.appendValues = (spreadsheetId, range, valueInputOption, _values) => { let values = [ [ // Cell values ... ] // Additional rows ... ]; try { let valueRange = Sheets.newRowData(); valueRange.values = values; let appendRequest = Sheets.newAppendCellsRequest(); appendRequest.sheetId = spreadsheetId; appendRequest.rows = [valueRange]; const result = Sheets.Spreadsheets.Values.append(valueRange, spreadsheetId, range, {valueInputOption: valueInputOption}); return result; } catch (err) { // TODO (developer) - Handle exception console.log('Failed with error %s', err.message); } };
자바
import com.google.api.client.googleapis.json.GoogleJsonError; import com.google.api.client.googleapis.json.GoogleJsonResponseException; import com.google.api.client.http.HttpRequestInitializer; import com.google.api.client.http.javanet.NetHttpTransport; import com.google.api.client.json.gson.GsonFactory; import com.google.api.services.sheets.v4.Sheets; import com.google.api.services.sheets.v4.SheetsScopes; import com.google.api.services.sheets.v4.model.AppendValuesResponse; import com.google.api.services.sheets.v4.model.ValueRange; import com.google.auth.http.HttpCredentialsAdapter; import com.google.auth.oauth2.GoogleCredentials; import java.io.IOException; import java.util.Collections; import java.util.List; /* Class to demonstrate the use of Spreadsheet Append Values API */ public class AppendValues { /** * Appends values to a spreadsheet. * * @param spreadsheetId - Id of the spreadsheet. * @param range - Range of cells of the spreadsheet. * @param valueInputOption - Determines how input data should be interpreted. * @param values - list of rows of values to input. * @return spreadsheet with appended values * @throws IOException - if credentials file not found. */ public static AppendValuesResponse appendValues(String spreadsheetId, String range, String valueInputOption, List<List<Object>> values) throws IOException { /* Load pre-authorized user credentials from the environment. TODO(developer) - See https://developers.google.com/identity for guides on implementing OAuth2 for your application. */ GoogleCredentials credentials = GoogleCredentials.getApplicationDefault() .createScoped(Collections.singleton(SheetsScopes.SPREADSHEETS)); HttpRequestInitializer requestInitializer = new HttpCredentialsAdapter( credentials); // Create the sheets API client Sheets service = new Sheets.Builder(new NetHttpTransport(), GsonFactory.getDefaultInstance(), requestInitializer) .setApplicationName("Sheets samples") .build(); AppendValuesResponse result = null; try { // Append values to the specified range. ValueRange body = new ValueRange() .setValues(values); result = service.spreadsheets().values().append(spreadsheetId, range, body) .setValueInputOption(valueInputOption) .execute(); // Prints the spreadsheet with appended values. System.out.printf("%d cells appended.", result.getUpdates().getUpdatedCells()); } catch (GoogleJsonResponseException e) { // TODO(developer) - handle error appropriately GoogleJsonError error = e.getDetails(); if (error.getCode() == 404) { System.out.printf("Spreadsheet not found with id '%s'.\n", spreadsheetId); } else { throw e; } } return result; } }
자바스크립트
function appendValues(spreadsheetId, range, valueInputOption, _values, callback) { let values = [ [ // Cell values ... ], // Additional rows ... ]; values = _values; const body = { values: values, }; try { gapi.client.sheets.spreadsheets.values.append({ spreadsheetId: spreadsheetId, range: range, valueInputOption: valueInputOption, resource: body, }).then((response) => { const result = response.result; console.log(`${result.updates.updatedCells} cells appended.`); if (callback) callback(response); }); } catch (err) { document.getElementById('content').innerText = err.message; return; } }
Node.js
/** * Appends values in a Spreadsheet. * @param {string} spreadsheetId The spreadsheet ID. * @param {string} range The range of values to append. * @param {object} valueInputOption Value input options. * @param {(string[])[]} _values A 2d array of values to append. * @return {obj} spreadsheet information */ async function appendValues(spreadsheetId, range, valueInputOption, _values) { const {GoogleAuth} = require('google-auth-library'); const {google} = require('googleapis'); const auth = new GoogleAuth({ scopes: 'https://www.googleapis.com/auth/spreadsheets', }); const service = google.sheets({version: 'v4', auth}); let values = [ [ // Cell values ... ], // Additional rows ... ]; const resource = { values, }; try { const result = await service.spreadsheets.values.append({ spreadsheetId, range, valueInputOption, resource, }); console.log(`${result.data.updates.updatedCells} cells appended.`); return result; } catch (err) { // TODO (developer) - Handle exception throw err; } }
PHP
use Google\Client; use Google\Service\Sheets; function appendValues($spreadsheetId, $range, $valueInputOption) { /* Load pre-authorized user credentials from the environment. TODO(developer) - See https://developers.google.com/identity for guides on implementing OAuth2 for your application. */ $client = new Google\Client(); $client->useApplicationDefaultCredentials(); $client->addScope('https://www.googleapis.com/auth/spreadsheets'); $service = new Google\Service\Sheets($client); try { $values = []; //add the values to be appended //execute the request $body = new Google_Service_Sheets_ValueRange([ 'values' => $values ]); $params = [ 'valueInputOption' => $valueInputOption ]; $result = $service->spreadsheets_values->append($spreadsheetId, $range, $body, $params); printf("%d cells appended.", $result->getUpdates()->getUpdatedCells()); return $result; } catch (Exception $e) { // TODO(developer) - handle error appropriately echo 'Message: ' . $e->getMessage(); }
Python
import google.auth from googleapiclient.discovery import build from googleapiclient.errors import HttpError def append_values(spreadsheet_id, range_name, value_input_option, _values): """ Creates the batch_update the user has access to. Load pre-authorized user credentials from the environment. TODO(developer) - See https://developers.google.com/identity for guides on implementing OAuth2 for the application. """ creds, _ = google.auth.default() # pylint: disable=maybe-no-member try: service = build("sheets", "v4", credentials=creds) values = [ [ # Cell values ... ], # Additional rows ... ] body = {"values": values} result = ( service.spreadsheets() .values() .append( spreadsheetId=spreadsheet_id, range=range_name, valueInputOption=value_input_option, body=body, ) .execute() ) print(f"{(result.get('updates').get('updatedCells'))} cells appended.") return result except HttpError as error: print(f"An error occurred: {error}") return error if __name__ == "__main__": # Pass: spreadsheet_id, range_name value_input_option and _values) append_values( "1CM29gwKIzeXsAppeNwrc8lbYaVMmUclprLuLYuHog4k", "A1:C2", "USER_ENTERED", [["F", "B"], ["C", "D"]], )
Ruby
values = [ [ # Cell values ... ], # Additional rows ... ] value_range = Google::Apis::SheetsV4::ValueRange.new(values: values) result = service.append_spreadsheet_value(spreadsheet_id, range_name, value_range, value_input_option: value_input_option) puts "#{result.updates.updated_cells} cells appended."
업데이트 요청의 본문은
ValueRange
드림
객체에 액세스할 수 있지만 유일한 필수 필드는 values
입니다. range
가 지정되면
URL의 범위와 일치해야 합니다. ValueRange
에서
이것의
majorDimension
기본적으로 ROWS
가 사용됩니다. COLUMNS
가 지정되면 각 내부 배열은
행이 아닌 열에 기록됩니다.
입력 범위는 기존 데이터를 검색하고 '테이블'을 찾는 데 사용됩니다. 이내
확인할 수 있습니다. 값은 표의 다음 행에 추가되며,
표의 첫 번째 열에 표시됩니다. 예를 들어 다음과 같은 Sheet1
를 생각해 보세요.
A | B | C | D | E | |
1 | x | y | z | ||
2 | x | y | z | ||
3 | |||||
4 | x | y | |||
5 | y | z | |||
6 | x | y | z | ||
7 |
시트에 테이블 2개(A1:C2
, B4:D6
)가 있습니다. 추가된 값은
다음 모든 range
입력의 경우 B7
에서 시작합니다.
Sheet1
: 시트의 모든 데이터를 검사하고B4:D6
에 있는 테이블이 마지막 테이블이라는 것을 알 수 있습니다.B4
또는C5:D5
- 둘 다B4:D6
테이블에 있으므로B2:D4
, 범위의 마지막 테이블이B4:D6
테이블이므로 (A1:C2
테이블도 포함되어 있음)A3:G10
, 범위의 마지막 테이블이B4:D6
테이블이므로 (이전과 이후에 종료되더라도)
다음 range
입력은 B7
에서 쓰기를 시작하지 않습니다.
A1
는A1:C2
테이블에 있는A3
에서 쓰기를 시작합니다.E4
는 어떤 테이블에도 없기 때문에E4
에서 쓰기를 시작합니다. (A4
도 같은 이유로A4
에서 쓰기를 시작합니다.)
또한 파일을 삭제한 후 기존 데이터를 덮어쓸지 여부를
새 데이터를 위한 새 행을 삽입할 수 있습니다. 기본적으로 입력은 데이터를 덮어씁니다.
을 입력합니다. 새 데이터를 새 행에 쓰려면 다음을 사용합니다.
InsertDataOption
드림
insertDataOption=INSERT_ROWS
를 지정합니다.
Sheets의 셀 및 행 제한에 대한 자세한 내용은 다음을 참고하세요. Google Drive에 저장할 수 있는 파일