1. Introduction
L'objectif de cet atelier de programmation est de vous aider à comprendre comment écrire une fonction Cloud pour réagir à l'importation d'un fichier CSV dans Cloud Storage, lire son contenu et l'utiliser pour mettre à jour une feuille de calcul Google Sheets à l'aide de l'API Sheets.
Cela peut être considéré comme l'automatisation d'une étape d'importation manuelle au format CSV. Vous pourrez ainsi analyser les données (peut-être produites par une autre équipe) dans une feuille de calcul dès qu'elles seront disponibles.
Voici à quoi ressemble l'implémentation :
2. Préparation
Configuration de l'environnement d'auto-formation
- Connectez-vous à Cloud Console, puis créez un projet ou réutilisez un projet existant. (Si vous n'avez pas encore de compte Gmail ou G Suite, vous devez en créer un.)
Mémorisez l'ID du projet. Il s'agit d'un nom unique permettant de différencier chaque projet Google Cloud (le nom ci-dessus est déjà pris ; vous devez en trouver un autre). Il sera désigné par le nom PROJECT_ID tout au long de cet atelier de programmation.
- Vous devez ensuite activer la facturation dans Cloud Console pour pouvoir utiliser les ressources Google Cloud.
L'exécution de cet atelier de programmation est très peu coûteuse, voire sans frais. Veillez à suivre les instructions de la section "Nettoyer" qui indique comment désactiver les ressources afin d'éviter les frais une fois ce tutoriel terminé. Les nouveaux utilisateurs de Google Cloud peuvent participer au programme d'essai sans frais pour bénéficier d'un crédit de 300 $.
3. Créer et configurer une feuille de calcul Google Sheets, puis activer l'API
Commençons par créer une feuille de calcul (elle peut appartenir à n'importe quel utilisateur). Une fois créé, n'oubliez pas son identifiant. Il sera utilisé comme variable d'environnement pour la fonction que nous allons écrire :
Dans la console GCP, activez l'API Google Sheets sur le projet que vous venez de créer en accédant à la section "API et services", puis à "Bibliothèque d'API" :
Dans la section "IAM et administration ", accédez à"Comptes de service " et notez l'adresse e-mail du compte de service App Engine par défaut. Elle doit se présenter au format your-project-id@appspot.gserviceaccount.com. Vous pouvez également créer votre propre compte de service dédié à cette action.
Enfin, accordez simplement à ce compte de service des droits de modification sur votre feuille de calcul à l'aide du bouton "Partager" :
Avec cette configuration, nous pouvons maintenant écrire notre fonction Cloud et la configurer pour qu'elle utilise ce compte de service. Il pourra écrire dans la feuille de calcul que nous venons de créer.
4. Créer un bucket de stockage
Créons le bucket que notre fonction Cloud surveillera pour trouver de nouveaux fichiers CSV.
Dans la console, utilisez le menu de gauche pour accéder à "Stockage"… :
... et créez un bucket nommé csv2sheet-POSTFIX (remplacez le POSTFIX par un nom unique) avec tous les autres paramètres définis sur leurs valeurs par défaut :
5. Créer la fonction Cloud
Nous pouvons maintenant créer une fonction Cloud appelée csv2sheet qui est déclenchée lors de l'importation de fichiers dans un bucket Cloud Storage spécifique. Le code sera écrit en Node.js 8 avec des fonctions asynchrones à l'aide de l'éditeur intégré directement dans la console Cloud :
Assurez-vous de définir le déclencheur sur "Cloud Storage" et d'ajuster le nom du bucket à celui que vous avez créé à l'étape précédente.
Mettez également à jour le point d'entrée de la fonction que nous allons écrire sur csv2sheet :
Modifiez maintenant le corps de la fonction comme suit :
- utiliser les API Cloud Storage et Sheets ;
- marquez la fonction
csv2sheetcommeasync. - Récupérez le
fileNameà partir des métadonnées de l'événement Cloud Storage et dérivez un nom pour la nouvelle feuille que nous allons créer :
const {google} = require("googleapis");
const {Storage} = require("@google-cloud/storage")
exports.csv2sheet = async (data, context) => {
var fileName = data.name;
// basic check that this is a *.csv file, etc...
if (!fileName.endsWith(".csv")) {
console.log("Not a .csv file, ignoring.");
return;
}
// define name of new sheet
const sheetName = fileName.slice(0, -4);
// TODO!
};
L'utilisation de async est requise ici pour utiliser await, comme nous le verrons dans un instant.
Voici quelques options importantes à prendre en compte lors de la création de cette fonction (cliquez sur le lien "Plus" en bas de l'écran) :
- Utilisez le menu déroulant pour sélectionner le compte de service mentionné ci-dessus.
- Définissez une variable d'environnement appelée
SPREADSHEET_IDqui doit correspondre à la feuille de calcul que vous avez créée précédemment :
Pour terminer la configuration, voici le contenu de package.json avec les API Cloud Storage et Google Sheets comme deux dépendances que nous allons utiliser (utilisez l'onglet "PACKAGE.JSON" de l'éditeur intégré de la console) :
{
"name": "csv2sheet",
"version": "0.0.42",
"dependencies": {
"googleapis": "^51.0.0",
"@google-cloud/storage": "^5.0.1"
}
}
Une fois que vous avez tout configuré comme décrit, cliquez sur "Créer". Au bout d'une minute, votre fonction devrait être créée et déployée.
6. Configurer l'authentification et l'API Sheets
Avant d'écrire d'autres codes dans notre fonction Cloud à l'aide de l'éditeur intégré, nous devons bloquer la création d'une API Google Client avec les bons niveaux d'accès Storage et Sheets (n'oubliez pas que cela fait partie d'une fonction async).
Dans l'éditeur de fonctions de la console, cliquez sur "MODIFIER" et ajoutez le code suivant au corps de votre fonction csv2sheet :
// block on auth + getting the sheets API object
const auth = await google.auth.getClient({
scopes: [
"https://www.googleapis.com/auth/spreadsheets",
"https://www.googleapis.com/auth/devstorage.read_only"
]
});
À partir de là, nous pouvons créer un client API Sheets :
const sheetsAPI = google.sheets({version: 'v4', auth});
7. Utiliser l'API Sheets pour créer une feuille de calcul vide
Avec un client Sheets API, nous pouvons créer une simple feuille de calcul dans notre document. Mais avant d'aller plus loin, voici une brève note sur le vocabulaire :
- Une feuille de calcul est le document en lui-même. Elle est référencée par son identifiant (mentionné ci-dessus et visible dans l'URL du document).
- Une feuille est l'un des onglets du document. Elle peut être référencée par son nom (le nom de l'onglet) ou par un identifiant généré lors de sa création.
Dans cette optique, voici une fonction utilisant le client de l'API Sheets pour créer une feuille vide à la position 2 (généralement après la feuille par défaut "Feuille1"), avec 26 colonnes et 2 000 lignes, et la première ligne figée (ajoutez-la à votre fonction à l'aide de l'éditeur intégré) :
function addEmptySheet(sheetsAPI, sheetName) {
return new Promise((resolve, reject) => {
const emptySheetParams = {
spreadsheetId: process.env.SPREADSHEET_ID,
resource: {
requests: [
{
addSheet: {
properties: {
title: sheetName,
index: 1,
gridProperties: {
rowCount: 2000,
columnCount: 26,
frozenRowCount: 1
}
}
}
}
]
}
};
sheetsAPI.spreadsheets.batchUpdate( emptySheetParams, function(err, response) {
if (err) {
reject("The Sheets API returned an error: " + err);
} else {
const sheetId = response.data.replies[0].addSheet.properties.sheetId;
console.log("Created empty sheet: " + sheetId);
resolve(sheetId);
}
}
);
});
}
Notez qu'au lieu de coder en dur la référence à la feuille de calcul, nous nous appuyons sur la variable d'environnement SPREADSHEET_ID créée précédemment.
Nous devons nous souvenir de sheetId pour les prochaines requêtes adressées à cette feuille en particulier. De plus, le nom de la feuille doit être unique. Si une feuille portant le nom sheetName existe déjà, la création échouera.
La fonction batchUpdate de l'API Sheets est un moyen courant d'interagir avec les documents. Elle est décrite ici.
8. Lire les données d'un fichier CSV de stockage
Maintenant que nous avons un emplacement pour stocker nos données, développons davantage notre fonction cloud dans l'éditeur intégré et utilisons l'API Cloud Storage pour récupérer les données réelles du fichier qui vient d'être importé et les stocker dans une chaîne :
function readCSVContent(sheetsAPI, file, sheetName) {
return new Promise((resolve, reject) => {
const storage = new Storage();
let fileContents = new Buffer('');
storage.bucket(file.bucket).file(file.name).createReadStream()
.on('error', function(err) {
reject('The Storage API returned an error: ' + err);
})
.on('data', function(chunk) {
fileContents = Buffer.concat([fileContents, chunk]);
})
.on('end', function() {
let content = fileContents.toString('utf8');
console.log("CSV content read as string : " + content );
resolve(content);
});
});
}
9. Remplir la feuille nouvellement créée
Il est maintenant temps de remplir la feuille que nous avons créée à l'aide de la même API cliente Sheets et des données que nous venons de collecter. Nous allons également profiter de cette occasion pour ajouter du style aux colonnes de la feuille (en modifiant la taille de la police de la première ligne et en la mettant en gras) :
function populateAndStyle(sheetsAPI, theData, sheetId) {
return new Promise((resolve, reject) => {
// Using 'batchUpdate' allows for multiple 'requests' to be sent in a single batch.
// Populate the sheet referenced by its ID with the data received (a CSV string)
// Style: set first row font size to 11 and to Bold. Exercise left for the reader: resize columns
const dataAndStyle = {
spreadsheetId: process.env.SPREADSHEET_ID,
resource: {
requests: [
{
pasteData: {
coordinate: {
sheetId: sheetId,
rowIndex: 0,
columnIndex: 0
},
data: theData,
delimiter: ","
}
},
{
repeatCell: {
range: {
sheetId: sheetId,
startRowIndex: 0,
endRowIndex: 1
},
cell: {
userEnteredFormat: {
textFormat: {
fontSize: 11,
bold: true
}
}
},
fields: "userEnteredFormat(textFormat)"
}
}
]
}
};
sheetsAPI.spreadsheets.batchUpdate(dataAndStyle, function(err, response) {
if (err) {
reject("The Sheets API returned an error: " + err);
} else {
console.log(sheetId + " sheet populated with " + theData.length + " rows and column style set.");
resolve();
}
});
});
}
Ce code doit être ajouté à notre fonction Cloud, qui est maintenant terminée à 99 %.
Notez que les données et le style sont combinés en plusieurs requests dans un seul appel batchUpdate de l'API Sheets. La mise à jour est ainsi plus efficace et atomique.
Notez également que nous définissons une plage de modification qui correspond à la taille de la feuille que nous avons créée. Cela signifie que le contenu qui dépasse 26 colonnes (la valeur columnCount utilisée lors de la création de la feuille) échouera avec ce code spécifique.
Si tout se passe bien, vous pouvez à ce stade :
- enregistrer la fonction modifiée
- déposer un fichier CSV dans le bucket ;
- Les données correspondantes s'affichent alors dans votre feuille de calcul.
10. Assembler toutes les briques et tester le flux
Les appels aux fonctions que nous venons d'évoquer peuvent être effectués en tant qu'appels bloquants successifs dans la fonction csv2sheet d'origine :
const sheetId = await addEmptySheet(sheetsAPI, sheetName);
const theData = await readCSVContent(sheetsAPI, data, sheetName);
await populateAndStyle(sheetsAPI, theData, sheetId);
Si vous avez besoin du code source complet de la fonction, il est disponible ici (il est probablement plus facile de tout obtenir en un seul ensemble).
Une fois que tout est en place, il vous suffit d'importer un fichier CSV dans le bucket approprié. Votre feuille de calcul sera alors mise à jour avec une nouvelle feuille contenant le contenu du fichier. Voici un exemple de fichier CSV si vous n'en avez pas sous la main.
Essayez d'importer plusieurs fichiers dans le bucket pour voir ce qui se passe.
11. Et voilà ! Il est temps de démanteler l'infrastructure
Je plaisante, il n'y a pas d'infrastructure à détruire, tout a été fait sans serveur !
Si vous le souhaitez, vous pouvez supprimer la fonction Cloud et le bucket que vous avez créés, ou même l'intégralité du projet.
12. Étape suivante
Cet atelier de programmation est terminé. Vous avez appris à écouter les importations dans un bucket Cloud Storage dans une fonction Cloud pour mettre à jour une feuille de calcul Google à l'aide de l'API appropriée.
Pour aller plus loin :
- Consultez les guides pratiques Cloud Functions (qui incluent certaines bonnes pratiques).
- Suivez l'un des tutoriels Cloud Functions.
- En savoir plus sur l'API Google Sheets
Si vous avez rencontré des problèmes avec cet atelier de programmation, n'hésitez pas à les signaler en utilisant le lien en bas à gauche.
Vos commentaires nous sont précieux.