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

Baylou Patrick a écrit :
Bonjour,
Dans un onglet "fichier client", je souhaiterais récupérer les infos d'un onglet "base de données" sur un client que j'aurais choisi.
Après avoir effectué quelques modifications, j'aimerais pouvoir valider celles-ci et que la bdd se mette à jour.
Est-ce que vous sauriez m'aider à réaliser ça?

je vois bien la "fichier client", mais pas la "base de données"
pour pouvoir modifier, il faut pouvoir relier à la ligne de la base de données ... quel est l'identifiant qui permet de retrouver la ligne et afficher puis modifier les données ?
question subsidiaire : tu as abandonné l'idée du formulaire latéral ? pourquoi pas compte tenu du nombre d'informations

cela me rappelle ceci https://docs.google.com/spreadsheets/d/1tU3koMXsQjKwPj2uXNvcTgaPAUHDQ4hRIb9CfptWL0c/copy que j'avais fait à mes débuts il y a un an ... mais il faudrait optimiser l'écriture car vu le nombre d'info cela risque de prendre du temps !

De toute façon je vais partir sur un projet générique paramétré facile à adapter, le plus important pour moi est de connaître le point 2

Le 20/01/2022 à 10:42

1. La "base de données" est l'onglet "réponses"

2. A part le numéro de ligne qui est unique, pour l'instant, je n'ai pas d'autres

3.Oui, j'ai finalement opté pour un formulaire google classique. C'est beaucoup plus simple à manipuler et plus rapide à ouvrir.

Le 20/01/2022 à 11:25

ah ok ok ok tes données sont issues d'une form google et tu souhaites alors visualiser et modifier aussi ?

Le 20/01/2022 à 11:47

Compte tenu du nombre de données je vais te laisser intégrer l'appli que j'ai réalisée hier

Dans la feuille de saisie, voir entouré en rouge

mets une check-box en A1
mets n'importe quoi en première ligne dans la dernière colonne utile
mets n'importe quoi en première colonne dans la dernière ligne utile
mets une validation de données dans la zone qui recevra l'horodatage issu de la form google, ce qui facilitera la recherche de la "ligne"

Dans la feuille de données, en première ligne, indique les cellules où seront présentes les informations

Voici le script

// mike steelson

const doc = SpreadsheetApp.getActiveSpreadsheet();
const bdd = doc.getSheetByName('BdD')
const saisie = doc.getSheetByName('Saisie')
var adresses = bdd.getRange('1:1').getValues().flat();

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

function onEdit(event) {
  var f = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  if (f.getName() == 'Saisie'){
    if (f.getRange(1,1).isChecked()){ // transferts auto d'une zone à l'autre
      var item = adresses.indexOf(r.getA1Notation());
      if (item < adresses.length - 1){ // sauf le dernier
        f.setActiveSelection(adresses[item + 1]);
      }
    }
    if (r.getA1Notation() == bdd.getRange('A1').getValue()){
      var liste = bdd.getRange(3,1,bdd.getLastRow()-2,1).getValues().flat()
      if (liste.indexOf(r.getValue()) == -1){
        r.setValue(event.oldValue)
      } else {
        afficher(r.getValue())
      }
    }
  }
}

function saisir(){
  var coord = [], result = []
  adresses.forEach(function(ad){ coord.push([ ad.match(/\d+/)[0]-1 , letterToColumn(ad.match(/\D+/)[0])-1 ]) })
  var data = saisie.getRange(1,1,saisie.getLastRow(),saisie.getLastColumn()).getValues()
  coord.forEach(function(r){ result.push (data[r[0]][r[1]]) })
  var liste = bdd.getRange(3,1,bdd.getLastRow()-2,1).getValues().flat()
  var id = saisie.getRange(bdd.getRange('A1').getValue()).getValue()
  if (liste.indexOf(id) != -1){
    bdd.getRange(liste.indexOf(id)+3,1,1,result.length).setValues([result])
    SpreadsheetApp.getActive().toast('Modification(s) enregistrée(s) !', 'Fin de script 🗃️')
  }
}

function afficher(id){
  var liste = bdd.getRange(3,1,bdd.getLastRow()-2,1).getValues().flat()
  if (liste.indexOf(id) != -1){
    var data = bdd.getRange(3,1,bdd.getLastRow()-2,bdd.getLastColumn()).getValues().filter(r => r[0] == id).flat()
    var coord = []
    adresses.forEach(function(ad){ coord.push([ ad.match(/\d+/)[0]-1 , letterToColumn(ad.match(/\D+/)[0])-1 ]) })
    var myForm = saisie.getDataRange().getValues()
    for (var i =0; i<data.length;i++){
      myForm[coord[i][0]][coord[i][1]] = data[i]
    }
    saisie.getDataRange().setValues(myForm)
  }
}

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;
}

Si A1 est coché, alors le parcours d'une zone à l'autre est automatique.

