OFFICE SCRIPTS – Une Introduction
OFFICE SCRIPTS c’est quoi ?
Globalement, 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).
Ce qu’il nous faut 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 mars 2022, 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 :
Trouvons de l’aide sur OFFICE SCRIPTS
En mars 2022 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.
L’ouvrage ‘Typescript notions fondamentales’ aux éditions ENI s’adresse aux développeurs disposant déjà d’une pratique de programmation dans d’autres langages.
Voyons les différences essentielles entre OFFICE SCRIPTS et VBA
(Constat de mars 2022)
Portée d’Office Scripts versus portée du VBA
- Alors que le VBA n’est utilisable dans EXCEL qu’en mode bureau, a contrario, OFFICE SCRIPTS n’est (actuellement) disponible qu’en mode online.
- Cependant, il semble que Microsoft ait la volonté d’étendre l’usage OFFICE SCRIPTS à l’avenir. Pour autant, compte-tenu du nombre considérable de solutions VBA actuellement en œuvre, il est quasi 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.
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 :
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
- Dans la version de mars 2022, OFFICE SCRIPTS est capable de déclencher l’exécution de script au moyen d’un bouton déposé sur la feuille du classeur.
Contrairement aux macros VBA qui peuvent être déclenchées sur un évènement (par exemple lorsque la valeur d’une certaine cellule change) OFFICE SCRIPTS n’en a pas la capacité dans sa version actuelle (gageons que ces limitations évolueront dans un futur proche, restons vigilants…). - 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 d’une heure ou d’une date déterminée.
Interaction avec les utilisateurs
- Encore une différence essentielle : dans la version mars 2022, OFFICE SCRIPTS ne nous permet pas d’interagir avec les utilisateurs.
Avec VBA nous interagissons avec les utilisateurs au moyen de formulaires “userforms” ou avec l’instruction “Input” ou bien encore avec l’instruction “Msgbox”. Toutes ces techniques sont actuellement indisponibles dans 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, dont nous sommes, 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) commençant par ‘//’ dans le code généré. Il ajoute également un commentaire lorsqu’il n’a pas pu générer le code sur une action particulière (ceci est bien utile pour avoir connaisance de ces actions non automatisables).
- Dans l’image suivante, les lignes de commentaires générées automatiquement sont surlignées en jaune :
Illustrons OFFICE SCRIPTS par un exemple
A titre d’exemple, reprenons l’application EXCELLONS ‘Jours fériés en France’ téléchargeable ici.
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…
En effet, 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 sera exécuté dans EXCEL mode online de la façon suivante:
A noter que les Office scripts se comportent de façon identique sur plateforme WINDOWS et MAC. A contrario, le code VBA de ce classeur ne peut s’exécuter sur MAC (du fait de l’absence de l’objet ‘MSXML2.XMLHTTP’ dans cette environnement).
Ci-dessous, nous fournissons l’intégralité du code Office Scripts permettant de réaliser, en environnement EXCEL Online, la mise à jour du tableau de résultats en fonction du territoire et de l’année choisis :
//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 on 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 constitue la formule à copier dans la colonne 'Jour de la semaine' pour obtenir le 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 teste le statut de la réponse de 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 colonne du tableau avec la formule donnant le jour de la semaine
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);
}
}L’IA connait Office Script !
Nous sommes tous friands des nouveautés technologiques et impatients, à leur émergence, d’en découvrir les possibilités et limites.
C’est avec curiosité et gourmandise que nous avons posé le prompt suivant à Copilot (IA de Microsoft) :
- Comment générer un tableau structuré avec Office Script ?
Copilot a renvoyé la réponse suivante :
Whaou…ça décoiffe…Dans quelque temps, à quoi les programmeurs spécialistes seront-ils employés ?
