For some of my reportings, I am using an Office script because sharing/working my excel file in the cloud like in the sharepoint, it is not possible to use a VBA. So to automatize my actions, I have to use the script function. The advantage is that I can use Power Automate Cloud to run the script online, in comparison with a macro which is linked with Power Automate Desktop.

Office Script or also known as Typescript or simply Script offers the same possibility as a macro meaning that I can automatize at least 99% of my actions. The only thing that you have to know, it is that the script option is only available if you have the Office 365 version, in this case, you should see this “automate” tab:

As for the macro, it is time consuming to create it but once done, it will save a lot of time, in particular for reports that I have to work daily. The disadvantage is that it is not possible to combine 2 or more scripts into 1 single script, neither it is not possible to make a call reference to run another script and last, neither from Power Automate Cloud is possible to create a flow to run multiple scripts to do one after the other.

The purpose of this topic is not to explain you everything, there are books that will explain better than me including from the official Microsoft Office Script website. Here, I will just give you an introduction and some simple codes that I am using most of the time dividing in 2 parts. For this first part, I will explain about creating, editing, selecting and some tips.

The easier way is to use the “record actions” because it will create automatically the code for you based on your action in the sheet so it is very useful if you don’t have any knowledge about coding. You just have to click on:

• 1. automate -> record actions
• 2. A panel will appear
• 3. Work on your sheet as you get used to do (your actions will be displayed in the panel)
• 4. Once done, click on “stop”

The panel will change where I can see different options

I will use mainly those ones, I can click on:

• “script” to change the name
• “edit” to see the script
• “add in workbook” to add a button on my sheet (for a direct and quick access)
• “...” (the 3 dots) to have more options (in the “edit”, the 3 dots offers more options)
• “run” to launch the script so I can test it

The only point is that sometimes, it is not working properly and in this case, you need to get dirty your hands. To create manually a script, you just have to click on “automate -> new script”:

An automatic code is put inside that I will have to delete everything except the line 2 and 12:

And put my code between them. Take note that the script will be saved automatically.

To select a sheet, they are different ways but mainly I use those ones:

function main(workbook: ExcelScript.Workbook) {
  // normal code line
  workbook.getWorksheet('Sheet1');
  // give the sheet a short name
  let shortname = workbook.getWorksheet('Sheet1');
  // go to the sheet
  shortname.activate();
}

If the sheet has another name, for instance “SLA problem”, I will put “workbook.getWorksheet('SLA problem')”. I can also define a short name for my sheet, quite useful when my code line is very long (see below how it works).

The other way to select a sheet is to put its name before any actions:

function main(workbook: ExcelScript.Workbook) {
  // normal code line
  workbook.getWorksheet('Sheet1').getRange("A2").setValue("test");
  // using the short name 
  let shortname = workbook.getWorksheet('Sheet1');
  shortname.getRange("A2").setValue("test");
}

To select the current/active sheet:

function main(workbook: ExcelScript.Workbook) {
  workbook.getActiveWorksheet();
}

NOTE: if in my code I count the number of rows, the result will be different based on the sheet or on the table:

• If from a sheet, it will count rows including the header (row 1)
• If from a table, it will count rows excluding the header (row 1)

And to select a table:

function main(workbook: ExcelScript.Workbook) {
  // to select the table without referencing a sheet
  workbook.getTable("Table");
  // to select the table for a particular sheet
  workbook.getWorksheet('Sheet1').getTable("Table");
}

Take note that Office Script defines the row and column numbers totally different than the macro, the better explanation is to show you:

Script	Macro

So if in my code, I have to use numbers to define a column or a row, I will use “0” for the column A or the row 1.

The challenge is when I have to write a long script so some cautions need to be taken for it to work properly. My recommendation will be no matter how big it is your script it is to well define your string, integer, etc. for different actions with a different short names. For instance, if I define a range to get values like that “let rng = sheet.getUsedRange().getValues();” for different categories, instead to use the same short name “let rng” for all , I prefer to give different ones like “let rngsupport”, “let rngpriority”, etc.

For the second part, read Use a script to copy, cut, paste, replace and delete in an excel report (part 2).