Traduction d'une macro VBA Excel en macro Apps Script Google Sheets

Bonjour à tous,

J'ai vu qu'il était possible d'utiliser une macro sur un document partagé google sheets.

J'ai réalisé mon codage sur VBA Excel, voir ci-dessous, mais je ne sais pas comment le traduire en langage Apps Script.

Quelqu'un pourrait m'aider ?

Codage VBA Excel :

Private Sub Workbook_Open()

'1ère partie qui me permet de connaître la date (en numéro semaine) de la dernière ouverture du fichier

Dim fs As Object
Dim fichier As Object
Dim date_derniere_modification As Date
Dim chemin_fichier As String

'chemin du fichier à controler
chemin_fichier = "C:\Users\henri.chollet.SATIFRANCE\Desktop\Sati\Planning Moyen terme SATI\Planning à Moyen terme SATI.xlsm"

Set fs = CreateObject("Scripting.FileSystemObject")
Set fichier = fs.GetFile(chemin_fichier)

'identifier la date de dernière modification
date_derniere_modification = Format(fichier.DateLastModified, "dd/mm/yyyy")

'------------------------------------------------------------------------------------------------------------------------------
'2ème partie qui me permet de réaliser automatiquement quelques calculs simples

For i = 8 To Sheets("Planning moyen terme").Range("A8").Range("A1000").Row

If DatePart("ww", Date) = DatePart("ww", date_derniere_modification) And Sheets("Planning moyen terme").Cells(i, 19).Value <> "" Then

Sheets("Planning moyen terme").Cells(i, 26).Value = Sheets("Planning moyen terme").Cells(i, 20).Value + Sheets("Planning moyen terme").Cells(i, 21).Value
Sheets("Planning moyen terme").Cells(i, 20).Value = Sheets("Planning moyen terme").Cells(i, 26).Value
Sheets("Planning moyen terme").Cells(i, 21).Value = Sheets("Planning moyen terme").Cells(i, 22).Value
Sheets("Planning moyen terme").Cells(i, 22).Value = Sheets("Planning moyen terme").Cells(i, 23).Value
Sheets("Planning moyen terme").Cells(i, 23).Value = Sheets("Planning moyen terme").Cells(i, 24).Value
Sheets("Planning moyen terme").Cells(i, 24).Value = Sheets("Planning moyen terme").Cells(i, 25).Value
Sheets("Planning moyen terme").Cells(i, 25).Value = ""

End If

If Sheets("Analyse").Cells(i, 20) = "absent" Or Sheets("Analyse").Cells(i, 21) = "absent" Or Sheets("Analyse").Cells(i, 22) = "absent" Or Sheets("Analyse").Cells(i, 23) = "absent" Then
Worksheets("Analyse").Range("B1000").End(xlUp).Offset(1, 0) = Sheets("Planning moyen terme").Cells(i + 22, 2).Value
Worksheets("Analyse").Range("B1000").End(xlUp).Offset(0, 1) = Sheets("Planning moyen terme").Cells(i + 22, 3)
Worksheets("Analyse").Range("B1000").End(xlUp).Offset(0, 2) = Sheets("Planning moyen terme").Cells(i + 22, 4).Value
Worksheets("Analyse").Range("B1000").End(xlUp).Offset(0, 3) = Sheets("Planning moyen terme").Cells(i + 22, 5)
Worksheets("Analyse").Range("B1000").End(xlUp).Offset(0, 4) = Sheets("Planning moyen terme").Cells(i + 22, 11).Value
Worksheets("Analyse").Range("B1000").End(xlUp).Offset(0, 5) = Sheets("Planning moyen terme").Cells(i + 22, 17).Value

End If

Next i

Sheets("Planning moyen terme").Columns("Z:Z").Delete

End Sub

Merci d'avance :)

Bonjour,

1. Le langage est différent : VBA est un langage propre à microsoft, google app script est un langage plus universel très proche du javascript.
2. Mais aussi, Google Sheets possède des fonctions qui évitent d'écrire parfois des scripts/macros.

Pour ton cas, il serait bien de mettre un lien vers un début de projet sous google sheets https://www.sheets-pratique.com/fr/cours/partage et expliquer littéralement ce que tu souhaites

Bonjour,

Alors je ne peux pas partager ce document qui est interne à l'entreprise.

Cependant, est-ce que vous connaissez le codage Google Sheets pour :

- Récupérer la date de dernière modification d'un document Google Sheets

- Réaliser une boucle (dans le style For i = ... ; next i)

- Réaliser un If

