Nettoyage de macro, transpose

Le 17/03/2022 à 14:51

Bonjour à tous,

Je suis entrain de me mettre au script sur google sheet pour me créer un fichier autonome.
Je dois copier des cellules qui sont à différents endroits pour les mettre en ligne dans une autre feuille.

Mon fichier source se trouvant dans la feuille "saisie" avec les cellules C5, C7, C8, C9, C11, C13, C5 et E5, E7, E9, E11, E13
Ces cellules sont copiées pour être collées dans une ligne sur une autre feuille après avoir inséré une nouvelle ligne au dessus.

Voici le code que j'ai commencé à nettoyer.

function Ajouter() {
  var spreadsheet = SpreadsheetApp.getActive();
  var saisie = spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Saisie'), true);
  var liste = spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Liste'), true);
  spreadsheet.getRange('C5').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Liste'), true);
  spreadsheet.getActiveSheet().insertRowsBefore(spreadsheet.getActiveRange().getRow(), 1);
  spreadsheet.getActiveRange().offset(0, 0, 1, spreadsheet.getActiveRange().getNumColumns()).activate();
  spreadsheet.getRange('A5').activate();
saisie;
liste;
  spreadsheet.getRange('A5').activate();
  spreadsheet.getRange('Saisie!C5').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
saisie;
  spreadsheet.getRange('C7').activate();
 liste;
  spreadsheet.getRange('B5').activate();
  spreadsheet.getRange('Saisie!C7').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
 saisie;
  spreadsheet.getRange('C9').activate();
liste;
  spreadsheet.getRange('C5').activate();
  spreadsheet.getRange('Saisie!C9').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
saisie;
  spreadsheet.getRange('C11').activate();
liste;
  spreadsheet.getRange('D5').activate();
  spreadsheet.getRange('Saisie!C11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
 saisie;
  spreadsheet.getRange('C13').activate();
liste;
  spreadsheet.getRange('E5').activate();
  spreadsheet.getRange('Saisie!C13').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
saisie;
  spreadsheet.getRange('C15').activate();
liste;
  spreadsheet.getRange('F5').activate();
  spreadsheet.getRange('Saisie!C15').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
saisie;
  spreadsheet.getRange('E5').activate();
liste;
  spreadsheet.getRange('G5').activate();
  spreadsheet.getRange('Saisie!E5').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
saisie;
  spreadsheet.getRange('E7').activate();
liste;
  spreadsheet.getRange('H5').activate();
  spreadsheet.getRange('Saisie!E7').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
saisie;
  spreadsheet.getRange('E5').activate();
liste;
  spreadsheet.getRange('I5').activate();
  spreadsheet.getRange('Saisie!E9').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
saisie;
  spreadsheet.getRange('E11').activate();
liste;
  spreadsheet.getRange('J5').activate();
  spreadsheet.getRange('Saisie!E11').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
saisie;
  spreadsheet.getRange('E13').activate();
liste;
  spreadsheet.getRange('K5').activate();
  spreadsheet.getRange('Saisie!E13').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
  spreadsheet.getRange('Saisie!E13').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Saisie'), true);
  spreadsheet.getRange('C5').activate();
  effacersanslesformules();
};

J'aimerai optimiser ce code avec une boucle, les opérations suivantes se répètent

spreadsheet.getRange('A5').activate();
spreadsheet.getRange('B5').activate();
spreadsheet.getRange('C5').activate();
...

Je pense donc que je peux optimiser ces lignes en passant par une boucle. mais j'ai un peu du mal. Je ne sais pas trop comment écrire la chose.

Ou si quelqu'un à une bien meilleure idée pour faire ce genre de petit programme je suis preneur !
Merci d'avance !

Le 17/03/2022 à 16:34

Bonjour,

partage STP un bout de fichier anonymisé sur lequel on pourra optimiser ta solution, cela permettra aussi d'y voir plus clair

à noter qu'il est plutôt contre-productif d'activer des cellules dans un script, par exemple, au lieu de

spreadsheet.getRange('A5').activate();
spreadsheet.getRange('Saisie!C5').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);

il est préférable de mettre

