Importer des données d'une API dans Google Sheets avec Apps Script

Votre logiciel de facturation, votre boutique en ligne ou votre outil de suivi propose une API, et vous recopiez encore ses chiffres à la main dans un tableau ? Avec Google Sheets et Apps Script, la feuille peut aller chercher elle-même les données et se mettre à jour toute seule. Voici un script complet, et ses points de vigilance.

Ce que fait le script

Il appelle une API qui renvoie une liste au format JSON, garde la clé d'accès à l'abri, ne retient que les champs qui vous intéressent, puis réécrit l'onglet API en une seule opération. Si l'API est en panne ou change de format, la feuille n'est pas abîmée et vous recevez un e-mail.

Prérequis

• Une API qui renvoie du JSON sous forme de tableau d'objets (par exemple [{"id": 1, "client": "Alice"}]) et une clé d'accès à transmettre en « Bearer ». Le script utilise une adresse fictive, api.exemple.com : remplacez-la par celle de votre service, indiquée dans sa documentation.
• Un script créé depuis la feuille (Extensions > Apps Script).
• La liste des champs du JSON que vous voulez récupérer, par exemple id, client, montant et statut.

Le code

Étape 1 : les réglages

L'adresse de l'API, l'onglet de destination et la liste des champs à reprendre. Cette liste sert aussi d'en-tête aux colonnes, et son ordre est celui des colonnes de la feuille.

// ---- Réglages à adapter ----
const URL_API = 'https://api.exemple.com/v1/commandes'; // adresse de votre API
const NOM_ONGLET = 'API';                               // onglet de destination
// Champs JSON à reprendre, dans l'ordre des colonnes (ils servent aussi d'en-têtes)
const COLONNES = ['id', 'client', 'montant', 'statut'];

Étape 2 : récupérer et écrire les données

Trois précautions. La clé est lue dans les propriétés du script, jamais dans une cellule : toute personne qui peut lire la feuille pourrait la copier. L'option muteHttpExceptions évite qu'Apps Script s'interrompe brutalement sur une erreur HTTP : on contrôle nous-mêmes le code de réponse et on affiche le début du message renvoyé, pour comprendre ce qui se passe. Enfin, on vérifie que la réponse est bien un tableau JSON avant de toucher à la feuille.

function importerDepuisApi() {
  // La clé est lue dans les propriétés du script, jamais dans la feuille
  const cle = PropertiesService.getScriptProperties().getProperty('API_KEY');
  if (!cle) {
    throw new Error('Propriété de script API_KEY manquante.');
  }

  const reponse = UrlFetchApp.fetch(URL_API, {
    method: 'get',
    headers: { Authorization: 'Bearer ' + cle },
    muteHttpExceptions: true // on gère nous-mêmes les codes d'erreur (401, 500...)
  });

  const code = reponse.getResponseCode();
  if (code !== 200) {
    throw new Error("L'API a répondu " + code + ' : ' + reponse.getContentText().slice(0, 200));
  }

  let donnees;
  try {
    donnees = JSON.parse(reponse.getContentText());
  } catch (erreur) {
    throw new Error("La réponse n'est pas du JSON valide : " + erreur.message);
  }
  if (!Array.isArray(donnees)) {
    throw new Error('Format inattendu : un tableau JSON était attendu.');
  }

  // Un objet JSON devient une ligne : on ne garde que les champs de COLONNES
  const lignes = donnees.map(function (objet) {
    return COLONNES.map(function (champ) {
      const valeur = objet[champ];
      return valeur === undefined || valeur === null ? '' : valeur;
    });
  });

  ecrireDansFeuille(lignes);
  return lignes.length;
}

function ecrireDansFeuille(lignes) {
  const classeur = SpreadsheetApp.getActiveSpreadsheet();
  const feuille = classeur.getSheetByName(NOM_ONGLET) || classeur.insertSheet(NOM_ONGLET);

  // On vide l'ancien contenu (en-tête excepté), puis on réécrit en un seul appel
  const derniereLigne = feuille.getLastRow();
  if (derniereLigne > 1) {
    feuille.getRange(2, 1, derniereLigne - 1, COLONNES.length).clearContent();
  }
  feuille.getRange(1, 1, 1, COLONNES.length).setValues([COLONNES]);
  if (lignes.length > 0) {
    feuille.getRange(2, 1, lignes.length, COLONNES.length).setValues(lignes);
  }
}

