This section introduces the looping (repetitive processing) method in Office scripts.
This loop repeats the same process under certain conditions.
Operations
loop
There are three main looping methods. (Loops for arrays are separate)
| ループ | 説明 |
|---|---|
| while | Repeat while certain conditions are met. |
| do-while | Loop to be judged behind. |
| for | Repetition by loop counter. |
while(repeats while certain conditions are met)
Repeat the same process while the result of the conditional expression is true.
The while is described as follows. (Red text should be changed accordingly.)
while (Conditional expressions that continue as long as they are true) {
iterative process
}function main(workbook: ExcelScript.Workbook) {
let value: string = "";
let count: number = 0;
let total: number = 0;
value = workbook.getWorksheet("Test").getCell(count,0).getValue().toString();
console.log(value);
while(value != "Exit" ){
total += Number(value);
count++;
value = workbook.getWorksheet("Test").getCell(count, 0).getValue().toString();
}
workbook.getWorksheet("Test").getRange("D1").setValue(total);
workbook.getWorksheet("Test").getRange("D2").setValue(count);
}
If the result of the conditional expression is true when the while starts, it will not be executed once.
Increment operator
Although this is a departure from loops, "count++" and "count = count + 1" in line 11 mean the same thing.
The ++ is an increment operator that adds 1 to the specified variable.
It is often used for brevity and by convention in the original language (and therefore somewhat more advanced).
Neither is correct, but since many users of Office scripts are not professional programmers, the latter is easier to write, so the latter should be used.
(However, since professional programmers tend to use the former description, it is better to be aware of its existence.)
Assignment addition
In line 10, "total += Number(value)" and "total = total + Number(value)" mean the same thing.
The += is called assignment addition.
The latter is safer to use for the same reason as the aforementioned increment operator.
do-while
Repeat the same process while the result of the conditional expression is true.
The do-while is described as follows.
do{
iterative process
}while (Conditional expressions that continue while true)In the case of do-while, the decision comes after the repeating process, so it is executed at least once.
Other than that, it is the same as while.
for(iteration with loop counter)
Repeat the same process by specifying an initialization expression, a continuation condition expression, and an amount to change (update expression).
This is suitable for a numerical value that changes by a certain amount each time it loops, such as a loop counter,
This is suitable for use as an end condition for a loop counter.
for (initialization expression; continuation condition expression; update expression) {
iterative process
}First an initialization expression is executed, and the process is repeated while the continuation condition expression is false (the opposite of while), The update expression is executed after the repeated process. The following example repeats 10 times.
The update formula can be anything other than 1 addition. (Simple count-up by 1 is the most common.) Other arithmetic operations are possible, but are most likely to be used in practice.
Other arithmetic operations are possible, but subtraction is the most likely to be used in practice.
It does not have to be except for the conditional formula for continuations. (although there are few situations in which this is necessary)
Starting value is 0 or 1?
Basically, it is better to start with 0 and set the end condition as counter < end value (i<10 in the example).
The "first row and column of cell access (getCell)" and the "index of the first element of the array" are
Both are 0, because there are many opportunities to use them in the loop.
If for some reason you want to use 1 as the starting value,
the end condition should be counter<=end value (i<=10 in the example).
Variable name
In the code example, the loop counter variable is named i.
If you use i, it is followed by j, k… and so on.
I've given the example because it is often used as a convention by programmers in their day jobs.
(a tradition from its ancestors Javascript and C).
In Office scripting, names such as count are recommended.
(similar situation to the aforementioned increment operator).
Loop Control
There are ways to change the flow in a repetitive process.
| Control | Description |
|---|---|
| continue | The subsequent process is skipped and the next loop is executed. |
| break | The loop is terminated, skipping subsequent processing. |
Basically, it is used when some condition (If or Switch) is satisfied.
continue(next loop)
continue interrupts the loop and moves on to the next loop.
In the following example, if the counter is even, continue.
(If the remainder divided by 2 is 0, it is even; if it is 1, it is odd. i%2==0)
The result of continued will not write to the cell if it is even.
break(exit loop)
break interrupts the loop and leaves the loop for that cycle.
Make it a never-ending loop condition, like while(true){},
There is also a way to break the loop at several points, such as {}while(true){}.
(Care must be taken to avoid an infinite loop.)
In the following example, the counter breaks when it reaches 5.
As a result of the break, no further writing will be done to the cell.
What to do when an infinite loop occurs
If an Office script is started with an exit condition that is not true, the Office script will not end.
If such a condition occurs, you can force it to end by pressing the "Stop" button.
Variable declaration and scope within a loop
Variables declared with let are available only in the area (block) enclosed by {} and
only available in blocks lower than {}.
Variables declared in a loop block are not accessible outside the loop.
In the following example, loopOutValue is still available in line 12,
count and loopInValue are not available in lines 13 and 14.
Variables used only in loops should be declared inside the loop.
This makes it easier for others to understand the structure when they read it.
(This is because they will know that the variable is not used outside of the loop.)
It is possible to declare a variable with the same name as the variable in the loop after exiting the loop, but this is deprecated because it would be very complicated,
However, this is deprecated because it is very complicated.
Variable declarations with var (deprecated)
Variables can also be declared with the statement var instead of let.
With var, though, variables declared inside a loop can be accessed outside the loop,
This characteristic is useful at the programming stage, but detrimental to later reading.
If you are familiar with JavaScript, you are likely to use var, so I will describe it here.
(because let was not available in some older browsers).
However, it is better to use "let" instead of "var".
For those who want to learn Office script effectively
The information on this site is now available in an easy-to-read e-book format.
Or Kindle Unlimited (unlimited reading).
You willl discover how to about basic operations.
By the end of this book, you will be equipped with the knowledge you need to use Excel Office Script to streamline your workflow.