Récupérer info d'une base de données

Le 21/01/2022 à 13:37

Voici

4 paramètres en haut du script

// mike steelson

const nomFeuilleBaseDeDonnees = 'Réponses'
const debutDonnees = 4
const nomFeuilleSaisie = 'Fiche client'
const rangeID = 'G1'

const doc = SpreadsheetApp.getActiveSpreadsheet();
const bdd = doc.getSheetByName(nomFeuilleBaseDeDonnees)
const saisie = doc.getSheetByName(nomFeuilleSaisie)

function onOpen() {
  SpreadsheetApp.getUi().createMenu('↓ MENU ↓')
    .addItem('👉 Appliquer les modifications', 'saisir')
    .addToUi();
}

function onEdit(e) {
  var f = e.source.getActiveSheet();
  var r = e.source.getActiveRange();
  if (f.getName() == nomFeuilleSaisie && r.getA1Notation() == rangeID){ 
    if (r.getValue()>0 && r.getValue()<=(bdd.getLastRow()-debutDonnees+1)){afficher(r.getValue())} 
    else {r.setValue(e.oldValue)}
  }
}

function saisir(){
  var id = saisie.getRange(rangeID).getValue()
  var data = bdd.getRange(id+debutDonnees-1,1,1,bdd.getLastColumn()).getValues()
  var coord = coordXY()
  var myForm = saisie.getDataRange().getValues()
  for (var i =0; i<coord.length;i++){if(coord[i][0]!=-1){data[0][i]=myForm[coord[i][0]][coord[i][1]]}}
  bdd.getRange(id+debutDonnees-1,1,1,bdd.getLastColumn()).setValues(data)
  SpreadsheetApp.getActive().toast('Modification(s) enregistrée(s) !', 'Fin de script 🗃️')
}

function afficher(id){
  var data = bdd.getRange(id+debutDonnees-1,1,1,bdd.getLastColumn()).getValues().flat()
  var coord = coordXY()
  var myForm = saisie.getDataRange().getValues()
  for (var i =0; i<coord.length;i++){if(coord[i][0]!=-1){myForm[coord[i][0]][coord[i][1]] = data[i]}}
  saisie.getDataRange().setValues(myForm)
}

function coordXY(){
  var coord=[]
  var adresses = bdd.getRange('1:1').getValues().flat();
  adresses.forEach(function(ad){ 
    if(ad!='') {coord.push([ ad.match(/\d+/)[0]-1 , letterToColumn(ad.match(/\D+/)[0])-1 ])}
    else {coord.push([-1,-1])}
  })
  return coord
}

function letterToColumn(letter) {
  var column = 0, length = letter.length;
  for (var i = 0; i < length; i++) { column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1) }
  return column;
}

ce que j'imagine maintenant est qu'il puisse y avoir plusieurs vues différentes qui agissent sur la BdD en mettant ligne 1, ligne 2 etc. les coordonnées pour chaque vue (une vue par onglet)

Le 21/01/2022 à 20:41

C'est super. Ca marche parfaitement.

J'aurais une autre question. J'ai voulu mettre une formule pour indiquer l'âge d'un enfant à partir de sa date de naissance par exemple.

Lorsque je change le numéro en G1, la formule est écrasée par son résultat et le calcul ne peut plus se faire. J'ai essayé d'ajouter une colonne dans la base de données avec le formule mais là aussi, si je fais un changement dans la fiche client et que je clique sur modifier, ça écrase la formule par son résultat. Est-ce qu'il y aurait une solution qui me permettrait de créer des formules et qu'elles ne soient pas écrasées lorsque des données sont recherchés ou modifiés?

Le 22/01/2022 à 00:32

J'avais essayé de mettre des formules aussi, mais elles sont en effet écrasées.

Le problème est qu'avec 61 cellules à modifier, je ne peux pas le faire une par une sans dépasser le temps d'exécution du script, sans compter aussi l'effet lenteur de l'affichage. Il faut donc que j'importe globalement la feuille de saisie dans un tableau, que je le modifie et que je retransmette le tableau complet.

J'avais essayé avec setformulas mais dans ce cas c'étaient les valeurs qui se trouvaient écrasées.

Une solution très spécifique consisterait à recréer les quelques formules dans le script. Ou alors, les placer hors du champ de copie des données.

J'essaierai toutefois avec la procédure batchupdate mais sans conviction.

Au passage, j'ai supprimé la checkbox et l'effet de saut de cellule en cellule pour le moment.

edit : j'ai trouvé une solution pour les formules. Si elles ne sont pas nombreuses on ne devrait pas trop ralentir le bazar.

function afficher(id){
  var data = bdd.getRange(id+debutDonnees-1,1,1,bdd.getLastColumn()).getValues().flat()
  var coord = coordXY()
  var myForm = saisie.getDataRange().getValues()
  var formules = saisie.getDataRange().getFormulas()
  for (var i =0; i<coord.length;i++){if(coord[i][0]!=-1){myForm[coord[i][0]][coord[i][1]] = data[i]}}
  saisie.getDataRange().setValues(myForm)
  for (var i=0;i<formules.length;i++){
    for (j=0;j<formules[i].length;j++){
      if (formules[i][j]!=''){saisie.getRange(i+1,j+1).setFormula(formules[i][j])}
    }
  }
}
Le 24/01/2022 à 08:15

