Lire et écrire les valeurs des cellules

Les feuilles de calcul peuvent comporter plusieurs feuilles, chacune pouvant contenir un nombre illimité de lignes ou de colonnes. Une cellule est un emplacement situé à l'intersection d'une ligne et d'une colonne spécifiques. Elle peut contenir une valeur de données. L'API Google Sheets fournit la ressource spreadsheets.values pour permettre la lecture et l'écriture de valeurs.

Cette page décrit les principes de base de l'utilisation de la ressource spreadsheets.values. Si vous devez insérer des lignes ou mettre à jour le format et d'autres propriétés dans une feuille, vous devez utiliser la méthode spreadsheets.batchUpdate décrite dans Mettre à jour des feuilles de calcul.

Méthodes

La ressource spreadsheets.values fournit les méthodes suivantes pour lire et écrire des valeurs, chacune pour une tâche spécifique:

Accès à la plage Lecture Écriture
Plage unique spreadsheets.values.get spreadsheets.values.update
Plusieurs plages spreadsheets.values.batchGet spreadsheets.values.batchUpdate
Ajouts spreadsheets.values.append

En général, il est recommandé de combiner plusieurs lectures ou mises à jour avec les méthodes batchGet et batchUpdate (respectivement), car cela améliore l'efficacité.

Vous trouverez des exemples de chacune de ces méthodes sur les pages d'exemples Lecture de base et Écriture de base. Pour afficher tous les exemples, consultez la page de présentation des exemples.

Lire

Pour lire les valeurs de données d'une feuille, vous avez besoin de l'ID de la feuille de calcul et de la notation A1 pour la plage. Spécifier la plage sans l'ID de la feuille (A1:B2) signifie que la requête s'exécute sur la première feuille de la feuille de calcul. Pour en savoir plus sur les ID de feuille de calcul et la notation A1, consultez la présentation de l'API Google Sheets.

Plusieurs paramètres de requête facultatifs contrôlent le format de la sortie:

Paramètre de format Valeur par défaut
majorDimension ROWS
valueRenderOption FORMATTED_VALUE
dateTimeRenderOption SERIAL_NUMBER

Notez que vous ne devez utiliser dateTimeRenderOption que si valueRenderOption n'est pas FORMATTED_VALUE.

La quantité de données renvoyées n'est pas limitée explicitement. Les erreurs ne renvoient aucune donnée. Les lignes et colonnes vides de fin sont omises.

Les méthodes d'extraction unique et groupée sont décrites ci-dessous. Pour obtenir des exemples d'opérations de lecture de base, consultez la section Lecture de base.

Lire une seule plage

Pour lire une seule plage de valeurs à partir d'une feuille de calcul, utilisez une requête spreadsheets.values.get:

Apps Script

sheets/api/spreadsheet_snippets.gs
/**
 * Gets the values of the cells in the specified range
 * @param {string} spreadsheetId id of the spreadsheet
 * @param {string} range specifying the start and end cells of the range
 * @returns {*} Values in the range
 */
Snippets.prototype.getValues = function(spreadsheetId, range) {
  // This code uses the Sheets Advanced Service, but for most use cases
  // the built-in method SpreadsheetApp.getActiveSpreadsheet()
  //     .getRange(range).getValues(values) is more appropriate.
  try {
    const result = Sheets.Spreadsheets.Values.get(spreadsheetId, range);
    const numRows = result.values ? result.values.length : 0;
    return result;
  } catch (err) {
    // TODO (developer) - Handle exception
    console.log('Failed with error %s', err.message);
  }
};

Java

sheets/snippets/src/main/java/GetValues.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.ValueRange;
import com.google.auth.http.HttpCredentialsAdapter;
import com.google.auth.oauth2.GoogleCredentials;
import java.io.IOException;
import java.util.Collections;

/* Class to demonstrate the use of Spreadsheet Get Values API */
public class GetValues {
  /**
   * Returns a range of values from a spreadsheet.
   *
   * @param spreadsheetId - Id of the spreadsheet.
   * @param range         - Range of cells of the spreadsheet.
   * @return Values in the range
   * @throws IOException - if credentials file not found.
   */
  public static ValueRange getValues(String spreadsheetId, String range) 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();

    ValueRange result = null;
    try {
      // Gets the values of the cells in the specified range.
      result = service.spreadsheets().values().get(spreadsheetId, range).execute();
      int numRows = result.getValues() != null ? result.getValues().size() : 0;
      System.out.printf("%d rows retrieved.", numRows);
    } 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 result;
  }
}

JavaScript

