本頁面由 Cloud Translation API 翻譯而成。

更新試算表

除了儲存格中的值資料外，試算表還包含許多其他類型的資料，例如：

尺寸
儲存格格式和框線
已命名範圍
受保護的範圍
條件式格式設定

控制試算表的外觀和作業的多種資料類型。spreadsheets.batchUpdate 方法可讓您更新任何這些試算表詳細資料。系統會將變更分組為批次，因此若其中一個要求失敗，系統就不會寫入任何其他 (可能依附) 的變更。

本頁說明使用 spreadsheets.batchUpdate 方法的基本概念。如果需要讀取及寫入儲存格值資料，您也可以使用讀取及寫入儲存格值中所述的 spreadsheets.values 資源。

作業類別

spreadsheets.batchUpdate 支援的特定作業可分為以下廣泛類別：

類別	說明
新增 (並「複製」)	新增物件 (有時以舊物件為基礎，就像重複要求一樣)。
更新 (和 Set)	更新物件的特定屬性，通常保留舊屬性 (而 Set 要求會覆寫先前的資料)。
刪除	移除物件。

下一節會使用這些類別來說明特定作業的行為。

批次更新作業

spreadsheets.batchUpdate 方法的運作方式是採用一或多個 Request 物件，每個物件都指定要執行單一要求種類。要求種類有很多種以下為不同類別的要求類型細分資料。

物件	新增 / 重複	更新 / 設定	刪除
試算表屬性		UpdateSpreadsheetPropertiesRequest
試算表	AddSheetRequest DuplicateSheetRequest	UpdateSheetPropertiesRequest	DeleteSheetRequest
維度 (包括維度範圍)	InsertDimensionRequest AppendDimensionRequest	UpdateDimensionPropertiesRequest MoveDimensionRequest AutoResizeDimensionsRequest	DeleteDimensionRequest
儲存格 (包括值、格式、資料驗證等)		RepeatCellRequest UpdateCellsRequest AppendCellsRequest
已命名範圍	AddNamedRangeRequest	UpdateNamedRangeRequest	DeleteNamedRangeRequest
邊界		UpdateBordersRequest
篩選器 (包括篩選器檢視畫面和基本篩選器)	AddFilterViewRequest DuplicateFilterViewRequest	UpdateFilterViewRequest SetBasicFilterRequest	ClearBasicFilterRequest
資料驗證		SetDataValidationRequest
條件式格式規則	AddConditionalFormatRuleRequest	UpdateConditionalFormatRuleRequest	DeleteConditionalFormatRuleRequest
受保護的範圍	AddProtectedRangeRequest	UpdateProtectedRangeRequest	DeleteProtectedRangeRequest
嵌入式物件 (包括圖表)	AddChartRequest	UpdateChartSpecRequest UpdateEmbeddedObjectPositionRequest	DeleteEmbeddedObjectRequest
合併	MergeCellsRequest		UnmergeCellsRequest

此外，還有一些會模擬使用者動作用以操控資料的要求：

限制

如要進一步瞭解 Google 試算表的儲存格與資料列限制，請參閱「可存放在 Google 雲端硬碟的檔案」一文。

欄位遮罩

許多「更新」要求都需要欄位遮罩。這些是以半形逗號分隔的欄位清單，僅更新物件中的特定欄位，其他欄位則維持不變。* 的欄位遮罩就像萬用字元，是指定訊息中所有欄位的簡寫 (如果未在要求中指定欄位值，欄位可能會還原為預設狀態)。如要進一步瞭解欄位遮罩，請參閱「使用欄位遮罩」一文。

以下範例使用 UpdateSpreadsheetPropertiesRequest 只更新試算表的標題：

要求：

POST .../v4/spreadsheets/spreadsheetId:batchUpdate

要求主體：

{
  "requests": [{
      "updateSpreadsheetProperties": {
          "properties": {"title": "TITLE"},
          "fields": "title"
      }
  }]
}

以試算表的新標題取代 TITLE。

回應

更新試算表時，某些要求可能會傳回回應。這些引數會以陣列傳回，且每個回應都佔用與對應要求相同的索引。有些要求沒有回應，而這些要求為空白。

一般來說，「新增」要求包含會傳回資訊的回應，例如新增物件的 ID。如需支援回應的清單，請參閱「回應」。

範例

以下程式碼範例會執行這些動作：

使用 title 變數更新試算表的標題。
使用 find 和 replacement 變數尋找並取代試算表中的儲存格值。

Apps Script

sheets/api/spreadsheets_snippets.gs

前往 GitHub 查看