C'est parfait. C'est exactement ce qu'il fallait. Bravo.

Le 24/01/2022 à 08:27

Une dernière chose. Est-ce qu'il serait possible, lorsqu'on choisit l'option "gagné" pour "Etat 1° inscription" ou "Etat réinscription" dans la fiche client (B3 et C3), d'ajouter une colonne dans l'onglet "A faire" qui reprend le nom et le prénom du client concerné?

Le 24/01/2022 à 09:33

Une solution serait de mettre en A1 de "À faire"

=transpose(query('Réponses'!B3:M;"select L,M where B='Gagné' or C='Gagné' "))
  • l'avantage est que
    • cela comprend aussi les réponses directes au formulaire et pas seulement les changements opérés dans la fiche client
    • cela tient compte aussi des modifications et fait disparaître le cas échéant
  • l'inconvénient est que en cas de changements les colonnes ne suivront pas les informations ajoutées

la réflexion n'est pas simple ...

C'est un autre sujet, je pense qu'il faut fermer celui-ci et en ouvrir un autre si besoin.

Le 24/01/2022 à 15:02

C'est ce que j'avais fait au départ mais ça n'allait pas parce que comme tu le fais remarquer, dès qu'il y a des changements, les colonnes ne suivaient pas. Je vais ouvrir un autre sujet

Le 24/01/2022 à 15:09

Il se passe quelque chose de bizarre pour l'une de mes formules

=query({ arrayformula(ROW('Réponses'!B3:CL)) \ 'Réponses'!B3:CL };" select Col1, Col6, Col55, Col12, Col13, Col4, Col58, Col60, Col62, Col10, Col9 where Col2 = 'Gagné' and Col3='' and Col4='Mouaad'")

Etonnamment, cette formule fait apparaître la ligne 3 qui pourtant ne correspond pas au critère (Col2='gagné' etc.). Est-ce que vous savez d'où ça peut venir?

Le 24/01/2022 à 16:12

3 correspond aux en-têtes que tu peux supprimer en ajoutant 0 à la fin

=query({ arrayformula(ROW('Réponses'!B3:CL)) \ 'Réponses'!B3:CL };" select Col1, Col6, Col55, Col12, Col13, Col4, Col58, Col60, Col62, Col10, Col9 where Col2 = 'Gagné' and Col3='' and Col4='Mouaad'";0)
Le 24/01/2022 à 16:14
Baylou Patrick a écrit :

C'est ce que j'avais fait au départ mais ça n'allait pas parce que comme tu le fais remarquer, dès qu'il y a des changements, les colonnes ne suivaient pas. Je vais ouvrir un autre sujet

dans ce cas, il faut mettre les données de chaque col;onne dans une BdD, c'est toujours le même problème

ou bien écrire à la main les noms et prénoms pour qu'ils soient figés

le problème du script, qui peut se faire, est qu'il faut prévoir la suppression de la colonne quand on change le statut, il faut aussi prévoir de pré-renseigner les colonnes dès la réception des réponses à la form

Le 25/01/2022 à 08:30

correspond aux en-têtes que tu peux supprimer en ajoutant 0 à la fin

Super. C'est parfait. Merci

Le 26/01/2022 à 13:03
Le 26/01/2022 à 15:54

Ok. Ne t'inquiète pas. Je le ferai.

Je me trouve confronté à un pb que je n'avais pas anticipé.

En fait, on est plusieurs à travailler sur le même fichier. Sur le fichier précédent, l'ouverture du formulaire pouvait se faire simultanément par plusieurs personnes. Dans le nouveau fichier, l'onglet "fiche client" ne peut être manipulé que par une seule personne. On est donc coincé si on veut travailler à plusieurs sur des fiches clients différentes.

Je me disais qu'il faudrait que j'externalise la base de données dans un ficher séparé que j'appellerais "BDD B2C" et que je crée plusieurs fichiers (un pour chaque personne qui travaille sur le B2C) qui iraient chercher et modifier les infos de ce fichier "BDD B2C". Est-ce que c'est possible ?

Le 26/01/2022 à 17:16
Baylou Patrick a écrit :

En fait, on est plusieurs à travailler sur le même fichier. Sur le fichier précédent, l'ouverture du formulaire pouvait se faire simultanément par plusieurs personnes. Dans le nouveau fichier, l'onglet "fiche client" ne peut être manipulé que par une seule personne. On est donc coincé si on veut travailler à plusieurs sur des fiches clients différentes.

Je me disais qu'il faudrait que j'externalise la base de données dans un ficher séparé que j'appellerais "BDD B2C" et que je crée plusieurs fichiers (un pour chaque personne qui travaille sur le B2C) qui iraient chercher et modifier les infos de ce fichier "BDD B2C". Est-ce que c'est possible ?