- Et réaliser une copie (Cellule A feuille B = Cellule A feuille A)

En sachant comment exécuter ces formules je pourrais me débrouiller de mon côté pour l'adapter à mon document Google Sheets.

Merci d'avance :p

Date de dernière modif

var date = DriveApp.getFileById('ID du fichier').getLastUpdated()

il faudrait tester car je n'ai jamais pratiqué sauf dans un cas qui est de faire la liste des fichiers du drive.

Boucle :

for (var i=2; i<=X; i++){
  //ici ce qu'il faut faire
}

If : exemple (ET s'écrit && OU s'écrit ||)

if (f.getRange('D' + ligne).getValue() >= debut && f.getRange('D' + ligne).getValue() < fin){
  //ici ce qu'il faut faire
}

Copie : exemple

var plage = f.getRange('AA' + lig + ':AX' + lig);
plage.copyTo(f.getRange('A' + lig), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false); 

on peut toujours faire un fichier simplifié

Je pense qu'une solution plus simple pour la date est de prévoir dans le fichier, une cellule qui recueillerait la date de dernière modification.

function onEdit(event){ 
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('espion').getRange('A1').setValue(new Date());
}

Bonjour,

D'abord merci pour vos réponses. Je ne parviens pas à retrouver la date de dernière modification du document (votre fonction m'affiche la date actuelle lorsque je l'exécute), je cherche à obtenir la date de dernière modification d'un même document. (càd : j'exécute le script dans le même document et je cherche à avoir la date de dernière modif de ce document (pas la date actuelle). Avez-vous quelque chose ?

Sinon pour les autres fonctions je suis en train de travailler dessus. Merci bien !

Il faut considérer 2 cas de figure :

1. la modification est celle liée à une formule ou un script, y compris la modification du script lui-même
2. la modification est celle liée plus restrictivement à une entrée manuelle dans une cellule quelconque

Dans le premier cas, ceci donnera la date de dernière modification, attention s'il y a un script à l'ouverture qui modifie le document cela peut être le cas ...

https://forum.excel-pratique.com/sheets/recuperer-les-dates-de-sauvegarde-fichier-158170#p980448

var fileID = SpreadsheetApp.getActiveSpreadsheet().getId();
var file = DriveApp.getFileById(fileID);
var lastUpdate = file.getLastUpdated();

Dans le second cas, on peut le faire avec onEdit

Je suis surpris que la fonction proposée ne donne pas le résultat. Quand tu dis "quand je l'exécute", ce n'est pas une fonction exécutable manuellement, elle est automatique dès qu'une action manuelle est faite sur le fichier.

function onEdit(event){ 
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('espion').getRange('A1').setValue(new Date());
}

J'ai une solution plus riche ici qui donne l'historique sur une journée et affiche à l'ouverture.

function onEdit(event){ 
  var f = event.source.getActiveSheet();
  var h = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('historique')
  if (f.getName() != 'historique'){ 
    var d = new Date();
    if(Math.floor(d/1000/60/24/24) != Math.floor(h.getRange('A2').getValue()/1000/60/24/24)){
      h.insertRowBefore(2);
      h.getRange('A2').setValue(d);
    }
  }
}
function onOpen(){
  var h = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('historique')
  toast(Utilities.formatDate(h.getRange('A2').getValue(), "GMT+2", "EEEE dd/MM/yyyy"),'dernière modification')
}
function toast(body, title, timeout) {
  return SpreadsheetApp.getActive().toast(
    body,
    title || "information",
    timeout || 5 
  );
}

https://docs.google.com/spreadsheets/d/1HQ3SQCnqeZS-Ctj_qWRJdvVozLDwU4CuG1CptH5bCGs/copy

edit : je viens de faire l'essai sur la fonction proposée et elle donne bien le résultat escompté dans l'onglet espion lors d'une modification manuelle, inclus dans le fichier ci-dessus

Donne un lien vers un document similaire simplifié si besoin de coller à ta fonction.

Merci pour ces informations, c'est vraiment très différent du vba excel que je connais, pour être honnête je suis perdu.

Je vous partage mon document : https://docs.google.com/spreadsheets/d/1VTa3Xgov57aPnf5gx6BRpiWMq_RwFQrSzVHqsLlct0A/edit#gid=4356173...

Ce que j'aimerais faire : A l'ouverture de mon fichier, exécuter automatiquement des calculs si la semaine de l'ouverture du document est différent de la semaine de la dernière modification de ce document. Le but est d'avoir un planning hebdomadaire à jour qui ne peut devenir obsolète.

J'ai réussi ce que je voulais avec le code VBA mais ici ce n'est peut-être pas la meilleure option...

L'accès est refusé, partage ton document comme suit https://www.sheets-pratique.com/fr/cours/partage

Oups, je pense que c'est bon : https://docs.google.com/spreadsheets/d/1VTa3Xgov57aPnf5gx6BRpiWMq_RwFQrSzVHqsLlct0A/edit?usp=sharing

Cdt,

Henri

Pour la date ...

function dernModif() {
  var fileID = SpreadsheetApp.getActiveSpreadsheet().getId();
  var file = DriveApp.getFileById(fileID);
  var lastUpdate = file.getLastUpdated();
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Planning moyen terme').getRange('B1').setValue(lastUpdate)
}

il faut lancer dernModif une première fois et donner les autorisations

ensuite, il faut définir un déclencheur pour l'avoir à l'ouverture

Il faut maintenant que je regarde comment obtenir directement le numéro de semaine (ISO) dans le script !

ATTENTION, pour la semaine,

Utilities.formatDate(lastUpdate, "GMT+2", "'Week'w")

donne une mauvaise réponse ! ce n'est pas la semaine ISO.

Emploie ceci plutôt :

  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Planning moyen terme').getRange('B2').setValue('Week'+lastUpdate.getWeekNumber());

// avec :
Date.prototype.getWeekNumber = function(){
  var d = new Date(Date.UTC(this.getFullYear(), this.getMonth(), this.getDate()));
  var dayNum = d.getUTCDay() || 7;
  d.setUTCDate(d.getUTCDate() + 4 - dayNum);
  var yearStart = new Date(Date.UTC(d.getUTCFullYear(),0,1));
  return Math.ceil((((d - yearStart) / 86400000) + 1)/7)
};

hé oui, nous sommes semaine 23 et non 24 ! c'est la même chose en excel souvent du reste !!

Incroyable ça fonctionne !

J'essai de voir comment fonctionne les If mais je pense que le plus dur est derrière nous.

Merci beaucoup !

Je suis plutôt satisfait du travail que j'ai réalisé aujourd'hui sur le document car j'arrive au résultat escompté.

Cependant, ma boucle met plus de 2 minutes pour faire 51 tours et j'aimerais, si possible, qu'elle en fasse 1000 pour être large.

Pensez-vous qu'il serait possible de l'optimiser ?

Voici le lien du document :

Je pose également le codage actuel : https://docs.google.com/spreadsheets/d/1VTa3Xgov57aPnf5gx6BRpiWMq_RwFQrSzVHqsLlct0A/edit#gid=4356173...

function dernModif() {
var fileID = SpreadsheetApp.getActiveSpreadsheet().getId();

var file = DriveApp.getFileById(fileID);

var lastUpdate = file.getLastUpdated();

var now = new Date SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Analyse').getRange('C4').setValue(lastUpdate) SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Analyse').getRange('C5').setValue(lastUpdate.getWeekNumber());
SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Analyse').getRange('C2').setValue(now); SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Analyse').getRange('C3').setValue(now.getWeekNumber());

