Google Script - Lister et modifier formules liées à d'autres spreadsheets

Le 04/02/2022 à 17:10

Pour les autres feuilles, il y a peu de formules. Une trentaine de plus maximum !

Ouep ça demande de la patience !

Le 04/02/2022 à 17:15

oui ca demande un peu de patience !

Y en a très peu sur les autres feuilles...une trentaine maxi !

Le 04/02/2022 à 17:29

Cela veut dire quand même qu'une feuille est blindée de formules. Dans tous les cas il faut passer par du batchupdate comme ici https://forum.excel-pratique.com/sheets/google-script-lister-et-modifier-formules-liees-a-d-autres-s...

MAIS le problème est plus grave ... que je suspecte depuis le début : dans excel, tu fais sans doute des liens cellule à cellule *, dans GSheets tu gagnerais à faire des liens vers des plages complètes de données, et ce même si la limite de 50 IMPORTRANGE par fichier a été relevée semble-t-il. Je crois aussi que pour bénéficier pleinement de la puissance de GSheets il faudra ré-optimiser le fichier et les liens.

Je vais te préparer quelque chose à la fois pour les [xx] mais aussi les liens en http car en réalité tu n'as pas besoin de l'url complète mais juste l'id du fichier.

Question : quelle est l'ampleur des cas de [xx] car je ne vois pas comment faire simplement puisqu'il faut toucher à la place de l'apostrophe ' éventuelle et mettre derrière la cellule ")

(*) si c'est en tirant une formule en bas et à droite, il faudrait réparer cela rapidement.

Le 04/02/2022 à 18:35

MAIS le problème est plus grave ... que je suspecte depuis le début : dans excel, tu fais sans doute des liens cellule à cellule *, dans GSheets tu gagnerais à faire des liens vers des plages complètes de données, et ce même si la limite de 50 IMPORTRANGE par fichier a été relevée semble-t-il. Je crois aussi que pour bénéficier pleinement de la puissance de GSheets il faudra ré-optimiser le fichier et les liens.

--> Non ce n'est que des recherches V ou H, à lexception près

Question : quelle est l'ampleur des cas de [xx] car je ne vois pas comment faire simplement puisqu'il faut toucher à la place de l'apostrophe ' éventuelle et mettre derrière la cellule ")

--> Voici ci-dessous quelques exemples de formules effectué sous excel et transformé par google sheet.

Je pense qu'il faudrait extraire [13]11'!$B$51:$J$53 complètement, pour le transformer en importrange("http.xxxxxxxxxxxxx";"11'!$B$51:$J$53")

'=IF(DL$21<>"Hy";SUMIFS('28052021'!$D:$D;'28052021'!$C:$C;DL$23;'28052021'!$A:$A;$AG26);HLOOKUP(DL$22;'[13]11'!$B$51:$J$53;3;FALSE))

'=VLOOKUP($C95;'[14]is'!$C:$JF;264;FALSE)

'=IF($P168<400;VLOOKUP($C168;'[17]sis'!$B:$FF;18;FALSE);0)

Le 04/02/2022 à 18:49

VINCENT31000000 a écrit :
=VLOOKUP($C95;'[14]is'!$C:$JF;264;FALSE)

C'est là toute la difficulté !! car en plus il y a l'apostrophe à gérer ! et on ne peut donc pas remplacer un bloc alphanumérique par un autre. Le traitement risque d'être très très long !!

Pour les formules en http je n'ai pas de soucis si ce n'est que je ne peux pas mettre la liste dans une feuille en particulier qui serait elle aussi impactée par la modification, il faut mettre les valeurs en dur dans le script !

Tu peux déjà faire un essai avec ceci :

// enable Sheets API at Advanced Google services
function traitement() {
  const replaceConditions = {
    "https://docs.google.com/spreadsheets/d/xxxxxxxxxxxx/edit#gid=0": "yyyyyyyyyyyyy",
    "xxxxxxxxxxxx": "yyyyyyyyyyyyy",
  };
  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());
}

où xxxxxxxxx (id ancien) et yyyyyyyyyyyy (nouvel id) sont les ID (ce n'est pas la peine de mettre toute l'url dans un importrange)

Le 05/02/2022 à 08:37

Maintenant pour les formules comportant [xx], voici une proposition (adapte bien sur les 2 premières valeurs)

ATTENTION = ceci suppose des formules compatibles FR (en non UK)

function modifXX() {

  var num = 12 // mettre le n°
  var id = '1_RJ-uIfqRmKK4lZ_y0Z75byHYSA5Q_YZ4neoc0TgdNs' // mettre l'ID

  var doc = SpreadsheetApp.getActiveSpreadsheet()
  var db = doc.getSheetByName('Dashboard')
  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.match(/\[\d+\]/g) != null) {
              newFormule = remplace(remplace(formule,num,id))
              var result = [f.getName(), numRow, numCol, "'" + formule]
              db.getRange(db.getLastRow() + 1, 1, 1, 4).setValues([result])
              f.getRange(numRow,numCol).setFormula(newFormule)
            }
          }
        })
      })
    }
  })
}
function remplace(txt,num,id){
  newtxt = txt
  txt.split(';').forEach(function(t){
    var x = (t.match(/.*\[\d+\].*/g))
    if (x!=null && x[0].match('['+num+']')){
      var apostrophe = x[0].match("'") ? "'" : ""
      newx = x[0].replace( /([']{0,1}\[\d+\])/gm , `importrange("${id}";"${apostrophe}` ) + `")`
      newtxt = newtxt.replace(x[0],newx)
    }
  })
  return newtxt
}

que j'ai testée avec

=query('[12]donnees'!A1:E15;"select Col1 where Col2>5000000")
=ARRAYFORMULA(VLOOKUP("Hauts-de-France";[12]donnees!A1:E15;{1\2\3\4\5};0))

et
  var num = 12
  var id = '1_RJ-uIfqRmKK4lZ_y0Z75byHYSA5Q_YZ4neoc0TgdNs'

mais la mise au point n'a pas été limpide, et je présume qu'on découvrira des erreurs

Le 07/02/2022 à 07:22

Salut Mike,

Concernant le script de changement des formules de excel en sheet cela fonctionne très bien (script ""), sur le sheet que je t'avais partagé.

par contre sur mon document de travail, le script se lance et se termine, mais aucun changement n'apparait. Peux tu me confirmer que seule les colonne de A à D de l'onglet Dashboard on une influence dans le script. Pas besoin d remplir la colonne F?

Concernant le script, "" il ne fonctionne pas !

Le 07/02/2022 à 10:41

VINCENT31000000 a écrit :
(script ""),
le script, ""

va falloir préciser

c'est un piège ... quand on copie ici, il faut ensuite cliquer sur la flèche droite du clavier, sinon on écrase ce qui a été copié !

Le 07/02/2022 à 10:43

VINCENT31000000 a écrit :
Concernant le script de changement des formules de excel en sheet cela fonctionne très bien (script ""), sur le sheet que je t'avais partagé.
par contre sur mon document de travail, le script se lance et se termine, mais aucun changement n'apparait.

s'il s'agit de https://forum.excel-pratique.com/sheets/google-script-lister-et-modifier-formules-liees-a-d-autres-s..., as-tu activé Sheets API ?

Le 07/02/2022 à 10:52