Mettre à jour les feuilles de calcul

Ce document décrit les principes de base de l'utilisation de la spreadsheets.batchUpdate méthode sur la spreadsheets ressource.

Outre les données de valeur contenues dans ses cellules, une feuille de calcul inclut de nombreux autres types de données, tels que :

• Dimensions
• Formats et bordures des cellules
• Plages nommées
• Plages protégées
• Mise en forme conditionnelle

Il s'agit de quelques-uns des nombreux types de données qui contrôlent l'apparence et le fonctionnement d'une feuille de calcul. La méthode spreadsheets.batchUpdate vous permet de mettre à jour n'importe lequel de ces détails de la feuille de calcul. Les modifications sont regroupées dans un lot. Ainsi, si une requête échoue, aucune des autres modifications (potentiellement dépendantes) n'est écrite.

Si vous devez lire et écrire des données de valeur de cellule, vous pouvez également utiliser la spreadsheets.values ressource, comme décrit dans Lire et écrire des valeurs de cellule.

Types d'opération

Les opérations spécifiques compatibles avec la méthode spreadsheets.batchUpdate peuvent être regroupées dans les types d'opérations généraux suivants :

Catégorie	Description
Ajouter (et Dupliquer)	Ajouter de nouveaux objets (parfois basés sur d'anciens, comme dans les requêtes Dupliquer).
Mettre à jour (et Définir)	Mettre à jour certaines propriétés d'un objet, en laissant généralement les anciennes propriétés telles quelles (alors qu'une requête Définir écrase les données précédentes).
Supprimer	Supprimer des objets.

Ces catégories sont utilisées dans la section suivante pour décrire le comportement d'opérations spécifiques.

Opérations de mise à jour par lot

La méthode spreadsheets.batchUpdate fonctionne en prenant un ou plusieurs Request objets, chacun spécifiant un seul type de requête à effectuer. Le tableau suivant liste les types de requêtes de mise à jour groupée, regroupés par objet de ressource et type d'opération :

Objet	AJOUTER / DUPLIQUER	METTRE À JOUR / DÉFINIR	SUPPRIMER
BandedRange	AddBandingRequest	UpdateBandingRequest	DeleteBandingRequest
Borders		UpdateBordersRequest
Cells (y compris les valeurs, les formats, la validation des données, etc.)	InsertRangeRequest	RepeatCellRequest UpdateCellsRequest AppendCellsRequest	DeleteRangeRequest
ConditionalFormatRule	AddConditionalFormatRuleRequest	UpdateConditionalFormatRuleRequest	DeleteConditionalFormatRuleRequest
DataSource	AddDataSourceRequest	UpdateDataSourceRequest RefreshDataSourceRequest CancelDataSourceRefreshRequest	DeleteDataSourceRequest
DataValidationRule		SetDataValidationRequest
developerMetadata	CreateDeveloperMetadataRequest	UpdateDeveloperMetadataRequest	DeleteDeveloperMetadataRequest
Dimension (y compris DimensionRange)	InsertDimensionRequest AppendDimensionRequest	UpdateDimensionPropertiesRequest MoveDimensionRequest AutoResizeDimensionsRequest	DeleteDimensionRequest
DimensionGroup	AddDimensionGroupRequest	UpdateDimensionGroupRequest	DeleteDimensionGroupRequest
EmbeddedChart	AddChartRequest	UpdateChartSpecRequest UpdateEmbeddedObjectPositionRequest UpdateEmbeddedObjectBorderRequest	DeleteEmbeddedObjectRequest
FilterView (y compris BasicFilter)	AddFilterViewRequest DuplicateFilterViewRequest	UpdateFilterViewRequest SetBasicFilterRequest	ClearBasicFilterRequest DeleteFilterViewRequest
Fusionner des cellules (y compris 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

Requêtes de manipulation des données

Il existe également des requêtes supplémentaires qui imitent les actions de l'utilisateur pour manipuler les données :

Requête	Description
AutoFillRequest	Remplit automatiquement d'autres données en fonction des données existantes.
CopyPasteRequest	Copie des données d'une zone et les colle dans une autre.
CutPasteRequest	Coupe des données d'une zone et les colle dans une autre.
DeleteDuplicatesRequest	Supprime les lignes contenant des valeurs en double dans les colonnes spécifiées d'une plage de cellules.
FindReplaceRequest	Recherche et remplace des occurrences de texte par un autre texte.
PasteDataRequest	Colle des données (HTML ou délimitées) dans une feuille.
RandomizeRangeRequest	Randomise l'ordre des lignes dans une plage.
SortRangeRequest	Trie les données dans une plage.
TextToColumnsRequest	Convertit une colonne de texte en plusieurs colonnes de texte.
TrimWhitespaceRequest	Supprime les espaces blancs des cellules (tels que les espaces, les tabulations ou les nouvelles lignes).

Pour en savoir plus sur les limites de cellules et de lignes dans Google Sheets, consultez Fichiers que vous pouvez stocker dans Google Drive.

Utiliser des masques de champ pour mettre à jour des champs spécifiques

De nombreuses requêtes de mise à jour nécessitent un FieldMask. Un masque de champ est une liste de champs délimités par une virgule, utilisée pour indiquer les champs d'un objet à mettre à jour, tout en laissant tous les autres champs inchangés. L'utilisation d'un masque de champ empêche l'écrasement accidentel de champs qui ne sont pas spécifiés dans la requête.

Pour en savoir plus sur les masques de champ, consultez Mettre à jour avec un masque de champ.

L'exemple de code suivant montre comment utiliser les UpdateSpreadsheetPropertiesRequest pour ne mettre à jour que le titre d'une feuille de calcul :

Requête

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

Corps de la requête

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

Remplacez TITLE par le nouveau titre de la feuille de calcul.

Réponses de mise à jour groupée

Lors de la mise à jour d'une feuille de calcul, certains types de requêtes peuvent renvoyer des réponses. Elles sont renvoyées dans un tableau, chaque réponse occupant le même index que la requête correspondante. Certaines requêtes n'ont pas de réponse, et pour celles-ci, la réponse est vide.

En règle générale, les requêtes "Ajouter" ont des réponses qui renvoient des informations telles que l'ID de l'objet ajouté. Pour obtenir la liste des réponses acceptées, consultez Responses.

Exemple de code : mise à jour groupée d'une feuille de calcul

L'exemple de code suivant montre comment effectuer les actions suivantes :

1. Mettre à jour le titre de la feuille de calcul à l'aide de la variable title.
2. Rechercher et remplacer des valeurs de cellules dans la feuille de calcul à l'aide des variables find et replacement.

Apps Script

sheets/api/spreadsheet_snippets.gs

Afficher sur 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

Afficher sur 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

Afficher sur 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

Afficher sur 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

Afficher sur 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

Afficher sur 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

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