Accès simple à Google Spreadsheet (node.js)

Un simple module Node.js pour la lecture et la manipulation de données dans Google Spreadsheets.

• avec ou sans autorisation
• API basée sur les cellules - lecture, écriture, mises à jour en masse
• API basée sur les lignes - lire, mettre à jour, supprimer
• gestion des feuilles de calcul - ajouter, supprimer, redimensionner, changer le titre

Installation

Usage basique

Cet exemple est simplement destiné à montrer certaines des choses que vous pouvez faire.

Notez (les commentaires) que beaucoup d'appels sont réellement asynchrones, mais j'ai ignoré l'affichage des rappels pour rendre l'exemple plus court. Vous n'avez pas non plus besoin d'utiliser async pour le contrôle de flux, mais je le trouve utile.

var GoogleSpreadsheet =  require ( ' google-tableur ' );

var  async  =  require ( ' async ' );

// clé de feuille de calcul est l'identifiant long dans l'URL des feuilles 

var doc =  new  GoogleSpreadsheet ( ' <clé de feuille de calcul> ' );

feuille de var ;

Async . series ([

   function  setAuth ( step ) {

     // voir les notes ci-dessous pour les instructions d'authentification! 

    var creds =  require ( ' ./google-generated-creds.json ' );

     // OU, si vous ne pouvez pas enregistrer le fichier localement (comme sur heroku) 

    var creds_json = {

      client_email :  " yourserviceaccountemailhere@google.com " ,

      private_key :  ' votre longue clé privée ici '

    }

    doc . useServiceAccountAuth (creds, step);

  },

  fonction  getInfoAndWorksheets ( étape ) {

     doc . getInfo ( function ( err , info ) {

       console . log ( ' Doc chargé: ' + info . titre + ' par ' + info . auteur . email );

      fiche =  info . feuilles de travail [ 0 ];

      console . log ( ' sheet 1: ' + sheet . title + '  ' + sheet . rowCount + ' x ' + sheet . colCount );

      step ();

    });

  },

  fonction  workingWithRows ( étape ) {

     // Google fournit des options de requête 

    feuille . getRows ({

      offset :  1 ,

      limite :  20 ,

      orderby :  ' col2 ' 

    }, fonction ( err , lignes ) {

       console . log ( ' Lecture ' + lignes . Longueur + ' lignes ' );

      // la ligne est un objet avec des clés définies par les en-têtes de colonne 

      lignes [ 0 ]. colname  =  ' new val ' ;

      lignes [ 0 ]. save (); // c'est asynchrone

      // effacer une rangée de 

      rangées [ 0 ]. del ();  // c'est asynchrone

      step ();

    });

  },

  fonction  workingWithCells ( step ) {

     sheet . getCells ({

       ' min-row ' :  1 ,

       ' max-row ' :  5 ,

       ' return-empty ' :  true 

    }, fonction ( err , cellules ) {

       var cellule = cellules [ 0 ];

       console . log ( ' Cellule R ' + cellule . rangée+ ' C ' + cellule . col + ' = ' + cellule . valeur );

      // les cellules ont une valeur, numericValue et une 

      cellule de formule . valeur  ==  ' 1 '

       cellule . valeur numérique  ==  1 ;

      cellule . formule  ==  ' = ROW () ' ;

      // mettre à jour `valeur` est" intelligent "et gère généralement les choses pour votre 

      cellule . valeur  =  123 ;

      cellule . valeur  =  ' = A1 + B2 '

       cellule . save (); // async

      // Les mises à jour en masse facilitent la mise à jour de plusieurs cellules à la fois 

      [ 0 ]. valeur  =  1 ;

      cellules [ 1 ]. valeur  =  2 ;

      cellules [ 2 ]. formule  =  ' = A1 + B1 ' ;

      feuille . bulkUpdateCells (cellules); // async

      step ();

    });

  },

  fonction  managementSheets ( step ) {

     doc . addWorksheet ({

      titre :  ' ma nouvelle feuille ' 

    }, fonction ( err , feuille ) {

      // change la feuille de titre d'une 

      feuille . setTitle ( ' nouveau titre ' ); // async

      // redimensionne une 

      feuille . resize ({rowCount :  50 , colCount :  20 }); // async

      feuille . setHeaderRow ([ ' nom ' , ' âge ' , ' téléphone ' ]); // async

      // suppression d' une feuille de 

      feuille . del (); // async

      step ();

    });

  }

], fonction ( err ) {

     if (err) {

       console . log ( ' Error: ' + err);

    }

});

