Convertir un google sheet entier d'une devise à une autre peu importe les changements de son format

Vous utilisez google sheets pour un projet financier, et vous aimeriez convertir l'ensemble de la feuille dans une autre devise ? Ca tombe bien, j'avais le même besoin et je vous le partage. Désormais faite-le en 1 clic !

Objectif

L’objectif de cette automatisation est de :

  1. Pouvoir modifier un onglet source à notre guise : déplacement de cellules, formatage (couleurs de texte et de fond, bordures, gras, italique, …), modifications de la valeur des cellules, … En ayant certaines cellules exprimées en une devise initiale ;
  2. Garder toutes ces modifications dans un nouvel onglet, à l’exception de la devise qui sera convertie dans une autre ;
  3. Synchroniser ponctuellement les 2 onglets en 1 clic.

Et cela est valable peu importe la devise initiale et finale de votre choix !

NB : la première contrainte explique pourquoi de simples formules ne suffiseront pas. En effet, le changement fréquent de format et le déplacement de cellules ne se répercuteront pas via les formules.

Démo

Avant et après l'automatisation

Si vous êtes actuellement connecté à votre compte google, vous pouvez consulter la démo ici. Comme expliqué plus bas dans cet article, il faudra autoriser le script à s’exécuter sur votre compte google.
Vous pouvez aussi le faire sur votre google sheet personnel sans passer par la démo, en allant directement aux étapes associées.

Applications directes

Vous vous demandez sûrement : quel intérêt de faire ça ? Je vous cite 3 exemples concrets ci-dessous.

Facture en différentes devises

Si votre client réside dans un autre pays, vous pouvez rédiger de A à Z votre facture dans votre monnaie locale, puis à la fin de son édition, la convertir dans l’autre devise (ou vice versa) afin de l’envoyer à votre client.

Encaissement de devise étrangère

Si vous encaissez des montants dans une devise étrangère de manière régulière, et que vous tracez ces encaissements sur google sheets, cette solution de conversion en 1 clic pourra également vous aider.

Projet avec plusieurs parties prenantes

Si vous avez un projet financier commun avec des personnes qui vivent à l’étranger, il y a 2 possibilités :

Dans les 2 cas, la situation n’est pas idéale : c’est pour cela que basculer d’1 devise à 1 autre est automatisée dans cet article (et c’est le cas d’usage qui m’a poussée à rédiger cet article).

Pré-requis

Et puis c’est tout !

Les étapes

Dans l’exemple qui va suivre, je pars de la devise initiale MGA et j’aimerais convertir ma feuille google sheets en EUROS. A noter que 1 EURO vaut environ 4650 MGA en Mai 2024.

Créer le projet sur Google Apps Script

Google Apps Script, c’est la plateforme qui permet d’éditer du code qui s’exécutera dans le contexte d’une application google (google drive, google sheets, google docs, etc.). En bref, c’est l’équivalent de VBA dans la suite office, dans le but d’automatiser un maximum de tâches.

Pour créer le projet sur Google Apps Script, allez sur votre fichier google sheets, puis cliquez sur Extension > Apps Script. Extension > Apps Script

Un nouvel onglet va s’ouvrir comme indique la capture ci-dessous. Contenu nouveau projet appscript par défaut

Supprimez le contenu par défaut de Code.gs, collez-y le code suivant puis sauvegardez votre projet :

// feuille démo : https://docs.google.com/spreadsheets/d/1manezG_mqv3p0jg4eY6AV2GdnHnTGs5_3il0T2_gH7w/edit?usp=sharing

/****************************** DEBUT MODIFICATIONS A FAIRE ************************************/
const nom_feuille_initiale = 'Solde journalier (MGA)' //à modifier selon le nom de votre onglet source
const DEVISE_INITIALE = 'MGA' //doit être contenu dans le nom_feuille_initiale
const DEVISE_FINALE = 'EUROS' //mot remplaçant la DEVISE_INITIALE 
const CONVERSION_DEVISE_INIT_VERS_FINAL = 4650 // 1 devise finale = CONVERSION_DEVISE_INIT_VERS_FINAL devise initiale
/****************************** FIN MODIFICATIONS A FAIRE ************************************/


const ws = SpreadsheetApp.getActiveSpreadsheet()  
const FORMAT_DEVISE_FINALE = '#,##0.00[$ '+DEVISE_FINALE+']'

