Google Script - Lister et modifier formules liées à d'autres spreadsheets
Google Script - Lister et modifier formules liées à d'autres spreadsheets
Bonjour,
J'ai essayé de chercher si ce thème avait déjà des solutions mais je n'ai pas réussi à trouver.
D'où mon problème:
Je travaille avec des spreadsheets complexe est lourd. Du coup ces spreadsheets sont connectés entre eux via des fonctions importrange.
En même temps, j'ai besoin de faire évoluer ces fichiers tout en gardant la version antérieure pour lui faire faire des itérations.
Du coup, je me retrouve avec une version 1 de mes spreadsheets (version 1 de toutes les spreadsheets) connectées entre eux via des formules importrange et je duplique toutes ces spreadsheets (création version 2 de toutes les spreadsheets) pour refaire les liens entre elles via de nouvelles formules importrange (fonctionnalité existant sur excel mais pas sur sheet pour changer les liens entre feuilles).
Ces manipulations de création de version 2, puis 3 ,..., sont lourdes et sources d'erreurs.
Nota: Egalement ce script pourrait servir, lorsque l'on transforme un excel en Google Sheet !
Ma question:
Savez vous s'il est possible de développer un script pour faire cette fonctionnalité manquante dans Google sheet?
C-a-d:
1- le script scanne toutes les formules du spreadsheet
2- Le script liste tous les IDs et le nom des autres spreadsheet avec lequel il est lié
3- L'utilisateur, indique le nouvel Id par lequel, il souhaite le remplacer
4- L'utilisateur lance un nouveau script pour que celui -ci remplace l'ancien Id par le nouveau dans tout le spreadsheet.
5- Egalement, ce script pourrait effectuer ce scan dans le cadre des nouveau fichier Google sheet créé via un fichier excel ...
Merci pour votre aide et conseil par rapport à cette problématique.
Cordialement
Bonjour,
1- le script scanne toutes les formules du spreadsheet
2- Le script liste tous les IDs et le nom des autres spreadsheet avec lequel il est lié
3- L'utilisateur, indique le nouvel Id par lequel, il souhaite le remplacer
4- L'utilisateur lance un nouveau script pour que celui -ci remplace l'ancien Id par le nouveau dans tout le spreadsheet.
5- Egalement, ce script pourrait effectuer ce scan dans le cadre des nouveau fichier Google sheet créé via un fichier excel ...
Merci pour votre aide et conseil par rapport à cette problématique.
Cordialement
houlà, c'est sûr que travailler avec un sac de nœuds c'est pas facile ... je compatis !
j'espère qu'il y a quand même une hiérarchie de fichiers (maitre > esclave) et qu'il n'y a pas d'imports croisés ...
pour commencer, voici une façon de repérer toutes les formules avec importrange
on doit pouvoir ensuite en tirer l'ID si besoin avec une expression régulière (sachant que dans une même formule il peut y avoir plusieurs importrange ...)
mais ce n'est pas nécessaire si on a une table IDavant et IDaprès => dans ce cas il faut aussi modifier le script pour connaître la cellule où se trouve cette formule
les seules questions que je me pose c'est comment déclarer les feuilles liées (demande de google quand on écrit une importrange il me semble)
je n'ai pas compris le point 5 sur excel !
edit : avec N° de ligne et de colonne concernées
script complété plus loin ...Il y a plus simple ...
en mettant les conditions avant et après, ceci changera directement les données dans les formules
// enable Sheets API at Advanced Google services
function remplacement() {
const replaceConditions = {
'ABC': "abc",
'DEF': "def",
};
const ss = SpreadsheetApp.getActiveSpreadsheet();
const requests = Object.entries(replaceConditions).map(([k, v]) => ({
findReplace: {
find: k.toString(),
replacement: v.toString(),
includeFormulas: true,
allSheets: true,
},
}));
Sheets.Spreadsheets.batchUpdate({ requests: requests }, ss.getId());
}
Bonjour Mike
encore merci pour ta réactivité. Désolé de ne pas l'être autant que toi....
je suis en déplacement sans accès à mon ordinateur...du coup je regarderais ça à mon retour.
Mais, voici des réponses à tes questions:
Je n'ai pas compris le point 5 sur excel !
en fait lorsque l'on transforme un Excel qui est construit sur la même base que décrit sur mon post d'origine, et qu'on le transforme en sheet, forcément ces liens entre fichier n'existent plus. Sheet transforme le nom du fichier dans la formule par [ n+1], cad [ 1] puis [ 2] et ainsi de suite.
Du coup, je me disais que ce script pourrait lister toutes les cases où existent une formule contenant [ n+1].
Le script ferait ensuite pareil....
en effet, il faudra ensuite associer les feuilles
dans mon cas de figure, les feuilles disposent du même nom entre deux versions de fichier. Du coup, si le scripte arrive à lister toutes ces références à des spredsheets extérieures et qu'il les remplacent par d'autres pré sélectionnés par l'utilisateur, alors ça fonctionnera.
j'ai essayé hier matin, de réutiliser ton premier script, mais je ne suis pas arrivé à remplacer la fonction
Logger.log(f.getName() + ' ligne=' + numRow + ' colonne=' + numCol + ' ' + formule)
j'ai essayé par appendrow, mais par manque de temps je n'y suis pas arrivé à logger les infos, sur une sheet. L'apprentissage en script est long et laborieux !
je réessaie mardi ou mercredi.
désolé et encore merci
Bonjour,
Je n'ai pas compris le point 5 sur excel !
en fait lorsque l'on transforme un Excel qui est construit sur la même base que décrit sur mon post d'origine, et qu'on le transforme en sheet, forcément ces liens entre fichier n'existent plus. Sheet transforme le nom du fichier dans la formule par [ n+1], cad [ 1] puis [ 2] et ainsi de suite.
Du coup, je me disais que ce script pourrait lister toutes les cases où existent une formule contenant [ n+1].
intéressant ... pourrais-tu mettre en ligne un extrait d'un tel fichier ?
j'ai essayé hier matin, de réutiliser ton premier script, mais je ne suis pas arrivé à remplacer la fonction
Logger.log(f.getName() + ' ligne=' + numRow + ' colonne=' + numCol + ' ' + formule)
j'ai essayé par appendrow, mais par manque de temps je n'y suis pas arrivé à logger les infos, sur une sheet. L'apprentissage en script est long et laborieux !
- dans ce cas, il faut ajouter y mettre la nouvelle formule, c'est à dire en remplaçant l'ID
f.getRange(numRow,numCol).setFormula("ici la nouvelle formule")ce qui donne ...
function remplacementImportFunctions(){
var doc = SpreadsheetApp.getActiveSpreadsheet()
doc.getSheets().forEach(function(f){
var formules = f.getDataRange().getFormulas()
var numRow = 0
formules.forEach(function(row){
numRow++
var numCol = 0
row.forEach(function(formule){
numCol++
if (formule!=''){
if (formule.toLowerCase().indexOf('importrange') > 0) {
f.getRange(numRow,numCol).setFormula(remplacement(formule))
}
}
})
})
})
}
function remplacement(formule){
var ids = ['idAvant1👉idApres1','idAvant2👉idApres2']
var nouvelleFormule = formule
for (var i in ids){
var regExp = new RegExp(ids[i].split('👉')[0], "g");
var nouvelleFormule = nouvelleFormule.replaceAll(regExp, ids[i].split('👉')[1])
}
return (nouvelleFormule)
}- en fait, il est plus intéressant d'utiliser le second script
- quoique cela dépendra de ce qu'on trouvera dans le fichier d'origine excel
prends ton temps, je suis à ta disposition ...
Bonjour Mike,
j'ai essayé de trouver quelques minutes pour te répondre.
Voici le résultat d'un fichier excel transformé par Google (via la fonction drag & Drop dans drive) pour obtenir un Google Sheet.
J'ai très, très simplifié le fichier dont voici un extrait de résultat:
https://docs.google.com/spreadsheets/d/1uIy6XPhHSAN2VfQO7Qmwkoc8_gvmjtMoLPRMF-X7PNM/edit?usp=sharing
voici le type de formules qui apparaissent :
=VLOOKUP($C141,[16]PI!$B:$BR,36,FALSE)
=VLOOKUP(B3;'[17]FI-Synthesis'!$B:$FF;7;FALSE)
=SUM('[15]Centralisat'!$K:$K)
=VLOOKUP($C156,[18]Export!$B:$DG,[18]Export!CD$1,FALSE)
Concernant ta proposition de script, je suis désolé mais je ne suis arrivé à rien faire...!!!!!
Je vais avoir besoins de quelques années pour m'en sortir je crois, avec l'apprentissage de javascript !
1- En fait rien ne s'affiche dans ma feuille pour essayer de loguer tous les résultats de la recherche des importrange de ton premier script
2- Dans ton dernier script, je ne comprends pas, comment puis-je dire au script, les nouvelles formules à utiliser?
A bientôt
houlà, je n'ai jamais vu une telle traduction !!
on va prendre les choses dans l'ordre ...
- je suppose que [15], [16], [17] et [18] sont normalement des fichiers externes ? si oui, as-tu ces fichiers au format GSheets ? et je suppose que c'est là que tu mets tes importrange ...
- les 2 formules en erreur proviennent du fait qu'il s'agit d'une notation anglosaxone de la formule alors que ton fichier est en paramètre FR ... il faut alors remplacer les
,par des;- la raison est qu'en France la virgule sert de séparateur des nombres décimaux
une fois ces 2 points résolus on pourra se pencher sur le reste
je vais préparer un jeu de 3 fichiers pour te montrer comment les scripts fonctionnent sur des fichiers sans erreur avec importrange
une démo
je vais préparer un jeu de 3 fichiers pour te montrer comment les scripts fonctionnent sur des fichiers sans erreur avec importrange
- prends une copie du fichier https://docs.google.com/spreadsheets/d/1FjnxUCX-UQfpIlSFD8kGvMFh1uy00Ire06JYiMqscEs/edit?usp=sharing
- va dans le nouveau menu
- actionne l'une ou l'autre des fonctions (les fichiers de base sont identiques, la seule chose qui diffère c'est le tri)
- regarde ce qui se passe dans la fonction de la cellule jaune
En réponse:
- je suppose que [15], [16], [17] et [18] sont normalement des fichiers externes ? si oui, as-tu ces fichiers au format GSheets ? et je suppose que c'est là que tu mets tes importrange ...
Oui tous les xslx sont transformés en sheets et je récupérerais les nouveaux IDs via u. scan du drive
D'accord je comprends mieux comment cela fonctionne, mais je n'ai pas réussit à faire fonctionner la seconde version !
Je me suis créé un fichier de travail basé sur ton modèle
J'ai ajouté un onglet "Dashboard", où j'aimerais que le script logue toutes les formules contenants "importrange" ou "[15], [16], [17] et [18]" en colonne A, B, C.
Une fois, l'étape précédente effectuée, l'utilisateur saisie les liens des nouveaux google sheet en colonne E et mets true, s'il souhaite que le script effectue le changement.
Une fois, terminé, l'utilisateur lance la fin du script, via le menu (pas besoin de onchange function), pour effectuer la modification des liens, sur toutes les formules où la case checkbox est cochée.
Merci et à bientôt
ok, je regarde cela si possible dans la journée
je regarde aussi s'il est possible de transformer les , en ;
question quand même : pourquoi tu utilises à la base excel et qu'ensuite tu passes à GSheets ?
Bonjour Mike,
"question quand même : pourquoi tu utilises à la base excel et qu'ensuite tu passes à GSheets ?"
Parce que c'est une base que j'utilise dans mon entreprise, que l'on utilise depuis quelques années, basée sur excel.
Seulement, nous avons basculé nos outils de microsoft sur Google.
Bonne journée
J'ai ajouté un onglet "Dashboard", où j'aimerais que le script logue toutes les formules contenants "importrange" ou "[15], [16], [17] et [18]" en colonne A, B, C.
evolution ci-dessouspour la colonne A je m'en suis tenu pour le moment qu'à une seule occurence
petite évolution ...
- tenir compte du fait que dans une seule formule il peut y avoir plusieurs importrange, plusieurs [xx]
- tenir compte du fait que dans importrange il est tout à fait possible de n'indiquer que l'ID du fichier
function importCurrentIdFormulas() {
var doc = SpreadsheetApp.getActiveSpreadsheet()
var db = doc.getSheetByName('Dashboard')
var regExp = /\[\d+\]/g
var regExp3 = /"[\s\S\w]+?"/g
var regExp2 = /'*\[\d+\][\s\S\w]+?,|'*\[\d+\][\s\S\w]+?;/g
var regExp1 = /\([\s\S\w]+?",|\([\s\S\w]+?";/g
doc.getSheets().forEach(function (f) {
if (f.getName() != db.getName()) {
var formules = f.getRange(1, 1, f.getLastRow(), 3).getFormulas()
var numRow = 0
formules.forEach(function (row) {
numRow++
var numCol = 0
row.forEach(function (formule) {
numCol++
if (formule != '') {
if (formule.toLowerCase().indexOf('importrange') > 0) {
// var result = [formule.split('/')[5], f.getName(), columnToLetter(numCol) + numRow, "'" + formule]
var result = [formule.match(regExp1).join('\n').match(regExp3).join('\n'), f.getName(), columnToLetter(numCol) + numRow, "'" + formule]
db.getRange(db.getLastDataRow() + 1, 1, 1, 4).setValues([result])
}
if (formule.match(regExp) != null) {
var result = [formule.match(regExp).join('\n'), f.getName(), columnToLetter(numCol) + numRow, "'" + formule]
db.getRange(db.getLastDataRow() + 1, 1, 1, 4).setValues([result])
}
}
})
})
}
})
}
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;
}
Object.prototype.getLastDataRow = function (col) {
var lastRow = this.getLastRow();
if (col == null) { col = 'A' }
var range = this.getRange(col + lastRow);
if (range.getValue() !== "") {
return lastRow;
} else {
return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
}
}Salut Mike,
ça y est je suis de retour...ton script fonctionne mais il liste uniquement deux formules dans l'ensemble du fichier alors que dans cet exemple il en contient 4:
J'ai essayé sur mon fichier pro plus complexe et cela est pareil
Bonjour,
tu m'avais précisé ...
en colonne A, B, C.
dans ce cas
var formules = f.getRange(1, 1, f.getLastRow(), f.getLastColumn()).getFormulas()et j'en trouve 5
parfait Super Mike ca fonctionne bien et cela me liste toutes les formules.
Est il possible maintenant que tu m'aides à développer le script pour remplacer celles ci-par celles qui sont écrites en "colonne F" de l'onglet du "Dashboard", uniquement si la chechbox est cochée?
Merci envore
ok, pas de soucis, c'est le but
attention à bien prévoir les cas où une même formule fait appel à plusieurs sources
mais néanmoins, ne faut-il pas prévoir de les faire en bloc toutes ensemble avec juste une table de conversion avant/après ?
grosso modo, combien en as-tu par fichier ?
Ben, en fait le script a listé 1000 formules mais je pense qu'il s'est arrêter au bout d'un certains temps. Je crois qu'il y a un temps maxi dans les scripts google.
sur mon fichier de travail, il doit y en avoir entre 2 et 3000 !
ces limites de google ne sont pas un problème car je lancerais le script en plusieurs batch, ou sur les premieres lignes je déroulerais sur les autres !
Oui tu as raison, cela n'a pas de sens de faire cette condition par checkbox. Vaut mieux lancer le script pour l'ensemble des formules
Ben, en fait le script a listé 1000 formules mais je pense qu'il s'est arrêter au bout d'un certains temps. Je crois qu'il y a un temps maxi dans les scripts google.
sur mon fichier de travail, il doit y en avoir entre 2 et 3000 !
hum ... copieux ! et comment tu le faisais à la main ? wahoo
combien par feuille ?