Formulaire de saisie dans google Sheets et Apps Script

Bonjour tout le monde et bienvenue dans cette série où nous allons créer ensemble un simple formulaire de saisie dans google Sheets avec l’aide de Google Apps Script. 

Mise en place

Création de la page config

Nous allons commencer par créer nos pages qui vont survivre dans notre projet. La première page sera notre page de configuration ou nous allons utiliser afin de configurer notre projet comme l’identification des entrées de notre base de données. 

Puis nous allons nommer notre champ qui contient le numéro d’identification.

Création de la page Student

Désormais nous allons créer notre page étudiant qui sera tout simplement notre base de donnée ou nous allons ajouter, modifier ou supprimer des étudiants.

Création de la page Formulaire

Et pour finir nous allons créer notre page qui contiendra notre formulaire de saisi et nous allons nommer les champs d’entrée.

Pour finir nous allons ajouter 3 boutons à notre formulaire qui vont survivre à exécuter l’action rechercher, sauvegarder ou effacer un étudiant de notre base de donnée.

Création d’un nouvelle étudiant

Récupérer l’identifiant 

Nous allons commencer par récupérer l’identifiant de la page config de notre projet. Alors pour rappelle nous avons nommé le champ qui contient  l’identifiant. 

Avant de commencer nous allons créer un fichier que nous allons nommer spreadSheetsUtils ou nous allons créer une variable qui contiendra notre fichier actif. ceci nous évitera de toujour devoir indiquer SpredsheetApp.getActive() partout

let spreadsheet = SpreadsheetApp.getActive();

Et donc ici dans notre éditeur de code nous allons créer un fichier que nous allons nommer “configUtil” qui va contenir toute nos logique qui aura un rapport avec la page config. Dans cette page nous allons tout simplement récupérer et retourner la valeur du champ nommée.

function getNextStudentId(){
  return spreadsheet.getRangeByName(StudentID).getValue();
}

Mettre à jour l’identifiant

Pour terminer nous allons mettre à jour l’identifiant. Pour cela nous allons créer une fonction qui va récupérer la valeur du champs nommée qui contient l’identifiant et puis nous allons l’incrémenter de 1.

function setNextStudentId(){
  let actualStudentId = getNextStudentId();
  spreadsheet.getRangeByName(StudentID).setValue(++ actualStudentId)
}

Création de la classe Student

Afin de faciliter les choses nous allons créer une classe qui représentera notre Étudiant. Pour cela nous allons notre éditeur de code créer un fichier que nous allons nommée “Student class”.

class Student{    constructor(id,firstName,lastName,birthday,street,postalCode,city,country){
    this.id = id;
    this.firstName = firstName;
    this.lastName = lastName;
    this.birthday = birthday;
    this.street = street;
    this.postalCode = postalCode;
    this.city = city;
    this.country = country;
  }
}

Creation d’un étudiant

dans notre éditeur de code nous allons créer un nouveau fichier que nous allons nommer StudentUtil qui contiendra toutes les logique qui manipule notre base de données.

et ici aussi pour faciliter les chose nous allons récupérer la page Student et le placer dans une variable.

let studentPage = spreadsheet.getSheetByName('Student');

puis nous allons créer notre fonction qui nous permettra de créer un étudiant.

function createStudent(studentRecord){
  studentPage.appendRow([
    studentRecord.id,
    studentRecord.firstName,
    studentRecord.lastName,
    studentRecord.birthday,
    studentRecord.street,
    studentRecord.postalCode,
    studentRecord.city,
    studentRecord.country,
    new Date()   
  ]);
  setNextStudentId();
}

Recuper l’étudiant du formulaire

Et voilà nous avons notre fonction qui nous permet de créer un étudiant dans notre base de donnée, il est temps de récupérer les informations de notre formulaire afin de les transmettre à notre nouvelle fonction afin de créer directement l’entrée dans la base de données depuis le formulaire.

Pour cela nous allons créer un fichier que nous allons nommer FormUtil et ici nous allons  créer des variable qui vont contenir les champs de nos champs nommée.