sheets/snippets/sheets_get_values.js
function getValues(spreadsheetId, range, callback) {
  try {
    gapi.client.sheets.spreadsheets.values.get({
      spreadsheetId: spreadsheetId,
      range: range,
    }).then((response) => {
      const result = response.result;
      const numRows = result.values ? result.values.length : 0;
      console.log(`${numRows} rows retrieved.`);
      if (callback) callback(response);
    });
  } catch (err) {
    document.getElementById('content').innerText = err.message;
    return;
  }
}

Node.js

sheets/snippets/sheets_get_values.js
/**
 * Gets cell values from a Spreadsheet.
 * @param {string} spreadsheetId The spreadsheet ID.
 * @param {string} range The sheet range.
 * @return {obj} spreadsheet information
 */
async function getValues(spreadsheetId, range) {
  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});
  try {
    const result = await service.spreadsheets.values.get({
      spreadsheetId,
      range,
    });
    const numRows = result.data.values ? result.data.values.length : 0;
    console.log(`${numRows} rows retrieved.`);
    return result;
  } catch (err) {
    // TODO (developer) - Handle exception
    throw err;
  }
}

PHP

sheets/snippets/src/SpreadsheetGetValues.php
use Google\Client;
use Google\Service\Drive;
use Google\Service\Sheets\BatchUpdateSpreadsheetRequest;
/**
 * get values of a particular spreadsheet(by Id and range).
 */
function getValues($spreadsheetId, $range)
    {   
        /* 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);
        $result = $service->spreadsheets_values->get($spreadsheetId, $range);
        try{
        $numRows = $result->getValues() != null ? count($result->getValues()) : 0;
        printf("%d rows retrieved.", $numRows);
        return $result;
    }
        catch(Exception $e) {
            // TODO(developer) - handle error appropriately
            echo 'Message: ' .$e->getMessage();
        }
    }

Python

sheets/snippets/sheets_get_values.py
import google.auth
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError


def get_values(spreadsheet_id, range_name):
  """
  Creates the batch_update 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)

    result = (
        service.spreadsheets()
        .values()
        .get(spreadsheetId=spreadsheet_id, range=range_name)
        .execute()
    )
    rows = result.get("values", [])
    print(f"{len(rows)} rows retrieved")
    return result
  except HttpError as error:
    print(f"An error occurred: {error}")
    return error


if __name__ == "__main__":
  # Pass: spreadsheet_id, and range_name
  get_values("1CM29gwKIzeXsAppeNwrc8lbYaVMmUclprLuLYuHog4k", "A1:C2")

Ruby

sheets/snippets/lib/spreadsheet_snippets.rb
result = service.get_spreadsheet_values(spreadsheet_id, range_name)
num_rows = result.values ? result.values.length : 0
puts "#{num_rows} rows received."

La réponse à cette requête est renvoyée sous la forme d'un objet ValueRange.

Lire plusieurs plages

Pour lire plusieurs plages de valeurs discontinues dans une feuille de calcul, utilisez une requête spreadsheets.values.batchGet qui vous permet de spécifier plusieurs plages à récupérer:

Apps Script

sheets/api/spreadsheet_snippets.gs
/**
 * Get the values in the specified ranges
 * @param {string} spreadsheetId spreadsheet's ID
 * @param {list<string>} _ranges The span of ranges
 * @returns {*} spreadsheet information and values
 */
Snippets.prototype.batchGetValues = (spreadsheetId,
  _ranges) => {
  // This code uses the Sheets Advanced Service, but for most use cases
  // the built-in method SpreadsheetApp.getActiveSpreadsheet()
  //     .getRange(range).getValues(values) is more appropriate.
  let ranges = [
    //Range names ...
  ];
  try {
    const result =
      Sheets.Spreadsheets.Values.batchGet(spreadsheetId, {ranges: ranges});
    return result;
  } catch (err) {
    // TODO (developer) - Handle exception
    console.log('Failed with error %s', err.message);
  }
};

Java

sheets/snippets/src/main/java/BatchGetValues.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.BatchGetValuesResponse;
import com.google.auth.http.HttpCredentialsAdapter;
import com.google.auth.oauth2.GoogleCredentials;
import java.io.IOException;
import java.util.Collections;
import java.util.List;

/* Class to demonstrate the use of Spreadsheet Batch Get Values API */
public class BatchGetValues {
  /**
   * Returns one or more ranges of values from a spreadsheet.
   *
   * @param spreadsheetId - Id of the spreadsheet.
   * @param ranges        - Range of cells of the spreadsheet.
   * @return Values in the range
   * @throws IOException - if credentials file not found.
   */
  public static BatchGetValuesResponse batchGetValues(String spreadsheetId,
                                                      List<String> ranges)
      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();

    BatchGetValuesResponse result = null;
    try {
      // Gets the values of the cells in the specified range.
      result = service.spreadsheets().values().batchGet(spreadsheetId)
          .setRanges(ranges).execute();
      System.out.printf("%d ranges retrieved.", result.getValueRanges().size());
    } 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 result;
  }
}

