OFFICE SCRIPTS c’est quoi ?

Grossièrement, Office Scripts est pour Excel mode online l’équivalent de ce que l’enregistreur de macros et de l’éditeur VBE est pour EXCEL mode bureau.

Nous écrivons les scripts d’OFFICE SCRIPTS en langage TypeScript lui-même hérité du langage JavaScript (ce qui rapproche ces scripts de ceux écrits en Google Apps Script pour les GOOGLE SHEETS).

Usage basique d’OFFICE SCRIPTS

Sur le même principe que l’enregistreur de macros d’EXCEL mode bureau, l’enregistreur d’actions nous permet d’enregistrer une série d’actions dans EXCEL online, de donner un nom à cette série puis de la reproduire en l’exécutant.

Usage avancé d’OFFICE SCRIPTS

Sur le même principe que l’éditeur VBE d’EXCEL mode bureau, l‘éditeur de code intégré nous permet de créer des scripts à partir de rien ou de modifier les scripts générés par l’enregistreur d’actions. Avant de nous lancer dans l’écriture de code, nous devons posséder un minima de connaissance du langage TypeScript. L’investissement nécessaire à cet apprentissage dépend grandement de notre connaissance préalable de la programmation (Si nous maîtrisons le langage Javascript, cet investissement devrait être relativement léger).

Prérequis pour bénéficier d’OFFICE SCRIPTS

Pour pouvoir bénéficier d’OFFICE SCRIPTS, nous devons disposer d’un des abonnements Microsoft 365 listés ici.

Dans sa version de septembre 2021, OFFICE SCRIPTS n’est pas disponible dans EXCEL mode bureau

Lorsque nous disposons d’un abonnement Microsoft 365 éligible à OFFICE SCRIPTS, l’onglet Automatiser est ajouté au ruban EXCEL mode online :

Menu_Debogage_Compiler

Trouvons de l’aide sur OFFICE SCRIPTS

En septembre 2021 le produit OFFICE SCRIPTS est encore récent et, de ce fait, l’offre d’aide est encore limitée.

La documentation en ligne d’OFFICE SCRIPTS de Microsoft est accessible ici.

Nous disposons de vidéos de présentation d’Office Scripts. En premier lieu celles réalisées par Microsoft ou encore celles réalisées par d’autres contributeurs : celle de Kevin Stratvert (en anglais) ou celle de Leila Gharani (en anglais) par exemple.

Au moment de l’écriture de cet article, aucune formation dédiée à Office Scripts n’a pu être trouvée. A défaut, nous pouvons, si besoin, nous former à JavaScript. La plateforme de formations Udemy en propose plusieurs dont certaines réalisées par des formateurs de langue française.

Nous pouvons soumettre nos problèmes et questions sur les forums d’échanges en français tel que celui d’EXCEL-PRATIQUE auquel nous contribuons.

Passons en revue les différences entre OFFICE SCRIPTS et le VBA

(Constat de septembre 2021) 

Portée d’Office Scripts versus portée du VBA

  • Alors que le VBA n’est utilisable dans EXEL qu’en mode bureau, a contrario, OFFICE SCRIPTS n’est actuellement disponible qu’en mode online. Il semble cependant que la volonté de Microsoft soit d’étendre l’usage OFFICE SCRIPTS dans les années à venir. Pour autant, compte-tenu du nombre considérable de solutions VBA actuellement en œuvre, il est certain que la fin du VBA n’est pas pour demain.

Stockage des Scripts

  • Avec le VBA les macros sont stockées dans le même fichier que le classeur alors que les fichiers de scripts OFFICE SCRIPTS (d’extension .osts) sont stockés dans le dossier « Scripts Office » de notre espace OneDrive. Cela offre l’avantage de pouvoir aisément et à notre convenance partager nos scripts entre nos divers classeurs et d’en faire bénéficier d’autres utilisateurs de notre organisation.
Menu_Debogage_Compiler

Déclaration des objets manipulés

  • Alors que VBA tolère la déclaration implicite des objets manipulés dans un classeur, OFFICE SCRIPTS impose une déclaration explicite de tout objet manipulé. Ainsi par exemple pour l’objet ‘worksheet’, VBA (à défaut d’une déclaration explicite) considère la feuille sélectionnée dans le classeur comme étant la feuille manipulée, alors que pour OFFICE SCRIPTS la déclaration explicite de la feuille manipulée est impérative. Nous réalisons cette déclaration dans le code TypeScript avec une instruction let :
Menu_Debogage_Compiler

