試算表可以含有多份工作表,每張工作表的列數各不相同
或資料欄中的資訊儲存格是位於
某一列和欄的交集,而且可能包含資料值。
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
。
傳回的資料量沒有明確限制。錯誤未傳回任何資料。 結尾會省略空白的結尾列和欄。
以下說明單一和批次 get 方法。基本 樣本 適用 讀取作業,請參閱基本讀取。
讀取單一範圍
如要從試算表中讀取單一範圍的值,請使用
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); } };
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")
小茹
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")
小茹
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
如需儲存大量結構化物件
建議使用 Cloud Bigtable
寫入
如要寫入工作表,必須具備試算表 ID (A1 中的儲存格範圍) 標記,以及您希望在適當要求主體中寫入的資料 物件。如要進一步瞭解試算表 ID 和 A1 標記法,請參閱 Google Sheets API 總覽。
必須提供有效的更新
ValueInputOption
參數。
如果是單一更新,這是必填的查詢參數。如果是批次更新
這個參數在要求主體中為必要項目。ValueInputOption
控制項
如何解讀輸入資料,以及輸入字串是否會剖析,或是
則不同,詳情請見下表:
ValueInputOption |
說明 |
---|---|
RAW |
系統不會剖析輸入內容,並以字串形式插入。例如,輸入內容「=1+2」放置字串,而非公式,「=1+2」。(非字串或數字等非字串值一律視為 RAW )。 |
USER_ENTERED |
系統會比照在試算表 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); } };
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"]], )
小茹
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
,
必須與網址中的範圍相符。在 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); } };
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"]], )
小茹
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); } };
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"]], )
小茹
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
,
必須與網址中的範圍相符。在 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
。附加的值
從 B7
開始,符合下列所有 range
輸入:
Sheet1
,因為它會檢查工作表中的所有資料,並判斷 位於B4:D6
的表格是最後一個表格。B4
或C5:D5
,因為兩者都位於B4:D6
資料表中。B2:D4
,因為範圍內最後一個資料表是B4:D6
表格 (但其中也包含A1:C2
資料表)。A3:G10
,因為範圍內最後一個資料表是B4:D6
表格 開頭和結尾都是
下列 range
輸入內容無法在 B7
開始寫入:
A1
會從A3
開始寫入,因為該資料表位於A1:C2
資料表中。E4
會在E4
開始寫入,因為此函式不在任何資料表中。 (基於相同原因,A4
也會在A4
開始寫入)。
此外,您也可以選擇在備份後的
資料表或插入新資料列來加入新資料。根據預設,輸入內容會覆寫資料
。如要將新資料寫入新的資料列,請使用
InsertDataOption
敬上
並指定 insertDataOption=INSERT_ROWS
如要進一步瞭解 Google 試算表的儲存格和列數上限,請參閱 可存放於 Google 雲端硬碟的檔案。