Numeriblog Google Workspace Google Apps Script, la puissance des outils Google à portée de scripts BigQuery, Google Apps Script, Google Sheets : comment télécharger un fichier Excel (ou un fichier de données CSV) de plus de 5 millions de cellules dans une table BigQuery avec Apps Script

BigQuery, Google Apps Script, Google Sheets : comment télécharger un fichier Excel (ou un fichier de données CSV) de plus de 5 millions de cellules dans une table BigQuery avec Apps Script

L’outil BigQuery vous permet de manipuler des données volumineuses et de les exploiter avec de nombreux outils interconnectables. De plus, lors du téléchargement d’un fichier de plus de 5 Mo vers Google Drive via Apps […]

personnes ont consulté cet article

5 minutes

Rédigé par Thierry Vanoffe - il y a 3 ans et modifié le 27/07/2023 à 09:17

Ce que vous allez découvrir

  • Prérequis :
  • Téléchargement du fichier de données : côté client
  • Import des données dans une nouvelle table BigQuery : côté serveur
  • Félicitations ! 
  • Pour aller plus loin, essayez de :
  • Code

BigQuery, Google Apps Script, Google Sheets : comment télécharger un fichier Excel (ou un fichier de données CSV) de plus de 5 millions de cellules dans une table BigQuery avec Apps Script

L’outil BigQuery vous permet de manipuler des données volumineuses et de les exploiter avec de nombreux outils interconnectables. De plus, lors du téléchargement d’un fichier de plus de 5 Mo vers Google Drive via Apps Script, vous devez mettre en place un téléchargement continuable qui permet de reprendre un téléchargement involontairement arrêté. 

Si vous êtes dans l’incapacité d’exploiter un fichier de données (Excel ou autre) dans Google Workspace à cause de sa taille et de la limite de 5 millions de cellules par fichier Google Sheets, vous pouvez intégrer ces données dans BigQuery dont la limite est en pétaoctets.

Rappel :  1 pétaoctet  = 1024 teraoctets = 1 048 576 gigaoctets

Prérequis :

  • Posséder un projet Google Cloud Platform
  • Avoir créé un ensemble de données (dataset) dans BigQuery
  • Avoir sauvegardé un fichier Excel en CSV ou avoir à disposition un fichier de données CSV
  • Créer un nouveau Google Sheets (astuce : taper sheet.new dans la barre URL de votre navigateur), renommer un onglet “Params” et y ajouter l’ID du dossier Google Drive qui recevra le fichier dans la cellule B2

Le code Apps Script est divisé en deux parties : client (front end) et serveur (back end).

Le déroulement du script est le suivant :

  • Un utilisateur lance le script depuis le fichier Google Sheets, puis il sélectionne un fichier CSV via la barre latérale HTML
  • Le fichier CSV est téléchargé vers le dossier Google Drive dont vous avez renseigné l’ID dans la cellule B2 de l’onglet “Params”
  • Les données du fichier CSV sont intégrées dans une nouvelle table BigQuery

Téléchargement du fichier de données : côté client

Le fichier index.html

En implémentant la librairie JavaScript ResumableUploadForGoogleDrive_js de l’incroyable tanaikech vous pouvez télécharger des fichiers de plus de 5 Mo vers Google Drive.

Pour permettre à votre script d’utiliser cette librairie, il vous faut ajouter le code ci-dessous dans la balise <head> du fichier index.html

<script src="https://cdn.jsdelivr.net/gh/tanaikech/ResumableUploadForGoogleDrive_js@master/resumableupload_js.min.js"></script>

Indiquez au script (toujours côté client), qu’une fois le téléchargement du fichier dans Google Drive terminé, il faut lancer l’exécution du chargement du fichier dans BigQuery via une fonction côté serveur :

google.script.run
  .withSuccessHandler(updateDone)
  .updateBigQueryTable(resource.fileName);

Import des données dans une nouvelle table BigQuery : côté serveur

Le reste de l’exécution du script se déroule désormais côté serveur.

Image de référence

Le fichier server.gs

Vous récupérez le fichier téléchargé précédemment

function updateBigQueryTable(fileName){
 try{
   if(folderId === undefined) getParams();
      const folder = DriveApp.getFolderById(folderId);
      const files = folder.getFiles();
      while (files.hasNext()){
         const file = files.next();
         const driveFileName = file.getName();
         if (fileName === driveFileName) return loadCsv(file);
       }
      return `File ${fileName} not found in folder ${folder.getName()}`;
 }catch(e){
   Logger.log(e);
   return e.message;
 } 
}

Il faut ensuite le charger dans une nouvelle table BigQuery via le service Apps Script BigQuery qui communique pour vous avec l’API Google BigQuery.

Configurez les paramètres pour :

  • Créer une table dans BigQuery avec :

BigQuery.Tables.insert(table, projectId, datasetId)

  • Charger les données dans la table fraîchement créée avec :

BigQuery.Jobs.insert(job, projectId, data)

Veillez à bien configurer le “job”de chargement avec :

  •  autodetect: true : pour indiquer une détection automatique du schéma de la nouvelle table BigQuery pour le nom de chaque en-entête ainsi que le type des valeurs

 fieldDelimiter: « ; » : pour modifier le caractère de délimitation des valeurs présent dans le fichier CSV. Le point-virgule est utilisé lors des exports CSV depuis Excel.

Félicitations ! 