Le menu (entouré en bleu dans la copie d'écran) permet l'enregistrement de la modification.

L'identifiant est ce qui est dans la colonne A.

Le 20/01/2022 à 14:31

On est d'accord que dans mon cas 'BdD', c'est 'réponses', n'est-ce pas?

Le 20/01/2022 à 14:32

Ca ne donne pas grand chose. Bizarrement, même le menu ne s'affiche pas.

Le 20/01/2022 à 15:46

Baylou Patrick a écrit :
On est d'accord que dans mon cas 'BdD', c'est 'réponses', n'est-ce pas?

oui bien sûr

et 'saisie' est 'Fiche client'

le menu s'affichera à la prochaine ouverture et chaque fois que tu ouvriras
il manque un point essentiel, c'est l'horodateur dans ta fiche qui est la clé
j'avais un bug ici if (f.getName() == 'Fiche client'){
maintenant il faut que je vois comment faire avec des horodatages en clé, ce que je n'avais pas prévu ... => je vais donc effectivement changer en mettant la ligne si je n'arrive pas à résoudre ce point ……… à suivre d'ici demain

Le 20/01/2022 à 17:00

ok, il faut travailler avec les dates de même nature, donc avec une fonction de type

function trans(liste){
  var newListe = []
  liste.forEach(function(d){
    newListe.push(Utilities.formatDate(d, Session.getScriptTimeZone(), "dd/MM/yyyy hh:mm:ss"))
  })
  return(newListe)
}

j'ai pris une copie pour y travailler d'ici demain matin

Le 20/01/2022 à 17:43

j'ai modifié le script

la saisie se fait en G1 sur le n° de ligne

en fait il y avait aussi un autre écart qui est que tu ne reprenais pas toutes les données de la feuille réponses ... j'ai pris cela en compte aussi

fais quelques essais

// mike steelson

const doc = SpreadsheetApp.getActiveSpreadsheet();
const bdd = doc.getSheetByName('Réponses')
const nomFeuilleSaisie = 'Fiche client'
const saisie = doc.getSheetByName(nomFeuilleSaisie)
var adresses = bdd.getRange('A1:BI1').getValues().flat();

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

function onEdit(event) {
  var f = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  if (f.getName() == nomFeuilleSaisie){
    if (f.getRange(1,1).isChecked()){ // transferts auto d'une zone à l'autre
      var item = adresses.indexOf(r.getA1Notation());
      if (item < adresses.length - 1){ // sauf le dernier
        f.setActiveSelection(adresses[item + 1]);
      }
    }
    if (r.getA1Notation() == 'G1'){
      if (r.getValue()>0 && r.getValue()<=(bdd.getLastRow()-2)){
        afficher(r.getValue())
      } else {
        r.setValue(event.oldValue)
      }
    }
  }
}

function saisir(){
  var coord = [], result = []
  adresses.forEach(function(ad){ coord.push([ ad.match(/\d+/)[0]-1 , letterToColumn(ad.match(/\D+/)[0])-1 ]) })
  var data = saisie.getRange(1,1,saisie.getLastRow(),saisie.getLastColumn()).getValues()
  coord.forEach(function(r){ result.push (data[r[0]][r[1]]) })
  var id = saisie.getRange('G1').getValue()
  bdd.getRange(id+2,1,1,result.length).setValues([result])
  SpreadsheetApp.getActive().toast('Modification(s) enregistrée(s) !', 'Fin de script 🗃️')
}

function afficher(id){
    var data = bdd.getRange(id+2,1,1,bdd.getLastColumn()).getValues().flat()
    var coord = []
    adresses.forEach(function(ad){ coord.push([ ad.match(/\d+/)[0]-1 , letterToColumn(ad.match(/\D+/)[0])-1 ]) })
    var myForm = saisie.getDataRange().getValues()
    for (var i =0; i<coord.length;i++){
      myForm[coord[i][0]][coord[i][1]] = data[i]
    }
    saisie.getDataRange().setValues(myForm)
}

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;
}

Le 21/01/2022 à 09:24

Merci pour ta proposition.
Ca marche en partie mais il y a deux problèmes. Ca ne renvoie dans l'onglet "fiche client" que jusqu'à la colonne J et ça renvoie avec un décalage de deux lignes.
Par exemple, si je mets 6 en G1, il renvoie les infos de la ligne 8.

Quels paramètres je dois bouger pour modifier ce décalage?

Le 21/01/2022 à 10:46

Baylou Patrick a écrit :
Ca marche en partie mais il y a deux problèmes.

Tout fonctionne ou fonctionnait mais tu as ajouté une ligne et les formats ne sont cohérents entre tes données et ta feuille

Les données étaient bien renvoyées jusque la colonne BI ... si cela se bloque en J c'est que ta donnée n'est pas compatible avec le format que tu imposes à la cellule E10
La ligne est celle des données utiles, donc commençant à la ligne 3. J'ai anticipé le fait qu'elle commence plus bas ce matin, mais il faut avant tout régler de ton côté le problème de format qui bloque.
Dans l'évolution que je suis en train de faire, j'ai aussi prévu que certaines colonnes ne soient pas reprises (comme tu l'avais fait après BI mais cette fois-ci à l'intérieur de la série entre A et BI)

Le 21/01/2022 à 11:49

Super. Ca fonctionne. J'ai réglé le pb de format qui bloquait tout.

Le 21/01/2022 à 11:51

Dans l'évolution que je suis en train de faire, j'ai aussi prévu que certaines colonnes ne soient pas reprises (comme tu l'avais fait après BI mais cette fois-ci à l'intérieur de la série entre A et BI)

Ce serait une super évolution. Ca pourrait être très utile. Merci

Le 21/01/2022 à 11:52

Il y aurait ce problème de décalage. Quel est le paramètre qui anticipe les données utiles à la ligne 3?