Sheets 
Agenda 
Forms 
Apps Script 
Gmail 
Chat 
Meet 
Contacts 
Chrome 
Sites 
Looker Studio 
Slides 
Docs 
Drive 
AppSheet 
Android 
Chromebook 
Equipements Google 
Google Cloud Platform 
Google Photos 
Maps 
Youtube 
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 […]
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 :
- Posséder un projet Google Cloud Platform (GCP)
- Disposer de données dans une table BigQuery (Cf. article 1 BigQuery, Google Apps Script, Google Sheets : visualiser des données volumineuses dans Google Sheets)
- Avoir des connaissances de base en SQL
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- Articles connexes
- Plus de l'auteur
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.