Vous venez de créer votre propre ETL (Extract Transform Load) semi-automatique. Vous pouvez désormais exploiter ces données avec Data Studio, Connected Sheets, Google Sheets & Apps Script et bien d’autres encore…

Pour aller plus loin, essayez de :

  • Automatiser la récupération des fichiers de données
  • Mettre à disposition les données de la table BigQuery dans Google Sheets via Apps Script

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 !

index.html 

<!DOCTYPE html>
<html>
 <head>
   <script src="https://cdn.jsdelivr.net/gh/tanaikech/ResumableUploadForGoogleDrive_js@master/resumableupload_js.min.js"></script>
   <base target="_top">
 </head>
 <body>
   <h3>CSV vers table BigQuery</h3>
   <br>
   <form>Téléchargement du fichier de données<input name="file" id="uploadfile" type="file" /></form>
   <br>
   <div id="progress"></div>
<script>
 var folderId;
 google.script.run
   .withSuccessHandler(params =>{
     folderId = params.folderId;
   })
   .getParams();
 document.getElementById("uploadfile").addEventListener("change", run, false);
  function run(obj) {
   google.script.run
     .withSuccessHandler(accessToken =>
       ResumableUploadForGoogleDrive(accessToken, obj)
     )
     .getAuth();
 }
 
 function ResumableUploadForGoogleDrive(accessToken, obj) {
   const file = obj.target.files[0];
   if (file.name != "") {
     let fr = new FileReader();
     fr.fileName = file.name;
     fr.fileSize = file.size;   
     fr.fileType = file.type;
     fr.accessToken = accessToken;
     fr.readAsArrayBuffer(file);
     fr.onload = resumableUpload;
   }
 }
 
 function resumableUpload(e) {
   document.getElementById("progress").innerHTML = "Initializing.";
   const f = e.target;
   const resource = {
     fileName: f.fileName,
     fileSize: f.fileSize,
     fileType: f.fileType,
     fileBuffer: f.result,
     accessToken: f.accessToken,
     folderId: folderId,
   };
   const ru = new ResumableUploadToGoogleDrive();
   ru.Do(resource, function(res, err) {
     if (err) {
       console.log(err);
       return;
     }
     console.log(res);
     let msg = "";
     if (res.status == "Uploading") {
       msg =
         Math.round(
           (res.progressNumber.current / res.progressNumber.end) * 100
         ) + "%";
     }
     else {
       if(res.status == "Done") google.script.run.withSuccessHandler(updateDone).updateBigQueryTable(resource.fileName);
       msg = res.status;
     }
     document.getElementById("progress").innerText = msg;
   });
 }
 
 function updateDone(msg){
   document.getElementById("progress").innerText = msg;
 }
</script>
 </body>
</html>

server.gs

let folderId;
function onOpen(e) {
 SpreadsheetApp.getUi()
 .createMenu("Fichier CSV vers table BigQuery")
 .addItem("Lancer add-on","showSidebar")
 .addToUi();
}
function showSidebar() {
 var html = HtmlService.createHtmlOutputFromFile("index");
 SpreadsheetApp.getUi().showSidebar(html);
}
function getAuth() {
 // DriveApp.createFile(blob) // This is used for adding the scope of "https://www.googleapis.com/auth/drive".
 return ScriptApp.getOAuthToken();
}
function getParams(){
 folderId = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Params").getRange(2,2).getValue();
 return {folderId: folderId};
}
function updateBigQueryTable(fileName){
 try{
   if(folderId === undefined) getParams();
      const folder = DriveApp.getFolderById(folderId);
      const files = folder.getFiles();
      while (files.hasNext()){
         const file = files.next();
         const driveFileName = file.getName();
         if (fileName === driveFileName) return loadCsv(file);
       }
      return `File ${fileName} not found in folder ${folder.getName()}`;
 }catch(e){
   Logger.log(e);
   return e.message;
 } 
}
function loadCsv(file) {
 try{
   // Remplacer par l’ID de votre projet GCP
   const projectId = 'XXXXXXX’';
   // Remplacer par l’ID de votre dataset
   const datasetId = ‘XXXXXX’;   
   const csvFileId = file.getId();
   // Create the table.
   const tableId = file.getName().split(".")[0] + "-" + new Date().getTime();
   let table = {
     tableReference: {
       projectId: projectId,
       datasetId: datasetId,
       tableId: tableId
     }  
   };
   table = BigQuery.Tables.insert(table, projectId, datasetId);
   Logger.log('Table created: %s', table.id);
 
   // On récupère les données du fichier CSV et on les formate correctement
   const data = file.getBlob().setContentType('application/octet-stream');
 
   // On crée le job de chargement
   let job = {
     configuration: {
       load: {
         destinationTable: {
           projectId: projectId,
           datasetId: datasetId,
           tableId: tableId
         },
         skipLeadingRows: 1,
         autodetect: true,
         fieldDelimiter: ";"
       }
     }
   };
   job = BigQuery.Jobs.insert(job, projectId, data);
   const finalMsg = `Le job de chargement BigQuery a commencé !`;
   Logger.log(finalMsg);
   return finalMsg;
 }catch(e){
   Logger.log(e);
   return e.message;
 }
}

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 similaires

  • Articles connexes
  • Plus de l'auteur

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
1 Commentaire
Le plus ancien
Le plus récent Le plus populaire
Commentaires en ligne
Afficher tous les commentaires
THIERRY VANOFFE

Belle performance Pierre !