La seconde fonction efface l'ancien contenu (en-tête excepté) puis écrit toutes les lignes en un seul appel à setValues. C'est l'écriture cellule par cellule qui rend les scripts lents : autant écrire en bloc dès le départ.

Étape 3 : l'alerte et le déclencheur

Le déclencheur ne doit pas appeler directement importerDepuisApi : si l'API tombe la nuit, personne ne le saurait. La fonction ci-dessous intercepte l'erreur et vous envoie un e-mail. Avec un import toutes les heures, une longue panne produira un e-mail par heure : espacez le déclencheur si cela vous gêne.

// Fonction à brancher sur le déclencheur : elle intercepte l'erreur et vous prévient.
function importerAvecAlerte() {
  try {
    const nombre = importerDepuisApi();
    Logger.log(nombre + ' ligne(s) importée(s).');
  } catch (erreur) {
    Logger.log("Échec de l'import : " + erreur.message);
    MailApp.sendEmail(
      Session.getEffectiveUser().getEmail(),
      "Échec de l'import depuis l'API",
      'Le classeur "' + SpreadsheetApp.getActiveSpreadsheet().getName() +
        '" n\'a pas pu être mis à jour : ' + erreur.message
    );
  }
}

// À lancer UNE seule fois, à la main : programme un import toutes les heures.
function creerDeclencheurHoraire() {
  ScriptApp.getProjectTriggers().forEach(function (declencheur) {
    if (declencheur.getHandlerFunction() === 'importerAvecAlerte') {
      ScriptApp.deleteTrigger(declencheur);
    }
  });
  ScriptApp.newTrigger('importerAvecAlerte').timeBased().everyHours(1).create();
}

Installer le script pas à pas

1. Collez les trois blocs de code à la suite dans Code.gs, remplacez URL_API et la liste COLONNES, puis enregistrez.
2. Ouvrez les Paramètres du projet (roue dentée), descendez jusqu'à Propriétés du script, ajoutez une propriété nommée API_KEY ayant votre clé pour valeur, puis enregistrez.
3. Choisissez la fonction importerDepuisApi et cliquez sur Exécuter. Autorisez le script à la première exécution : il demande notamment l'accès aux services externes, puisqu'il contacte une adresse sur Internet.
4. Consultez l'onglet API, et le journal d'exécution en cas de problème.
5. Exécutez une fois creerDeclencheurHoraire pour programmer la mise à jour automatique.

Erreurs fréquentes

• Erreur 401 ou 403 : clé absente, expirée ou transmise sous une mauvaise forme. Certaines API attendent un autre en-tête (par exemple X-Api-Key) : la documentation de l'API fait foi.
• « Un tableau JSON était attendu » : beaucoup d'API enveloppent la liste dans un objet, du type {"data": [...]}. Ajoutez alors, juste après le bloc try / catch, la ligne donnees = donnees.data;.
• Cellules vides ou « [object Object] » : le champ demandé n'existe pas (faute de frappe, majuscule) ou contient lui-même un objet. Les champs imbriqués doivent être aplatis, par exemple en lisant objet.adresse.ville.
• Dates ou nombres transformés : Google Sheets interprète un texte qui ressemble à une date ou à un nombre comme s'il avait été saisi à la main. Mettez la colonne au format « Texte brut » pour conserver la valeur telle quelle.
• Seule la première page de résultats arrive : de nombreuses API découpent leurs réponses en pages. Il faut alors répéter l'appel en suivant la pagination décrite dans la documentation ; ce script ne le fait pas.

Quand faire appel à un développeur ?

Ce script suffit pour lire une liste simple à intervalle régulier. Il ne suffit plus quand l'API est paginée ou limite le nombre d'appels, quand les données doivent être croisées avec d'autres sources, quand il faut aussi écrire dans l'API et pas seulement lire, ou quand le volume rend Google Sheets lui-même trop lent. Apps Script impose par ailleurs ses propres limites (durée d'exécution, appels externes), à consulter dans la documentation de Google. Une feuille n'est pas une base de données : au-delà d'un certain usage, un vrai stockage devient préférable.

Deux points de sécurité à ne pas négliger : la clé d'API donne les droits de son titulaire, utilisez donc si possible une clé en lecture seule ; et toute personne autorisée à modifier le script peut consulter ses propriétés. Nous concevons ce type d'intégration : synchronisation entre vos outils, imports et exports planifiés, alertes. Voir notre page automatisation de tâches et intégrations sur mesure et, pour la démarche d'ensemble, l'automatisation sur mesure.