let formIdRange = spreadsheet.getRangeByName('studentId');
let formFirstNameRange = spreadsheet.getRangeByName('firstName');
let formLastNameRange = spreadsheet.getRangeByName('lastName');
let formBirthdayRange = spreadsheet.getRangeByName('birdthday');
let formStreetRange = spreadsheet.getRangeByName('street');
let formPostalCodeRange = spreadsheet.getRangeByName('postalCode');
let formCityRange = spreadsheet.getRangeByName('city');
let formCountryRange = spreadsheet.getRangeByName('country');

avec ceci nous avons les information de notre formulaire.

Création de la fonction save du formulaire

Ici nous allons créer une fonction que nous allons nommer saveBtn(). qui va simplement créer un objet Student avec les données du formulaire et puis simplement faire appelle à la fonction createStudent().

function saveBtn(){
  let student = new Student(
    formIdRange.getNextStudentId(),
    formFirstNameRange.getValue(),
    formLastNameRange.getValue(),
    formBirthdayRange.getValue(),
    formStreetRange.getValue(),
    formPostalCodeRange.getValue(),
    formCityRange.getValue(),
    formCountryRange.getValue()
  )
  createStudent(student);
  formIdRange.setValue(student.id);
}

Et il ne faut pas oublier de lier la fonction saveBtn() au bouton de sauvegarde. voila désormais on est capable de sauvegarder un étudiant dans notre base de données.

Rechercher un étudiant

Rechercher un étudiant avec son nom et prénom

Pour récupérer un étudiant par son nom et son prénom nous allons devoir travailler en plusieurs étapes. La première consistera à récupérer tous les étudiants de notre base de donnée puis de filtrer les étudiants qui contiennent le nom et prénom mentionner. 

Heureusement pour nous il y a déjà deux fonctions qui nous permettent de faire cela très facilement. notamment le getDataRange().getValues() de Google Sheets et la fonction filtre de javascript . Et donc dans notre feuille studentUitil nous allons créer une nouvelle fonction.

function getStudentByFirstNameAndLastName(firstName, lastName){

  const studentList = studentPage.getDataRange().getValues();

  let student = studentList.filter(data => {return data[1] == firstName && data[2] == lastName})[0];

  console.log("the student information found are : "+student);
}

Ici, si on veut tester notre nouvelle fonction et que nous recherchons un étudiant de notre base de donnée qui contient le nom et le prénom du paramètre, la variable student contiendra une liste qui contient les informations du client.

Alors pour terminer la partie nous allons devoir créer un objet de type Student qui contiendra notre étudiant, pour cela nous allons créer une nouvelle fonction qui se chargera de cela. 

function constructStudent(studentArray){

  return new Student(
    studentArray[0],
    studentArray[1],
    studentArray[2],
    studentArray[3],
    studentArray[4],
    studentArray[5],
    studentArray[6],
    studentArray[7]
  );
}

et pour finir nous allons modifier notre fonction getStudentByFirstNameAndLastName afin de retourner l’objet Student que nous avons construit.

function getStudentByFirstNameAndLastName(firstName, lastName){

  const studentList = studentPage.getDataRange().getValues();

  let student = studentList.filter(data => {return data[1] == firstName && data[2] == lastName})[0];

  if(student){
    return constructStudent(student);
  }  

}

on peut ajouter encore une petite fonctionnalité supplémentaire si un étudiant n’est pas trouver. 

  let ui = SpreadsheetApp.getUi();
  ui.alert('No Student found under the name '+firstName+' '+lastName);

Ajouter la fonction de recherche au formulaire

Sur la feuille formUtil nous allons créer une fonction qui sera bien sûr associer au bouton de recherche, qui devra prendre le nom et le prénom du formulaire et utiliser ces information comme paramètre de l’appelé de la fonction getStudentByFirstAndLasName.

La fonction retournera un objet du type Student qui contient les informations de l’étudiant et on affichera les informations trouvées dans les bon champs de notre formulaire.