JavaScript

sheets/snippets/sheets_batch_get_values.js
function batchGetValues(spreadsheetId, _ranges, callback) {
  let ranges = [
    // Range names ...
  ];
  ranges = _ranges;
  try {
    gapi.client.sheets.spreadsheets.values.batchGet({
      spreadsheetId: spreadsheetId,
      ranges: ranges,
    }).then((response) => {
      const result = response.result;
      console.log(`${result.valueRanges.length} ranges retrieved.`);
      if (callback) callback(response);
    });
  } catch (err) {
    document.getElementById('content').innerText = err.message;
    return;
  }
}

Node.js

sheets/snippets/sheets_batch_get_values.js
/**
 * Batch gets cell values from a Spreadsheet.
 * @param {string} spreadsheetId The spreadsheet ID.
 * @param {string} _ranges The mock sheet range.
 * @return {obj} spreadsheet information
 */
async function batchGetValues(spreadsheetId, _ranges) {
  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});
  let ranges = [
    // Range names ...
  ];
  try {
    const result = await service.spreadsheets.values.batchGet({
      spreadsheetId,
      ranges,
    });
    console.log(`${result.data.valueRanges.length} ranges retrieved.`);
    return result;
  } catch (err) {
    // TODO (developer) - Handle exception
    throw err;
  }
}

PHP

sheets/snippets/src/SpreadsheetBatchGetValues.php
use Google\Client;
use Google\Service\Drive;
use Google\Service\Sheets;
/**
 * method to get a spreadsheet values in batch
 */

function batchGetValues($spreadsheetId)
    {
        /* 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{
            $ranges = 'Sheet1!A1:B2';
            $params = array(
                'ranges' => $ranges
            );
            //execute the request
            $result = $service->spreadsheets_values->batchGet($spreadsheetId, $params);
            printf("%d ranges retrieved.", count($result->getValueRanges()));
            return $result;
        }
        catch(Exception $e) {
            // TODO(developer) - handle error appropriately
            echo 'Message: ' .$e->getMessage();
          }
        }

Python

sheets/snippets/sheets_batch_get_values.py
import google.auth
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError


def batch_get_values(spreadsheet_id, _range_names):
  """
  Creates the batch_update 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)
    range_names = [
        # Range names ...
    ]
    result = (
        service.spreadsheets()
        .values()
        .batchGet(spreadsheetId=spreadsheet_id, ranges=range_names)
        .execute()
    )
    ranges = result.get("valueRanges", [])
    print(f"{len(ranges)} ranges retrieved")
    return result
  except HttpError as error:
    print(f"An error occurred: {error}")
    return error


if __name__ == "__main__":
  # Pass: spreadsheet_id, and range_name

  batch_get_values("1CM29gwKIzeXsAppeNwrc8lbYaVMmUclprLuLYuHog4k", "A1:C2")

Ruby

sheets/snippets/lib/spreadsheet_snippets.rb
range_names = [
  # Range names ...
]
result = service.batch_get_spreadsheet_values(spreadsheet_id,
                                              ranges: range_names)
puts "#{result.value_ranges.length} ranges retrieved."

La réponse à cette requête est renvoyée sous la forme d'un objet BatchGetValuesResponse contenant l'spreadsheetId et une liste d'objets ValueRange.

Écriture

Pour écrire dans une feuille, vous avez besoin de l'ID de la feuille de calcul, de la plage de cellules en notation A1 et des données que vous souhaitez écrire dans un objet de corps de requête approprié. Pour en savoir plus sur les ID de feuilles de calcul et la notation A1, consultez la présentation de l'API Google Sheets.

Les mises à jour nécessitent un paramètre ValueInputOption valide. Pour les mises à jour uniques, il s'agit d'un paramètre de requête obligatoire. Pour les mises à jour par lot, ce paramètre est obligatoire dans le corps de la requête. ValueInputOption contrôle la façon dont les données d'entrée doivent être interprétées et si les chaînes d'entrée sont analysées ou non, comme décrit dans le tableau suivant:

ValueInputOption Description
RAW L'entrée n'est pas analysée et est insérée sous forme de chaîne. Par exemple, la saisie "=1+2" place la chaîne "=1+2" dans la cellule, et non la formule. (Les valeurs autres que des chaînes, comme les valeurs booléennes ou numériques, sont toujours gérées en tant que RAW.)
USER_ENTERED L'entrée est analysée exactement comme si elle avait été saisie dans l'interface utilisateur de Sheets. Par exemple, "1er mars 2016" devient une date, et "=1+2" devient une formule. Les formats peuvent également être inférés.Ainsi, "100,15 $" devient un nombre au format devise.

Les méthodes de mise à jour unique et groupée sont décrites ci-dessous. Pour obtenir des exemples d'opérations d'écriture de base, consultez la section Écriture de base.

Écrire dans une seule plage

Pour écrire des données dans une seule plage, utilisez une requête spreadsheets.values.update:

Apps Script

sheets/api/spreadsheet_snippets.gs
/**
 * Updates the values in the specified range
 * @param {string} spreadsheetId spreadsheet's ID
 * @param {string} range the range of cells in spreadsheet
 * @param {string} valueInputOption determines how the input should be interpreted
 * @see
 * https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption
 * @param {list<list<string>>} _values list of string lists to input
 * @returns {*} spreadsheet with updated values
 */
Snippets.prototype.updateValues = (spreadsheetId, range,
  valueInputOption, _values) => {
  // 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.
  let values = [
    [
      // Cell values ...
    ]
    // Additional rows ...
  ];

  try {
    let valueRange = Sheets.newValueRange();
    valueRange.values = values;
    const result = Sheets.Spreadsheets.Values.update(valueRange,
      spreadsheetId, range, {valueInputOption: valueInputOption});
    return result;
  } catch (err) {
    // TODO (developer) - Handle exception
    console.log('Failed with error %s', err.message);
  }
};

Java

sheets/snippets/src/main/java/UpdateValues.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.UpdateValuesResponse;
import com.google.api.services.sheets.v4.model.ValueRange;
import com.google.auth.http.HttpCredentialsAdapter;
import com.google.auth.oauth2.GoogleCredentials;
import java.io.IOException;
import java.util.Collections;
import java.util.List;

/* Class to demonstrate the use of Spreadsheet Update Values API */
public class UpdateValues {
  /**
   * Sets values in a range of a spreadsheet.
   *
   * @param spreadsheetId    - Id of the spreadsheet.
   * @param range            - Range of cells of the spreadsheet.
   * @param valueInputOption - Determines how input data should be interpreted.
   * @param values           - List of rows of values to input.
   * @return spreadsheet with updated values
   * @throws IOException - if credentials file not found.
   */
  public static UpdateValuesResponse updateValues(String spreadsheetId,
                                                  String range,
                                                  String valueInputOption,
                                                  List<List<Object>> values)
      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();

    UpdateValuesResponse result = null;
    try {
      // Updates the values in the specified range.
      ValueRange body = new ValueRange()
          .setValues(values);
      result = service.spreadsheets().values().update(spreadsheetId, range, body)
          .setValueInputOption(valueInputOption)
          .execute();
      System.out.printf("%d cells updated.", result.getUpdatedCells());
    } 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 result;
  }
}

JavaScript

sheets/snippets/sheets_update_values.js
function updateValues(spreadsheetId, range, valueInputOption, _values, callback) {
  let values = [
    [
      // Cell values ...
    ],
    // Additional rows ...
  ];
  values = _values;
  const body = {
    values: values,
  };
  try {
    gapi.client.sheets.spreadsheets.values.update({
      spreadsheetId: spreadsheetId,
      range: range,
      valueInputOption: valueInputOption,
      resource: body,
    }).then((response) => {
      const result = response.result;
      console.log(`${result.updatedCells} cells updated.`);
      if (callback) callback(response);
    });
  } catch (err) {
    document.getElementById('content').innerText = err.message;
    return;
  }
}

Node.js

sheets/snippets/sheets_update_values.js
/**
 * Updates values in a Spreadsheet.
 * @param {string} spreadsheetId The spreadsheet ID.
 * @param {string} range The range of values to update.
 * @param {object} valueInputOption Value update options.
 * @param {(string[])[]} _values A 2d array of values to update.
 * @return {obj} spreadsheet information
 */
async function updateValues(spreadsheetId, range, valueInputOption, _values) {
  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});
  let values = [
    [
      // Cell values ...
    ],
    // Additional rows ...
  ];
  const resource = {
    values,
  };
  try {
    const result = await service.spreadsheets.values.update({
      spreadsheetId,
      range,
      valueInputOption,
      resource,
    });
    console.log('%d cells updated.', result.data.updatedCells);
    return result;
  } catch (err) {
    // TODO (Developer) - Handle exception
    throw err;
  }
}

PHP

sheets/snippets/src/SpreadsheetUpdateValues.php
use Google\Client;
use Google\Service\Drive;
use Google\Service\Sheets\ValueRange;


