更新試算表

本文說明如何使用 spreadsheets 資源的 spreadsheets.batchUpdate 方法。

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

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

這些只是眾多資料類型中的一小部分，可控制試算表的外觀和運作方式。spreadsheets.batchUpdate 方法可讓您更新任何試算表詳細資料。變更會分批處理，因此如果其中一項要求失敗，系統就不會寫入其他 (可能相依的) 變更。

如要讀取及寫入儲存格值資料，也可以使用 spreadsheets.values 資源，詳情請參閱「讀取及寫入儲存格值」。

作業類型

spreadsheets.batchUpdate 方法支援的特定作業可歸類為下列廣泛的作業類型：

類別 說明
新增 (和複製)新增物件 (有時會根據舊物件，如複製要求)。
更新 (和設定)更新物件的特定屬性，通常會保留舊屬性 (而 Set 請求會覆寫先前的資料)。
刪除移除物件。

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

批次更新作業

spreadsheets.batchUpdate 方法會採用一或多個 Request 物件，每個物件都會指定要執行的單一要求類型。下表列出批次更新要求類型，並依資源物件和作業類型分組：

物件 新增 / 複製 UPDATE / SET 刪除
BandedRange AddBandingRequest UpdateBandingRequest DeleteBandingRequest
Borders UpdateBordersRequest
Cells (包括值、格式、資料驗證等) InsertRangeRequest RepeatCellRequest
UpdateCellsRequest
AppendCellsRequest		 DeleteRangeRequest
ConditionalFormatRule AddConditionalFormatRuleRequest UpdateConditionalFormatRuleRequest DeleteConditionalFormatRuleRequest
DataSource AddDataSourceRequest UpdateDataSourceRequest
RefreshDataSourceRequest
CancelDataSourceRefreshRequest		 DeleteDataSourceRequest
DataValidationRule SetDataValidationRequest
developerMetadata CreateDeveloperMetadataRequest UpdateDeveloperMetadataRequest DeleteDeveloperMetadataRequest
Dimension (包括 DimensionRange) InsertDimensionRequest
AppendDimensionRequest		 UpdateDimensionPropertiesRequest
MoveDimensionRequest
AutoResizeDimensionsRequest		 DeleteDimensionRequest
DimensionGroup AddDimensionGroupRequest UpdateDimensionGroupRequest DeleteDimensionGroupRequest
EmbeddedChart AddChartRequest UpdateChartSpecRequest
UpdateEmbeddedObjectPositionRequest
UpdateEmbeddedObjectBorderRequest		 DeleteEmbeddedObjectRequest
FilterView (包括 BasicFilter) AddFilterViewRequest
DuplicateFilterViewRequest		 UpdateFilterViewRequest
SetBasicFilterRequest		 ClearBasicFilterRequest
DeleteFilterViewRequest
合併儲存格 (包括 MergeType) MergeCellsRequest UnmergeCellsRequest
NamedRange AddNamedRangeRequest UpdateNamedRangeRequest DeleteNamedRangeRequest
ProtectedRange AddProtectedRangeRequest UpdateProtectedRangeRequest DeleteProtectedRangeRequest
Sheets AddSheetRequest
DuplicateSheetRequest		 UpdateSheetPropertiesRequest DeleteSheetRequest
Slicer AddSlicerRequest UpdateSlicerSpecRequest
SpreadsheetProperties UpdateSpreadsheetPropertiesRequest
Table AddTableRequest UpdateTableRequest DeleteTableRequest

資料操縱要求

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

要求 說明
AutoFillRequest 根據現有資料自動填入更多資料。
CopyPasteRequest 從一個區域複製資料，然後貼到另一個區域。
CutPasteRequest 將資料從一個區域剪下並貼到另一個區域。
DeleteDuplicatesRequest 移除儲存格範圍中指定資料欄含有重複值的資料列。
FindReplaceRequest 尋找並將某些文字取代為其他文字。
PasteDataRequest 將資料 (HTML 或分隔符號) 貼到工作表中。
RandomizeRangeRequest 隨機排列範圍內的資料列。
SortRangeRequest 排序範圍內的資料。
TextToColumnsRequest 將文字欄轉換成多個文字欄。
TrimWhitespaceRequest 移除儲存格中的空白字元 (例如空格、定位點或換行符號)。

如要進一步瞭解 Google 試算表的儲存格和列數上限，請參閱「Google 雲端硬碟可存放的檔案」。

使用欄位遮罩更新特定欄位

許多更新要求都需要 FieldMask。欄位遮罩是以半形逗號分隔的欄位清單，用於指出要更新物件中的哪些欄位，同時保留所有其他欄位不變。使用欄位遮罩可避免意外覆寫要求中未指定的欄位。

如要進一步瞭解欄位遮罩，請參閱使用欄位遮罩更新。

以下程式碼範例說明如何使用 UpdateSpreadsheetPropertiesRequest 僅更新試算表的標題：

要求

POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate

要求主體

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

將 TITLE 替換為試算表的新標題。

批次更新回覆

更新試算表時，部分類型的要求可能會傳回回應。 這些回應會以陣列形式傳回，每個回應佔用的索引與對應要求相同。部分要求沒有回應，因此回應為空白。

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

程式碼範例：批次更新試算表

下列程式碼範例說明如何執行這些動作：

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

Apps Script

sheets/api/spreadsheet_snippets.gs
/**
 * 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.
    const updateSpreadsheetPropertiesRequest =
      Sheets.newUpdateSpreadsheetPropertiesRequest();
    updateSpreadsheetPropertiesRequest.properties =
      Sheets.newSpreadsheetProperties();
    updateSpreadsheetPropertiesRequest.properties.title = title;
    updateSpreadsheetPropertiesRequest.fields = "title";

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

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

    const 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
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
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
import {GoogleAuth} from 'google-auth-library';
import {google} from 'googleapis';

/**
 * Performs a batch update on a spreadsheet.
 * Updates the spreadsheet title and finds and replaces a string.
 * @param {string} spreadsheetId The ID of the spreadsheet to update.
 * @param {string} title The new title for the spreadsheet.
 * @param {string} find The string to find.
 * @param {string} replacement The string to replace the found string with.
 * @return {Promise<object>} The response from the batch update.
 */
async function batchUpdate(spreadsheetId, title, find, replacement) {
  // Authenticate with Google and get an authorized client.
  const auth = new GoogleAuth({
    scopes: 'https://www.googleapis.com/auth/spreadsheets',
  });

  // Create a new Sheets API client.
  const service = google.sheets({version: 'v4', auth});

  // Create a list of requests to be executed in the batch update.
  const requests = [];

  // Request to change the spreadsheet's title.
  requests.push({
    updateSpreadsheetProperties: {
      properties: {
        title,
      },
      fields: 'title',
    },
  });

  // Request to find and replace text.
  requests.push({
    findReplace: {
      find,
      replacement,
      allSheets: true,
    },
  });

  // Add more requests here if needed.

  // Create the batch update request.
  const batchUpdateRequest = {requests};

  // Execute the batch update request.
  const response = await service.spreadsheets.batchUpdate({
    spreadsheetId,
    requestBody: batchUpdateRequest,
  });

  // Get the response from the find and replace request and log the number of occurrences.
  const findReplaceResponse = response.data.replies[1].findReplace;
  console.log(`${findReplaceResponse.occurrencesChanged} replacements made.`);
  return response;
}

PHP

sheets/snippets/src/SpreadsheetBatchUpdate.php
<?php
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
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")

小茹

sheets/snippets/lib/spreadsheet_snippets.rb
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."