Récupérer les valeurs des cellules avec Google Apps Script

Introduction

Nous allons voir ensemble comment récupérer les valeurs des cellules.  Alors cette vidéo est très importante car nous allons voir comment sélectionner une feuille de calcule et puis récupérer les données d’une cellule.

Sélectionner une page

Alors pour notre exemple nous avons une page qui se nomme “Student” et qui contient un petit tableau avec le nom et prénom des étudiants.

dans notre App Script nous allons renommer la fonction “myFunction” par « getStudents » afin que la fonction soit plus représentative.  

puis nous allons indiquer de quelle page nous voulons récupérer les données en indiquant à Google Script que nous voulons cibler la page “Student” du tableur active et placer le résultat dans une variable que nous allons nommée “sheet”.

function getStudents() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student');
}

Sélectionner une plage de donnée

Désormais que nous avons notre page nous allons devoir indiquer la plage de donnée que nous voulons récupérer. Pour cela nous allons tout simplement dire à Google Script de prendre le range “A1” et nous allons placer le résultat dans une variable que nous allons nommée range.

function getStudents() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student');
  let range = sheet.getRange('A1');
}

voilà nous avons les données de notre plage de données et si on veux voir ce que contient notre plage de donnée nous pouvons faire une consol.log(range) puis on exécute le code et la grande surprise on n’obtient pas la valeur de la cellule mais la liste de toutes les fonctions de l’objet range.

Afin d’avoir la valeur d’une cellule on peut utiliser la fonction getValue() qui retourne la première valeur de la plage de donnée. Quand on fait un console log on obtient bel et bien la valeur de la cellule.

Sélectionner plusieure plage de donnée

Nous avons donc vu comment sélectionner les données d’une cellule de notre page. Il est temps de voir comment sélectionner palussiere plage de données, dans notre exemple on voudrait récupérer la totalité de notre tableau.

Pour récupérer tout notre simple il n’y a rien de plus simple que de sélectionner la plage que nous voulons comme suite

function getStudents() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student');
  let range = sheet.getRange('A1:B7');
  console.log(range.getValue());
}

Par contre dans notre log on ne voit que la première cellule de notre tableau et non pas l’entièreté du tableau. Pour résoudre cela nous allons devoir faire un petit changement en remplaçant la fonction getValue par getValues. 

function getStudents() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student');
  let range = sheet.getRange('A1:B7');
  console.log(range.getValues());
}

Ici le problème est que nous devons à chaque fois modifier le code quand il y a une nouvelle entrée dans notre tableau or nous voulons que cela se fasse automatiquement. Pour cela nous avons deux solutions.

la première solution consiste à récupérer la dernière ligne de notre pable en utilisant le sheet.getLastRow() puis d’ajouter la valeur reçu au champ à récupérer comme suite 

function getStudents() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student');
  const lastRow = sheet.getLastRow();
  let range = sheet.getRange('A1:B'+lastRow);  
  console.log(range.getValues());
}

La seconde solution consiste à récupérer l’ensemble des données de la page en remplaçant getRange() par getDataRange(). Cette méthode est pratique si la page n’est composée que d’un seul tableau de données.

function getStudents() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student');
  const lastRow = sheet.getLastRow();
  let range = sheet.getDataRange();
  console.log(range.getValues());
}

Sélectionner une cellule de la plage des données

Alors nous avons nos données qui se trouvent dans le range et nous voulons récupérer ces données afin de pouvoir les utiliser. Pour cela nous pouvons faire appelle à une autre méthode du range qui est le getCell() ou il faut indiquer le numéro de ligne et le numéro de colonne. 

 console.log(
    'first name : '+range.getCell(1,1).getValue() + 
    ' last name : ' +range.getCell(1,2).getValue()
  );

Si on veut récupérer la seconde ligne on fait la même chose sauf qu’il faut changer le numéro de ligne. Comme vous pouvez vous en douter ce n’est pas le but de faire cela pour chaque ligne mais en utilisant une boucle for.

Boucler sur une plage de donnée

alors pour boucler sur un plage de donnée on peut faire appelle a la boucle for ou nous allons devoir définir la valeur de départ, comparer cette valeur à une condition et si la condition est vraie on refait la boucle.

for(let x = 1; x <= lastRow ; x ++){
    console.log(
    'first name : '+range.getCell(x,1).getValue() + 
    ' last name : ' +range.getCell(x,2).getValue()
    );
}

une version plus simple de cette boucle for est possible en utilisant la boucle “for in” si nous ne voulons utiliser que les valeurs des cellules et non accéder aux autres fonctions du range. Pour cela nous allons stocker la valeur dans une variable et puis nous allons utiliser la boucle “for in”

function getStudents() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student');
  let rangeValue = sheet.getDataRange().getValues();
  for(index in rangeValue){
    console.log(
    'first name : '+rangeValue[index][0] + 
    ' last name : ' +rangeValue[index][1]
    );
  }
}

sinon encore plus simple si on veut récupérer uniquement les valeurs on peut utiliser la boucle “for of” comme suite.

function getStudents() {
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student');
  for(value of sheet.getDataRange().getValues()){
    console.log(
    'first name : '+value[0] + 
    ' last name : ' +value[1]
    );
  }
}