In one of my excel reports, I needed to use a script in order to automatize some actions. I was not able to use a macro because the main condition was to use it online in the sharepoint. As you may know, online excel can’t use macro (may be in the future) only script.

The function of the script is to check and compare each cell of a specific column to a particular value. If this value is found, it has to copy and paste as values a range of cells. The following example is to check the current date for a date column and when it finds the current date, it will copy the range of formula cells and paste them as values. This example can be adapted for other types of checking, here is a date but it can be a number, a specific word, etc.

This is my data, as we can see, the column B is a date column starting from 12/11 to 16/11 divided in 2 categories. The other columns are full of simple formula like this:

Before to create the script, I will add a new column called “day” with this formula in C2:

It will give me the day of the month. The reason for that is that using the script to compare a date, I need to provide an unique value in text format (not in date format). If you prefer the complete date, the formula should be:

=TEXT([@Date],"DD/MM/YYYY")

In VBA, to make the comparison, I can use “TODAY()” like this:

• If cell.Value = [Today()]

And it will work but in office script “new Date()” (equivalent of TODAY):

• if (rng[i][2] == new Date())

It won´t work as expected. To create the script, click on “automate -> new script”:

Put this code:

function main(workbook: ExcelScript.Workbook) {
// change Sheet2 by yours
let sheet = workbook.getWorksheet('Sheet2');
// change C1 by yours
let row_count = sheet.getRange("C1").getEntireColumn().getUsedRange().getRowCount();
let rng = sheet.getUsedRange().getValues();
let cdate = new Date().getDate();
// change 1 if the start checking row not row 2 (see picture below)
for (let i = 1; i < row_count; i++) {
// change 2 if not column C (see picture below)
if (rng[i][2] == cdate) {
// change 3 and 5 by yours (see picture below)
let firstcell = sheet.getCell(i, 3).getAddress();
let lastcell = sheet.getCell(i, 5).getAddress();
sheet.getCell(i, 3).copyFrom(`${firstcell}:${lastcell}`, ExcelScript.RangeCopyType.values);
}}}

In the code, you will see a comment “see picture below” so the following picture is to explain those numbers which are 1, 2, 3 and 5:

As you can see in the picture, office script defines the row 1 as 0 (in macro is 1) and the column A as 0 (in macro is 1).

For my script, I use getDate() to get the current date:

• new Date().getDate()

And not getTime():

• new Date().getTime()

The reason for that, it is because getTime() convert the date in milliseconds taking in account the current day/time (for instance 13/11/2023 15:27:36) and in my excel my date is 13/11/2023 meaning 13/11/2023 00:00:00 so using getTime() for comparison, it will not work. Once done, just click on “run” and this is the result for the current day (13/11/2023):

As we can see, the row 3 and 8 with the current date 13/11/2023 have only values and no more formula. If the result is not what you expect, use console.log() (equivalent to the VBA msgbox) to check the result of the “cdate” by putting below this line:

...
let cdate = new Date().getDate();
console.log(cdate);
...

If you prefer to use the full date, you will need to replate the line of “let cdate = new Date().getDate();” by:

...
let month = new Date().getMonth() + 1;
let cdate = new Date().getDate() + "/" + month + "/" + new Date().getFullYear();
...

NOTE: getMonth() will return January = 0, February = 1, March = 2, etc.

To create a button, just click on: