[Google Sheets] Générer automatiquement un formulaire

Bonjour,

Google form permet de générer des demandes reprises dans une base de données. Toutefois, cela ne permet pas de corriger ou compléter les données.

Une autre solution est de générer en automatique un formulaire html et de lui injecter les données déjà introduites.

objectif :
- générer automatiquement un formulaire de création et de modification d'informations d'une "base de données"

éléments gérés :
- textes
- dates
- listes déroulantes
- zones étendues (textarea)
- numéros de téléphone (format FR et CH pour le moment)
- ne rien mettre pour l'indicatif de téléphone qui doit précéder le n° de tel

paramétrage :
- ligne 3 (sauf 2 premières colonnes) : les en-têtes des données
- au-dessus en ligne 2 : les "invits" (placeholder) sauf pour les dates (c'est inutile dans ce cas)
- au-dessus en ligne 1 : le type d'élément, voir ci-dessus (si téléphone, alors laisser une colonne juste devant pour l'indicatif)
- pour les listes,
les éléments doivent être identifiés dans l'onglet listes,
les listes doivent être côte à côte dans l'ordre d'apprition dans la base de données à partir de la colonne 2 de l'onglet listes
ne pas mettre de noms particuliers, juste vérifier que le nom mesOptions recouvre bien toutes les listes et que chacune possède au moins un élément vide pour effacer

particularités :
- en modification, les valeurs sont rappelées (et dans ce cas la zone n'est plus grisée)
- la colonne 1 sert à indiquer la ligne à modifier
- la colonne 2 sert à activer un filtre et afficher un extrait des lignes et colonnes dans un autre onglet
- les n° de téléphones sont mis automatiquement au format CH ou FR

code gs :

// Mike Steelson alias Mikhail Staliyevich
// variables :
const nomBdd = 'BdD'            // nom de la feuille base de données
const nomFiltre = 'BdD_filtrée' // nom de la feuille filtre
const col = 3;                  // colonne à partir de laquelle les données sont enregistrées

const bdd = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(nomBdd);

// création du menu
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('** Base de Données **')
    .addItem('Créer une nouvelle ligne dans la BdD', 'creer')
    .addItem('Modifier la ligne sélectionnée', 'modifier')
    .addToUi();
}

// formulaire création
function creer() {
  const html = HtmlService
    .createTemplateFromFile('formCreation')
    .evaluate()
    .setWidth(400)
    .setHeight(785);
  SpreadsheetApp.getUi().showModelessDialog(html, 'Ajouter');
}

// formulaire lecture - modification
function modifier() {
  if (numLigne()<=1){
    Browser.msgBox('Sélectionner une ligne en colonne A !')
  }else{
    const html = HtmlService
      .createTemplateFromFile('formModification')
      .evaluate()
      .setWidth(400)
      .setHeight(785);
    SpreadsheetApp.getUi().showModelessDialog(html, 'Modifier');
  }
}

// transfert des paramètres permettant de créer le formulaire en dynamique
function transfererParametres(){
  return bdd.getRange(1,col,4,bdd.getLastColumn()-col+1).getValues()
}

// transfert des options pour les listes déroulantes
function transfererOptions(){
  return SpreadsheetApp.getActiveSpreadsheet().getRange("mesOptions").getValues()
}

// transfert des données de la base de données vers le formulaire modification
function transfererData() {
  var ligne = numLigne()
  return bdd.getRange(ligne,col,1,bdd.getLastColumn()-col+1).getValues()
}

// numéro de la ligne correspondant la la première case cochée colonne A
// pour transférer les données vers le formulaire modification
// pour enregistrer les données issues de la feuille modification
// pour indiquer au formulaire en commentaire du bouton le n° de ligne en cours de visualisation
function numLigne(){
  var f = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var values = f.getRange('A2:A').getValues().join().split(","); 
  var ligne = values.indexOf('true') + 2;
  if (f.getName()==nomBdd){return ligne}else if(f.getName()==nomFiltre){return f.getRange(ligne,2).getValue()}else{return 0}
}

// ajout des données en provenance du formulaire création
function ajouterLigne(tab) {
  var ligne = getLastDataRow(bdd) + 1
  bdd.getRange(ligne,col,1,tab.length).setValues([tab])
}

// modification des données en provenance du formulaire modification
function modifierLigne(tab) {
  var ligne = numLigne()
  bdd.getRange(ligne,col,1,tab.length).setValues([tab])
}