var cel = spreadsheet.getRange('A5') // plutôt que d'activer, j'identifie juste la cellule en question
spreadsheet.getRange('Saisie!C5').copyTo(cel, SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
Le 17/03/2022 à 17:32

Merci pour ta participation Steelson.

Il n'ya rien de confidentiel, j'essaye de créer une application d'étude de budget travaux.
Le but de ce classeur et de lister l'ensemble des taches, de regrouper l'ensemble des corps de métiers et d'établit des graphiques en fonction du prix de chaque corp de métier.

Pour l'instant ce n'est pas trop compliqué. lister les taches, les importer dans une autre feuille, supprimer les lignes, une box alerte au cas ou.

la ou ca va vraiment se compliquer c'est de récupérer certaines valeurs de la feuille liste pour faire des graphiques en script....

Voici le lien du document :

https://docs.google.com/spreadsheets/d/1ARfzrVKZmP1sqYO3OPM4gH6U7bu4oMCB2I2yZoInjIM/edit?usp=sharing

Le 17/03/2022 à 17:56

Accès refusé ... pour partager https://www.sheets-pratique.com/fr/cours/partage

Le 17/03/2022 à 18:00

C'est étrange, pourtant c'est ce que j'ai fais. Je te vois dans les personnes ayant accès au fichier.

Le 17/03/2022 à 18:02

ok, maintenant c'est bon (j'avais demandé l'accès et il a dû être donné en automatique)

je regarde ... un peu plus tard !

Le 17/03/2022 à 18:08

cela me rappelle ceci

function saisir(){
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var f1 = SpreadsheetApp.getActive();
  var n = f1.getRange('E11').getValue();
  var origine = ["C3","C5","C7","C9","C11"];
  if (n == 5) {
    var destination = ["A","B","C","D","E"];
    var f2 = doc.getSheetByName('BDD');
    var derL = getLastDataRow(f2) + 1;
    f1.getRange('C13').setValue(f2.getRange('G' + (derL - 1)).getValue() + 1);
    for (var i = 0; i < origine.length; i++){
      f2.getRange(destination[i] + derL).setValue(f1.getRange(origine[i]).getValue());
    }
    f1.getRangeList(origine).clearContent();
    //f1.setActiveSelection(origine[0]);
  } else {
    Browser.msgBox('Merci de renseigner tous les champs !');
    f1.setActiveSelection(origine[0]);
  }
}

je regarde comment l'adapter ... un peu plus tard, à moins que tu puisses t'y inspirer !

Le 17/03/2022 à 18:18

Top, merci beaucoup de ton aide.

Pour le moment je suis la dessus :

function saisir(){
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var f1 = SpreadsheetApp.getActive();
  var n = f1.getRange('E11').getValue();
  var origine = ["C5","C7","C9","C11","C13","C15","E5","E7","E9","E11","E13"];
  if (n == 11) {
    var destination = ["A","B","C","D","E","F","G","H","I","J","K"];
    var f2 = doc.getSheetByName('Liste');
    var derL = getLastDataRow(f2) + 1;
    f1.getRange('C13').setValue(f2.getRange('G' + (derL - 1)).getValue() + 1);
    for (var i = 0; i < origine.length; i++){
      f2.getRange(destination[i] + derL).setValue(f1.getRange(origine[i]).getValue());
    }
    f1.getRangeList(origine).clearContent();
    //f1.setActiveSelection(origine[0]);
  } else {
    Browser.msgBox('Merci de renseigner tous les champs !');
    f1.setActiveSelection(origine[0]);
  }
}

Je comprends pas trop ce que fait le :

      var n = f1.getRange('E11').getValue();
et
      f1.getRange('C13').setValue(f2.getRange('G' + (derL - 1)).getValue() + 1);
Le 18/03/2022 à 00:40

Excellente démarche, je vois que tu as commencé à adapter en te posant quelques questions pertinentes

timoxy a écrit :

Je comprends pas trop ce que fait le :

      var n = f1.getRange('E11').getValue();
et
      f1.getRange('C13').setValue(f2.getRange('G' + (derL - 1)).getValue() + 1);
var n = f1.getRange('E11').getValue();
il s'agit en fait d'une zone ajoutée qui compte le nombre de zones "obligatoires" renseignées, et tant que la valeur n'est pas à 11, je refusais l'enregistrement des données

f1.getRange('C13').setValue(f2.getRange('G' + (derL - 1)).getValue() + 1);

il s'agit d'un compteur qui prend la dernière valeur enregistrée dans la feuille "Liste" pour toi en l'incrémentant, genre numéro de dossier automatique

Le 18/03/2022 à 00:50

Le plus simple pour le moment est ceci

function saisir() {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var f1 = SpreadsheetApp.getActive();
  var f2 = doc.getSheetByName('Liste');
  var origine = ["C5", "C7", "C9", "C11", "C13", "C15", "E5", "E7", "E9", "E11", "E13"];
  var destination = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K"];
  var ligne = f2.getLastRow() + 1; // première ligne libre
  for (var i = 0; i < origine.length; i++) {
    f2.getRange(destination[i] + ligne).setValue(f1.getRange(origine[i]).getValue());
  }
}

f1 et f2 sont les 2 feuilles

tu as compris origine et destination (aujourd'hui je ne ferai plus tout à fait pareil, mais cela permet de comprendre les getValue() et setValue() )

la fonction getLastRow() permet de retrouver la dernière ligne

on fait une boucle via for (var ....)

et on utilise getValue() pour capter une valeur et setValue() pour renseigner une valeur

il peut y avoir ensuite des raffinements, notamment remettre le formulaire à "zéro",

function effacer(){
  var f1 = SpreadsheetApp.getActive();
  var zones = ["C5", "C7", "C9", "C11", "C13", "C15", "E5", "E9"];
  f1.getRangeList(zones).clearContent();
}

positionner les curseurs d'une zone à l'autre etc...

Le 24/03/2022 à 18:10

Au top Steelson, Ca fonctionne parfaitement ! Merci beaucoup.

Maintenant il me reste beaucoup de trucs à faire. Je reviendrais certainement demander de l'aide !

Le 25/03/2022 à 09:43

Bon en fait, me revoila plus vite que prévu.

Dans mon programme en le commençant je me suis dit. C'est bien, ca marche MAIS... une donnée change et y'a beaucoup de choses à modifier.

C'est pourquoi j'ai crée des plages nommées en suivant le tout dernier tuto de tutoderien en date du 20 mars 2022. Qui fait exactement ce que j'ai besoin de faire.

Donc voici pour les intéressés l'ensemble du programme :

let spreadsheet = SpreadsheetApp.getActive();

let formActionRange = spreadsheet.getRangeByName('Action');
let formCdmRange = spreadsheet.getRangeByName('Metier');
let formZoneRange = spreadsheet.getRangeByName('Zone');
let formQuantiteRange = spreadsheet.getRangeByName('Quantite');
let formUniteRange = spreadsheet.getRangeByName('Unite');
let formFournisseurRange = spreadsheet.getRangeByName('Fournisseur');
let formPuHTRange = spreadsheet.getRangeByName('PuHT');
let formTotalhtRange = spreadsheet.getRangeByName('TotalHT');
let formTauxTvaRange = spreadsheet.getRangeByName('TauxTva');
let formTVARange = spreadsheet.getRangeByName('TVA');
let formPrixTtcRange = spreadsheet.getRangeByName('PrixTtc');

function saveBtn(){
  let action = new Action(
  getNextStepId(),
  formActionRange.getValue(),
  formCdmRange.getValue(),
  formZoneRange.getValue(),
  formQuantiteRange.getValue(),
  formUniteRange.getValue(),
  formFournisseurRange.getValue(),
  formPuHTRange.getValue(),
  formTotalhtRange.getValue(),
  formTauxTvaRange.getValue(),
  formTVARange.getValue(),
  formPrixTtcRange.getValue(),
  )

  createAction(action);
}

class Action{
constructor(id,action,corpdemetier,zone,quantite,unite,fournisseur,puht,totalht,tauxtva,tva,prixttc){
  this.id = id;
  this.action = action;
  this.corpdemetier = corpdemetier;
  this.zone = zone;
  this.quantite = quantite;
  this.unite = unite;
  this.fournisseur = fournisseur;
  this.puht = puht;
  this.totalht = totalht;
  this.tauxtva = tauxtva;
  this.tva = tva;
  this.prixttc = prixttc;
}
}

let listePage = spreadsheet.getSheetByName('Liste');

function createAction(listeRecord){

  listePage.appendRow([
    listeRecord.id,
    listeRecord.action,
    listeRecord.corpdemetier,
    listeRecord.zone,
    listeRecord.quantite,
    listeRecord.unite,
    listeRecord.fournisseur,
    listeRecord.puht,
    listeRecord.totalht,
    listeRecord.tauxtva,
    listeRecord.tva,
    listeRecord.prixttc,
  ]);

  setNextStepId();
}

function getNextStepId() {
  return spreadsheet.getRangeByName('ActionId').getValue();

}

function setNextStepId() {
  let actualStepId = getNextStepId();
  spreadsheet.getRangeByName('ActionId').setValue(++ actualStepId);
}

function myFunction() {
  saveBtn()
}

Donc si vous avez un tableau qui ne bougera pas, la solution de Steelson fonctionne très bien et est plus rapide. Mais n'est pas forcément aisé si il y'a des modifications de colonnes etc.

Rechercher des sujets similaires à "nettoyage macro transpose"