Référencement par indice

  • Une différence essentielle : le référencement des cellules par indice.
    Dans VBA le référencement des cellules par indice a pour base 1 alors que dans OFFICE SCRIPTS le référencement des cellules par indice a pour base 0.
    Ainsi, pour référencer par indice la cellule « A1 » de la feuille active, dans VBA nous écrivons :
    set oCell = Activesheet.Cells(1,1)
    alors que dans un script OFFICE SCRIPTS nous écrivons :
    let oCell = SelectedSheet.getCells(0,0)

Déclenchement des scripts

  • Autre différence essentielle : dans la version septembre 2021, OFFICE SCRIPTS n’est pas capable de déclencher l’exécution de script sur un évènement.
    Nous sommes habitués à déclencher l’exécution des macros VBA en les attachant à un bouton, à une image ou lorsque la valeur d’une certaine cellule change. Ces types de déclenchement sont actuellement indisponibles pour les scripts d’OFFICE SCRIPTS. Au demeurant, l’application POWER AUTOMATE (incluse dans les abonnements Microsoft 365) nous permet de déclencher les scripts OFFICE SCRIPTS sur certains évènements comme : l’arrivée d’un mail dans OUTLOOK 365 ou la survenue une heure ou d’une date déterminée (gageons que ces limitations évolueront dans un futur proche. Restons vigilants…).

Interaction avec les utilisateurs

  • Encore une différence essentielle : dans la version septembre 2021, OFFICE SCRIPTS ne nous permet pas d’interagir avec les utilisateurs.
    Avec VBA nous interagissons avec les utilisateurs au moyen de formulaires « userforms » ou bien avec l’instruction « Input » ou bien encore avec l’instruction « Msgbox ». Toutes ces techniques sont actuellement indisponibles dans les scripts d’OFFICE SCRIPTS. L’application POWER AUTOMATE (incluse dans les abonnements Microsoft 365) pourrait nous offrir une alternative.

Gestion des erreurs et exceptions

  • Une bonne nouvelle : OFFICE SCRIPTS comporte une instruction ‘try…catch…finally’.
    Grâce à l’instruction ‘try…catch…finally’ OFFICE SCRIPTS nous permet de gérer les erreurs et les exceptions de façon structurée.
    (Un ouf de soulagement est poussé par les tenants de la programmation structurée horrifiés par les instructions « On Error… » parfois incontournables en VBA).

 Ajout automatique de commentaires par Office Scripts

  • L’enregistreur d’actions OFFICE SCRIPTS ajoute automatiquement des lignes de commentaires (en anglais actuellement) dans le code qu’il génére. Par exemple, dans le script suivant les lignes de commentaires ajoutés automatiquement sont surlignés en jaune :
Menu_Debogage_Compiler

Illustrons OFFICE SCRIPTS par un exemple

A titre d’exemple, reprenons l’application EXCEL ‘Jours fériés en France’ téléchargeable ici.

Application EXCEL "Jours fériés en France"

Lorsque nous ouvrons ce classeur dans EXCEL en mode bureau, si nous modifions le territoire ou l’année, le tableau des jours fériés est automatiquement mis à jour, de même que le nombre de jours ouvrés.

Par contre, lorsque nous ouvrons ce classeur dans EXCEL en mode online, si nous modifions le territoire ou l’année… OUPS! Rien ne se passe…

La raison est qu’EXCEL mode online ne prend pas en charge ni les évènements du classeur ni les macros VBA (comme vu dans le chapitre précédent). Dans le cas où nous voudrions transposer les fonctions réalisées par VBA, il nous faut réaliser un script OFFICE SCRIPTS en TypeScript.

Une fois mis au point, ce script pourra être alors exécuté dans EXCEL mode online de la façon suivante:

Application EXCEL "Jours fériés en France"

A noter que les Office scripts se comportent de façon identique sur plateforme WINDOWS et MAC. A contrario, le code VBA ne peut s’exécuter sur MAC (du fait de l’absence de l’objet ‘MSXML2.XMLHTTP’ dans cette environnement).

Ci-dessous le code du script :