// permet d'inclure dans les formulaires un autre fichier html, css.html en l'occurence
function rapatrier(filename){
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

function getLastDataRow(sheet) {
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange(lastRow,col);
  if (range.getValue() !== "") {
    return lastRow;
  } else {
    return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  }              
}

// pseudo boutons radio colonne 1
function onEdit(e) {
  var r = e.range;
  var f = SpreadsheetApp.getActiveSheet();
  if (r.getColumn() == 1 && (f.getName()==nomBdd || f.getName()==nomFiltre)) {
    if (r.getValue()) {
      var radio = f.getRange('A4:A'+f.getLastRow()).getValues()
      for (var i=0;i<radio.length;i++){radio[i][0]=false}
      radio[r.getRow()-4][0] = true
      f.getRange('A4:A'+f.getLastRow()).setValues(radio)
    }
  }
}

code html

<!DOCTYPE html>
<html>
  <head>
    <?!= rapatrier('css') ?>
    <?!= rapatrier('tel') ?>
  </head>
  <body>
    <script>
      <? 
        var params = transfererParametres();
        var mesOptions = transfererOptions();
        var data = transfererData();
        var ligne = numLigne();
      ?>
      function changeColor(s) {s.style.color = "blue";}
      window.onload = function(){
        <? 
        for(var j=0;j < data[0].length; j++){
          ?>
          if (<?=(data[0][j])?> !='') {
            changeColor(document.getElementById("p<?= j ?>"))
          }
          <?
        }
        ?>
      }
    </script>
    <form>
    <? 
    var numListe=0
    for (var j=0; j<params[0].length; j++) { 
      if (params[0][j]=="text"){
        ?>
        <?= params[2][j] ?> :<br>
        <input type="<?= params[0][j] ?>" name="p<?= j ?>" id="p<?= j ?>" value="<?= data[0][j] ?>" placeholder="<?= params[1][j] ?>">
        <? 
      }
      else if (params[0][j]=="date"){
        ?>
        <?= params[2][j] ?> :<br>
        <input type="<?= params[0][j] ?>" name="p<?= j ?>" id="p<?= j ?>" value="<?= ((data[0][j]=='')?'':data[0][j].getFullYear()+'-'+('0'+(data[0][j].getMonth()+1)).slice(-2)+'-'+('0'+data[0][j].getDate()).slice(-2)) ?>" onchange="changeColor(this);">
        <?  
      }
      else if (params[0][j]=="textarea"){
        ?>
        <?= params[2][j] ?> :<br>
        <textarea name="p<?= j ?>" id="p<?= j ?>" placeholder="<?= params[1][j] ?>"><?= data[0][j] ?></textarea>
        <?  
      }
      else if (params[0][j]=="tel"){
        ?>
        <?= params[2][j-1] ?> + <?= params[2][j] ?> :<br>
        <select name="p<?= j-1 ?>" id="p<?= j-1 ?>" onchange="changeColor(this);">
              <option value="" disabled selected ><?= params[1][j-1] ?></option>
          <option value="+33" <?= ((data[0][j-1]=='+33')?'selected':'') ?> >+33</option>
              <option value="+41" <?= ((data[0][j-1]=='+41')?'selected':'') ?> >+41</option>
        </select>
        <input type="text" name="p<?= j ?>" id="p<?= j ?>" value="<?= data[0][j] ?>" placeholder="<?= params[1][j] ?>" onkeyup="format(<?= j ?>)">
        <?  
      }
      else if (params[0][j]=="liste"){
        ?>
        <?= params[2][j] ?> :<br>
        <select name="p<?= j ?>" id="p<?= j ?>" onchange="changeColor(this);">
          <option value="" disabled selected ><?= params[1][j] ?></option>
          <? for (var k = 0; k < mesOptions.length; k++) { ?>
            <option value="<?= mesOptions[k][numListe] ?>" <?= ((mesOptions[k][numListe]==data[0][j] && data[0][j]!='')?'selected':'') ?> ><?= mesOptions[k][numListe] ?></option>
          <? } ?>
        </select>
        <?  
        numListe+=1
      }
    } 
    ?>
    <input type="button" value="Modifier ligne <?= ligne ?> de la base de données" onclick="modifier()">
    </form>
    <script>
      function modifier() {
        var donnees = document.forms[0]
        var tab = []
        for (var i=0;i<donnees.length;i++){
          if (donnees[i].type != "button"){
            tab.push(donnees[i].value)
          }
        }
        if (tab.join('') == '') {
          alert('Le formulaire est vide !');
          return;
        }
        var d = new Date()
        tab.push(d.toString())
        google.script.run.modifierLigne(tab);
      }
    </script>
  </body>
</html>

copie fichier à venir dans la rubrique téléchargement ...

Bonjour,

T'es un grand malade 😘

Bravo pour tout ce que tu fais et la vitesse à laquelle tu ingurgites tout ceci !

J'en ai un peu bavé pour cette partie

      window.onload = function(){
        <? 
        for(var j=0;j < data[0].length; j++){
          ?>
          if (<?=(data[0][j])?> !='') {
            changeColor(document.getElementById("p<?= j ?>"))
          }
          <?
        }
        ?>
      }

C'est un mélange de javascript qui fait appel à des données de la feuille, à ne plus savoir où et quand il faut mettre <? ... ?> ! le for est dedans mais pas le if ! j'ai quand même réussi et au plus c'est difficile au plus on savoure un peu le résultat !

Bon, quand est-ce que tu nous rejoins sur GSheets ?

Je ne regarde tout ceci que de loin mais, plus je vois ce que tu fais et plus je suis persuadé que GSheets est en mesure de détrôner Excel d'ici quelques années.

Mais je ne connais aucun langage informatique. J'adore les langues et c'est ce qui m'a plu avec VBA : c'est super proche du langage humain.

Java script, html et consorts... Je n'arrive vraiment pas à accrocher et à retrouver l'esthétisme qui me plaît dans le VBA.

fichier à télécharger ici https://www.sheets-pratique.com/fr/telechargements/outils-de-gestion/automatiser-formulaire-sheets-n...