function updateValues($spreadsheetId, $range, $valueInputOption)
    {
        /* 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{
        $values = [["sample", 'values']];

        $body = new Google_Service_Sheets_ValueRange([
            'values' => $values
        ]);
        $params = [
            'valueInputOption' => $valueInputOption
        ];
        //executing the request
        $result = $service->spreadsheets_values->update($spreadsheetId, $range,
        $body, $params);
        printf("%d cells updated.", $result->getUpdatedCells());
        return $result;
    }
    catch(Exception $e) {
            // TODO(developer) - handle error appropriately
            echo 'Message: ' .$e->getMessage();
          }
    }

Python

sheets/snippets/sheets_update_values.py
import google.auth
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError


def update_values(spreadsheet_id, range_name, value_input_option, _values):
  """
  Creates the batch_update 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)
    values = [
        [
            # Cell values ...
        ],
        # Additional rows ...
    ]
    body = {"values": values}
    result = (
        service.spreadsheets()
        .values()
        .update(
            spreadsheetId=spreadsheet_id,
            range=range_name,
            valueInputOption=value_input_option,
            body=body,
        )
        .execute()
    )
    print(f"{result.get('updatedCells')} cells updated.")
    return result
  except HttpError as error:
    print(f"An error occurred: {error}")
    return error


if __name__ == "__main__":
  # Pass: spreadsheet_id,  range_name, value_input_option and  _values
  update_values(
      "1CM29gwKIzeXsAppeNwrc8lbYaVMmUclprLuLYuHog4k",
      "A1:C2",
      "USER_ENTERED",
      [["A", "B"], ["C", "D"]],
  )

Ruby

sheets/snippets/lib/spreadsheet_snippets.rb
values = [
  [
    # Cell values ...
  ]
  # Additional rows ...
]
data = [
  {
    range:  range_name,
    values: values
  },
  # Additional ranges to update ...
]
value_range_object = Google::Apis::SheetsV4::ValueRange.new(range:  range_name,
                                                            values: values)
result = service.update_spreadsheet_value(spreadsheet_id,
                                          range_name,
                                          value_range_object,
                                          value_input_option: value_input_option)
puts "#{result.updated_cells} cells updated."

Le corps de la requête de mise à jour doit être un objet ValueRange, mais le seul champ obligatoire est values. Si range est spécifié, il doit correspondre à la plage de l'URL. Dans ValueRange, vous pouvez éventuellement spécifier son majorDimension. Par défaut, ROWS est utilisé. Si COLUMNS est spécifié, chaque tableau interne est écrit dans une colonne au lieu d'une ligne.

Lors de la mise à jour, les valeurs sans données sont ignorées. Pour effacer les données, utilisez une chaîne vide ("").

Écrire plusieurs plages

Si vous souhaitez écrire plusieurs plages discontinues, vous pouvez utiliser une requête spreadsheets.values.batchUpdate:

Apps Script

sheets/api/spreadsheet_snippets.gs
/**
 * Updates the values in the specified range
 * @param {string} spreadsheetId spreadsheet's ID
 * @param {string} range range of cells of the spreadsheet
 * @param {string} valueInputOption determines how the input should be interpreted
 * @see
 * https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption
 * @param {list<list<string>>} _values list of string values to input
 * @returns {*} spreadsheet with updated values
 */
Snippets.prototype.batchUpdateValues =
  (spreadsheetId, range, valueInputOption,
    _values) => {
    // 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.
    let values = [
      [
        // Cell values ...
      ]
      // Additional rows ...
    ];

    try {
      let valueRange = Sheets.newValueRange();
      valueRange.range = range;
      valueRange.values = values;

      let batchUpdateRequest = Sheets.newBatchUpdateValuesRequest();
      batchUpdateRequest.data = valueRange;
      batchUpdateRequest.valueInputOption = valueInputOption;

      const result = Sheets.Spreadsheets.Values.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/BatchUpdateValues.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.BatchUpdateValuesRequest;
import com.google.api.services.sheets.v4.model.BatchUpdateValuesResponse;
import com.google.api.services.sheets.v4.model.ValueRange;
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 Values API */
public class BatchUpdateValues {
  /**
   * Set values in one or more ranges of spreadsheet.
   *
   * @param spreadsheetId    - Id of the spreadsheet.
   * @param range            - Range of cells of the spreadsheet.
   * @param valueInputOption - Determines how input data should be interpreted.
   * @param values           - list of rows of values to input.
   * @return spreadsheet with updated values
   * @throws IOException - if credentials file not found.
   */
  public static BatchUpdateValuesResponse batchUpdateValues(String spreadsheetId,
                                                            String range,
                                                            String valueInputOption,
                                                            List<List<Object>> values)
      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<ValueRange> data = new ArrayList<>();
    data.add(new ValueRange()
        .setRange(range)
        .setValues(values));

    BatchUpdateValuesResponse result = null;
    try {
      // Updates the values in the specified range.
      BatchUpdateValuesRequest body = new BatchUpdateValuesRequest()
          .setValueInputOption(valueInputOption)
          .setData(data);
      result = service.spreadsheets().values().batchUpdate(spreadsheetId, body).execute();
      System.out.printf("%d cells updated.", result.getTotalUpdatedCells());
    } 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 result;
  }
}

JavaScript

sheets/snippets/sheets_batch_update_values.js
function batchUpdateValues(spreadsheetId, range, valueInputOption, _values, callback) {
  let values = [
    [
      // Cell values ...
    ],
    // Additional rows ...
  ];
  values = _values;
  const data = [];
  data.push({
    range: range,
    values: values,
  });
  // Additional ranges to update.

  const body = {
    data: data,
    valueInputOption: valueInputOption,
  };
  try {
    gapi.client.sheets.spreadsheets.values.batchUpdate({
      spreadsheetId: spreadsheetId,
      resource: body,
    }).then((response) => {
      const result = response.result;
      console.log(`${result.totalUpdatedCells} cells updated.`);
      if (callback) callback(response);
    });
  } catch (err) {
    document.getElementById('content').innerText = err.message;
    return;
  }
}

Node.js

sheets/snippets/sheets_batch_update_values.js
/**
 * Batch Updates values in a Spreadsheet.
 * @param {string} spreadsheetId The spreadsheet ID.
 * @param {string} range The range of values to update.
 * @param {object} valueInputOption Value update options.
 * @param {(string[])[]} _values A 2d array of values to update.
 * @return {obj} spreadsheet information
 */
async function batchUpdateValues(
    spreadsheetId,
    range,
    valueInputOption,
    _values,
) {
  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});
  let values = [
    [
      // Cell values ...
    ],
    // Additional rows ...
  ];
  const data = [
    {
      range,
      values,
    },
  ];
  // Additional ranges to update ...
  const resource = {
    data,
    valueInputOption,
  };
  try {
    const result = await service.spreadsheets.values.batchUpdate({
      spreadsheetId,
      resource,
    });
    console.log('%d cells updated.', result.data.totalUpdatedCells);
    return result;
  } catch (err) {
    // TODO (developer) - Handle exception
    throw err;
  }
}

