[Script] Addition et Suppression de plusieurs feuilles
Bonjour,
J'ai un fichier B:
https://docs.google.com/spreadsheets/d/16SiFv-SRmxkBzen6UdkNI5_f4UEc1UV-ucQ9N07_NXk/edit?usp=sharing
Je voudrais dans la feuille "M" faire l'addition de toutes les donnees qui sont sur les feuilles avant la feuille "M".
exemple: B3 des feuilles "01", "02" et "03" doivent etre additionner. De meme pour les autres cellules qui se trouvent au meme endroit sur les feuilles.
Il y en a 3 feuilles avant la feuille "M" mais il pourrait en avoir jusqu'a 31 par mois (cellules remplis ou non au meme endroit avec des chiffres differents).
- Creer un bouton dans la feuille "M" nomme "CALCUL MOIS" pour additionner les feuilles qui sont avant la feuille "M". Lier le script de l'addition.
- Creer un bouton dans la feuille "M" nomme "DEL ALL" qui supprimera en fin de mois toutes les feuilles qui se trouve avant la feuille "M". Lier le script de la suppression.
Merci pour votre aide.
Jack
Tu peux mettre cette fonction dans les cellules concernées
=sum3D()avec ce script
function sum3D(){
var fs = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var plage = SpreadsheetApp.getActiveSheet().getActiveCell().getA1Notation()
for (var i=0 ; i<fs.length ; i++){
if(fs[i].getName() == 'd'){var f1=i}
if(fs[i].getName() == 'f'){var f2=i}
}
var result = 0
for (var i=f1+1 ; i<f2 ; i++){
result += fs[i].getRange(plage).getValue()
}
return result
}et en mettant une feuille appelée d avant la première feuille à prendre en compte et une feuille appelée f après la dernière feuille à prendre en compte
par contre, il est toujours plus judicieux de travailler les données globalement, toutes journées confondues, dans un seul tableau et en faire des synthèses par TCD
La fonction "=sum3D()" je dois la mettre dans quelle feuille? Toutes ou bien que "M"?
peux tu me donne un exemple d'une cellule avec =sum3D() s'il te plaît?
Les feuilles d et f sont des feuilles vides à placer au début et à la fin?
Merci
Jack
que la feuille M
je peux améliorer et ne pas voir à créer les feuilles d et f s'il s'agit de toutes les feuilles avant M, ou toutes les feuilles de la 2ème à celle avant M
s'il y a beaucoup de cellules, on peut aussi procéder de façon groupée
mets un exemple "ouvert" que je modifierai
Autre version ... faire un "mapping" des cellules à sommer
function onOpen() {
SpreadsheetApp.getUi().createMenu('⇩ M E N U ⇩')
.addItem('👉 Actualiser la somme des données', 'sum3DbyMapping')
.addToUi();
}
function sum3DbyMapping() {
var mapping = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('mapping');
var cells = mapping.getRange(1, 1, mapping.getLastRow(), mapping.getLastColumn()).getValues();
var result = Array.from({ length: mapping.getLastRow() }, () => Array.from({ length: mapping.getLastColumn() }, () => 0));
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
var synth = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('synthèse')
var index = synth.getIndex();
Logger.log(index)
for (var i = 0; i < index - 1; i++) {
data = sheets[i].getRange(1, 1, mapping.getLastRow(), mapping.getLastColumn()).getValues();
cells.forEach((row, i) => {
row.forEach((cell, j) => {
result[i][j] += (cell != '') ? data[i][j] * 1 : 0
})
});
}
cells.forEach((row, i) => {
row.forEach((cell, j) => {
if (cell != '') { synth.getRange(i + 1, j + 1).setValue(result[i][j]) }
})
});
}Dans synthèse on retrouvera la somme de toutes les données des feuilles précédentes !
Même version plus rapide s'il y a beaucoup de plages
Nécessite d'activer le service Google Sheets API
function quickSum3DbyMapping() {
var mapping = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('mapping');
var synth = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('synthèse')
var cells = mapping.getRange(1, 1, mapping.getLastRow(), mapping.getLastColumn()).getValues();
var result = Array.from({ length: mapping.getLastRow() }, () => Array.from({ length: mapping.getLastColumn() }, () => 0));
for (var i = 0; i < synth.getIndex() - 1; i++) {
var x = SpreadsheetApp.getActiveSpreadsheet().getSheets()[i].getRange(1, 1, mapping.getLastRow(), mapping.getLastColumn()).getValues();
cells.forEach((row, i) => {
row.forEach((cell, j) => {
result[i][j] += (cell != '') ? x[i][j] * 1 : 0
})
});
}
const ranges = []
const values = []
cells.forEach((row, i) => {
row.forEach((cell, j) => {
if (cell != '') {
ranges.push(synth.getName() + '!' + columnToLetter(j + 1) + (i + 1))
values.push([result[i][j]])
}
})
});
const data = ranges.map((e, i) => ({ range: e, values: [values[i]] }));
const spreadsheetId = SpreadsheetApp.getActiveSpreadsheet().getId()
Sheets.Spreadsheets.Values.batchUpdate({ data, valueInputOption: "USER_ENTERED" }, spreadsheetId);
}
function columnToLetter(column) {
var temp, letter = '';
while (column > 0) {
temp = (column - 1) % 26;
letter = String.fromCharCode(temp + 65) + letter;
column = (column - temp - 1) / 26;
}
return letter;
}https://docs.google.com/spreadsheets/d/16SiFv-SRmxkBzen6UdkNI5_f4UEc1UV-ucQ9N07_NXk/edit?usp=sharing
Bonjour voici le lien en prenant en conte le faite qu il puisse avoir jusqu a 31 feuille avant la feuilles M.
Si tu peux me le faire sans les feuille de debut et fin que tu as mis.
J aimerais bien avoir un bouton dans la feuille M quand je clique dessus, quelque soit le nombre de jour avant ca me fait les totaux et aussi un bouton pour supprimer toutes les feuilles avant le M en fin de mois.
merci
jack
Je n'aime pas travailler avec les boutons, j'ai mis un menu.
J'ai indiqué dans la feuille "mapping" par ♣ les cellules qui font l'objet d'une somme des feuilles précédents la feuille M; A compléter si besoin.
Pour la suppression c'est à venir.
suppression feuilles
c'est ajouté au menu
function suppFeuilles() {
var ss = SpreadsheetApp.getActiveSpreadsheet()
ss.getSheets().forEach(function (sh) {
if (sh.getIndex() < ss.getSheetByName('M').getIndex()) { ss.deleteSheet(sh) }
})
}Génial et ca se fait automatiquement a chaque ouverture?
si je veux cacher la feuille mapping ca ne changera rien?
Comment a tu créer ta feuille mapping? je serais curieux de savoir.
Jack
la feuille mapping a été créée comme copie de la feuille M, j'y ai ajouté ensuite le trèfle dans certaines cases
tu peux cacher cette feuille
pour lancer le calcul à chaque ouverture, ajoute, ajoute
function onOpen() {
quickSum3DbyMapping()
}attention, cela va ajouter quelques dizaines de secondes à l'ouverture
Comment se fait donc le calcul sans le faire à l'ouverture?
faut il que je lance le script?
Jack
A l'ouverture, un menu doit apparaître en haut à droite, tu choisis l'option
Super et si je veux refaire ça sur d'autres fichiers je fais comment le bouton MENU? Tu peux m'expliquer s'il te plaît?
le bouton menu est dans le code de onOpen
function onOpen() {
SpreadsheetApp.getUi().createMenu('⇩ M E N U ⇩')
.addItem('👉 Actualiser la somme des données', 'sum3DbyMapping')
.addToUi();
}Il y a un petit problème:
dans la feuille "M" les formules ne se font pas quand je lance le calcul. voir photo en vert.
Les intitules de la colonne A et les autres se mettent a ZERO une fois le calcul lance. voir photo en rouge.
Le script ne différencie pas le texte et les chiffres.
Je voudrais avoir les même intitules (colonne A et autres) que sur les feuille 01, 02 et 03.
Les chiffres du script des additions sont dans les tableaux avec bordures.
Peux tu le corriger stp
Jack
désolé, c'est corrigé
Merci.
Dans le meme principe mais differentes cellules a additionner: Feuille M toujours entre F4 et F27 pour les additions.
Fichier D:
https://docs.google.com/spreadsheets/d/1vKrNsVkv1S_SyMOAdY-_V3ZW2II09njXqTX7i9J1SSo/edit?usp=sharing
Peux tu faire la meme chose stp que le fichier B?
Jack
Crée une feuille mapping dans laquelle tu mets ♣ aux endroits où le calcul doit se faire, et tu recopies le script.
Ce que j'ai fait ... mais pas testé.