var app = SpreadsheetApp;var SheetAnalyse = app.getActiveSpreadsheet().getSheetByName('Analyse');

var SheetPlaMoyTer = app.getActiveSpreadsheet().getSheetByName('Planning moyen terme');

var CellDerSem = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Analyse').getRange(5,3).getValue();

var CellSem = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Analyse').getRange(3,3).getValue();

for (var i=8; i<51; i++){

var CellTotal = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Planning moyen terme').getRange(i,19).getValue();

var CellModifS = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Planning moyen terme').getRange(i,20);

var CellModifS1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Planning moyen terme').getRange(i,21);var CellModifS2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Planning moyen terme').getRange(i,22);

var CellModifS3 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Planning moyen terme').getRange(i,23);

var CellModifS4 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Planning moyen terme').getRange(i,24);

var CellModifS5 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Planning moyen terme').getRange(i,25);

var CellModifS6 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Planning moyen terme').getRange(i,26);

var CellModifS7 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Planning moyen terme').getRange(i,27);

var CellModifS8 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Planning moyen terme').getRange(i,28);

if (CellDerSem > CellSem && CellTotal > 0){

SheetAnalyse.getRange(2,4).setValue("Rien faire");
} else {

SheetAnalyse.getRange(2,4).setValue("Action !!!")
CellModifS8.setValue(CellModifS.getValue() + CellModifS1.getValue());

CellModifS.setValue(CellModifS8.getValue());

CellModifS1.setValue(CellModifS2.getValue());

CellModifS2.setValue(CellModifS3.getValue());

CellModifS3.setValue(CellModifS4.getValue());

CellModifS4.setValue(CellModifS5.getValue());

CellModifS5.setValue(CellModifS6.getValue());

CellModifS6.setValue(CellModifS7.getValue());

CellModifS7.setValue(""); CellModifS8.setValue("");
}
}
}
Date.prototype.getWeekNumber = function(){

var d = new Date(Date.UTC(this.getFullYear(), this.getMonth(), this.getDate()));

var dayNum = d.getUTCDay() || 7; d.setUTCDate(d.getUTCDate() + 4 - dayNum);

var yearStart = new Date(Date.UTC(d.getUTCFullYear(),0,1)); return Math.ceil((((d - yearStart) / 86400000) + 1)/7)};