PHP

sheets/snippets/src/SpreadsheetBatchUpdateValues.php
/**
 * to update values in batch for a particular spreadsheet
 */
use Google\Client;
use Google\Service\Drive;
use Google\Service\Sheets;

function batchUpdateValues($spreadsheetId, $range, $valueInputOption)
    {
        /* 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);
        $values = [];
        try{

            $data[] = new Google_Service_Sheets_ValueRange([
                'range' => $range,
            'values' => $values
        ]);
        $body = new Google_Service_Sheets_BatchUpdateValuesRequest([
            'valueInputOption' => $valueInputOption,
            'data' => $data
        ]);
        $result = $service->spreadsheets_values->batchUpdate($spreadsheetId, $body);
        printf("%d cells updated.", $result->getTotalUpdatedCells());
        return $result;
    }

        catch(Exception $e) {
            // TODO(developer) - handle error appropriately
            echo 'Message: ' .$e->getMessage();
          }
    }

Python

sheets/snippets/sheets_batch_update_values.py
import google.auth
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError


def batch_update_values(
    spreadsheet_id, range_name, value_input_option, _values
):
  """
  Creates the batch_update 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)

    values = [
        [
            # Cell values ...
        ],
        # Additional rows
    ]
    data = [
        {"range": range_name, "values": values},
        # Additional ranges to update ...
    ]
    body = {"valueInputOption": value_input_option, "data": data}
    result = (
        service.spreadsheets()
        .values()
        .batchUpdate(spreadsheetId=spreadsheet_id, body=body)
        .execute()
    )
    print(f"{(result.get('totalUpdatedCells'))} cells updated.")
    return result
  except HttpError as error:
    print(f"An error occurred: {error}")
    return error


if __name__ == "__main__":
  # Pass: spreadsheet_id, range_name value_input_option and _values)
  batch_update_values(
      "1CM29gwKIzeXsAppeNwrc8lbYaVMmUclprLuLYuHog4k",
      "A1:C2",
      "USER_ENTERED",
      [["F", "B"], ["C", "D"]],
  )

Ruby

sheets/snippets/lib/spreadsheet_snippets.rb
values = [
  [
    # Cell values ...
  ]
  # Additional rows ...
]
data = [
  {
    range:  range_name,
    values: values
  },
  # Additional ranges to update ...
]
batch_update_values = Google::Apis::SheetsV4::BatchUpdateValuesRequest.new(
  data:               data,
  value_input_option: value_input_option
)
result = service.batch_update_values(spreadsheet_id, batch_update_values)
puts "#{result.total_updated_cells} cells updated."

Le corps de la requête de mise à jour par lot doit être un objet BatchUpdateValuesRequest, qui contient un ValueInputOption et une liste d'objets ValueRange (un pour chaque plage écrite). Chaque objet ValueRange spécifie son propre range, majorDimension et données d'entrée.

Ajouter des valeurs

Pour ajouter des données après une table de données dans une feuille, utilisez une requête spreadsheets.values.append:

Apps Script

sheets/api/spreadsheet_snippets.gs
/**
 * Appends values to the specified range
 * @param {string} spreadsheetId spreadsheet's ID
 * @param {string} range range of cells in the spreadsheet
 * @param valueInputOption determines how the input should be interpreted
 * @see
 * https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption
 * @param {list<string>} _values list of rows of values to input
 * @returns {*} spreadsheet with appended values
 */
Snippets.prototype.appendValues = (spreadsheetId, range,
  valueInputOption, _values) => {
  let values = [
    [
      // Cell values ...
    ]
    // Additional rows ...
  ];
  try {
    let valueRange = Sheets.newRowData();
    valueRange.values = values;

    let appendRequest = Sheets.newAppendCellsRequest();
    appendRequest.sheetId = spreadsheetId;
    appendRequest.rows = [valueRange];

    const result = Sheets.Spreadsheets.Values.append(valueRange, spreadsheetId,
      range, {valueInputOption: valueInputOption});
    return result;
  } catch (err) {
    // TODO (developer) - Handle exception
    console.log('Failed with error %s', err.message);
  }
};

Java

sheets/snippets/src/main/java/AppendValues.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.AppendValuesResponse;
import com.google.api.services.sheets.v4.model.ValueRange;
import com.google.auth.http.HttpCredentialsAdapter;
import com.google.auth.oauth2.GoogleCredentials;
import java.io.IOException;
import java.util.Collections;
import java.util.List;

/* Class to demonstrate the use of Spreadsheet Append Values API */
public class AppendValues {
  /**
   * Appends values to a spreadsheet.
   *
   * @param spreadsheetId    - Id of the spreadsheet.
   * @param range            - Range of cells of the spreadsheet.
   * @param valueInputOption - Determines how input data should be interpreted.
   * @param values           - list of rows of values to input.
   * @return spreadsheet with appended values
   * @throws IOException - if credentials file not found.
   */
  public static AppendValuesResponse appendValues(String spreadsheetId,
                                                  String range,
                                                  String valueInputOption,
                                                  List<List<Object>> values)
      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();

    AppendValuesResponse result = null;
    try {
      // Append values to the specified range.
      ValueRange body = new ValueRange()
          .setValues(values);
      result = service.spreadsheets().values().append(spreadsheetId, range, body)
          .setValueInputOption(valueInputOption)
          .execute();
      // Prints the spreadsheet with appended values.
      System.out.printf("%d cells appended.", result.getUpdates().getUpdatedCells());
    } 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 result;
  }
}

