スプレッドシートには複数のシートを含めることができ、各シートの行数は任意です。
指定します。セルとは
特定の行と列の交差部分であり、データ値が含まれている場合があります。「
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
メソッド(それぞれ)
向上します
それぞれの方法の例については、基本 読書および Basic サンプルページの作成すべてのサンプルを表示するには、 サンプルの概要ページをご覧ください。
読み取り
シートからデータ値を読み取るには、スプレッドシート ID と A1
指定することもできます。シート ID なしで範囲を指定(A1:B2
)
スプレッドシートの最初のシートに対してリクエストが実行されることを意味します。詳細
スプレッドシート ID と A1 表記の詳細については、Google Sheets API をご覧ください。
概要をご覧ください。
出力の形式を制御するオプションのクエリ パラメータは次のとおりです。
形式パラメータ | デフォルト値 |
---|---|
majorDimension |
行 |
valueRenderOption |
FORMATTED_VALUE |
dateTimeRenderOption |
SERIAL_NUMBER |
なお、dateTimeRenderOption
は、valueRenderOption
FORMATTED_VALUE
ではない。
返されるデータの量に明示的な制限はありません。エラーはデータを返しません。 空の行と列は省略されます。
単一取得メソッドとバッチ取得メソッドについて、以下で説明します。基本的な API の例として、 読み取りオペレーションについては、基本的な読み取りをご覧ください。
単一の範囲を読み取る
スプレッドシートから単一の範囲の値を読み取るには、
spreadsheets.values.get
request:
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); } };
Java
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; } }
JavaScript
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); } };
Java
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; } }
JavaScript
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
説明します。
書き込み
シートに書き込むには、スプレッドシート ID、A1 のセル範囲が必要です。 記述するデータを指定します。 渡されます。スプレッドシート ID と A1 表記の詳細については、をご覧ください。 Google Sheets API の概要をご覧ください。
更新には有効な
ValueInputOption
パラメータ。
単一更新の場合、これは必須のクエリ パラメータです。バッチ アップデートの場合
このパラメータはリクエスト本文に含める必要があります。ValueInputOption
のコントロール
入力データの解釈方法と、入力文字列を解析または
できません。次の表をご覧ください。
ValueInputOption |
説明 |
---|---|
RAW |
入力は解析されず、文字列として挿入されます。たとえば、"=1+2" という入力があります。数式「=1+2」ではなく文字列を置きます。セル内。(ブール値や数値などの文字列以外の値は、常に RAW として処理されます)。 |
USER_ENTERED |
入力は、Google スプレッドシートの UI に入力されたときとまったく同じように解析されます。例: 「2016 年 3 月 1 日」「=1+2」は日付になり、数式になります。形式も推測できるので、「$100.15」数値は通貨形式の数値になります。 |
単一更新とバッチ更新のメソッドについて、以下で説明します。基本的な API の例については、 基本的な書き込みをご覧ください。
単一の範囲に書き込む
1 つの範囲にデータを書き込むには、
spreadsheets.values.update
request:
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); } };
Java
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; } }
JavaScript
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
request:
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); } };
Java
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; } }
JavaScript
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
(書き込まれた範囲ごとに 1 つ)。各 ValueRange
オブジェクトには、それぞれ独自の
range
、majorDimension
、入力データ。
値の追加
シート内のデータ表の後にデータを追加するには、
spreadsheets.values.append
request:
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); } };
Java
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; } }
JavaScript
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 |
このシートには A1:C2
と B4:D6
の 2 つのテーブルがあります。末尾に値を追加すると、
次の range
入力のすべてで、B7
から始まります。
Sheet1
。これは、シート内のすべてのデータを調べて、B4:D6
のテーブルが最後のテーブルだとします。B4
またはC5:D5
。両方ともB4:D6
テーブルに含まれているため。B2:D4
: 範囲の最後のテーブルがB4:D6
テーブルであるため (A1:C2
テーブルも含まれています)。A3:G10
: 範囲の最後のテーブルがB4:D6
テーブルであるため (開始と終了は関係ありません)。
次の range
入力は、B7
で書き込みを開始しません。
A1:C2
テーブルに含まれているため、A1
はA3
から書き込みを開始します。E4
はどのテーブルにもないため、E4
から書き込みを開始します。 (A4
でも、同じ理由でA4
から書き込みが開始されます)。
また、有効期限の経過後に既存のデータを上書きするかどうかも
新しいデータに対して新しい行を挿入しますデフォルトでは、入力によってデータが上書きされる
表の後に表示されます。新しいデータを新しい行に書き込むには、
InsertDataOption
insertDataOption=INSERT_ROWS
を指定します。
スプレッドシートのセルと行の上限について詳しくは、以下をご覧ください。 Google ドライブに保管可能なファイル。