//OfficeScript de récupération des jours fériés en France pour un territoire donné et une année donnée.
async function main(workbook: ExcelScript.Workbook){
//On vérifie que le classeur est celui attendu
const cWorkbookName = 'JoursFeries.xslm';
let sWorkbookName: string = workbook.getName();
if (sWorkbookName = cWorkbookName) {
    try {
        //On affecte la feuille et on vérifie que la feuille active est celle attendue
        let oActiveSheet: ExcelScript.Worksheet = workbook.getWorksheet('Accueil');
        try {
            //On affecte toutes les zones nommées et vérifie leur présence
            let sChoixZone = oActiveSheet.getRange('txtZone').getValue();
            let sChoixAnnee = oActiveSheet.getRange('txtAnnee').getValue().toString();
            let oCellStatut = oActiveSheet.getRange('txtStatut');
            let oTableau: ExcelScript.Table = oActiveSheet.getTable('tblResult');
            let oCellMsg = oActiveSheet.getRange('txtMessage');

            //On efface le message et le statut
            oCellMsg.clear(ExcelScript.ClearApplyTo.all);
            oCellStatut.clear(ExcelScript.ClearApplyTo.all);

            //On efface le contenu du tableau de résultats
            oTableau.getRangeBetweenHeaderAndTotal().clear(ExcelScript.ClearApplyTo.all)

            //On initialise la cellule statut
            oCellStatut.getFormat().getFill().setColor('orange');
            oCellStatut.setValue('En cours...');

            //On déclare l'URL racine de l'API
            const cUrl = 'https://calendrier.api.gouv.fr/jours-feries/';
            //On constitue la requête pour l'API
            const sRequestString = cUrl + sChoixZone + '/' + sChoixAnnee + '.json';
            //On constirue la formule à copier dans la colle 'Jour de la semaine'
            const sFormula = "=SI(ESTNUM([@[Jour férié]]);TEXTE([@[Jour férié]];\"jjjj\");\"\")";

            //On affecte l'objet Request API
            const oRequest = await fetch(sRequestString);            
            try {
                //On envoie la requête à l'API
                if (oRequest.status === 200) {
                    //On récupère la réponse de l'API
                    const oResponseJSON: string = await oRequest.json();
                   
                    //On parse la réponse de l'API
                    const sResponse = JSON.stringify(oResponseJSON);
                    let lLen = sResponse.length;
                    let sUsageData = sResponse.substring(1, lLen - 1);
                    const aUsageData = sUsageData.split(',');

                    //On traite chaque ligne de la réponse parsée
                    for (let i = 0; i < aUsageData.length; i++) {
                        let aUsageText = aUsageData[i].split(':');

                        //On remplit la première colonne du tableau avec les dates reçues
                        let sText: string  = aUsageText[0].trim();
                        lLen = sText.length;                    
                        sText = sText.substring(1, lLen - 1);
                        oTableau.getRangeBetweenHeaderAndTotal().getCell(i, 0).setValue(sText);
                        oTableau.getRangeBetweenHeaderAndTotal().getCell(i, 0).getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);

                        //On remplit la deuxième colonne du tableau avec le libellé reçu
                        let sText2: string = aUsageText[1].trim();
                        lLen = sText2.length;
                        sText2 = sText2.substring(1, lLen - 1);
                        oTableau.getRangeBetweenHeaderAndTotal().getCell(i, 1).setValue(sText2);

                        //On remplit la troisième collone du tableau avec la formule
                        oTableau.getRangeBetweenHeaderAndTotal().getCell(i, 2).setFormulaLocal(sFormula);                        
                    }
                    //On met en forme le message de résultat et le statut à OK
                    oCellStatut.getFormat().getFill().setColor('green');
                    oActiveSheet.getRange('E5').getFormat().getFont().setColor("FFFFFF");
                    oCellStatut.getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
                    oCellStatut.setValue('OK');
                    oCellMsg.setValue("L'API a renvoyé " + aUsageData.length + " jours fériés en " + sChoixAnnee + " pour le territoire '" + sChoixZone + "'." );
                    oCellMsg.getFormat().getFont().setColor('green');

                    //On fait pointer le surseur sur la ligne de titre
                    let oRange = oActiveSheet.getRange("B2:D2");
                    oRange.select();
                }
                else {
                    //Dans le cas où l'on obtient une erreur de l'API
                    oCellStatut.getFormat().getFill().setColor('red');
                    oCellStatut.setValue('Erreur');
                    oCellStatut.getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
                    //On ajoute un commentaire à la cellule contenant le message 
                    let oComment = oActiveSheet.addComment(oCellMsg.getAddress(), "L'API ne peut répondre à la requête pour le territoire et l'année indiquée !");
                    
                }
            }
            catch {
                //Dans le cas où l'on ne peut atteindre l'API
                oCellStatut.getFormat().getFill().setColor('orange');
                oCellStatut.setValue('Indisponibilité');
                oCellStatut.getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);
                oCellMsg.setValue("Le service interrogé est momentanément indisponible. Attendre quelques minutes puis retenter l'exécution de ce script.");
                console.log(oRequest.status);
            }
        }
        catch{																											//Dans le cas où une zone nommée n'est pas présente
            console.log("Le classeur actif ne semble pas correspondre à ce traitement. Traitement stoppé.")
        }
    }
    catch {																												//Dans le cas où la feuille attendue n'est pas présente
        console.log("La feuille voulue n'est pas présente dans le classeur actif. Traitement stoppé.")
    }
}
else {
    //Dans le cas où le classeur n'est pas celui attendu
    console.log(sWorkbookName);
}
}