更新电子表格

本文档介绍了在 spreadsheets资源上使用 spreadsheets.batchUpdate 方法的基础知识。

除了单元格中包含的值数据之外，a 电子表格还包含许多 其他类型的数据，例如：

  • 维度
  • 单元格格式和边框
  • 命名的范围
  • 受保护的范围
  • 条件格式

这些是控制电子表格外观和操作的多种数据中的一部分。借助 spreadsheets.batchUpdate 方法，您可以更新任何此类电子表格详细信息。更改会分组在一个批次中，这样，如果一个请求失败，则不会写入任何其他（可能依赖于该请求的）更改。

如果您需要读取和写入单元格值数据，还可以使用 spreadsheets.values 资源，如读取和写入单元格 值中所述。

操作类型

spreadsheets.batchUpdate 方法支持的特定操作可以分为以下大致的操作类型：

类别 说明
添加 （和复制 ）添加新对象（有时基于旧对象，如复制请求中所示）。
更新 （和设置 ）更新对象的某些属性，通常保留旧属性不变（而设置请求会覆盖之前的数据）。
删除 移除对象。

下一部分将使用这些类别来描述特定操作的行为。

批量更新操作

The spreadsheets.batchUpdate method works by taking one or more Request 对象，每个对象都指定要执行的单个请求类型。下表列出了按资源对象和操作类型分组的批量更新请求类型：

对象 添加 / 复制 更新 / 设置 删除
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 脚本

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

Ruby

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