Ce site utilise des cookies à des fins d’analyse et pour améliorer votre expérience. En cliquant sur Accepter, vous consentez à notre utilisation des cookies. En savoir plus dans notre politique de confidentialité.

L'atelier informatique

,

Maîtrisez l’ajout de données dans Google Sheets avec l’API avancée et Apps Script

L’atelier informatique

·

Pour des opérations plus complexes et flexibles, le service avancé de l’API Google Sheets offre une alternative bien plus puissante : la méthode append. Voyons ensemble comment cette fonctionnalité peut transformer votre manière d’interagir avec vos feuilles de calcul.

Les avantages de la méthode append de l’API Sheets

Contrairement à son homologue de base, la méthode append du service avancé débloque plusieurs fonctionnalités clés :

  • Ajout de plusieurs lignes : Vous pouvez ajouter plusieurs lignes de données en une seule opération.
  • Insertion ou écrasement : Elle vous donne le choix entre insérer de nouvelles lignes (déplaçant les données existantes vers le bas) ou simplement écrire sur les cellules vides existantes.
  • Ciblage d’une plage spécifique : Vous pouvez définir une plage précise pour l’ajout5. C’est idéal si vous avez des données de synthèse ou des tableaux sous votre plage de données principale et que vous ne souhaitez pas les écraser6. La recherche de la dernière ligne se fera uniquement dans cette plage, ignorant le contenu des autres colonnes.
  • Formatage des données : Il est possible de choisir si Google Sheets doit interpréter les données comme si un utilisateur les saisissait manuellement (par exemple, en convertissant les formules) ou les insérer comme des données brutes.

Exemple de code complet

Avant de détailler chaque paramètre, voici une fonction complète que vous pouvez adapter. Elle montre comment ajouter un ensemble de nouvelles données à une feuille de calcul spécifique, en définissant une plage et en utilisant plusieurs options.

function appendDataWithAdvancedApi() {
  const SPREADSHEET_ID = "VOTRE_ID_DE_FEUILLE_DE_CALCUL_ICI"; // Remplacez par votre ID

  // Données à ajouter, sous forme de tableau à 2 dimensions.
  // Notez le mélange de nombres, chaînes de caractères et formules.
  const dataToAppend = [
    [94925, 17, "retrocecal", "15/02/2025"],
    [94300, 7, "=B21", "16/02/2025"], // Formule faisant référence à une autre cellule
    [30397, 8, "retrocecal", "=TODAY()"], // Formule dynamique
    [82188, "10", "pelvic", "20/02/2025"] // Nombre en tant que chaîne de caractères
  ];

  // La plage dans laquelle l'API cherchera la première ligne vide.
  // Cela permet d'ignorer les données en dehors de cette plage (ex: A46 et au-delà).
  const rangeToSearch = "Sheet1!A5:A45";

  // Création du corps de la requête (le "resource object")
  const valueRange = {
    values: dataToAppend
  };

  // Création du payload avec les paramètres optionnels
  const payload = {
    valueInputOption: "USER_ENTERED", // Traite les données comme une saisie manuelle
    insertDataOption: "INSERT_ROWS",  // Insère de nouvelles lignes
    includeValuesInResponse: false    // N'inclut pas les données ajoutées dans la réponse
  };

  try {
    // Appel de la méthode append de l'API
    const result = Sheets.Spreadsheets.Values.append(
      valueRange,
      SPREADSHEET_ID,
      rangeToSearch,
      payload
    );

    // Affiche la plage mise à jour dans les journaux d'exécution
    console.log("Données ajoutées avec succès dans la plage : %s", result.updates.updatedRange);

  } catch (e) {
    // Gère les erreurs potentielles de l'appel API
    console.error("Échec de l'ajout des lignes. Erreur : " + e.message);
  }
}

Anatomie de la méthode Sheets.Spreadsheets.Values.append

La structure de base de l’appel de la méthode est la suivante:

Sheets.Spreadsheets.Values.append({values: tableau_2d}, ID_de_la_feuille, plage_a_ajouter, objet_payload);

