Эта страница переведена с помощью Cloud Translation API.

Обновить таблицы

В этом документе описываются основные принципы использования метода spreadsheets.batchUpdate для ресурса spreadsheets .

Помимо данных, содержащихся в ячейках, электронная таблица включает в себя множество других типов данных, таких как:

Размеры
Форматы ячеек и границы
Именованные диапазоны
Охраняемые полигоны
Условное форматирование

Это лишь некоторые из множества типов данных, которые управляют внешним видом и работой электронной таблицы. Метод spreadsheets.batchUpdate позволяет обновлять любые из этих данных электронной таблицы. Изменения группируются в пакет, так что если один запрос не удался, никакие другие (потенциально зависимые) изменения не будут записаны.

Если вам необходимо считывать и записывать данные значений ячеек, вы также можете использовать ресурс spreadsheets.values , как описано в разделе «Чтение и запись значений ячеек» .

Типы операций

Операции, поддерживаемые методом spreadsheets.batchUpdate , можно сгруппировать в следующие основные типы операций:

Категория	Описание
Добавить (и продублировать )	Добавление новых объектов (иногда на основе старых, как в случае с дублирующимися запросами).
Обновить (и установить )	Обновить определенные свойства объекта, обычно оставляя старые свойства без изменений (в то время как запрос Set перезаписывает предыдущие данные).
Удалить	Удалите объекты.

В следующем разделе эти категории используются для описания поведения конкретных операций.

Операции пакетного обновления

Метод spreadsheets.batchUpdate принимает один или несколько объектов Request , каждый из которых определяет тип выполняемого запроса. В следующей таблице перечислены типы запросов пакетного обновления, сгруппированные по объекту ресурса и типу операции:

Объект	ДОБАВИТЬ / ДУБЛИРОВАТЬ	ОБНОВИТЬ / УСТАНОВИТЬ	УДАЛИТЬ
`BandedRange`	AddBandingRequest	UpdateBandingRequest	DeleteBandingRequest
`Borders`		UpdateBordersRequest
`Cells` (включая значения, форматы, проверку данных и т. д.)	InsertRangeRequest	RepeatCellRequest UpdateCellsRequest AppendCellsRequest	DeleteRangeRequest
`ConditionalFormatRule`	AddConditionalFormatRuleRequest	UpdateConditionalFormatRuleRequest	DeleteConditionalFormatRuleRequest
`DataSource`	Добавить запрос источника данных	UpdateDataSourceRequest RefreshDataSourceRequest Отменить запрос на обновление источника данных	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 . FieldMask — это список полей, разделенных запятыми, который используется для указания того, какие поля в объекте следует обновить, оставив все остальные поля без изменений. Использование FieldMask предотвращает случайное перезаписывание полей, которые не указаны в запросе.

Для получения дополнительной информации о масках полей см. раздел «Обновление с использованием маски поля» .

Приведенный ниже пример кода демонстрирует, как использовать метод UpdateSpreadsheetPropertiesRequest для обновления только заголовка электронной таблицы:

Запрос

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

Текст запроса

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

Замените TITLE на новое название электронной таблицы.

Ответы на пакетное обновление

При обновлении электронной таблицы некоторые типы запросов могут возвращать ответы. Ответы возвращаются в виде массива, причем каждый ответ занимает тот же индекс, что и соответствующий запрос. Некоторые запросы не имеют ответов, и для них ответ является пустым.

Как правило, запросы на добавление (add) получают в ответ информацию, такую как идентификатор добавляемого объекта. Список поддерживаемых ответов см. в разделе « Responses ».

Пример кода: Пакетное обновление электронной таблицы

Следующий пример кода демонстрирует, как выполнить эти действия:

Обновите заголовок электронной таблицы, используя переменную title .
Найдите и замените значения ячеек в электронной таблице, используя переменные find и replacement .

Apps Script

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

Руби

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 Script

Java

JavaScript

Node.js

PHP

Python

Руби

Обновить таблицы