Commencement JavaScript / google sheets
Bonjour la communauté, j'espère que vous allez bien. Je voudrais s'il vous plait solliciter votre aide pour mes premiers scripts que je fais dans le langage Java.
Ayant "assez" maitrisé le VBA depuis juin 2022, je me trouve en besoin de pouvoir coder en Java car dans notre entreprise on migre progressivement vers Sheets au lieu d'Excel.
pour faire simple j'ai dans cette feuille "OPERATEURS" le suivant :
et j'ai aussi sur la même feuille ce tableau "type" qui reste toujours vierge :
Ce que je souhaite faire c'est que lorsque j'ajoute un nouvel opérateur, ce tableau se copie sur une deuxième feuille appelée "CONTRATS". Puis lorsque j'en rajoute un autre opérateur ça se met à la suite dans la feuille "CONTRATS", comme suit :
Ce que j'ai réussi à faire jusque-là c'est ce résultat :
Mettant la mise en forme de coté pour le moment ... le "collage" s'est fait juste à la suite car je n'ai pas pu définir (comme dans VBA), la derligne+11 à laquelle je voudrais coller un nouveau tableau...
Voici mes codes JS (très débutants me semble-t-il):
Fonction OnEdit qui à la fin appelle la fonction operatCont (celle de l'ajout dans la feuille CONTRATS)
function onEdit(e) {
const ss = SpreadsheetApp.getActive(); // Pour éviter d'exécuter "getActive()" à double
const f1 = ss.getSheetByName('OPERATEURS');
const f2 = ss.getSheetByName('CONTRATS');
const celluleModifiee = e.range;
// Si la cellule A4
if (celluleModifiee.getA1Notation() == 'A4') {
// Convertir en majuscules
celluleModifiee.setValue(celluleModifiee.getValue().toUpperCase());
const valeur = celluleModifiee.getValue();
const cellule2 = f1.getRange('R3');
cellule2.setValue(valeur)
operatCont()
}
}là j'ai défini le OnEdit sur une seule cellule la A4 pour tester mais vraiment j'aurais besoin de faire comme un "If not intersect (Target) is nothing then" du VBA sur toute la colonne A. Je serais reconnaissant :)
La fonction d'ajout :
function operatCont() {
const ss = SpreadsheetApp.getActive(); // Pour éviter d'exécuter "getActive()" à double
const f1 = ss.getSheetByName('OPERATEURS');
const f2 = ss.getSheetByName('CONTRATS');
// Cellules de la plage à copier
const cellules = f1.getRange('K3:T12');
// Valeurs des cellules de la plage à copier
const tableau = cellules.getValues();
// Ligne 1 du tableau
const ligneTableau = tableau[0];
// Insertion des valeurs
f2.appendRow(ligneTableau);
//Ligne 2 du tableau
const ligneTableau2 = tableau[1]; //console.log(ligneTableau);
// Insertion des valeurs
f2.appendRow(ligneTableau2);
};J'espère avoir été assez clair et concis :) et merci d'avance de vos retours,
Ms
Bonjour,
Pour ce qui est de votre 1ere demande (que la condition se déclenche que si la colonne modifiée est la A et à partir de la ligne 4) vous pouvez faire ainsi :
if (celluleModifiee.getColumn() == 1 && celluleModifiee.getRow() >= 4) {Pour ce qui est de rajouter des lignes supplémentaires au niveau de votre import, si vous souhaitez que les nouvelles entrées soient en haut, changez :
// Ligne 1 du tableau
const ligneTableau = tableau[0];
// Insertion des valeurs
f2.appendRow(ligneTableau);
//Ligne 2 du tableau
const ligneTableau2 = tableau[1]; //console.log(ligneTableau);
// Insertion des valeurs
f2.appendRow(ligneTableau2);Par
f2.insertRowsAfter(1,11); // insere 11 lignes après la 1ère ligne
f2.getRange(2,1,tableau.length,tableau[0].length).setValues(tableau);Si vous souhaitez que ce soit inséré à la fin de la feuille :
f2.insertRowsAfter(f2.getLastRow(),11); // insere 11 lignes après la 1ère ligne
f2.getRange(f2.getLastRow()-11,1,tableau.length,tableau[0].length).setValues(tableau);Tout ceci est à tester, toutefois, je pense que transposer en l'état du VBA > GAS est peu efficient, il vaut mieux repartir d'une page blanche.
si vous avez un peu de temps et l'envie, allez lire les tutos :
https://www.sheets-pratique.com/fr/apps-script/introduction
Si vous n'avez ni le temps, ni l'envie, ChatGPT. (converti ce script VBA vers google apps script)
EDIT, mon message est un peu "brut" si bien sûr vous souhaitez un nouveau script on peut repartir de 0 avec plaisir
Bonsoir Pierre, je vous remercie pour votre appui aussi riche.
Effectivement je n'ai pas assez de temps pour apprendre le javaScript à l'image de ce que j'ai investi pour le VBA (je suis parti de 0).
Par l'occasion, je voudrais remercier ce site/forum de m'avoir accompagné le long de mon apprentissage. Je voudrais remercier Monsieur Dan de m'avoir tant aidé en mode messagerie privée.
Pour le ChatGPT et l'intelligence artificielle je n'y crois pas trop et je ne voudrais pas compter là-dessus car justement je vise en même temps une reconversion professionnelle, donc je devrai apprendre. ça sera très long et trop pénible je le sais :)
En ce qui concerne vos bouts de codes Pierre ça marche pour les suivants :
- la condition se déclenche pour la colonne A et à partir de la ligne 4
if (celluleModifiee.getColumn() == 1 && celluleModifiee.getRow() >= 4) {- les nouvelles entrées soient en haut :
f2.insertRowsAfter(1,11); // insere 11 lignes après la 1ère ligne
f2.getRange(2,1,tableau.length,tableau[0].length).setValues(tableau);MAIS pour ce dernier, ça n'a pas marché en fait ...
- Si vous souhaitez que ce soit inséré à la fin de la feuille :
f2.insertRowsAfter(f2.getLastRow(),11); // insere 11 lignes après la 1ère ligne
f2.getRange(f2.getLastRow()-11,1,tableau.length,tableau[0].length).setValues(tableau);A part ça je voudrais bien une explication de cette ligne s'il vous plait (en rédaction si possible) : les valeurs entre parenthèse
f2.getRange(2,1,tableau.length,tableau[0].length).setValues(tableau);Merci beaucoup
Ms
Bonjour,
Si vous pouviez partager un fichier avec votre trame (sans info sensible) voir pourquoi le script qui insère à la fin ne fonctionne pas.
Pour ce qui est de la ligne :
f2.getRange(2,1,tableau.length,tableau[0].length).setValues(tableau);Vos données sont stockés dans un tableau 2D :
// Cellules de la plage à copier
const cellules = f1.getRange('K3:T12');
// Valeurs des cellules de la plage à copier
const tableau = cellules.getValues();Voici un exemple de tableau 2D :
Ça donne ça dans la variable : [[fruit, poids, prix], [pomme, 2.0, 4.0], [poire, 1.5, 8.0], [banane, 5.0, 9.0]]
Il y a 4 sous liste de 3 données chacune.
Si on mesure la taille avec length voici ce que ça donne :
tableau.length = 4
tableau[0].length = 3
Donc, la fameuse ligne
f2.getRange(2,1,tableau.length,tableau[0].length).setValues(tableau);dans la feuille 2, on selectionne la plage (Range) qui débute à la ligne 2, colonne 1, sur un nombre de ligne = au nombre de sous liste (dans mon ex 4) et avec un nombre de colonnes = au nombre de données (dans mon ex 3), puis on insère les données.
Merci Pierre :) bien assimilé :
f2.getRange(2,1,tableau.length,tableau[0].length).setValues(tableau);
// le tableau qui reçoit la copie de "tableau" commence à la cellule 'A2' (2,1) et s'étend vers :
ligne x = tableau.length
// renseigne le nombre de lignes de la variable tableau à copier.
colonne y= tableau[0].length
// renseigne le nombre de colonnes de la variable tableau à copier (compte à partir de la première ligne [0]).A votre avis je marque le sujet clos ou bien je peux continuer avec autres choses en lien avec ces deux feuilles ?
En effet j'ai encore des conditions Find et Match à convertir en JS : si un opérateur est saisi dans feuille OPERATEURS colonne A, alors si je l'inscrit une deuxième fois, rien ne se passe dans la feuille CONTARTS ... des choses de la sorte en fait.
Ms
Vous pouvez continuer on en est qu'à 5 posts
j'ai encore des conditions Find et Match à convertir en JS : si un opérateur est saisi dans feuille OPERATEURS colonne A, alors si je l'inscrit une deuxième fois, rien ne se passe dans la feuille CONTARTS ... des choses de la sorte en fait.
Je ne connais rien en VBA, que souhaitez vous qu'il se passe si vous notez 2 fois le même opérateur en colonne A de votre feuille OPERATEURS ?
justement si je retape le même prénom en colonne A de la feuille OPERATEURS, rien ne se passe dans la feuille CONTRATS donc pas d'exécution du {....
j'ai remarqué aussi que lorsque j'efface un nom de la colonne A, le tableau se copier dans feuille CONTRATS mais sans nom. peut-être nous pourrions ajouter une condition similaire à "Si la cellule de la colonne A est non vide { ........ " on exécute le code.
C'est deux conditions à ce qui parait.
Pour la mise en forme je l'ai fait :
code :
// Cellules de la plage ou le collage se fait
const rangecontrat = f2.getRange(2,1,tableau.length,tableau[0].length);
const rangecontrat2 = f2.getRange(2,1,1,tableau[0].length);
const rangecontrat3 = f2.getRange(3,1,1,tableau[0].length);
// Insertion au début le dernier saisi le premier affiché dans CONTARTS
f2.insertRowsAfter(1,11); // insere 11 lignes après la 1ère ligne
rangecontrat.setValues(tableau);
rangecontrat.setBorder(true,true,true,true,true,true,'black',SpreadsheetApp.BorderStyle.SOLID);
rangecontrat2.activate();
rangecontrat2.mergeAcross();
rangecontrat2.setBackground("grey");
rangecontrat2.setFontColor("white");
rangecontrat2.setFontWeight("bold");
rangecontrat3.setFontWeight("bold");
rangecontrat3.setHorizontalAlignment("center");
rangecontrat2.setHorizontalAlignment("center");
rangecontrat2.setFontsize(16);
rangecontrat3.setFonsize(12);Ms
Afin de voir si la modification est en colonne A est bien l'ajout de texte, il est possible en effet d'ajouter une condition
if (celluleModifiee.getColumn() == 1 && celluleModifiee.getRow() >= 4 && celluleModifiee.getValue()!= "") {celluleModifiee.getValue()!= ""
on vérifie ici si la valeur de celluleModifiee n'est pas du vide.
Pour ce qui est du non déclenchement de la fonction operatCont() alors que vous avez modifié une cellule dans la plage c'est étonnant, pouvez vous partagez un fichier ?
Désolé je me suis mal exprimé Pierre! En effet je voudrais que lorsque je retape le même prénom en colonne A de la feuille OPERATEURS, que rien ne se passe dans la feuille CONTRATS donc pas d'exécution du {....
pour le partage comment puis-je faire ça ? J'envoie le lien Web de ma sheet ?
Ok donc pour éviter un doublon ? dans cas il faut vérifier si la nouvelle entrée en colonne A n'est pas déjà dans l'autre fichier, pour cela il faut au préalable stocker les données de la feuille contrat dans une variable.
Si le nom est bien dans la colonne J de la feuille contrat :
let nomContrats = f2.getRange(1,10,f2.getLastRow(),1).getValues();Ensuite dans la comparaison on vérifie si la celluleModifie est dans nomContrats
if (celluleModifiee.getColumn() == 1 && celluleModifiee.getRow() >= 4 && celluleModifiee.getValue() != "" && nomContrats.indexOf(celluleModifiee.getValue()) === -1) {Pour partager un fichier, oui le lien sheet, accessible par lien en lecture seule.
le paramètre en lecture seule est d'emblée là ou je dois le régler ?
Bonjour Pierre ainsi qu'à ceux qui suivent la discussion.
En effet, j'ai fait un test de ce bout de code qui vérifie l'existence ou pas d'un prénom dans la colonne H de la feuille CONTRATS :
let nomContrats = f2.getRange(1,8,f2.getLastRow(),1).getValues();
console.log(nomContrats)
console.log(nomContrats.indexOf('ERIC'));
// f2 étant la feuille CONTRATSj'ai cette vue sur la feuille :
ERIC existe bel et bien dans la colonne H et pourtant j'ai ça :
là il me donne les valeurs de la colonne correctement mais il met -1 au lieu du rang de ERIC dans la colonne !!
Pouvez vous m'éclaircir cela s'il vous plait ?
Merci
Bonjour,
Je vois d'où provient le problème, indexOf vérifie si Eric est dans nom contrat, mais uniquement la 1ère dimension, pas les secondes :
[ indexOf cherche ici [ pas ici ] , [ pas ici ] ]
La solution est de tout mettre sur 1 dimension, ajouter .flat() ainsi :
let nomContrats = f2.getRange(1,8,f2.getLastRow(),1).getValues().flat();
ça marche là ... impeccable. Je devrais apprendre ça et comprendre mieux en faisant des test et voir le résultat dans le journal d'exécution.
Sinon, il y a un autre soucis : quand les deux feuilles sont vierges, quand j'ajoute un nom rien ne se passe dans la feuille CONTRATS :
Feuille OPERATEURS :
Feuille CONTRATS
mais lorsque je place par exemple "AAA" dans H1 de la feuille CONTRATS, tout marche :
C'est en rapport avec la dernière ligne vide ici ? ... je laisse ça en mode "Bricolage" en masquant la ligne 1 contenant le "AAA" ou il y a quelque chose à améliorer au niveau du code, me semble-t-il ici :
// Insertion au début c'est à dire : le dernier saisi dans OPERATEURS est le premier affiché dans CONTARTS
f2.insertRowsAfter(1,11); // insere 11 lignes après la 1ère ligne
rangecontrat.setValues(tableau);ou rangecontrat est :
// Cellules de la plage ou le collage se fait
const rangecontrat = f2.getRange(2,1,tableau.length,tableau[0].length);Merci,
Il faudrait que vous partagiez votre fichier, je ne peux étudier un tel script de tête.
Si je mets le lien Web de la google sheet ça sera partagé en lecture seule ou faudra que je paramètre ça. En effet c'était la première fois où je fais ça !!
Bonjour,
Si je mets le lien Web de la google sheet ça sera partagé en lecture seule ou faudra que je paramètre ça.
Merci beaucoup Sébastien et Pierre, voici le lien de la sheet :
https://docs.google.com/spreadsheets/d/1Lk9lawxL73d-uuFaMhrVWZGA3YDFj2biAVpCanWXf0s/edit?usp=sharing
Ms
Peut etre en editeur c'est mieux Pierre non ? :
https://docs.google.com/spreadsheets/d/1Lk9lawxL73d-uuFaMhrVWZGA3YDFj2biAVpCanWXf0s/edit?usp=sharing
Ms