Merci encore !

C'est comme pour excel ... pour accélérer, il faut limiter les lecture/écriture avec la feuille, donc importer les données en une seule fois dans un tableau/array, les traiter, et ensuite les réinjecter en bloc dans la feuille. Une différence avec excel : les calculs sont faits par le serveur avec toute sa puissance de calcul, mais s'il y a des allers/retours avec la feuille cela prend du temps.

HenriChollet a écrit :

je pense que le plus dur est derrière nous.

et du coup, le plus exaltant est devant nous !

Exemple importation globale de données

var items=SpreadsheetApp.getActiveSheet().getRange('E6:H6').getValues()

items sera un tableau à 2 dimensions avec une première dimension ici car on prend les données sur une seule ligne, donc de 0 à 0 et la seconde de 0 à items[0].length-1

for (var i=0;i< items[0].length;i++){
    var item=items[0][i]
    Logger.log(items[0][i])
  }

pour ré-exporter un résultat

SpreadsheetApp.getActiveSheet().getRange(7,5,1,result.length).setValues([result])

ou

SpreadsheetApp.getActiveSheet().getRange(7,5,items.length,1).setValues(result)

selon que le résultat est un tableau en ligne ou en colonne

ou avec une combinaison des 2 ... il faudrait faire quelques exemples précis.

La vitesse sera fulgurante !!

Bonjour, je reviens vers vous car j'ai dû abandonner le projet puis le reprendre aujourd'hui. Je suis resté sur une base de codage mais je n'ai pas le temps de tout reprendre. Pouvez-vous m'aider ?

Voici le lien du document partagé : https://docs.google.com/spreadsheets/d/1VTa3Xgov57aPnf5gx6BRpiWMq_RwFQrSzVHqsLlct0A/edit?usp=sharing

Ce que j'aimerais faire : A l'ouverture de mon fichier, exécuter automatiquement le calcul suivant dans l'onglet "Planning moyen terme" :

Si la semaine de l'ouverture du document est différente de la semaine de la dernière modification de ce document, alors :

Les valeurs de la colonne T = valeur de la colonne T + valeur de la colonne U

La valeur de la colonne U = la valeur de la colonne V

La valeur de la colonne V = la valeur de la colonne W

.........

.........

La valeur de la colonne Z = la valeur de la colonne AA

La valeur de la colonne AA = rien

Le but est d'avoir un planning hebdomadaire à jour qui ne peut devenir obsolète. Pensez-vous pouvoir réaliser ce code ?

(Vous pouvez modifier directement ce document partagé c'est une copie)

Merci par avance !

ok, je vais quand même en reprendre une copie pour ne pas perturber les autres potentiels contributeurs et réfléchir quand même. Je regarderai ce midi pendant ma pause !

Ta fonction ne semble pas donner les bons résultats ! sauf erreur de ma part ... si je l'applique sur le 1/1/2024 ou le 1/1/2029 qui sont des lundis, je devrais avoir 1 et la fonction donne 52 !

Date.prototype.getWeekNumber = function(){
  var d = new Date(Date.UTC(this.getFullYear(), this.getMonth(), this.getDate()));
  var dayNum = d.getUTCDay() || 7;
  d.setUTCDate(d.getUTCDate() + 4 - dayNum);
  var yearStart = new Date(Date.UTC(d.getUTCFullYear(),0,1));
  return Math.ceil((((d - yearStart) / 86400000) + 1)/7)
};

reste à savoir si tu te situes au Canada ou en Europe, mais ton profil est bien France / GMT+1

En réalité, c'est plus complexe que cela et plus intelligent de la part de google ... car il tient compte de l'heure et des paramètres régionaux !! Dans l'exemple ci-dessus je n'ai mis que la date, sous-entendu à 0h00.

Donc je pars là-dessus quand même.