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

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

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

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

На этой странице описаны основы использования метода spreadsheets.batchUpdate . Если вам нужно прочитать и записать данные значений ячеек, вы также можете использовать ресурс spreadsheets.values описанный в разделе Чтение и запись значений ячеек .

Категории эксплуатации

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

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

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

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

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

Объект	ДОБАВИТЬ/ДУБЛИКИРОВАТЬ	ОБНОВЛЕНИЕ/УСТАНОВКА	УДАЛИТЬ
Свойства электронной таблицы		Запрос свойств обновленной таблицы
Листы	Аддлистрекуест Запрос дубликата листа	Запрос свойств обновления листа	Удалитьлистрекуест
Размеры (включая диапазон размеров )	Инсертдименсионрекуест Аппенддименсионреквест	Запрос UpdateDimensionPropertiesRequest MoveDimensionRequest Запрос AutoResizeDimensionsRequest	Деледименсионрекуест
Ячейки (включая значения, форматы, проверку данных и т. д.)		Повторить запрос ячейки UpdateCellsRequest Аппендселлсреквест
Именованные диапазоны	Адднамедранжерекуест	UpdateNamedRangeRequest	Делетенамедранжерекуест
Границы		Запрос обновления границ
Фильтры (включая представления фильтров и базовый фильтр )	Аддфильтрвиевреквест ДубликатФильтерПросмотрЗапрос	ОбновлениеФильтрПросмотрЗапрос SetBasicFilterRequest	ClearBasicFilterRequest
Проверка данных		SetDataValidationRequest
Правила условного форматирования	Аддкондиционалформатрулерекуест	UpdateConditionalFormatRuleRequest	Удалитькондиционалформатрулерекуест
Защищенные диапазоны	Аддпротектедранжерекуест	ОбновлениеProtectedRangeRequest	Удалитьпротектедранжерекуест
Встроенные объекты (включая диаграммы )	Аддчартреквест	ОбновлениеChartSpecRequest UpdateEmbeddedObjectPositionRequest	УдалитьEmbeddedObjectRequest
Слияния	Запрос слияния ячеек		UnmergeCellsRequest

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

• Запрос автозаполнения
• ВырезатьВставитьЗапрос
• КопироватьВставитьЗапрос
• НайтиЗаменитьЗапрос
• Вставить запрос данных
• TextToColumnsRequest
• SortRangeRequest

Пределы

Дополнительную информацию об ограничениях ячеек и строк в Google Таблицах см. в разделе Файлы, которые можно хранить на Google Диске .

Маски полей

Многие запросы «обновления» требуют масок полей. Это список полей, разделенных запятыми, позволяющий обновлять только определенные поля объекта, оставляя остальные поля неизменными. Маска поля * рассматривается как подстановочный знак и является сокращением для указания каждого поля в сообщении (это означает, что поле может вернуться к состоянию по умолчанию, если вы не укажете для него значение в запросе). Дополнительные сведения о масках полей см. в разделе Использование масок полей .

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

Запрос:

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

Тело запроса:

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

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

Ответы

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

Обычно запросы «добавить» имеют ответы, возвращающие такую ​​информацию, как идентификатор добавленного объекта. Список поддерживаемых ответов см. в разделе «Ответы» .

Пример

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

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

Скрипт приложений

листы/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);
  }
};

Ява

листы/фрагменты/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_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_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;
  }
}

PHP

листы/фрагменты/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_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")

Руби

листы/фрагменты/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."