Authentification

IMPORTANT: Google a récemment déconseillé son accès à ClientLogin (nom d'utilisateur + mot de passe). La situation est donc un peu plus compliquée maintenant. Les anciennes versions de ce module le supportaient, alors sachez que les choses ont changé.

Accès non authentifié (accès en lecture seule sur les documents publics)

Par défaut, ce module effectue des requêtes non authentifiées et ne peut donc accéder qu'aux feuilles de calcul "publiques".

Le guide de référence et de développement de l'API de données Google Spreadsheets est un peu ambigu quant à la manière dont vous accédez à une feuille de calcul publique "publiée".

Si vous souhaitez utiliser une feuille de calcul Google sans authentification, celle-ci doit non seulement être visible sur le Web, mais elle doit également avoir été explicitement publiée à l'aide de l'option de menu "Fichier> Publier sur le Web" dans l'interface graphique de Google Spreadsheet.

De nombreuses feuilles apparemment "publiques" n'ont pas également été "publiées", ce qui peut entraîner une certaine confusion.

Les demandes non authentifiées permettent la lecture, mais pas l'écriture dans les feuilles. Pour écrire sur une feuille, vous devez vous authentifier.

Compte de service (méthode recommandée)

Il s'agit d'une méthode oauth à deux pieds conçue pour être "un compte appartenant à votre application plutôt qu'à un utilisateur final individuel". Utilisez cette option pour une application qui doit accéder à un ensemble de documents auxquels vous avez pleinement accès. ( lire plus )

Instructions d'installation

1. Accédez à la console Google Developers.
2. Sélectionnez votre projet ou créez-en un nouveau (puis sélectionnez-le)
3. Activer l'API de lecteur pour votre projet

• Dans la barre latérale à gauche, développez API et authentification > API.
• Recherchez "drive"
• Cliquez sur "Drive API"
• cliquez sur le bouton bleu "Activer l'API"

1. Créez un compte de service pour votre projet

• Dans la barre latérale à gauche, développez API et authentification > Informations d' identification.
• Cliquez sur le bouton bleu "Ajouter des informations d'identification"
• Sélectionnez l'option "Compte de service"
• Cochez la case "Fournir une nouvelle clé privée".
• Sélectionnez l'option de type de clé "JSON"
• Cliquez sur le bouton bleu "Créer"
• votre fichier de clé JSON est généré et téléchargé sur votre ordinateur ( c'est la seule copie! )
• notez l'adresse e-mail de votre compte de service (également disponible dans le fichier de clé JSON)

1. Partagez la ou les doc (s) avec votre compte de service en utilisant le courrier électronique mentionné ci-dessus.

Limitations de l'API Google

L'API de Google est quelque peu limitatif. Les appels sont passés à deux API conçues différemment, l'une pour traiter les cellules et l'autre pour gérer les lignes. Ces API vous permettent de gérer les données de vos feuilles, mais vous ne pouvez pas modifier le formatage des cellules.

Limites des API basées sur les lignes

L'API basée sur les lignes suppose que la "ligne d'en-tête" (première ligne) de votre feuille est définie. Ils ont des limites sur les noms de colonnes qu’ils accepteront - toutes en minuscules sans symboles ni espaces. Si les valeurs de votre feuille ne suivent pas leurs règles, leur API adaptera la clé qu'elle vous renvoie réellement. Je recommande simplement de suivre leurs règles pour éviter toute confusion.

Vous pouvez définir une valeur de formule dans une cellule à l'aide de l'API basée sur les lignes, mais lors de la lecture de lignes, vous ne pouvez pas accéder à la formule, ni même savoir qu'il en existe une dans la cellule. Toutes les cellules avec des formules renverront la valeur calculée de la formule. Si vous essayez de mettre à jour une ligne, la cellule avec une formule sera écrasée à sa valeur calculée.

IMPORTANT L'API basée sur les lignes suppose également qu'il n'y a pas de lignes vides dans votre feuille. Si une ligne est complètement vide, vous ne pourrez accéder à aucune ligne après la ligne vide à l'aide de l'API basée sur les lignes.

API

Ce module suit les conventions de rappel "normales" des noeuds:

• Chaque méthode qui prend un rappel le prend comme dernier paramètre
• Chaque callback sera appelé avec l'erreur (ou null) comme premier paramètre
• Certaines méthodes ont des paramètres optionnels

GoogleSpreadsheet

La classe principale qui représente une feuille de calcul entière.

new GoogleSpreadsheet(sheet_id, [auth], [options])

Créez un nouvel objet Google Tableur.

• sheet_id - l'ID de la feuille de calcul (à partir de son URL)
• auth - (facultatif) un jeton d'authentification existant
• options - (optionnel)
  • visibility- par défaut publicsi anonyme
  • projection- par défaut valuessi anonyme

GoogleSpreadsheet.useServiceAccountAuth(account_info, callback)

Utilise une adresse électronique de compte de service et une clé publique / privée pour créer un jeton à utiliser pour les demandes authentifiées. Normalement, vous transmettez simplement le résultat de la demande du fichier json que Google génère pour vous lorsque vous créez un compte de service.

Voir la section "Authentification" pour plus d'informations.

Si vous utilisez heroku ou un autre environnement dans lequel vous ne pouvez pas enregistrer un fichier local, vous pouvez simplement transmettre un objet avec

• client_email - l'adresse e-mail de votre compte de service
• private_key - la clé privée trouvée dans le fichier JSON

En interne, cette opération utilise un client JWT pour générer un nouveau jeton d'authentification valide pour votre compte de service d'une durée d'une heure. Le jeton sera automatiquement régénéré à son expiration.

NOTE SPÉCIALE POUR LES UTILISATEURS HEROKU

1. Enregistrez votre clé privée dans un fichier texte
2. Remplacer \npar les sauts de ligne réels
3. Remplacer \u003dpar=
4. heroku config: add GOOGLE_PRIVATE_KEY = "$ (cat votrefichier.txt)"

GoogleSpreadsheet.setAuthToken(id)

Utilisez un jeton d’authentification déjà créé pour toutes les demandes futures.

GoogleSpreadsheet.getInfo(callback)

Obtenir des informations sur la feuille de calcul. Appels en passant un objet contenant:

• id - l'URL / id renvoyés par Google
• title - le titre du document
• updated - dernière date et heure mise à jour
• author - info d'authentification dans un objet
  • name - nom de l'auteur
  • email - email de l'auteur
• worksheets- un tableau d' SpreadsheetWorksheetobjets (voir ci-dessous)

GoogleSpreadsheet.getRows(worksheet_id, options, callback)

Obtenez un tableau d'objets de ligne à partir de la feuille.

• worksheet_id - l'index de la feuille à lire (index commence à 1)
• options (optionnel)
  • offset - commence à lire à partir de la ligne #
  • limit - nombre maximum de lignes à lire en une fois
  • orderby - clé de colonne à commander par
  • reverse - résultats inversés
  • query- envoyer une requête structurée pour les lignes ( plus d'infos )
• callback(err, rows) - sera appelé avec un tableau d'objets SpreadsheetRow (voir ci-dessous)

REMARQUE L' reverseoption fonctionne uniquement avec orderby. Cela ne fonctionnera pas pour inverser le classement par défaut. Il s'agit d'un bug connu dans l'API de Google.

GoogleSpreadsheet.addRow(worksheet_id, new_row, callback)

Ajouter une seule ligne à la feuille.

• worksheet_id - l'index de la feuille à ajouter (l'index commence à 1)
• new_row - objet clé-valeur à ajouter - les clés doivent correspondre à la ligne d'en-tête de votre feuille
• callback(err, row) - sera appelé avec le nouveau SpreadsheetRow (voir ci-dessous)

GoogleSpreadsheet.getCells(worksheet_id, options, callback)

Obtenez un tableau d'objets de cellules.

• worksheet_id - l'index de la feuille à ajouter (l'index commence à 1)
• options (optionnel)
  • min-row - range range min (utilise # # visible à gauche)
  • max-row - rangée maximum
  • min-col - gamme de colonnes min (utilise des chiffres, pas des lettres!)
  • max-col - plage de colonne max
  • return-empty - inclure des cellules vides (booléens)

GoogleSpreadsheet.addWorksheet(options, callback)

Ajouter une nouvelle feuille de calcul à la doc.

• options (optionnel)
  • title - titre de la nouvelle feuille, doit être unique dans la doc (default = 'Worksheet {timestamp}')
  • rowCount - nombre de lignes (par défaut = 50)
  • colCount - nombre de colonnes (par défaut = 20)
  • headers - tableau de clés de chaîne à mettre dans la première ligne

GoogleSpreadsheet.removeWorksheet(sheet, callback)

Supprimer une feuille de calcul du document - par id, index ou objet SpreadsheetWorksheet

• sheet - peut être un objet SpreadsheetWorksheet, l'id de la feuille ou l'index (commence à 1)

SpreadsheetWorksheet

Représente une seule "feuille" de la feuille de calcul. Ce sont les différents onglets / pages visibles au bas de l'interface Google Sheets.

Ce sont les objets de feuille retournés comme worksheetslors de l'appel GoogleSpreadsheet.getInfo. De nombreux appels sont accessibles à partir de l'objet Spreadsheet principal en passant un ID de feuille (voir ci-dessus), mais certaines fonctionnalités ne sont disponibles que sur l'objet Feuille de travail car il nécessite diverses URL connues uniquement après avoir récupéré les feuilles pour effectuer des demandes.

Propriétés:

• url - l'URL de la feuille
• id - l'ID de la feuille
• title - le titre (visible sur les onglets dans l'interface de google)
• rowCount - Nombre de rangées
• colCount - le nombre de colonnes

SpreadsheetWorksheet.getRows(options, callback)

Voir au dessus.

SpreadsheetWorksheet.getCells(options, callback)

Voir au dessus.

SpreadsheetWorksheet.addRow(new_row, callback)

Voir au dessus.

GoogleSpreadsheet.bulkUpdateCells(cells, callback)

Faites une mise à jour en bloc sur les cellules.

• cells - un tableau d'objets SpreadsheetCell à sauvegarder

SpreadsheetWorksheet.del(callback)

Enlevez cette feuille du doc.

SpreadsheetWorksheet.setHeaderRow(values, callback)

Définir la première ligne de la feuille

• values - tableau de valeurs de chaîne à mettre dans la première ligne de la feuille

SpreadsheetWorksheet.clear(callback)

Efface tout le contenu de la feuille

SpreadsheetWorksheet.resize(options, callback)

Définir les dimensions de la feuille

• options
  • rowCount - Nombre de rangées
  • colCount - le nombre de colonnes

SpreadsheetWorksheet.setTitle(title, callback)

Définir le titre de la feuille

• title - nouveau titre pour la feuille de calcul

SpreadsheetRow

Représente une seule ligne d'une feuille.

Ceux-ci sont renvoyés de l'appel GoogleSpreadsheet.getRowset SpreadsheetWorksheet.getRows.

Vous pouvez traiter la ligne comme un objet javascript normal. Les clés d'objet proviendront de la ligne d'en-tête de votre feuille. Cependant, l'API Google modifie un peu les noms pour les simplifier. Il est plus simple de commencer par utiliser toutes les touches minuscules.

Voir les limitations ci-dessus pour des notes sur l'API Google basée sur les lignes!

SpreadsheetRow.save( callback )

Enregistre toutes les modifications apportées aux valeurs de la ligne.

SpreadsheetRow.del( callback )

Supprime la ligne de la feuille.

SpreadsheetCell

Représente une seule cellule de la feuille. L'utilisation de cellules est le seul moyen de lire et de modifier les formules de votre feuille.

Propriétés:

• id - l'identifiant de la cellule
• row - la rangée dans laquelle se trouve cette cellule
• col - la colonne dans laquelle se trouve cette cellule
• value - la valeur de la cellule sous forme de chaîne
• formula- la formule présente dans la cellule, par exemple =SUM(A3:B3)(le cas échéant)
• numericValue - la valeur de la cellule sous forme de nombre (le cas échéant)

IMPORTANT :

• Vous pouvez modifier value, numericValueou formula, et tout fonctionnera comme prévu
• Vous pouvez enregistrer une cellule en appelant saveou en effectuant une mise à jour en bloc.
• Définir valueou numericValuesur une cellule contenant une formule effacera la formule
• Définir une formulavaleur effacera les valueet numericValueet après la sauvegarde, les valeurs seront mises à jour

SpreadsheetCell.save(callback)

Enregistre la valeur ou la formule actuelle

SpreadsheetCell.del(callback)

Effacer la cellule - appels internes seulement .setValue('', callback)

SpreadsheetCell.setValue(val, callback)

Définit la valeur et l'enregistre (juste pour plus de commodité)

Autres possibilités et tâches

• obtenir la liste des feuilles de calcul disponibles pour un utilisateur authentifié
• plus d'options d'authentification

Liens

• https://developers.google.com/google-apps/spreadsheets/

Licence

node-google-spreadsheets est un logiciel du domaine public gratuit et non encombré. Pour plus d'informations, voir le fichier UNLICENSE joint.