[Script Google Sheets] Incrémenter une formule
Bonjour à tous,
Depuis samedi je bloque sur un script. Je dois créer un script qui a pour trigger "OnEdit" sur la colonne A, l'effet désiré serait d'insérer une formule en automatique sur la ligne qui vient d'être remplie en colonne F.
Je tiens à préciser que la feuille est mise à jour automatiquement avec Zapier, qui vient renseigner des données en colonnes A, B, C, D et E.
J'ai songé à simplement créer la formule est l'étendre manuellement, mais Zapier vient "écraser" les formules pour laisser des cases vides (et ce n'est pas quelque chose que l'on peut régler sur Zapier).
Pour information la formule est la suivante :
=iferror(VLOOKUP(D2,Formations!A:B,2,FALSE),D2)Et voici le maigre résultat que j'ai pu obtenir jusqu'à présent : (je préviens ce n'est pas bien glorieux)
/** @OnlyCurrentDoc */
function onEdit(e) {
// Set a comment on the edited cell to indicate when it was changed.
var column = e.column;
column.setNote('Last modified: ' + new Date());
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('E:E').activate();
};
Bonjour,
Détrompe toi, je trouve cela tout à fait glorieux comme début ... moi aussi j'ai un poil galéré.
Normalement, tu n'en as pas besoin (du script), si tu adoptes une formula eus toute la colonne avec arrayformula
Mais j'ai vu que tu ajoutais une note ... donc cela se justifie. Mais où veux-tu mettre cette annotation ?
Pour l'annotation
/** @OnlyCurrentDoc */
function onEdit(e) {
// Set a comment on the edited cell to indicate when it was changed.
var cel = e.source.getActiveRange();
cel.setNote('Last modified: ' + new Date());
};ou bien
/** @OnlyCurrentDoc */
function onEdit(e) {
// Set a comment on the edited cell to indicate when it was changed.
var cel = e.source.getActiveRange();
var d = Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy à hh:mm")
cel.setNote('Last modified: ' + d);
};Pour la formule, mets ceci en F2
=arrayformula(IFERROR(vlookup(D2:D,Formations!A:B,2,false),D2:D))Merci pour ces éléments de réponse. Cependant je n'arrive pas à comprendre, la formule arrayformula est-elle complémentaire du script ?
Le Script suivant :
/** @OnlyCurrentDoc */
function onEdit(e) {
// Set a comment on the edited cell to indicate when it was changed.
var cel = e.source.getActiveRange();
var d = Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy à hh:mm")
cel.setNote('Last modified: ' + d);
};Si je comprends bien le trigger onEdit est fonctionnel grâce à ce morceau de code. Autre question, comment peut-on coller une formule précise dans une colonne entière selon le nombre de ligne remplies ? Par exemple si j'ai 101 lignes remplies (car 101 ventes de faite), il faudrait que la formule vienne se mettre à la 101ème ligne dans la colonne F. Est-ce qu'on peut utiliser du code VBA ou est-ce un autre type de code ?
Merci pour ces éléments de réponse. Cependant je n'arrive pas à comprendre, la formule arrayformula est-elle complémentaire du script ?
Autre question, comment peut-on coller une formule précise dans une colonne entière selon le nombre de ligne remplies ? Par exemple si j'ai 101 lignes remplies (car 101 ventes de faite), il faudrait que la formule vienne se mettre à la 101ème ligne dans la colonne F.
C'est exactement la puissance de arrayformula (pas réellement d'équivalent en excel). Donc oui le formule est suffisante et complémentaire du script : les deux sont indépendants.
https://support.google.com/docs/answer/3093275?hl=fr
Permet d'afficher des valeurs issues d'une formule de tableau sur plusieurs lignes
Tu reprends ta formule de base, tu indiques la colonne entière sans limite (D2:D au lieu de D2) et un encapsule le tout avec arrayformula.
D'accord j'ai compris.
Mais ce que je ne comprends pas c'est comment l'association du script et de la formule va permettre de remettre la formule dans la colonne F ? Pour exemple :
J'ai la formule déjà étendue admettons jusqu'à la ligne 100 dans la colonne F.
Zapier rentre une nouvelle donné dans la ligne 100, par conséquent sur la ligne 100 la formule en colonne F est effacée.
Le script + la formule est censé recréer la formule qui a été effacée par Zapier ?
Désolé j'aime bien comprendre ;P
Mais ce que je ne comprends pas c'est comment l'association du script et de la formule va permettre de remettre la formule dans la colonne F ? Pour exemple :
J'ai la formule déjà étendue admettons jusqu'à la ligne 100 dans la colonne F.
Zapier rentre une nouvelle donné dans la ligne 100, par conséquent sur la ligne 100 la formule en colonne F est effacée.
La formule et le script sont indépendants.
Je ne connais pas zapier, désolé ! mais si quelqu'un ou un logiciel entre une valeur en D100, la formule arrayformula de F2 s'appliquera, je dis bien de F2 (j'espère que tu n'as pas mis de formule en F100 car il ne faut pas gêner la propagation de la formule); il ne faut rien rentrer en F100
nota : j'ai corrigé ci-dessus, la formule est à mettre en F2 et rien en dessous de F2
D'accord, et existe-t-il un script qui permet qu'à chaque modification de la feuille, on vienne intégralement coller une formule sur une colonne ?
Oui c'est possible. Exemple
f.getRange("F100").setFormula('=_________ma_formule___________');Pourrais-tu mettre un lien vers une copie simplifiée de ton fichier ?
Au top, je sens qu'on se rapproche de la solution ah ah.
Voici le lien : https://docs.google.com/spreadsheets/d/1zs61pmltvbI_KxQnMd73KKE-wdg54Kx8BMauN0HNw4Q/edit?usp=sharing
Si je comprends bien la formule suivante :
f.getRange("F100").setFormula('=_________ma_formule___________');Le setFormula insère la formule dans la cellule ?
Je poursuis la première voie avant tout !
Efface la colonne F et mets ceci en F1
={"Formations";iferror(VLOOKUP(D2:D,Formations!A:B,2,FALSE),D2:D)}la colonne F se remplira d'elle-même.
Regarde si zapier détruit la formule ...
https://docs.google.com/spreadsheets/d/18ypo2ZhuPxxS57ok_QHNZgUr4EQ8gZw62YpV8NTC0DI/edit?usp=sharing
Ah waw, d'accord je comprends mieux la puissance d'une telle formule !
Mais le vlookup ne fonctionne apparemment plus ><
- As-tu bien mis
D2:Dau lieu deD2(2 fois) ? - As-tu effacé toutes les autres cellules en F sauf la F1 ?
Regarde le fichier dont j'ai mis le lien.
Ce que j'ai ajouté ici, c'est le titre inclus dans la formule
={"ici_le_titre_de_la_colonne"; ___ici_la_formule_sur_TOUTE_la_colonne____ }={"Formations";iferror(VLOOKUP(D2:D,Formations!A:B,2,FALSE),D2:D)}Ah yes, j'ai bien compris. Alors c'est exactement ça qu'il me fallait, merci ! Voici ce qu'on obtient :
Mais on voit que la recherche V ne s'effectue pas, elle devrait changer certains nom comme par exemple : "MENTAL D'ACIER - EN PRÉVENTE JUSQU'À DIMANCHE SOIR 22H" en "Mental d'Acier" tout simplement. Cela peut-il venir du fait qu'on ait "D2:D" dans la recherche ? Ce qui génère une erreur et donc est corrigé par le "sierreur" automatiquement ? (J'espère avoir été compréhensible XDD)
Mais on voit que la recherche V ne s'effectue pas, elle devrait changer certains nom comme par exemple : "MENTAL D'ACIER - EN PRÉVENTE JUSQU'À DIMANCHE SOIR 22H" en "Mental d'Acier" tout simplement. Cela peut-il venir du fait qu'on ait "D2:D" dans la recherche ? Ce qui génère une erreur et donc est corrigé par le "sierreur" automatiquement ? (J'espère avoir été compréhensible XDD)
ok, j'ai compris, en effet GSheets ne comprend pas la formule VLOOKUP dans un ARRAYFORMULA ! car l'une et l'autre balayent une ou plusieurs colonnes.
mais ouf! cela fonctionne avec une plage nommée
={"Formations";arrayformula(iferror(VLOOKUP(D2:D,plage,2,FALSE),D2:D))}https://docs.google.com/spreadsheets/d/18ypo2ZhuPxxS57ok_QHNZgUr4EQ8gZw62YpV8NTC0DI/edit?usp=sharing
Très fort ! Je l'ai mis en place et ça fonctionne niquel, dès qu'une vente se fait je vais voir le résultat (si la magie opère) et je te confirme ça :P
Au top, un grand merci à toi ! :)