En effet ce serait possible avec pas mal de complexité sur l'ouverture du fichier de base et l'écriture.

Attention à ne pas modifier à plusieurs la même ligne.

Mais il y a une autre solution

  • créer plusieurs feuilles de siaise : saisie 1, saisie 2 etc... ou avec le nom de chacun
  • dans ce cas le code devient
// mike steelson

const nomFeuilleBaseDeDonnees = 'Réponses'
const debutDonnees = 4
const rangeID = 'G1'

const doc = SpreadsheetApp.getActiveSpreadsheet();
const bdd = doc.getSheetByName(nomFeuilleBaseDeDonnees)

function onOpen() {
  SpreadsheetApp.getUi().createMenu('↓ MENU ↓')
    .addItem('👉 Appliquer les modifications', 'saisir')
    .addToUi();
}

function onEdit(e) {
  var f = e.source.getActiveSheet();
  var r = e.source.getActiveRange();
  if (f.getName() != nomFeuilleBaseDeDonnees && r.getA1Notation() == rangeID){ 
    if (r.getValue()>0 && r.getValue()<=(bdd.getLastRow()-debutDonnees+1)){afficher(r.getValue())} 
    else {r.setValue(e.oldValue)}
  }
}

function saisir(){
  var saisie = doc.getActiveSheet()
  var id = saisie.getRange(rangeID).getValue()
  var data = bdd.getRange(id+debutDonnees-1,1,1,bdd.getLastColumn()).getValues()
  var coord = coordXY()
  var myForm = saisie.getDataRange().getValues()
  for (var i =0; i<coord.length;i++){if(coord[i][0]!=-1){data[0][i]=myForm[coord[i][0]][coord[i][1]]}}
  bdd.getRange(id+debutDonnees-1,1,1,bdd.getLastColumn()).setValues(data)
  SpreadsheetApp.getActive().toast('Modification(s) enregistrée(s) !', 'Fin de script 🗃️')
}

function afficher(id){
  var saisie = doc.getActiveSheet()
  var data = bdd.getRange(id+debutDonnees-1,1,1,bdd.getLastColumn()).getValues().flat()
  var coord = coordXY()
  var myForm = saisie.getDataRange().getValues()
  var formules = saisie.getDataRange().getFormulas()
  for (var i =0; i<coord.length;i++){if(coord[i][0]!=-1){myForm[coord[i][0]][coord[i][1]] = data[i]}}
  saisie.getDataRange().setValues(myForm)
  for (var i=0;i<formules.length;i++){
    for (j=0;j<formules[i].length;j++){
      if (formules[i][j]!=''){saisie.getRange(i+1,j+1).setFormula(formules[i][j])}
    }
  }
}

function coordXY(){
  var coord=[]
  var adresses = bdd.getRange('1:1').getValues().flat();
  adresses.forEach(function(ad){ 
    if(ad!='') {coord.push([ ad.match(/\d+/)[0]-1 , letterToColumn(ad.match(/\D+/)[0])-1 ])}
    else {coord.push([-1,-1])}
  })
  return coord
}

function letterToColumn(letter) {
  var column = 0, length = letter.length;
  for (var i = 0; i < length; i++) { column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1) }
  return column;
}

fais un essai en dupliquant l'onglet de saisie, je l'ai fait cela semble correct

Cela fait pense à guichet1, guichet2, guichet3 ...

Si cela ne fonctionne pas, j'ai une solution complémentaire.

Le 27/01/2022 à 08:07

C'était aussi une idée que j'avais eu et finalement, c'est très bien comme ça.

Je me disais juste que ce serait un peu lourd d'avoir tous ces onglets dans le même fichier.

En tout cas, ça fonctionne très bien.

Merci mille fois.

Le 27/01/2022 à 08:57

Attention toutefois que la cellule G1 ne soit pas une cellule "active" sur les autres onglets.

Ou alors il faudra ajouter une condition à

if (f.getName() != nomFeuilleBaseDeDonnees && r.getA1Notation() == rangeID)
Le 27/01/2022 à 09:10

Qu'est-ce que tu entends par active?

Le 27/01/2022 à 11:21

C'est à dire que si tu entres une valeur dans G1 d'une autre feuille, le code onEdit va réagir

Je propose donc plutôt de faire en sorte que les feuilles de saisie commencent toutes par "saisie" et que le contrôle soit

if (f.getName().substring(0,6) == 'saisie' && r.getA1Notation() == rangeID)
Le 28/01/2022 à 10:44

A la place de

if (f.getName() != nomFeuilleBaseDeDonnees && r.getA1Notation() == rangeID){

?

et je nomme les onglets "saisie Arthur", "saisie Zoé", etc. ?

Le 28/01/2022 à 11:08

absolument, tu as tout compris

enfin n'oublie pas de remettre { à la fin de la ligne

Rechercher des sujets similaires à "recuperer info base donnees"