試算表可以有多份工作表,每張工作表的列數或欄數都不一樣。「儲存格」是位於特定資料列和資料欄交集的位置,可能包含資料值。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
不是 FORMATTED_VALUE
的情況下,才應使用 dateTimeRenderOption
。
傳回的資料量沒有明確限制。錯誤不會傳回資料。 結尾會省略空白的結尾列和欄。
以下說明單一和批次 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
物件清單。
撰寫
如要寫入工作表,您需要試算表 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
。針對下列所有 range
輸入,附加的值會在 B7
開始:
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 雲端硬碟的檔案。