Array.(Microsoft Excel Office Scripts)

Japanese version.

Introduction to the use of array in Office scripts.

Operations

In an array, multiple variables are grouped together and treated as a single variable.

Individual data in an array are called "Item".

Creating Arrays

An array with empty items is declared as follows.

let variable: Item data type[] = [];

Specify the contents of the element, separated by commas, if you also want to initialize the contents.

let variable: Item data type[] = [Specify elements separated by ,separator];

For example, the following would result in an array with this structure

let array: string[] = ["Dogs","Cats","Horses"];

Index	Value
0	Dogs
1	Cats
2	Horses

Indexes are sequential numbers starting from 0 for accessing items.

read only

The data type can be prefixed with readonly to make it an unalterable array, like a constant.

let variable: readonly Item data type[] = [Specify elements separated by ,separator];

Retrieve items

Get item at specified index

variable[index];

Retrieve the first item.

If 0 is specified for the index, it is the first designation.

variable[0];

Retrieve the last item

Specifying variable name.length-1 for the index will specify the tail.

variable[variable.length - 1];

Retrieve all items (loop)

Repeat all items in a loop.

for and loop counter

for (let counter: number = 0; counter < array.length; counter++){
  array[counter]; // Current item
}

function main(workbook: ExcelScript.Workbook) {
  let nAry: number[] = [10,5,3];
  let total:number = 0;

  for(let count: number = 0; count < nAry.length; count++){
    total += nAry[count];
    workbook.getWorksheet("Test").getCell(count, 0).setValue(count);
  }

  workbook.getWorksheet("Test").getRange("B1").setValue(total);
}

This is useful when the counter is used inside a loop.
If you want to use the counter outside of the loop, you need to create (let) the counter in the block outside of for (in front of) the loop.

for-of (without counter)

If counters are not used, they can be expressed more concisely than for.

for (let item of array){
  item; // Current Element
}

function main(workbook: ExcelScript.Workbook) {
  let nAry: number[] = [10,5,3];
  let total:number = 0;

  for (let item of nAry){
    total += item;
  }

  workbook.getWorksheet("Test").getRange("A1").setValue(total);
}

Update item

Specify index

The following will change the item at the specified index.

array[index] = value;

All items

The following will modify the entire array.

array = [Specify items separated by ,separator];

Add item

Add to the tail

array.push(item);

function main(workbook: ExcelScript.Workbook) {
  let nAry: number[] = [];
  nAry.push(3);
  nAry.push(5);
  nAry.push(7);
  console.log(nAry); 
}

Add to the head

array.unshift(item);

Add to specified position

To add an item at a specified position, use thesplice method.

The index of any item after the specified position is moved by the amount of the added element.

array.splice(position,0,item);

function main(workbook: ExcelScript.Workbook) {
  let nAry: number[] = [];

  nAry.push(3);
  nAry.push(5);
  nAry.splice(1, 0, 7);

  console.log(nAry);
}

Get the number of items in the array

array.length;

function main(workbook: ExcelScript.Workbook) {
  let nAry: number[] = [];

  nAry.push(3);
  nAry.push(5);
  nAry.splice(1, 0, 7);

  console.log(nAry.length);
}

Delete items

All items

array = [];

Delete an item at the specified index

array.splice(position,Number of items to be deleted);

The following example deletes three items from index 1 (the second item).

function main(workbook: ExcelScript.Workbook) {
  let sAry: string[] = ["A","B","C","D","E"];

  console.log(sAry);
  
  sAry.splice(1,3);

  console.log(sAry);
}

Delete the first item

array.shift();

function main(workbook: ExcelScript.Workbook) {
  let sAry: string[] = ["A","B","C","D","E"];

  console.log(sAry);

  sAry.shift();

  console.log(sAry);
}

Delete the tail item

array.pop();

function main(workbook: ExcelScript.Workbook) {
  let sAry: string[] = ["A","B","C","D","E"];

  console.log(sAry);

  sAry.pop();

  console.log(sAry);
}

Merge

Merged array = array.concat(Array to be added);

function main(workbook: ExcelScript.Workbook) {
  let sAry1: string[] = ["A", "B"];
  let sAry2: string[] = ["C", "D", "E"];

  let sAry3 = sAry1.concat(sAry2);

  console.log(sAry3);
}

Get the index from the value of an item

First match

array.indexOf(value);

I have specified "Cat" as the value to search,
"Cat" has two indexes, 1 and 3.
Since the indexOf method retrieves the first one, the result is 1.

function main(workbook: ExcelScript.Workbook) {
  let sAry: string[] = ["Dog", "Cat", "Dog", "Cat", "Dog"];

  console.log(sAry.indexOf("Cat"));
}

Last match

array.lastIndexOf(value);

I have specified "Cat" as the value to search,

"Cat" has two indexes, 1 and 3.

The lastIndexOf method retrieves the last one, so the result is 3.

function main(workbook: ExcelScript.Workbook) {
  let sAry: string[] = ["Dog", "Cat", "Dog", "Cat", "Dog"];

  console.log(sAry.lastIndexOf("Cat"));
}

Get maximum and minimum values

Array items must be numbers, e.g., of type number.

Math.max.apply(null,array);
Math.min.apply(null,array);

function main(workbook: ExcelScript.Workbook) {
  let nAry: number[] = [3,5,2,10,6];

  console.log("Min:" + Math.min.apply(null, nAry).toString());
  console.log("Max:" + Math.max.apply(null, nAry).toString());
}

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.

Links

Office Scripts Articles

Office Scripts(Microsoft Excel)

See more