/**
 * Updates the specified sheet using advanced sheet services
 * @param {string} spreadsheetId id of the spreadsheet to be updated
 * @param {string} title name of the sheet in the spreadsheet to be updated
 * @param {string} find string to be replaced
 * @param {string} replacement the string to replace the old data
 * @returns {*} the updated spreadsheet
 */
Snippets.prototype.batchUpdate = (spreadsheetId, title,
  find, replacement) => {
  // 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.

  try {
    // Change the spreadsheet's title.
    let updateSpreadsheetPropertiesRequest =
      Sheets.newUpdateSpreadsheetPropertiesRequest();
    updateSpreadsheetPropertiesRequest.properties =
      Sheets.newSpreadsheetProperties();
    updateSpreadsheetPropertiesRequest.properties.title = title;
    updateSpreadsheetPropertiesRequest.fields = 'title';

    // Find and replace text.
    let findReplaceRequest = Sheets.newFindReplaceRequest();
    findReplaceRequest.find = find;
    findReplaceRequest.replacement = replacement;
    findReplaceRequest.allSheets = true;

    let requests = [Sheets.newRequest(), Sheets.newRequest()];
    requests[0].updateSpreadsheetProperties =
      updateSpreadsheetPropertiesRequest;
    requests[1].findReplace = findReplaceRequest;

    let batchUpdateRequest = Sheets.newBatchUpdateSpreadsheetRequest();
    batchUpdateRequest.requests = requests;

    // Add additional requests (operations)
    const result =
      Sheets.Spreadsheets.batchUpdate(batchUpdateRequest, spreadsheetId);
    return result;
  } catch (err) {
    // TODO (developer) - Handle exception
    console.log('Failed with error %s', err.message);
  }
};

Java

sheets/snippets/src/main/java/BatchUpdate.java

前往 GitHub 查看

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.BatchUpdateSpreadsheetRequest;
import com.google.api.services.sheets.v4.model.BatchUpdateSpreadsheetResponse;
import com.google.api.services.sheets.v4.model.FindReplaceRequest;
import com.google.api.services.sheets.v4.model.FindReplaceResponse;
import com.google.api.services.sheets.v4.model.Request;
import com.google.api.services.sheets.v4.model.SpreadsheetProperties;
import com.google.api.services.sheets.v4.model.UpdateSpreadsheetPropertiesRequest;
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 API */
public class BatchUpdate {
  /**
   * Updates spreadsheet's title and cell values.
   *
   * @param spreadsheetId - Id of the spreadsheet.
   * @param title         - New title of the spreadsheet.
   * @param find          - Find cell values
   * @param replacement   - Replaced cell values
   * @return response metadata
   * @throws IOException - if credentials file not found.
   */
  public static BatchUpdateSpreadsheetResponse batchUpdate(String spreadsheetId,
                                                           String title,
                                                           String find,
                                                           String replacement)
      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<Request> requests = new ArrayList<>();
    BatchUpdateSpreadsheetResponse response = null;
    try {
      // Change the spreadsheet's title.
      requests.add(new Request()
          .setUpdateSpreadsheetProperties(new UpdateSpreadsheetPropertiesRequest()
              .setProperties(new SpreadsheetProperties()
                  .setTitle(title))
              .setFields("title")));
      // Find and replace text.
      requests.add(new Request()
          .setFindReplace(new FindReplaceRequest()
              .setFind(find)
              .setReplacement(replacement)
              .setAllSheets(true)));

      BatchUpdateSpreadsheetRequest body =
          new BatchUpdateSpreadsheetRequest().setRequests(requests);
      response = service.spreadsheets().batchUpdate(spreadsheetId, body).execute();
      FindReplaceResponse findReplaceResponse = response.getReplies().get(1).getFindReplace();

      System.out.printf("%d replacements made.", findReplaceResponse.getOccurrencesChanged());
    } 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 response;
  }
}

JavaScript

sheets/snippets/sheets_batch_update.js

前往 GitHub 查看

function batchUpdate(spreadsheetId, title, find, replacement, callback) {
  const requests = [];
  // Change the spreadsheet's title.
  requests.push({
    updateSpreadsheetProperties: {
      properties: {
        title: title,
      },
      fields: 'title',
    },
  });
  // Find and replace text.
  requests.push({
    findReplace: {
      find: find,
      replacement: replacement,
      allSheets: true,
    },
  });
  try {
    // Add additional requests (operations) ...
    const batchUpdateRequest = {requests: requests};
    gapi.client.sheets.spreadsheets.batchUpdate({
      spreadsheetId: spreadsheetId,
      resource: batchUpdateRequest,
    }).then((response) => {
      const findReplaceResponse = response.result.replies[1].findReplace;
      console.log(`${findReplaceResponse.occurrencesChanged} replacements made.`);
      if (callback) callback(response);
    });
  } catch (err) {
    document.getElementById('content').innerText = err.message;
    return;
  }
}

