Numeriblog Google Workspace Google Apps Script, la puissance des outils Google à portée de scripts BigQuery, Apps Script, Google Sheets : afficher une table BigQuery filtrée par des paramètres utilisateurs dans Google Sheets

BigQuery, Apps Script, Google Sheets : afficher une table BigQuery filtrée par des paramètres utilisateurs dans Google Sheets

Vous disposez des données dans des tables BigQuery et vous voulez les exploiter dans Google Sheets ? Voici une solution qui utilise Google Apps Script et le service BigQuery pour afficher le résultat d’une requête […]

personnes ont consulté cet article

3 minutes

Rédigé par Thierry Vanoffe - il y a 1 an

BigQuery, Apps Script, Google Sheets : afficher une table BigQuery filtrée par des paramètres utilisateurs dans Google Sheets

Vous disposez des données dans des tables BigQuery et vous voulez les exploiter dans Google Sheets ? Voici une solution qui utilise Google Apps Script et le service BigQuery pour afficher le résultat d’une requête d’une table BigQuery avec des filtres indiqués par des utilisateurs dans un fichier Google Sheets.

Prérequis :

Contraintes :

  • L’exploitation des données dans Google Sheets est possible tant que le total des cellules renvoyées par vos requêtes ne dépasse pas les 5 millions.
  • Faire attention aux rafraîchissements des données qui définissent votre facture pour l’utilisation de BigQuery. Pas de panique, vous pouvez gratuitement stocker 10 Go de données ainsi qu’analyser (via les requêtes) jusqu’à 1 To par mois.

Le code Google Apps Script se décompose en 3 parties :

  • Vous récupérez les valeurs à filtrer et vous créez la requête SQL pour BigQuery
  • Vous envoyez la requête et vous récupérez le résultat
  • Vous affichez le résultat dans l’onglet courant

Création de la requête

Vous récupérez les données à filtrer avec la variable “params” et bouclez dedans avec “forEach()”.

