Google 会使用 AI 技术将内容翻译成您偏好的语言。AI 翻译可能包含错误。

更新电子表格

本文档介绍了如何使用 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。

代码示例：批量更新电子表格

以下代码示例展示了如何执行这些操作：

使用 title 变量更新电子表格的标题。
使用 find 和 replacement 变量查找和替换电子表格中的单元格值。

Apps 脚本

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

在 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 上查看

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

在 GitHub 上查看

<?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

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

更新电子表格 使用集合让一切井井有条 根据您的偏好保存内容并对其进行分类。

操作类型

批量更新操作

数据操纵请求

使用字段掩码更新特定字段

批量更新回答

代码示例：批量更新电子表格

Apps 脚本

Java

JavaScript

Node.js

PHP

Python

Ruby

更新电子表格