Node.js

sheets/snippets/sheets_batch_update.js

前往 GitHub 查看

/**
 * Updates the Spreadsheet title. Finds and replaces a string in the sheets.
 * @param {string} spreadsheetId The Spreadsheet to update
 * @param {string} title The new Spreadsheet title
 * @param {string} find The text to find
 * @param {string} replacement The text to replace
 * @return {obj} holding the information regarding the replacement of strings
 */
async function batchUpdate(spreadsheetId, title, find, replacement) {
  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});
  const requests = [];
  // Change the spreadsheet's title.
  requests.push({
    updateSpreadsheetProperties: {
      properties: {
        title,
      },
      fields: 'title',
    },
  });
  // Find and replace text.
  requests.push({
    findReplace: {
      find,
      replacement,
      allSheets: true,
    },
  });
  // Add additional requests (operations) ...
  const batchUpdateRequest = {requests};
  try {
    const response = await service.spreadsheets.batchUpdate({
      spreadsheetId,
      resource: batchUpdateRequest,
    });
    const findReplaceResponse = response.data.replies[1].findReplace;
    console.log(`${findReplaceResponse.occurrencesChanged} replacements made.`);
    return response;
  } catch (err) {
    // TODO (developer) - Handle exception
    throw err;
  }
}

PHP

sheets/snippets/src/SpreadsheetBatchUpdate.php

前往 GitHub 查看

use Google\Client;
use Google\Service\Drive;
use Google\Service\Sheets\BatchUpdateSpreadsheetRequest;

/**
 * to batch update a spreadsheet
 */
function batchUpdate($spreadsheetId, $title, $find, $replacement)
    {   
        /* 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{
            //execute the request
            $requests = [
                new Google_Service_Sheets_Request([
              'updateSpreadsheetProperties' => [
                  'properties' => [
                      'title' => $title
                    ],
                    'fields' => 'title'
              ]
          ]),
          new Google_Service_Sheets_Request([
              'findReplace' => [
                  'find' => $find,
                  'replacement' => $replacement,
                  'allSheets' => true
                  ]
                  ])
                ];
                $batchUpdateRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
                    'requests' => $requests
                ]);
        $response = $service->spreadsheets->batchUpdate($spreadsheetId, $batchUpdateRequest);
        $findReplaceResponse = $response->getReplies()[1]->getFindReplace();
        printf("%s replacements made.\n",
        $findReplaceResponse->getOccurrencesChanged());
        return $response;
    }
    catch(Exception $e) {
        // TODO(developer) - handle error appropriately
        echo 'Message: ' .$e->getMessage();
      }
    }

Python

sheets/snippets/sheets_batch_update.py

前往 GitHub 查看

import google.auth
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError


def sheets_batch_update(spreadsheet_id, title, find, replacement):
  """
  Update the sheet details in batch, 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)

    requests = []
    # Change the spreadsheet's title.
    requests.append(
        {
            "updateSpreadsheetProperties": {
                "properties": {"title": title},
                "fields": "title",
            }
        }
    )
    # Find and replace text
    requests.append(
        {
            "findReplace": {
                "find": find,
                "replacement": replacement,
                "allSheets": True,
            }
        }
    )
    # Add additional requests (operations) ...

    body = {"requests": requests}
    response = (
        service.spreadsheets()
        .batchUpdate(spreadsheetId=spreadsheet_id, body=body)
        .execute()
    )
    find_replace_response = response.get("replies")[1].get("findReplace")
    print(
        f"{find_replace_response.get('occurrencesChanged')} replacements made."
    )
    return response

  except HttpError as error:
    print(f"An error occurred: {error}")
    return error


if __name__ == "__main__":
  sheets_batch_update("spreadsheet_id", "title", "find", "replacement")

Ruby

sheets/snippets/lib/spreadsheet_snippets.rb

前往 GitHub 查看

requests = []
# Change the name of sheet ID '0' (the default first sheet on every
# spreadsheet)
requests.push({
                update_sheet_properties: {
                  properties: { sheet_id: 0, title: 'New Sheet Name' },
                  fields:     'title'
                }
              })
# Find and replace text
requests.push({
                find_replace: {
                  find:        find,
                  replacement: replacement,
                  all_sheets:  true
                }
              })
# Add additional requests (operations) ...

body = { requests: requests }
result = service.batch_update_spreadsheet(spreadsheet_id, body, {})
find_replace_response = result.replies[1].find_replace
puts "#{find_replace_response.occurrences_changed} replacements made."