const params = sheet.getRange(6,2,1,sheet.getLastColumn() - 1).getValues()[0];
params.forEach((param, i) => { 
    ... alimenter la requête avec les filtres utilisateurs ...
}

Vous aurez besoin de comprendre la syntaxe de “WHERE Column IN (Value1,Value2, … )” qui indique les valeurs que l’on veut récupérer pour la colonne. Ainsi que la syntaxe de ORDER BY Column qui trie les données selon une colonne, en ascendant par défaut.

Avec le script vous allez donc créer les filtres :

  • Prénom IN (« Jean », »Marie »)
  • Avis IN (« 1/5″, »2/5 »)
  • ORDER BY ClientID

Avec ces filtres vous recevrez un résultat qui contient les prénoms Jean ou Marie et les avis 1/5 ou 2/5. Le résultat sera trié par les valeurs de ClientID de manière ascendante.

Envoi de la requête et récupération du résultat

Avec l’utilisation du service BigQuery dédié vous lancez votre requête et récupérez son résultat en une seule ligne :

let queryResults = BigQuery.Jobs.query(request, projectId);

Si vous êtes curieux, vous vous apercevrez que l’appel à l’API BigQuery avec BigQuery.Jobs.query retourne tout un tas d’informations liées à votre requête et à sa réponse. Voir la documentation BigQuery pour plus de détails.

Comme vous êtes curieux, vous avez trouvé la propriété jobComplete qui contient comme valeur un booléen. (Vrai ou Faux)

Cette valeur permet d’indiquer à votre script d’attendre tant que la requête côté BigQuery n’est pas terminée.

//Tant que la requête côté BigQuery n’est pas terminée on attend deux fois plus longtemps que la fois précédente
 const sleepTimeMs = 500;
 while (!queryResults.jobComplete) {
   Utilities.sleep(sleepTimeMs);
   sleepTimeMs *= 2;
   queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
 }

Sur une petite table de données comme dans cet exemple, la requête est presque instantanée mais sur des tables volumineuses il sera nécessaire de boucler plusieurs fois afin d’attendre le résultat final.

Affichage du résultat

Le plus dur est derrière vous, il vous reste seulement à adapter le résultat de la requête au format attendu par la méthode setValues de la classe Range

D’ailleurs, c’est quoi une méthode ? C’est comme une fonction, non ? Pas tout à fait !

//On adapte le résultat au format attendu par la méthode setValues()
const data = rows.map(row => row.f.map(el => el.v));
sheet.getRange(9, 2, data.length, data[0].length).setValues(data);

Attention, ne pas oublier de lier le dessin « LANCER LA REQUÊTE » à la fonction du script lancerRequete() !

Félicitations

Vous avez récupéré des filtres indiqués par un utilisateur, et vous avez requêté votre table BigQuery avec ces filtres pour afficher le résultat dans un onglet de votre fichier Google Sheets !

Pour aller plus loin, essayez de :

  • Créer une requête complexe
  • Récupérer les filtres utilisateurs via une page HTML qui contient un formulaire

Code

Voici le code complet de ce cas d’usage, si vous avez besoin de conseils pour l’implémenter et/ou le modifier, n’hésitez pas à prendre contact avec nous !

Fichier code.gs

function lancerRequete() { 
 const ss = SpreadsheetApp.getActiveSpreadsheet();
 const sheet = ss.getSheets()[1];
 const projectId = 'XXXXXXXXX';
 const datasetId = sheet.getRange("A1").getValue().replace(/ /g, '').split(":")[1];
 const tableId = sheet.getRange("A2").getValue().replace(/ /g, '').split(":")[1];
 const headers = sheet.getRange(5,2,1,sheet.getLastColumn() - 1).getValues()[0];
 const params = sheet.getRange(6,2,1,sheet.getLastColumn() - 1).getValues()[0];
 const orderByIdx = sheet.getRange(7,2,1,sheet.getLastColumn() - 1)
   .getValues()[0].findIndex(el => el === "X");
 const resultRow = 9;
 
 
 //On nettoie la zone où le résultat sera affiché
 sheet.getRange(resultRow,2, sheet.getLastRow() - (resultRow - 1), headers.length).clearContent();

 //Création de la requête SQL en ajoutant les filtres indiqués par l'utilisateur
 let query = `SELECT * FROM \`${projectId}.${datasetId}.${tableId}\` WHERE `;
 numParam = 0;
 params.forEach((param, i) => {
   if (param !== "") {
     if(numParam > 0) query += ` AND `;
     const multipleParams = param.split(",");
     if(multipleParams.length > 1){
       const temp = [];
       query += `${headers[i]} IN (`;
       multipleParams.forEach(param => {
         temp.push(`\"${param.trim()}\"`);
       });
       query += `${temp.join(",")})`;
       numParam += 1;
     }else{
       query += `${headers[i]} = \"${param.trim()}\" `;
       numParam += 1;
     }
   }
 });
 query += ` ORDER BY ${headers[orderByIdx]} ;`;
 
 //Envoi + récupération du résultat de la requête
 const request = {
   query: query,
   useLegacySql: false   
 };
 let queryResults = BigQuery.Jobs.query(request, projectId);
 const jobId = queryResults.jobReference.jobId;
 
 //Tant que la requête n’est pas terminée on attend deux fois plus longtemps que la fois précédente 
 const sleepTimeMs = 500;
 while (!queryResults.jobComplete) {
   Utilities.sleep(sleepTimeMs);
   sleepTimeMs *= 2;
   queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
 }
 
 //On adapte les valeurs de chaque ligne dans un format attendu pour setValues (array[][])
 const rows = queryResults.rows;
 if (rows) {
   const data = rows.map(row => row.f.map(el => el.v));
   sheet.getRange(9, 2, data.length, data[0].length).setValues(data);
 } else {
   Logger.log('Aucune ligne renvoyée.');
 }
}

Besoin d'un peu plus d'aide sur Google Forms ?

Des formateurs sont disponibles toute l'année pour vous accompagner et optimiser votre utilisation de Google Forms, que ce soit pour votre entreprise ou pour vos besoins personnels !

Découvrir nos formations Google Forms

Rédacteur

Photo de profil de l'auteur
Thierry Vanoffe

Thierry VANOFFE, consultant, formateur, coach Google Workspace CEO de Numericoach, leader de la formation Google Workspace en France. Passionné par Google, ce blog me permet de partager cette passion et distiller tutos, trucs, astuces, guides sur les outils Google. N'hésitez pas à me solliciter pour vos projets de formation.

S’abonner
Notification pour
guest
0 Commentaires
Commentaires en ligne
Afficher tous les commentaires