Décortiquons chaque paramètre.

  1. Le tableau de données (un objet) : Le premier paramètre n’est pas directement le tableau de données. Il s’agit d’un objet contenant une propriété values, qui elle-même contient le tableau à deux dimensions (2D) des données à ajouter.
  2. L’ID de la feuille de calcul : Le second paramètre est simplement l’identifiant de votre Google Sheet.
  3. La plage de destination : Ce troisième paramètre définit la plage dans laquelle l’API doit rechercher la première ligne vide pour y ajouter les données. Par exemple, si vos données se trouvent en A4:D24 et vos totaux en A44, vous pouvez spécifier une plage comme Sheet1!A5:A45 pour vous assurer que les nouvelles données sont ajoutées après la ligne 24 sans affecter les totaux.
  4. L’objet payload (Les paramètres) : Il s’agit d’un objet contenant des paramètres optionnels qui modifient le comportement de l’ajout de données.

Exploration des paramètres clés du payload

Parmi les différentes options disponibles, trois sont particulièrement importantes.

valueInputOption (Obligatoire)

Ce paramètre est obligatoire pour chaque appel. Il détermine comment les données sont interprétées.

  • "USER_ENTERED" : L’API traitera les données comme si elles étaient saisies manuellement par un utilisateur. Les chaînes de caractères qui ressemblent à des nombres seront converties en nombres, et les formules comme "=TODAY()" seront calculées.
  • "RAW" : Les données seront insérées telles quelles. Les formules apparaîtront comme du texte (précédées d’une apostrophe), et les nombres fournis sous forme de chaînes de caractères resteront du texte.

Exemple de configuration :

const payload = {
  valueInputOption: "USER_ENTERED" // ou "RAW"
};

insertDataOption (Optionnel)

Cette option vous donne un contrôle précis sur la manière dont les lignes sont ajoutées.

  • "OVERWRITE" : L’API écrit les données dans les lignes vides qui suivent la dernière ligne de contenu, sans ajouter de nouvelles lignes à la feuille.
  • "INSERT_ROWS" : L’API insère de nouvelles lignes juste après la dernière ligne de données, puis y place les nouvelles valeurs. Cela a pour effet de décaler vers le bas tout contenu situé en dessous de la zone d’insertion.

Exemple de configuration :

const payload = {
  valueInputOption: "USER_ENTERED",
  insertDataOption: "INSERT_ROWS" // ou "OVERWRITE"
};

includeValuesInResponse (Optionnel)

Par défaut, cette option est définie sur false. Si vous la définissez sur true, l’objet de réponse renvoyé par l’API inclura les valeurs que vous venez d’ajouter. Notez que cela peut ralentir légèrement votre script, car des données supplémentaires doivent être renvoyées.

Exemple de configuration :

const payload = {
  valueInputOption: "USER_ENTERED",
  includeValuesInResponse: true
};

Comprendre la réponse de l’API

Lorsque l’appel est réussi, la méthode append retourne un objet JSON contenant des informations sur l’opération. L’information la plus utile est probablement updatedRange. Cette propriété vous donne la plage exacte (en notation A1) où les données ont été ajoutées, ce qui vous permet d’enchaîner avec d’autres opérations, comme l’application d’un formatage sur cette nouvelle plage.

Exemple d’utilisation de la réponse :

const result = Sheets.Spreadsheets.Values.append(/* ... */);
const newRange = result.updates.updatedRange; // Ex: 'Sheet1!A25:D28'

// Vous pouvez maintenant utiliser cette plage
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
sheet.getRange(newRange).setBackground("#f9e9c1"); // Met en surbrillance les nouvelles données

Conclusion

Comme vous pouvez le voir, la méthode append de l’API Google Sheets est une solution beaucoup plus polyvalente et robuste pour ajouter des données via Apps Script. Elle offre un contrôle granulaire qui ouvre la voie à des automatisations plus intelligentes et plus complexes. Et bien sûr, cette flexibilité n’est pas limitée à Apps Script ; l’API peut être utilisée avec de nombreux autres langages de programmation.