更新試算表

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

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

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

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

作業類別

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

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

這些類別會在下一節中用於說明特定作業的行為。

批次更新作業

spreadsheets.batchUpdate 方法會採用一或多個 Request 物件，每個物件都會指定要執行的單一類型要求。要求有許多不同類型。以下是要求類型細目，並分為不同類別。

物件	新增 / 複製	UPDATE / SET	刪除
試算表屬性		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

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

• AutoFillRequest
• CutPasteRequest
• CopyPasteRequest
• FindReplaceRequest
• PasteDataRequest
• TextToColumnsRequest
• SortRangeRequest

限制

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

欄位遮罩

許多「更新」要求都需要欄位遮罩。這些是以逗號分隔的欄位清單，可用於只更新物件中的特定欄位，而其他欄位則保持不變。* 欄位遮罩會視為萬用字元，並用於指定訊息中的每個欄位 (也就是說，如果您未在要求中指定欄位值，欄位可能會還原為預設狀態)。如要進一步瞭解欄位遮罩，請參閱「使用欄位遮罩」一文。

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

要求：

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

要求主體：

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

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

回應

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

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

範例

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

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

Apps ScriptJavaJavaScriptNode.jsPHPPython小茹

sheets/api/spreadsheet_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);
  }
};

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;
  }
}

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;
  }
}

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;
  }
}

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();
      }
    }

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")

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."