JavaScript

sheets/snippets/sheets_append_values.js
function appendValues(spreadsheetId, range, valueInputOption, _values, callback) {
  let values = [
    [
      // Cell values ...
    ],
    // Additional rows ...
  ];
  values = _values;
  const body = {
    values: values,
  };
  try {
    gapi.client.sheets.spreadsheets.values.append({
      spreadsheetId: spreadsheetId,
      range: range,
      valueInputOption: valueInputOption,
      resource: body,
    }).then((response) => {
      const result = response.result;
      console.log(`${result.updates.updatedCells} cells appended.`);
      if (callback) callback(response);
    });
  } catch (err) {
    document.getElementById('content').innerText = err.message;
    return;
  }
}

Node.js

sheets/snippets/sheets_append_values.js
/**
 * Appends values in a Spreadsheet.
 * @param {string} spreadsheetId The spreadsheet ID.
 * @param {string} range The range of values to append.
 * @param {object} valueInputOption Value input options.
 * @param {(string[])[]} _values A 2d array of values to append.
 * @return {obj} spreadsheet information
 */
async function appendValues(spreadsheetId, range, valueInputOption, _values) {
  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});
  let values = [
    [
      // Cell values ...
    ],
    // Additional rows ...
  ];
  const resource = {
    values,
  };
  try {
    const result = await service.spreadsheets.values.append({
      spreadsheetId,
      range,
      valueInputOption,
      resource,
    });
    console.log(`${result.data.updates.updatedCells} cells appended.`);
    return result;
  } catch (err) {
    // TODO (developer) - Handle exception
    throw err;
  }
}