function searchBtn(){

  let foundRecord = getStudentByFirstNameAndLastName(formFirstNameRange.getValue(),formLastNameRange.getValue());

  if(foundRecord != null){

    formIdRange.setValue(foundRecord.id);
    formFirstNameRange.setValue(foundRecord.firstName);
    formLastNameRange.setValue(foundRecord.lastName);
    formBirthdayRange.setValue(foundRecord.birthday);
    formStreetRange.setValue(foundRecord.street);
    formPostalCodeRange.setValue(foundRecord.postalCode);
    formCityRange.setValue(foundRecord.city);
    formCountryRange.setValue(foundRecord.country);
  }

}

vider les champs avant la recherche

donc désormais que nous avons notre fonction de recherche nous avons un petit problème quand on recherche un étudiant mais ou la base de donnée ne contient pas toutes ces informations après avoir recherché un étudiant ou la base de donnée contient toutes ces informations, les données de l’étudiant précédente sont toujours la.

donc ici nous allons devoir créer une fonction qui nous permettra de vider les champs de notre formulaire avant chaque recherche.

function clearForm(){

  formIdRange.setValue(null);
  formBirthdayRange.setValue(null);
  formStreetRange.setValue(null);
  formPostalCodeRange.setValue(null);
  formCityRange.setValue(null);
  formCountryRange.setValue(null);

}

Et notre nouvelle fonction nous allons l’appliquer au début de notre fonction du bouton recherche

function searchBtn(){
  clearForm();
//reste of code ……

et voilà nous avons notre recherche d’étudiant qui est désormais opérationnelle. bien sûr il faut faire attention car si on appuie sur le bouton save pour le moment un nouvel étudiant sera créer dans notre base de donnée.

Mettre à jour un étudiant

Récupérer le numéro de ligne

Alors afin de pouvoir mettre à jour un étudiant dans notre base de données nous avons besoin de récupérer le numéro de ligne et puis d’insérer les nouvelles données sur cette ligne. Et donc nous allons créer une fonction qui nous permettra de récupérer le numéro de ligne.  

function findStudentRowById(id){

  let data = studentPage.getDataRange().getValues();
  let foundIndex = data.findIndex(student => student[0] == id);

  return ++foundIndex;
}

mettre à jour l’étudiant

Voila nous savons sur quelle ligne il faut mettre à jour nos informations d’étudiant. Il est temps de le faire avec une nouvelle fonction.

function updateStudent(studentRecord){

  const rowNumber = findStudentRowById(studentRecord.id);

  if(rowNumber > 0){
    let recordRow = [
    studentRecord.id,
    studentRecord.firstName,
    studentRecord.lastName,
    studentRecord.birthday,
    studentRecord.street,
    studentRecord.postalCode,
    studentRecord.city,
    studentRecord.country  
    ];
 
    recordRow.push(studentPage.getRange('I'+rowNumber).getValue());
    recordRow.push(new Date());
    dataToUpdate = [recordRow];    
    studentPage.getRange(rowNumber,1,1,recordRow.length).setValues(dataToUpdate);
  }

}

création et mise à jour dans un bouton

Pour terminer cette partie nous allons faire en sorte que le bouton save puisse créer ou mettre à jour un donnée, cela nous allons le faire en contrôlant s’il y a une valeur dans la cellule qui contient le numéro d’identification. 

Si l’Id est vide alors on créer un entrée sinon on met à jour. 

function saveBtn(){

  let iscreate = formIdRange.isBlank() ? true : false;

  let student = new Student(
    formIdRange.getValue(),
    formFirstNameRange.getValue(),
    formLastNameRange.getValue(),
    formBirthdayRange.getValue(),
    formStreetRange.getValue(),
    formPostalCodeRange.getValue(),
    formCityRange.getValue(),
    formCountryRange.getValue()
  )

  if(iscreate){
    student.id = getNextStudentId();
    createStudent(student);

  }else {
    updateStudent(student);
  }
}

voilà désormais on est capable de créer et de mettre à jour un étudiant dans notre base de donnée avec une seul bouton

vider le formulaire après la sauvegarde

alors comme vous avez surement remarqué pendant vos test c’est que quand on sauvegarde un étudiant le formulaire ne se vide pas donc il y a un risque de modifier les informations de l’étudiant sans le vouloir.

Donc pour cela nous allons simplement supprimer toutes les informations du formulaire après chaque sauvegarde et pour cela nous devons modifier notre fonction clearForm pour qui’il supprime complètement ou partiellement notre formulaire selon la situation.

function clearForm(full){

  if(full){
    formFirstNameRange.setValue(null);
    formLastNameRange.setValue(null);
  }

  formIdRange.setValue(null);
  formBirthdayRange.setValue(null);
  formStreetRange.setValue(null);
  formPostalCodeRange.setValue(null);
  formCityRange.setValue(null);
  formCountryRange.setValue(null);

}

il ne faut pas oublier de modifier notre fonction recherche ou il faut mentionner qu’il faut faire une suppression partielle et non une suppression complète du formulaire. Pour cela nous allons vider le formulaire au début de la fonction searchBtn

clearForm(false);

Et pour finir dans notre fonction saveBtn nous allons ajouter à la fin de la fonction la suppression total du formulaire

clearForm(true);

Voilà nous avons notre bouton de sauvegarde qui crée ou met à jour une entrée de notre base de données .

Supprimer une donnée de notre base de données

Dans cette dernière partie nous allons voir comment supprimer un étudiant de notre base de données. Ici nous allons travailler en 3 étages, rechercher l’étudiant, confirmer la suppression et supprimer la donnée. 

Comment supprimer une donnée depuis un formulaire – Google sheet

la fonction de suppression 

Pour la suppression nous allons créer une nouvelle fonction dans notre feuil de studentUitl que nous allons nommée deleteStudent et qui prendre l’id de l’étudiant à supprimer. 

Mais ce que nous allons voir en premier c’est de vérifier si le paramètre n’est pas vide, si le paramètre est vide nous allons simplement alerter l’utilisateur que l’étudiant n’est pas trouver dans la base de donnée. 

function deleteStudent(studentId){
  let ui = SpreadsheetApp.getUi();
  if(!studentId){
    ui.alert('No student to delete from the database !');
    return false;
  }
}

Sinon si le paramètre n’est pas vide nous allons demander à l’utilisateur de confirmer la suppression de l’étudiant. 

let response = ui.alert('Delete Student', 'are you sure to delete the student ?', ui.ButtonSet.YES_NO);

si la réponse est oui donc YES alors nous allons tenter de retrouver le numéro de ligne de l’étudiant qui contient le numéro d’id du paramètre

if (response == ui.Button.YES) {    
    let rowToDelete = findStudentRowById(studentId);

Puis nous allons vérifier si la variable rowToDelete est <1 alors nous allons afficher un message afin de prévenir l’utilisateur que l’étudiant avec le numéro d’identifiant n’est pas retrouvé dans la base de donnée.

  if (rowToDelete < 1){

      ui.alert('The Sutdent with id '+studentId+
      ' could not be found in the database');

      return false
    }

voila il ne nous reste plus qu’a effacer la ligne qui contient l’étudiant 

studentPage.deleteRow(rowToDelete);

ui.alert('Student is deleted from the database');

return true;

fonction de suppression dans le formulaire

Désormais que nous avons notre fonction de suppression nous allons créer une fonction pour notre formulaire qui va récupérer l’id qui se trouve sur la page du formulaire puis le transmettre dans la fonction de suppression que nous venons de créer. 

Et pour finir nous allons vider les données de notre formulaire si la suppression à bien fonctionné. 

function deleteBtn(){

  if(deleteStudent(formIdRange.getValue())){

    clearForm(true);   
  }
}

et voilà nous avons un formulaire qui permet de tout faire, créer, rechercher, mettre à jour et supprimer des données.