更新电子表格

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

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