PHP

sheets/snippets/src/SpreadsheetAppendValues.php
use Google\Client;
use Google\Service\Sheets;


function appendValues($spreadsheetId, $range, $valueInputOption)
{
    /* 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('https://www.googleapis.com/auth/spreadsheets');
    $service = new Google\Service\Sheets($client);
    try {
        $values = []; //add the values to be appended
        //execute the request
        $body = new Google_Service_Sheets_ValueRange([
            'values' => $values
        ]);
        $params = [
            'valueInputOption' => $valueInputOption
        ];
        $result = $service->spreadsheets_values->append($spreadsheetId, $range, $body, $params);
        printf("%d cells appended.", $result->getUpdates()->getUpdatedCells());
        return $result;
    } catch (Exception $e) {
        // TODO(developer) - handle error appropriately
        echo 'Message: ' . $e->getMessage();
    }

Python

sheets/snippets/sheets_append_values.py
import google.auth
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError


def append_values(spreadsheet_id, range_name, value_input_option, _values):
  """
  Creates the batch_update 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)

    values = [
        [
            # Cell values ...
        ],
        # Additional rows ...
    ]
    body = {"values": values}
    result = (
        service.spreadsheets()
        .values()
        .append(
            spreadsheetId=spreadsheet_id,
            range=range_name,
            valueInputOption=value_input_option,
            body=body,
        )
        .execute()
    )
    print(f"{(result.get('updates').get('updatedCells'))} cells appended.")
    return result

  except HttpError as error:
    print(f"An error occurred: {error}")
    return error


if __name__ == "__main__":
  # Pass: spreadsheet_id, range_name value_input_option and _values)
  append_values(
      "1CM29gwKIzeXsAppeNwrc8lbYaVMmUclprLuLYuHog4k",
      "A1:C2",
      "USER_ENTERED",
      [["F", "B"], ["C", "D"]],
  )

Ruby

sheets/snippets/lib/spreadsheet_snippets.rb
values = [
  [
    # Cell values ...
  ],
  # Additional rows ...
]
value_range = Google::Apis::SheetsV4::ValueRange.new(values: values)
result = service.append_spreadsheet_value(spreadsheet_id,
                                          range_name,
                                          value_range,
                                          value_input_option: value_input_option)
puts "#{result.updates.updated_cells} cells appended."

Le corps de la requête de mise à jour doit être un objet ValueRange, mais le seul champ obligatoire est values. Si range est spécifié, il doit correspondre à la plage de l'URL. Dans ValueRange, vous pouvez éventuellement spécifier son majorDimension. Par défaut, ROWS est utilisé. Si COLUMNS est spécifié, chaque tableau interne est écrit dans une colonne au lieu d'une ligne.

La plage d'entrée permet de rechercher des données existantes et de trouver une "table" dans cette plage. Les valeurs sont ajoutées à la ligne suivante de la table, en commençant par la première colonne de la table. Prenons l'exemple de Sheet1, qui se présente comme suit:

A B C D E
1 x y z
2 x y z
3
4 x y
5 y z
6 x y z
7

La feuille contient deux tables: A1:C2 et B4:D6. Les valeurs ajoutées commenceraient à B7 pour toutes les entrées range suivantes:

  • Sheet1, car il examinera toutes les données de la feuille et déterminera que la table située à B4:D6 est la dernière.
  • B4 ou C5:D5, car ils figurent tous les deux dans la table B4:D6.
  • B2:D4, car la dernière table de la plage est la table B4:D6 (bien qu'elle contienne également la table A1:C2).
  • A3:G10, car la dernière table de la plage est la table B4:D6 (bien qu'elle commence avant et se termine après).

Les entrées range suivantes ne commenceraient pas à écrire à B7:

  • A1 commencerait à écrire à A3, car il s'agit de la table A1:C2.
  • E4 commencerait à écrire à E4, car il ne figure dans aucune table. (A4 commencerait également à écrire à A4 pour les mêmes raisons.)

Vous pouvez également choisir d'écraser les données existantes après une table ou d'insérer de nouvelles lignes pour les nouvelles données. Par défaut, l'entrée remplace les données après le tableau. Pour écrire les nouvelles données dans de nouvelles lignes, utilisez InsertDataOption et spécifiez insertDataOption=INSERT_ROWS.

Pour en savoir plus sur les limites de cellules et de lignes dans Sheets, consultez Fichiers pouvant être stockés dans Google Drive.