1. Introduction
L'objectif de cet atelier de programmation est de vous apprendre à écrire une fonction Cloud qui réagit à une importation de fichier CSV dans Cloud Storage, à en lire le 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 "importation au format CSV" manuelle. étape. Cela vous permettrait d'analyser les données (peut-être produites par une autre équipe) dans une feuille de calcul dès qu'elles sont disponibles.
L'implémentation se présente comme suit :
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 gratuite. 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 et activer l'API
Commençons par créer un document Sheets (cette feuille peut appartenir à n'importe quel utilisateur). Une fois le code créé, notez son identifiant. elle sera utilisée comme variable d'environnement pour la fonction que nous allons écrire :
Dans la console GCP, activez l'API Google Sheets dans le projet que vous venez de créer en accédant à la section "API et services". puis sur "Bibliothèque d'API" :
Dans la section administrateur" accédez à "Comptes de service" et notez l'adresse e-mail du compte de service App Engine par défaut. Il doit se présenter sous la forme your-project-id@appspot.gserviceaccount.com. Bien entendu, 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" bouton :
Cette configuration nous permet d'écrire notre fonction Cloud et de la configurer pour utiliser 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 POSTFIX par quelque chose d'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 se déclenche 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 :
Veillez à définir le déclencheur sur "Cloud Storage" et d'ajuster le nom du bucket en fonction de celui que vous avez créé à l'étape précédente.
Mettez également à jour le point d'entrée de la fonction que nous sommes sur le point d'écrire dans csv2sheet :
Remplacez maintenant le corps de la fonction par :
- utiliser les API Cloud Storage et Sheets
- marquer la fonction
csv2sheetcommeasync - Récupérez
fileNameà partir des métadonnées d'événement Cloud Storage et obtenez 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!
};
Comme nous le verrons plus tard, async est obligatoire pour utiliser await.
Voici quelques options importantes lors de la création de cette fonction (cliquez sur le lien « Plus » en bas de l'écran) :
- Dans le menu déroulant, sélectionnez le compte de service mentionné ci-dessus.
- Définissez une variable d'environnement nommée
SPREADSHEET_IDqui doit correspondre au document Sheets que vous avez créé précédemment :
Pour terminer la configuration, voici le contenu package.json avec les API Cloud Storage et Google Sheets comme deux dépendances que nous allons utiliser (dans 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 indiqué, cliquez sur "Créer" ! Au bout d'une courte minute, votre fonction devrait être créée et déployée.
6. Configurer l'authentification et l'API Sheets
Avant d'écrire du code supplémentaire dans notre fonction Cloud à l'aide de l'éditeur intégré, nous devons bloquer la création d'une API cliente Google avec les champs d'application Storage et Sheets appropriés (n'oubliez pas que cela fait partie d'une fonction async).
Dans l'éditeur de fonctions de la console, cliquez sur MODIFIER. 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 vide
Avec un client API Sheets, nous pouvons créer une nouvelle feuille simple dans notre document, mais avant d'aller plus loin, voici une remarque rapide sur le vocabulaire:
- Une feuille de calcul correspond au document réel, référencée par son identifiant (décrit 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 un identifiant généré lors de la création de la feuille.
En gardant cela à l'esprit, voici une fonction qui utilise le client API Sheets pour créer une feuille vide en position 2 (généralement après la feuille par défaut "Feuille1"), avec 26 colonnes, 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 mémoriser le sheetId pour les requêtes ultérieures adressées à cette feuille spécifique. De plus, le nom de la feuille doit être unique. Si une feuille nommée sheetName existe déjà, la création échouera.
La fonction batchUpdate de l'API Sheets est un moyen courant d'interagir avec des documents. Pour en savoir plus, cliquez ici.
8. Lire les données d'un fichier CSV de stockage
Maintenant que nous avons un emplacement pour vider nos données, développons la fonction Cloud dans l'éditeur intégré et utilisons l'API Cloud Storage pour récupérer les données à partir 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. Insérer des données dans la feuille que vous venez de créer
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 profiterons également de cette occasion pour ajouter un style aux colonnes de la feuille (en modifiant la taille de la police de la ligne supérieure 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();
}
});
});
}
Vous devez ajouter ce code à notre fonction Cloud, qui est désormais terminée à 99 %.
Notez que les données et les styles sont combinés sous la forme de plusieurs requests dans un seul appel batchUpdate de l'API Sheets. Cela permet une mise à jour plus efficace et atomique.
Notez également que nous définissons une plage d'édition correspondant à 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 particulier.
Si tout se passe comme prévu, vous pouvez maintenant:
- enregistrer la fonction mise à jour
- déposer un fichier CSV dans le bucket
- voir les données correspondantes dans votre feuille de calcul !
10. Assembler le tout et tester le flux
Les appels aux fonctions dont nous venons de parler peuvent être effectués en tant qu'appels de blocage 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, cliquez ici pour le consulter (il est probablement plus facile de l'obtenir dans un seul ensemble).
Une fois que tout est en place, il vous suffit d'importer un fichier CSV dans le bucket approprié et de regarder votre feuille de calcul être mise à jour avec une nouvelle feuille avec le contenu du fichier. Voici un exemple de fichier CSV si vous n'en avez pas à portée de main.
Essayez d'importer plusieurs fichiers dans le bucket pour voir ce qui se passe.
11. Et voilà ! Il est temps de supprimer l'infrastructure
Je plaisante, il n'y a pas d'infrastructure à détruire, tout cela s'est fait sans serveur !
Si vous le souhaitez, vous pouvez supprimer la fonction Cloud et le bucket que vous avez créé, voire l'intégralité du projet.
12. Étape suivante
Cet atelier de programmation vous explique comment écouter des importations dans un bucket Cloud Storage dans une fonction Cloud afin de mettre à jour une feuille de calcul Google Sheets à l'aide de l'API appropriée.
Pour aller plus loin :
- Consultez les guides d'utilisation de Cloud Functions (qui incluent quelques bonnes pratiques).
- Suivez l'un des tutoriels Cloud Functions.
- Explorez plus en détail l'API Google Sheets.
Si vous avez rencontré des problèmes avec cet atelier de programmation, n'hésitez pas à les signaler en cliquant sur le lien en bas à gauche.
Vos commentaires sont les bienvenus.