function dupliquer_feuille(nom_feuille_initiale, nom_feuille_finale, mode_valeurs_uniquement) {
  try{
    supprimer_feuille(nom_feuille_finale)
  }catch(err){
    console.error('Erreur: ',err)
  }finally {
    const new_sh = ws.getSheetByName(nom_feuille_initiale).copyTo(ws).setName(nom_feuille_finale)
    new_sh.activate()
    ws.moveActiveSheet(2)

    if(mode_valeurs_uniquement){
      new_sh.getRange(1, 1, new_sh.getMaxRows(), new_sh.getMaxColumns()).copyTo(new_sh.getRange('A1'), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false)
      new_sh.getRange(1, 1, new_sh.getMaxRows(), new_sh.getMaxColumns()).copyTo(new_sh.getRange('A1'), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
    }
  }
  
}

function supprimer_feuille(nom_feuille){
  ws.deleteSheet(ws.getSheetByName(nom_feuille))
}

function current_format(){
  console.log(ws.getActiveCell().getNumberFormat())
  // en euros : #,##0.00\ [$€-1]
  // en MGA : #,##0.00[$ MGA]
}

function convertir_en_nouvelle_devise(){
  const nom_feuille_finale = nom_feuille_initiale.replace(DEVISE_INITIALE,DEVISE_FINALE)

  dupliquer_feuille(nom_feuille_initiale,nom_feuille_finale,true)
  
  const sh = ws.getSheetByName(nom_feuille_finale)

  const derniere_colonne = sh.getLastColumn()
  const derniere_ligne = sh.getLastRow()
  console.log({derniere_colonne, derniere_ligne})
  
  //pour chaque cellule en DEVISE_INITIALE 
  for(ligne = 1; ligne <= derniere_ligne; ligne++){
    for(colonne = 1; colonne <= derniere_colonne; colonne++){
      console.log('ligne ',ligne, '      colonne ',colonne)
      const contenu = valeur_cellule(sh, ligne, colonne)
      //console.log(contenu)

      if (contenu.affichage.includes(DEVISE_INITIALE)) {
        //convertir en DEVISE_FINALE
        nouvelle_valeur = contenu.valeur / CONVERSION_DEVISE_INIT_VERS_FINAL
        //console.log({nouvelle_valeur})

        //écraser l'ancienne valeur
        sh.getRange(ligne,colonne).setValue(nouvelle_valeur)

        //changer le format en euros
        sh.getRange(ligne,colonne).setNumberFormat(FORMAT_DEVISE_FINALE)

        //console.log('   AVEC changement')

      } else {
        //console.log('   AUCUN changement')
      }
    }  
  }
  
}

function valeur_cellule(sh, ligne, colonne){
    const the_range = sh.getRange(ligne, colonne)

    return {
      valeur: the_range.getValue(),
      affichage: the_range.getDisplayValue(),
      formule: the_range.getFormula()

    }
}

Exécuter pour la première fois

Vérifiez que les valeurs des variables nom_feuille_initiale, DEVISE_INITIALE, DEVISE_FINALE et CONVERSION_DEVISE_INIT_VERS_FINAL correspondent bien à votre besoin. Variables à modifier

Placez-vous dans la liste déroulante de la fonction à executer, puis choisissez convertir_en_nouvelle_devise et cliquez sur Exécuter. Fonction convertir en une autre devise

Autoriser l’exécution

Vous devez ensuite autoriser l’exécution du script en cliquant sur Examiner les autorisations. Demande d'autorisation google apps script

Choisissez ensuite votre compte google, puis cliquez sur Paramètres avancées. Paramètres avancées

Enfin, cliquez sur Accéder à projet sans titre (non sécurisé), puis Autoriser. Accéder à Projet sans titre Autoriser Ne vous inquiétez pas, vous êtes le seul propriétaire de ce code, et il n’y a aucun risque encouru malgré toutes ces alertes de google.

Importer le script

Revenez ensuite sur votre google sheets. Normalement vous trouverez l’équivalent de votre onglet source mais dans une nouvelle devise.

Pour pouvoir réexecuter le script sans passer par l’interface de Google Apps Script, vous pouvez l’importer via Extensions > Macros > Importer une macro. Import de macro sur google sheet

Vous pouvez ensuite ajouter la fonction convertir_en_nouvelle_devise. Une alerte Fonction ajoutée s’affichera ensuite en bas à gauche de votre écran. Ajouter la fonction

Associer le script au clic d’une image

Toujours dans google sheets, revenez à l’onglet source. Cliquez sur Insertion > Image > Insérer une image sur les cellules. Insertion d'image sur

Choisissez la source de votre image qui permettra à son clic d’exécuter le script. Dans mon cas, j’ai choisi une icône trouvée sur internet: Choix image source pour bouton script

Cliquez sur les trois points de l’image, puis Assigner le script, puis saisissez le nom du script que vous venez d’importer : convertir_en_nouvelle_devise. Assignation du script à l'image Nom du script à exécuter

Ca y est, vous avez automatisé la conversion d’1 devise à l’autre en 1 clic, peu importe les modifications que vous avez apportées à l’onglet source !

Retourner à la liste de projets