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

1 856
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

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

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.

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