﻿Offece ScriptではじめるExcel on the web開発


掲載リスト




リスト1-1
function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();
  // Set range A1:A2 on selectedSheet
  selectedSheet.getRange("A1:A2").setValues([["1"],["2"]]);
  // Auto fill range
  selectedSheet.getRange("A1:A2").autoFill("A1:A10", ExcelScript.AutoFillType.fillDefault);
}




リスト2-1
const tax = 0.1
const price = 12500
const result = price * (1.0 + tax)
console.log(price + 'の税込価格は、' + result + '円です。')




リスト2-2
const arr = [100, 200, 300]
let total = 0
total += arr[0]
total += arr[1]
total += arr[2]
console.log('Total: ' + total)




リスト2-3
const x = 1234 //☆
if (x % 2 == 0) {
    console.log(x + 'は、偶数です。')
} else {
    console.log(x + 'は、奇数です。')
}




リスト2-4
let month = 7 //☆
let season = ''
let m:number
if (month == 12) {
    m = 0
} else {
    m = month
}
switch(Math.floor(m / 3)) {
    case 0:
        season = '冬'
        break
    case 1:
        season = '春'
        break
    case 2:
        season = '夏'
        break
    case 3:
        season = '秋'
        break
    default:
        season = '不明'
}
console.log(month + '月は、「' + season + '」です。')




リスト2-5
const max = 100
let total = 0
let n = 0


while(++n <= max) {
    total += n
}
console.log(max + "までの合計は、" + total + 'です。')




リスト2-6
const max = 100 //☆
let total = 0


for(let i = 1;i <= max;i++) {
    total += i
}
console.log(max + "までの合計は、" + total + 'です。')




リスト2-7
const arr = [10,20,30,40,50] //☆
let total = 0


for(let i in arr) {
    total += arr[i]
}
let ave = Math.floor(total / arr.length)
console.log("合計は、" + total + '、平均は、'
     + ave + 'です。')




リスト2-8
function hello() {
    console.log('Hello!')
}


hello()
hello()




リスト2-9
function hello(name:string) {
    console.log('Hello!, ' + name + '!!')
}


hello('Taro')
hello('Hanako')




リスト2-10
function total(max:number):number {
    let total = 0
    for(let i = 1;i <= max;i++) {
        total += i
    }
    return total
}


console.log('合計：' + total(100))
console.log('合計：' + total(200))




リスト2-11
function calc(price:number, tax?:number):number {
    let m:number
    if (tax == undefined) {
        m = 1.1
    } else {
        m = 1.0 + tax
    }
    return Math.floor(price * m)
}


console.log('税込価格：' + calc(12300))
console.log('税込価格：' + calc(12300,0.08))




リスト2-12
function calc(price:number, tax:number = 0.1):number {
    return Math.floor(price * (1.0 + tax))
}




リスト2-13
function add(value: number | string):void {
    let v = Number(value)
    if (v == NaN) {
        v = 0
    }
    let re = 0
    for (let i = 1;i <= v;i++) {
        re += i
    }
    console.log('total: ' + re)
}


add(10)
add('200')
add('abc')




リスト2-14
function getData(id: number):[string, number] {
    const data:[string,number][] = [
        ["taro", 39],
        ["hanako", 28],
        ["sachiko", 17]
    ]
    return data[id]
}


const [nm,ag] = getData(1) //☆
console.log('名前：' + nm + '、年齢：' + ag)




リスト2-15
function double(n: number):number {
    return n * 2
}


console.log(double(123))




リスト2-16
const double = function(n: number):number {
    return n * 2
}


console.log(double)
console.log(double(123))




リスト2-17
function print(n:number, f:Function):void {
    console.log('結果：' + f(n))
}


print(123,function(n:number){ return n+n})
print(123,function(n:number){ return Math.pow(n,2)})
print(123,function(n:number){ 
    return Math.floor(Math.sqrt(n))})




リスト2-18
function print(n:number, f:(n:number)=>number):void {
    console.log('結果：' + f(n))
}




リスト2-19
print(123,(n:number)=> n+n)
print(123,(n:number)=> Math.pow(n,2))
print(123,(n:number)=> Math.floor(Math.sqrt(n)))




リスト2-20
const person = {
    name:'Taro',
    mail:'taro@yamada',
    age:39
}


console.log(person.name + '(' + person.age
    + ')'  +  '[' + person.mail + ']')




リスト2-21
const person = {
    name:'Taro',
    mail:'taro@yamada',
    age:39,
    print:()=>{
        console.log(person.name + '(' 
            + person.age + ')'  +  '['
            + person.mail + ']')
    }
}


person.print()




リスト2-22
const person = {
    name:'Taro',
    mail:'taro@yamada',
    age:39,
    print:function():void {
        console.log(this.name + '(' 
            + this.age + ')'  +  '['
            + this.mail + ']')
    }
}




リスト2-23
class Person {
    name = ''
    mail  = ''
    age = 0
    print():void {
        console.log('《' + this.name + ', ' + this.age
            + ', ' + this.mail + '》')
    }
}


const taro = new Person()
taro.name = 'Taro'
taro.mail = 'taro@yamada'
taro.age = 39
taro.print()




リスト2-24
class Person {
    name:string
    mail:string
    age:number


    constructor(name:string, mail:string, age:number) {
        this.name = name
        this.mail  = mail
        this.age = age      
    }


    print():void {
        console.log('《' + this.name + ', ' + this.age
            + ', ' + this.mail + '》')
    }
}


const taro = new Person('Taro','taro@yamada', 39)
taro.print()
const hanako = new Person('Hanako','hanako@flower',28)
hanako.print()




リスト2-25
class Person {
    name:string
    mail:string
    age:number


    constructor(name:string, mail:string, age:number) {
        this.name = name
        this.mail  = mail
        this.age = age      
    }


    print():void {
        console.log('《' + this.name + ', ' + this.age
            + ', ' + this.mail + '》')
    }
}


class Family extends Person {
    family:string
    relation:string


    constructor(family:string, name:string, mail:string, 
            age:number, relation:string) {
        super(name,mail,age)
        this.family = family
        this.relation = relation
    }


    print():void {
        console.log('《' + this.family + '-' +this.name + ', ' 
            + this.age + ', ' + this.mail + ', ' + this.family 
            + '家の' + this.relation + '》')
    }
}


const taro = new Person('Taro','taro@yamada', 39)
taro.print()
const hanako = new Family('Tanaka','Hanako','hanako@flower',28,'長女')
hanako.print()
const sachiko = new Family('Matsuzawa','Sachiko','sachico@happy',17,'次女')
sachiko.print()




リスト2-26
class TaxCalc {
    static tax:number = 0.1


    static calc(price:number):number {
        return Math.floor(price * (1.0 + this.tax))
    }


    static print(price:number):void {
        const res = this.calc(price)
        console.log(price + ' の税込価格：' + res)
    }
}


TaxCalc.print(12500)
TaxCalc.tax = 0.08
TaxCalc.print(12500)




リスト2-27
interface Printing {
    print():void
}


class Person implements Printing {
    name:string
    age:number
    
    constructor(name:string,age:number) {
        this.name = name
        this.age = age
    }


    print():void {
        console.log('《' + this.name + '('
            + this.age + ')》')
    }
}


class Pet implements Printing {
    name:string
    kind:string


    constructor(name:string,kind:string) {
        this.name = name
        this.kind = kind
    }


    print():void {
        console.log('＊' + this.name
            + 'という' + this.kind + 'です。')
    }
}


const data:Printing[] = [
    new Person('taro',39),
    new Pet('ジョン','ハスキー犬'),
    new Person('hanako',28),
    new Pet('クロ','アメショー')
]
for(let i in data) {
    data[i].print()
}




リスト2-28
class Pet {
    name:string
    constructor(name:string) {
        this.name = name
    }
}
class Cat extends Pet {
    nyaon():void {
        console.log(this.name + 'だにゃ～ん。')
    }
}
class Dog extends Pet {
    waon() : void {
        console.log(this.name + 'だワン！')
    }
}
class PetShop<T> {
    data:T[] = []


    add(animal:T) {
        this.data.push(animal)
    }
    
    list():T[] {
        return this.data
    }
}


const cats = new PetShop<Cat>()
cats.add(new Cat('ローリー'))
cats.add(new Cat('ハチ'))
const data1 = cats.list()
for(let i in data1) {
    data1[i].nyaon()
}
const dogs = new PetShop<Dog>()
dogs.add(new Dog('ジョン'))
dogs.add(new Dog('たろう'))
const data2 = dogs.list()
for(let i in data2) {
    data2[i].waon()
}




リスト3-1
function main(workbook: ExcelScript.Workbook) {
  const wbname = workbook.getName()
  console.log(wbname)
}




リスト3-2
function main(workbook: ExcelScript.Workbook) {
  const sheet1 = workbook.getActiveWorksheet()
  const sheet2 = workbook.getWorksheet('Sheet1')
  const sheet3 = workbook.getFirstWorksheet()
  const sheet4 = workbook.getLastWorksheet()
  console.log(sheet1.getName())
  console.log(sheet2.getName())
  console.log(sheet3.getName())
  console.log(sheet4.getName())
}




リスト3-3
function main(workbook: ExcelScript.Workbook) {
  const sheets = workbook.getWorksheets()
  for (let i in sheets) {
    let sheet = sheets[i]
    sheet.setName('No,' + i)
  }
}




リスト3-4
function main(workbook: ExcelScript.Workbook) {
  workbook.addWorksheet('新しいシート')
  workbook.getFirstWorksheet().delete()
}




リスト3-5
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  sheet.getCell(2,2).setValue('C3セル')
  workbook.getActiveCell().setValue('active')
}




リスト3-6
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  range.setValue('*selected*')
}




リスト3-7
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const vals = range.getValues()
  console.log(vals)
  for(let i in vals) {
    for (let j in vals[i]) {
      vals[i][j] = '[' + i + ',' + j + ']' + vals[i][j]
    }
  }
  range.setValues(vals)
}




リスト3-8
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const range = sheet.getRangeByIndexes(1,2,10,1)
  const vals = [[9], [8], [7], [6], [5], [4], [3], [2], [1], [0]]
  range.setValues(vals)
}




リスト3-9
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const c = range.getColumnCount()
  for(let i = 0;i < c;i++) {
    let col = range.getColumn(i)
    col.setValue('No, ' + i)
  }
}




リスト3-10
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const r = range.getRowCount()
  for (let i = 0; i < r; i++) {
    let row = range.getRow(i)
    row.setValue('No, ' + i)
  }
}




リスト3-11
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const ra = sheet.getRanges('A1:C3,D4:F6,A7:C9,D10:F12')
  const rar = ra.getAreas()
  const vals = [
    ['A', 'one', 1],
    ['B', 'two', 2],
    ['C', 'three', 3]
  ]
  for(let i in rar) {
    rar[i].setValues(vals)
  }
}




リスト3-12
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const fmt = range.getFormat()
  fmt.setColumnWidth(70)
  fmt.setRowHeight(25)
}




リスト3-13
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const fmt = range.getFormat()
  fmt.getFill().setColor('blue')
}




リスト3-14
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const fmt = range.getFormat()
  const fnt = fmt.getFont()
  fnt.setBold(true)
  fnt.setSize(16)
  fnt.setUnderline(ExcelScript.RangeUnderlineStyle.doubleAccountant)
  fnt.setColor('red')
}




リスト3-15
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const fmt = range.getFormat()
  fmt.getRangeBorder(ExcelScript.BorderIndex.edgeTop)
    .setStyle(ExcelScript.BorderLineStyle.double)
  fmt.getRangeBorder(ExcelScript.BorderIndex.edgeTop)
    .setColor('aaaaaa')
  fmt.getRangeBorder(ExcelScript.BorderIndex.edgeBottom)
    .setStyle(ExcelScript.BorderLineStyle.double)
  fmt.getRangeBorder(ExcelScript.BorderIndex.edgeBottom)
    .setColor('aaaaaa')
  fmt.getRangeBorder(ExcelScript.BorderIndex.edgeLeft)
    .setStyle(ExcelScript.BorderLineStyle.double)
  fmt.getRangeBorder(ExcelScript.BorderIndex.edgeLeft)
    .setColor('aaaaaa')
  fmt.getRangeBorder(ExcelScript.BorderIndex.edgeRight)
    .setStyle(ExcelScript.BorderLineStyle.double)
  fmt.getRangeBorder(ExcelScript.BorderIndex.edgeRight)
    .setColor('aaaaaa')
}




リスト3-16
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const cell = range.getCell(0,0)
  makeRect(cell)
}


function makeRect(cell:ExcelScript.Range) {
  doFormat(cell.getOffsetRange(-2, -2))
  doFormat(cell.getOffsetRange(-1, -2))
  doFormat(cell.getOffsetRange(0, -2))
  doFormat(cell.getOffsetRange(1, -2))
  doFormat(cell.getOffsetRange(2, -2))
  doFormat(cell.getOffsetRange(-2, 2))
  doFormat(cell.getOffsetRange(-1, 2))
  doFormat(cell.getOffsetRange(0, 2))
  doFormat(cell.getOffsetRange(1, 2))
  doFormat(cell.getOffsetRange(2, 2))
  doFormat(cell.getOffsetRange(-2, -1))
  doFormat(cell.getOffsetRange(-2, 0))
  doFormat(cell.getOffsetRange(-2, 1))
  doFormat(cell.getOffsetRange(2, -1))
  doFormat(cell.getOffsetRange(2, 0))
  doFormat(cell.getOffsetRange(2, 1))
}
function doFormat(cell:ExcelScript.Range) {
  const fmt = cell.getFormat()
  fmt.setRowHeight(25)
  fmt.setColumnWidth(25)
  fmt.getFill().setColor('yellow')
}




リスト3-17
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const first = range.getCell(0,0)
  const last = range.getLastCell()
  const r1 = first.getRowIndex()
  const r2 = last.getRowIndex()
  range.setValue(r1 + '->' + r2)
}




リスト3-18
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const first = range.getCell(0, 0)
  const last = range.getLastCell()
  const c1 = first.getColumnIndex()
  const c2 = last.getColumnIndex()
  range.setValue(c1 + '->' + c2)
}




リスト3-19
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const first = range.getCell(0, 0)
  const last = range.getLastCell()
  const r1 = first.getRowIndex()
  const r2 = last.getRowIndex()
  const c1 = range.getColumnIndex() + 1
  const sheet = workbook.getActiveWorksheet()
  const cells = sheet.getRangeByIndexes(r1, c1, r2 - r1 + 1, 1)
  cells.setValue('＊＊＊')
}




リスト3-20
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const first = range.getCell(0, 0)
  const last = range.getLastCell()
  const r1 = first.getRowIndex()
  const r2 = last.getRowIndex()
  const c1 = first.getColumnIndex()
  const c2 = last.getColumnIndex()
  const sheet = workbook.getActiveWorksheet()
  set99(sheet, r1, c1, r2, c2)
}


function set99(sheet:ExcelScript.Worksheet, 
    r1: number, c1: number, r2: number, c2: number) {
  for(let i = 0;i <= r2 - r1;i++) {
    for(let j = 0;j <= c2 - c1;j++) {
      const i2:number = i + 1
      const j2:number = j + 1
      const f = i2 + 'x' + j2 + '='
      sheet.getCell(r1 + i, c1 + j).setValue(f + (i2 * j2))
    }
  }
}




リスト3-21
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const result = get99(range)
  range.setValues(result)
}


function get99(r: ExcelScript.Range): string[][] {
  const arr = r.getValues()
  const h = arr.length
  const w = arr[0].length
  let res: string[][] = new Array(h)
  for (let i = 0; i < h; i++) {
    const rw: string[] = new Array(w)
    res[i] = rw
    for (let j = 0; j < w; j++) {
      const i2: number = i + 1
      const j2: number = j + 1
      const f = i2 + 'x' + j2 + '='
      res[i][j] = f + (i2 * j2)
    }
  }
  return res
}




リスト3-22
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  range.getRowsAbove(2).setValue('Above!')
  range.getRowsBelow(2).setValue('Below!')
  range.getColumnsBefore(2).setValue('Before!')
  range.getColumnsAfter(2).setValue('After!')
}




リスト3-23
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const below = range.getRowsBelow(1)
  const after = range.getColumnsAfter(1)
  belowSet(below)
  afterSet(after)
}


function belowSet(r:ExcelScript.Range) {
  const n = r.getColumnCount()
  for (let i =0;i < n;i++) {
    r.getCell(0,i).setValue('col ' + i)
  }
}


function afterSet(r: ExcelScript.Range) {
  const n = r.getRowCount()
  for (let i = 0; i < n; i++) {
    r.getCell(i, 0).setValue('row ' + i)
  }
}




リスト3-24
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const format = range.setNumberFormat('#,###.00')
}




リスト3-25
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const format = range.setNumberFormat('ggg yy年 mm月 dd日')
}




リスト3-26
function main(workbook: ExcelScript.Workbook)
{
  const range = workbook.getSelectedRange()
  const next = range.getColumnsAfter(1).getCell(0,0)
  next.copyFrom(range)
}




リスト3-27
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const next = range.getColumnsAfter(1).getCell(0, 0)
  next.copyFrom(range, ExcelScript.RangeCopyType.values)
}




リスト3-28
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const sheet2 = workbook.getWorksheet('No,2')
  sheet2.activate()
  const next = sheet2.getCell(0,0)
  next.copyFrom(range)
}




リスト3-29
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  range.clear(ExcelScript.ClearApplyTo.all)
}




リスト3-30
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  range.delete(ExcelScript.DeleteShiftDirection.up)
}




リスト3-31
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const colrange = sheet.getRange('A:A')
  const rowrange = sheet.getRange('1:1')
  colrange.delete(ExcelScript.DeleteShiftDirection.left)
  rowrange.delete(ExcelScript.DeleteShiftDirection.up)
}




リスト3-32
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const colrange = sheet.getRange('A:A')
  const rowrange = sheet.getRange('1:1')
  colrange.insert(ExcelScript.InsertShiftDirection.right)
  rowrange.insert(ExcelScript.InsertShiftDirection.down)
}




リスト4-1
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const rn = range.getRowCount()
  for (let i = 0; i < rn; i++) {
    const rw = range.getRow(i)
    setRowSum(rw)
  }
  const cn = range.getColumnCount()
  for (let i = 0; i < cn; i++) {
    const cw = range.getColumn(i)
    setColSum(cw)
  }
}


function setRowSum(range: ExcelScript.Range) {
  const c1 = range.getCell(0, 0).getColumnIndex()
  const c2 = range.getLastCell().getColumnIndex()
  const c = c2 - c1 + 1
  const right = range.getLastCell().getOffsetRange(0, 1)
  const formula = '=SUM(RC[-' + c + ']:RC[-1])'
  right.setFormulaR1C1(formula)
}


function setColSum(range: ExcelScript.Range) {
  const r1 = range.getCell(0, 0).getRowIndex()
  const r2 = range.getLastCell().getRowIndex()
  const r = r2 - r1 + 1
  const bottom = range.getLastCell().getOffsetRange(1, 0)
  const formula = '=SUM(R[-' + r + ']C:R[-1]C)'
  bottom.setFormulaR1C1(formula)
}




リスト4-2
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  setRowSum(range)
  setColSum(range)
}


function setRowSum(range: ExcelScript.Range) {
  const c1 = range.getCell(0, 0).getColumnIndex()
  const c2 = range.getLastCell().getColumnIndex()
  const c = c2 - c1 + 1
  const right = range.getColumnsAfter(1)
  const formula = '=SUM(RC[-' + c + ']:RC[-1])'
  right.setFormulaR1C1(formula)
}


function setColSum(range: ExcelScript.Range) {
  const r1 = range.getCell(0, 0).getRowIndex()
  const r2 = range.getLastCell().getRowIndex()
  const r = r2 - r1 + 1
  const bottom = range.getRowsBelow(1)
  const formula = '=SUM(R[-' + r + ']C:R[-1]C)'
  bottom.setFormulaR1C1(formula)
}




リスト4-3
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  console.log(range.getFormula())
  console.log(range.getFormulaR1C1())
}




リスト4-4
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const ad1 = range.getCell(0,0).getAddress()
  const ad2 = range.getLastCell().getAddress()
  const formula = '=SUM(' + ad1 + ':' + ad2 + ')'
  const r = range.getLastCell().getColumnsAfter(1)
  r.setFormula(formula)
}




リスト4-5
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const cell1 = range.getCell(0,0)
  const cell2 = range.getCell(1,0)
  const cell3 = range.getLastCell()
  cell1.setValue('No, 1')
  cell2.setValue('No, 2')
  const ci = cell1.getColumnIndex()
  const ri = cell1.getRowIndex()
  const r = workbook.getActiveWorksheet()
      .getRangeByIndexes(ri, ci, 2, 1)
  const ad1 = cell1.getAddress()
  const ad2 = cell3.getAddress()
  r.autoFill(ad1 + ':' + ad2, 
      ExcelScript.AutoFillType.fillDefault)
}




リスト4-6
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const rnum = range.getRowCount()
  const r1 = range.getRow(0)
  const r2 = range.getRow(1)
  const v1 = r1.getValues()
  const v2 = r2.getValues()
  v1[0][0] = 'No, 1'
  v1[0][1] = 'No, 2'
  v2[0][0] = 'No, 2'
  v2[0][1] = 'No, 3'
  r1.setValues(v1)
  r2.setValues(v2)
  const ad1 = r1.getCell(0, 0).getAddress()
  const ad2 = r2.getCell(0, 1).getAddress()
  const ad3 = r2.getLastCell().getAddress()
  const fillrange1 = workbook.getActiveWorksheet()
      .getRange(ad1 + ':' + ad2)
  fillrange1.autoFill(ad1 + ':' + ad3, 
      ExcelScript.AutoFillType.fillDefault)
  const ad4 = range.getLastCell().getAddress()
  const fillrange2 = workbook.getActiveWorksheet()
      .getRange(ad1 + ':' + ad3)
  fillrange2.autoFill(ad1 + ':' + ad4, 
      ExcelScript.AutoFillType.fillDefault)
}




リスト4-7
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  range.calculate()
}




リスト4-8
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  let cfmt = range.addConditionalFormat(
      ExcelScript.ConditionalFormatType.iconSet)
  cfmt.getIconSet().setStyle(
      ExcelScript.IconSet.threeArrows)
}




リスト4-9
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  range.clearAllConditionalFormats()
}




リスト4-10
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const fmts = range.getConditionalFormats()
  fmts[0].delete()
}




リスト4-11
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  range.addConditionalFormat(
      ExcelScript.ConditionalFormatType.dataBar)
}




リスト4-12
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const ad1 = range.getCell(0, 0).getAddress()
  const ad2 = range.getLastCell().getAddress()
  let cfmt = range.addConditionalFormat(
      ExcelScript.ConditionalFormatType.colorScale)
  cfmt.getColorScale().setCriteria({
    minimum: {
      type: ExcelScript.ConditionalFormatColorCriterionType.lowestValue,
      color: '00FFFF'
    },
    maximum: {
      type: ExcelScript.ConditionalFormatColorCriterionType.highestValue,
      color: 'FF0000'
    }
  })
}




リスト4-13
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const ad1 = range.getCell(0,0).getAddress()
  const ad2 = range.getLastCell().getAddress()
  let cfmt = range.addConditionalFormat(
      ExcelScript.ConditionalFormatType.colorScale)
  cfmt.getColorScale().setCriteria({
    minimum: {
      type: ExcelScript.ConditionalFormatColorCriterionType.number,
      formula:'0',
      color:'00FF00'
    },
    maximum: {
      type: ExcelScript.ConditionalFormatColorCriterionType.formula,
      formula:'=MAX(' + ad1 + ':' + ad2 + ')',
      color: 'FF0000'
    }
  })
}




リスト4-14
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  let cfmt = range.addConditionalFormat(
      ExcelScript.ConditionalFormatType.cellValue)
  const cv = cfmt.getCellValue()
  const cvf = cv.getFormat()
  cvf.getFont().setColor("red")
  cvf.getFont().setUnderline(
      ExcelScript.ConditionalRangeFontUnderlineStyle.double)
  cvf.getFill().setColor('yellow')
  cv.setRule({
    operator: ExcelScript.ConditionalCellValueOperator.between,
    formula1: '30',
    formula2:'70'
  })
}




リスト4-15
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  let cfmt = range.addConditionalFormat(
      ExcelScript.ConditionalFormatType.containsText)
  const tc = cfmt.getTextComparison()
  const tcf = tc.getFormat()
  tcf.getFont().setColor("red")
  tcf.getFill().setColor('cyan')
  tc.setRule({
    operator: ExcelScript.ConditionalTextOperator.beginsWith,
    text: '*'
  })
}




リスト4-16
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  let cfmt1 = range.addConditionalFormat(
      ExcelScript.ConditionalFormatType.topBottom)
  const tb1 = cfmt1.getTopBottom()
  tb1.getFormat().getFont().setColor('blue')
  tb1.getFormat().getFill().setColor('CCFFFF')
  tb1.setRule({
    rank: 30,
    type: ExcelScript.ConditionalTopBottomCriterionType.topPercent
  })
  let cfmt2 = range.addConditionalFormat(
      ExcelScript.ConditionalFormatType.topBottom)
  const tb2 = cfmt2.getTopBottom()
  tb2.getFormat().getFont().setColor('red')
  tb2.getFormat().getFill().setColor('FFFFcc')
  tb2.setRule({
    rank: 30,
    type: ExcelScript.ConditionalTopBottomCriterionType.bottomPercent
  })
}




リスト4-17
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  range.getDataValidation().setRule({
    wholeNumber: {
      formula1: 0,
      formula2: 100,
      operator: ExcelScript.DataValidationOperator.between
    }
  })
}




リスト4-18
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  range.getDataValidation().setRule({})
}




リスト4-19
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  range.getDataValidation().setRule({
    wholeNumber: {
      formula1: 0,
      formula2: 100,
      operator: ExcelScript.DataValidationOperator.between
    }
  })
  range.getDataValidation().setPrompt({
    showPrompt: true,
    title: "入力",
    message: "０～100の範囲で入力して下さい。"
  })
}




リスト4-20
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const dv = range.getDataValidation()
  dv.setRule({
    wholeNumber: {
      formula1: 0,
      formula2: 100,
      operator: ExcelScript.DataValidationOperator.between
    }
  })
  dv.setErrorAlert({
    style: ExcelScript.DataValidationAlertStyle.information,
    showAlert:true,
    title:"NO!",
    message:"入力できる範囲を超えています。"
  })
}




リスト4-21
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const dv = range.getDataValidation()
  dv.setRule({
    textLength: {
      formula1: 5,
      formula2: 10,
      operator: ExcelScript.DataValidationOperator.between
    }
  })
  dv.setErrorAlert({
    style: ExcelScript.DataValidationAlertStyle.information,
    showAlert:true,
    title:"NO!",
    message:"5文字以上10文字以内で入力下さい。"
  })
}




リスト4-22
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const dv = range.getDataValidation()
  dv.setRule({
    date: {
      formula1: '=TODAY()',
      operator: ExcelScript.DataValidationOperator.lessThanOrEqualTo
    }
  })
  dv.setErrorAlert({
    style: ExcelScript.DataValidationAlertStyle.information,
    showAlert:true,
    title:"NO!",
    message:"今日より前の日付のみ受け付けます。"
  })
}




リスト4-23
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const dv = range.getDataValidation()
  dv.setRule({
    list: {
      inCellDropDown: true,
      source: "one,two,three"
    }
  })
  dv.setErrorAlert({
    style: ExcelScript.DataValidationAlertStyle.information,
    showAlert:true,
    title:"NO!",
    message:"選択肢の値のみ受け付けます。"
  })
}




リスト4-24
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const ad = range.getCell(0,0).getAddress()
  const dv = range.getDataValidation()
  dv.setRule({
    custom: {
      formula: '=COUNTIF(' + ad + ',”*OK*”)>0'
    }
  })
  dv.setErrorAlert({
    style: ExcelScript.DataValidationAlertStyle.information,
    showAlert:true,
    title:"NO!",
    message:"OKを含むテキストのみ受け付けます。"
  })
}


リスト4-25
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const dv = range.getDataValidation()
  if (dv.getValid()) {
    range.getFormat().getFill().setColor('white')
  } else {
    range.getFormat().getFill().setColor('red')
  }
}




リスト5-1
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const range = workbook.getSelectedRange()
  const chart = sheet.addChart(
      ExcelScript.ChartType.barStacked, range)
  chart.setLeft(250)
  chart.setTop(100)
  chart.setWidth(400)
  chart.setHeight(250)
  chart.activate()
}




リスト5-2
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const range = workbook.getSelectedRange()
  const chart = sheet.addChart(ExcelScript.ChartType.barStacked, range)
  chart.setPosition("E3","J10")
  chart.activate()
}




リスト5-3
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const chart = sheet.getCharts()[0]
  chart.setPosition('B8','G15')
}




リスト5-4
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const chart = sheet.getCharts()[0]
  const title = chart.getTitle()
  title.setText('サンプルチャート')
  title.setPosition(ExcelScript.ChartTitlePosition.bottom)
  title.setLeft(0)
}




リスト5-5
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const range = workbook.getSelectedRange()
  const chart = sheet.addChart(ExcelScript.ChartType.barStacked, range)
  const title = chart.getTitle()
  const fmt = title.getFormat()
  fmt.getFill().setSolidColor('eeeeff')
  fmt.getBorder().setLineStyle(ExcelScript.ChartLineStyle.continuous)
  fmt.getBorder().setWeight(2)
  fmt.getBorder().setColor('red')
  fmt.getFont().setSize(24)
}




リスト5-6
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const chart = sheet.getCharts()[0]
  const legend = chart.getLegend()
  legend.setPosition(ExcelScript.ChartLegendPosition.right)
  legend.setOverlay(true)
  legend.setWidth(200)
  legend.setHeight(50)
  legend.setTop(10)
  const fmt = legend.getFormat()
  fmt.getBorder().setColor('blue')
  fmt.getBorder().setLineStyle(ExcelScript.ChartLineStyle.continuous)
  fmt.getFont().setSize(14)
}




リスト5-7
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const chart = sheet.getCharts()[0]
  const legend = chart.getLegend()
  legend.getLegendEntries()[0].setVisible(false)
}




リスト5-8
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const chart = sheet.getCharts()[0]
  chart.setChartType(ExcelScript.ChartType.lineMarkersStacked)
}




リスト5-9
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const chart = sheet.getCharts()[0]
  chart.setChartType(ExcelScript.ChartType.barStacked)
  chart.setPlotBy(ExcelScript.ChartPlotBy.rows)
  chart.getLegend().setOverlay(false)
}




リスト5-10
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const chart = sheet.getCharts()[0]
  const axes = chart.getAxes()
  const fmt1 = axes.getChartAxis(
      ExcelScript.ChartAxisType.category).getFormat()
  fmt1.getFont().setSize(14)
  fmt1.getFont().setBold(true)
  fmt1.getFont().setColor('blue')
  fmt1.getLine().setColor('blue')
  fmt1.getLine().setLineStyle(
      ExcelScript.ChartLineStyle.continuous)
  fmt1.getLine().setWeight(1)
  const fmt2 = axes.getValueAxis().getFormat()
  fmt2.getFont().setSize(14)
  fmt2.getFont().setBold(true)
  fmt2.getFont().setColor('red')
  fmt2.getLine().setColor('red')
  fmt2.getLine().setWeight(1)
  fmt2.getLine().setLineStyle(
      ExcelScript.ChartLineStyle.continuous)
}




リスト5-11
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const chart = sheet.getCharts()[0]
  const ax1 = chart.getAxes().getValueAxis()
  ax1.setMajorTickMark(
      ExcelScript.ChartAxisTickMark.cross)
  ax1.setMajorUnit(10000)
  ax1.setMinorTickMark(
      ExcelScript.ChartAxisTickMark.outside)
  ax1.setMinorUnit(2000)
  const ax2 = chart.getAxes().getChartAxis(
      ExcelScript.ChartAxisType.category)
  ax2.setMajorTickMark(
      ExcelScript.ChartAxisTickMark.cross)
  ax2.setMajorUnit(1)
}




リスト5-12
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const chart = sheet.getCharts()[0]
  const w = chart.getWidth()
  const area = chart.getPlotArea()
  area.setWidth(w - 200)
  area.setInsideWidth(w - 250)
  area.setLeft(25)
}




リスト5-13
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const chart = sheet.getCharts()[0]
  const srs = chart.getSeries()
  const colors = ['600000','A00000',
      'cc0000','ff0000','ff6060','ffa0a0']
  let n = 0
  srs.forEach((val)=> {
    val.setGapWidth(10)
    val.setHasDataLabels(true)
    const fmt = val.getFormat()
    fmt.getFill().setSolidColor(colors[n++])
  })
}




リスト5-14
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const chart = sheet.getCharts()[0]
  const labels = chart.getDataLabels()
  labels.setShowValue(true)
  const fmt = labels.getFormat()
  fmt.getFont().setSize(14)
  fmt.getFont().setBold(true)
  fmt.getFont().setColor('white')
  fmt.getFill().setSolidColor('606090')
}




リスト5-15
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const rect = sheet.addGeometricShape(
      ExcelScript.GeometricShapeType.rectangle)
  rect.setTop(50)
  rect.setLeft(50)
  rect.setWidth(100)
  rect.setHeight(100)


  const oval = sheet.addGeometricShape(
      ExcelScript.GeometricShapeType.ellipse)
  oval.setTop(150)
  oval.setLeft(150)
  oval.setWidth(100)
  oval.setHeight(100)
}




リスト5-16
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const range = workbook.getSelectedRange()
  const x = range.getLeft()
  const y = range.getTop()
  const w = range.getWidth()
  const h = range.getHeight()
  const oval = sheet.addGeometricShape(ExcelScript.GeometricShapeType.ellipse)
  oval.setLeft(x)
  oval.setTop(y)
  oval.setWidth(w)
  oval.setHeight(h)
}




リスト5-17
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const shapes = sheet.getShapes()
  let count = 0
  let num = 50
  shapes.forEach((val)=>{
    val.setLeft(num * count)
    val.setTop(num)
    val.setWidth(num)
    val.setHeight(num)
    count++
  })
}




リスト5-18
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const shapes = sheet.getShapes()
  let count = 0
  const c = ['aa0000','ff0000','ff9090'] //☆
  shapes.forEach((val) => {
    const fill = val.getFill()
    fill.setSolidColor(c[count++])
    fill.setTransparency(0.5)
    if (count == c.length) {
      count = 0
    }
  })
}




リスト5-19
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const shapes = sheet.getShapes()
  const n = shapes.length
  let count = 1
  shapes.forEach((val) => {
    const fill = val.getFill()
    const d = 255 / n * count
    const c = getRGB(0, d, d)
    fill.setSolidColor(c)
    fill.setTransparency(0.5)
    count++
  })
}


function getRGB(r:number, g:number, b:number):string {
  return convertTo16(r) 
    + convertTo16(g) 
    + convertTo16(b)
}


function convertTo16(n:number):string {
  let c = Math.floor(n).toString(16)
  return c.length == 1 ? '0' + c : c
}




リスト5-20
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const shapes = sheet.getShapes()
  let count = 1
  shapes.forEach((val) => {
    const fmt = val.getLineFormat()
    fmt.setWeight(count)
    const n = 255 / shapes.length * count
    fmt.setColor(getRGB(0, 0, n))
    count++
  })
}


// getRGB, convertTo16は省略。




リスト5-21
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  for(let i = 1;i <= 10;i++) {
    const line = sheet.addLine(50 * i, 100, 50 * i + 100, 200, 
        ExcelScript.ConnectorType.straight)
    const fmt = line.getLineFormat()
    fmt.setWeight(i)
    fmt.setColor(getRGB(0, 25 * i, 0))
  }
}


// getRGB, convertTo16は省略。




リスト5-22
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const box = sheet.addTextBox('This is sample text box.')
  box.setLeft(200)
  box.setTop(50)
  box.setWidth(200)
  box.setHeight(200)
  const lnfm = box.getLineFormat()
  lnfm.setWeight(2)
  lnfm.setColor('blue')
}




リスト5-23
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const shapes = sheet.getShapes()
  shapes.forEach(val => {
    try {
      const frm = val.getTextFrame()
      const rng = frm.getTextRange()
      if (frm.getHasText()) {
        rng.setText('※サンプル\nこれは、サンプルで作成したテキストボックスです。')
        rng.getFont().setSize(16)
        rng.getFont().setColor('red')
        const srng = rng.getSubstring(0, 5)
        srng.getFont().setBold(true)
        srng.getFont().setSize(20)
      }
    } catch (e) {
      console.log(e)
    }
  })
}




リスト5-24
const link = "https://picsum.photos/200" //☆アクセス先


async function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const resp = await fetch(link)
  const data = await resp.arrayBuffer()
  const image = encodeBase64(data)
  const img = sheet.addImage(image)
  img.setLeft(50)
  img.setTop(50)
  img.setWidth(200)
  img.setHeight(200)
}


function encodeBase64(data: ArrayBuffer) {
  const int_arr = new Uint8Array(data)
  const count = int_arr.length
  const s_arr = new Array<string>(count)
  for (let i = 0; i < count; i++) {
    s_arr[i] = String.fromCharCode(int_arr[i])
  }
  return btoa(s_arr.join(''));
}




リスト6-1
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const table = workbook.addTable(range, true)
  table.setName('table1')
}




リスト6-2
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const table = workbook.getTable('table1')
  table.setPredefinedTableStyle('TableStyleMedium5')
}




リスト6-3
function main(workbook: ExcelScript.Workbook) {
  const style = workbook.addTableStyle('tablestyle1',false)
  const sheet = workbook.getActiveWorksheet()
  const table = workbook.getTable('table1')
  table.setPredefinedTableStyle('tablestyle1')
}




リスト6-4
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const table = workbook.getTable('table1')
  const f1 = table.getShowBandedColumns()
  const f2 = table.getShowBandedRows()
  const f3 = table.getShowHeaders()
  const f4 = table.getShowTotals()
  table.setShowBandedColumns(!f1)
  table.setShowBandedRows(!f2)
  table.setShowHeaders(!f3)
  table.setShowTotals(!f4)
}




リスト6-5
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const table = workbook.getTable('table1')
  const n = table.getRowCount()
  table.addRow(n, ['ニューヨーク',1230,2340])
}




リスト6-6
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const table = workbook.getTable('table1')
  const n = table.getColumns().length
  table.addColumn(n, null, '合計')
  sheet.getCell(1, n).setFormula('=SUM(B2:C2)')
}




リスト6-7
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const table = workbook.getTable('table1')
  const fltr = table.getColumn('支店名').getFilter()
  fltr.apply({
    filterOn: ExcelScript.FilterOn.values,
    values:['東京','ロンドン','ニューヨーク']
  })
}




リスト6-8
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const table = workbook.getTable('table1')
  const fltr = table.getAutoFilter()
  fltr.apply(table.getRange(), 0, {
    filterOn: ExcelScript.FilterOn.values,
    values:['東京','ロンドン','ニューヨーク']
  })
}




リスト6-9
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const table = workbook.getTable('table1')
  const fltr = table.getColumn('合計').getFilter()
  fltr.apply({
    filterOn: ExcelScript.FilterOn.topPercent,
    criterion1: '50'
  })
}




リスト6-10
fltr.apply({
  filterOn: ExcelScript.FilterOn.topItems,
  criterion1: '1'
})




リスト6-10
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const table = workbook.getTable('table1')
  const fltr = table.getAutoFilter()
  fltr.apply(table.getRange(), 3, {
    filterOn: ExcelScript.FilterOn.topPercent,
    criterion1:'50'
  })
}




リスト6-11
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const table = workbook.getTable('table1')
  const fltr = table.getColumn('支店名').getFilter()
  fltr.apply({
    filterOn: ExcelScript.FilterOn.fontColor,
    color: 'ff0000'
  })
}




リスト6-12
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const table = workbook.getTable('table1')
  const fltr = table.getColumn('合計').getFilter()
  fltr.apply({
    filterOn: ExcelScript.FilterOn.icon,
    icon: { 
      index: 0,
      set: ExcelScript.IconSet.threeArrows
    }
  })
}




リスト6-13
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const table = workbook.getTable('table1')
  const fltr = table.getColumn('支店名').getFilter()
  fltr.apply({
    filterOn: ExcelScript.FilterOn.custom,
    criterion1: '=??'
  })
}




リスト6-14
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const table = workbook.getTable('table1')
  const fltr = table.getColumn('合計').getFilter()
  fltr.apply({
    filterOn: ExcelScript.FilterOn.custom,
    operator: ExcelScript.FilterOperator.and,
    criterion1: '>=10000',
    criterion2: '<=20000'
  })
}




リスト6-15
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const table = workbook.getTable('table1')
  table.getAutoFilter().clearCriteria()
}




リスト6-16
function main(workbook: ExcelScript.Workbook) {
  const range = workbook.getSelectedRange()
  const sheet = workbook.addWorksheet('Pivot')
  sheet.activate()
  let pivot1 = workbook.addPivotTable("pivot1", range, sheet.getRange("A1"));
}




リスト6-17
function main(workbook: ExcelScript.Workbook) {
  const pivot = workbook.getPivotTable("pivot1")
  pivot.addRowHierarchy(pivot.getHierarchy("期間"))
  pivot.addRowHierarchy(pivot.getHierarchy("チャンネル"))
  pivot.addColumnHierarchy(pivot.getHierarchy("支店"))
  pivot.addDataHierarchy(pivot.getHierarchy("売上"))
}




リスト6-18
function main(workbook: ExcelScript.Workbook) {
  const pivot = workbook.getPivotTable("pivot1")
  const rows = pivot.getRowHierarchies()
  const r = rows[0].getName()
  pivot.removeRowHierarchy(rows[0])
  pivot.addRowHierarchy(pivot.getHierarchy(r))
}




リスト6-19
function main(workbook: ExcelScript.Workbook) {
  const pivot = workbook.getPivotTable("pivot1")
  const rows = pivot.getRowHierarchies()
  const cols = pivot.getColumnHierarchies()
  const r = rows[0].getName()
  const c = cols[0].getName()
  pivot.addRowHierarchy(pivot.getHierarchy(c))
  pivot.addColumnHierarchy(pivot.getHierarchy(r))
}




リスト6-20
function main(workbook: ExcelScript.Workbook) {
  const pivot = workbook.getPivotTable("Pivot1")
  const d = pivot.getDataHierarchies()[0]
  d.setSummarizeBy(ExcelScript.AggregationFunction.average)
  d.setNumberFormat('#,###')
}




リスト6-21
function main(workbook: ExcelScript.Workbook) {
  const pivot = workbook.getPivotTable("Pivot1")
  const d = pivot.getDataHierarchies()[0]
  d.setSummarizeBy(ExcelScript.AggregationFunction.sum)
  const f = pivot.getDataHierarchies()[0].getField()
  d.setShowAs({
    calculation: ExcelScript.ShowAsCalculation.percentOfColumnTotal
  })
}




リスト6-22
function main(workbook: ExcelScript.Workbook) {
  const pivot = workbook.getPivotTable("Pivot1");
  const layout = pivot.getLayout()
  const f1 = layout.getSubtotalLocation()
  const f2 = layout.getShowColumnGrandTotals()
  const f3 = layout.getShowRowGrandTotals()
  if (f1 == ExcelScript.SubtotalLocationType.off) {
    layout.setSubtotalLocation(
        ExcelScript.SubtotalLocationType.atBottom)
  } else {
    layout.setSubtotalLocation(
        ExcelScript.SubtotalLocationType.off)
  }
  layout.setShowColumnGrandTotals(!f2)
  layout.setShowRowGrandTotals(!f3)
}




リスト6-23
function main(workbook: ExcelScript.Workbook) {
  const pivot = workbook.getPivotTable("Pivot1");
  const rh = pivot.getRowHierarchy('チャンネル').getFields()[0]
  rh.applyFilter({
    labelFilter: {
      condition: ExcelScript.LabelFilterCondition.contains,
      substring: 'オンライン'
    }
  })
}




リスト6-24
function main(workbook: ExcelScript.Workbook) {
  const pivot = workbook.getPivotTable("Pivot1")
  const dh = pivot.getDataHierarchies()
  const rh = pivot.getRowHierarchy('チャンネル').getFields()[0]
  rh.applyFilter({
    valueFilter: {
      condition: ExcelScript.ValueFilterCondition.greaterThan,
      comparator:20000,
      value:dh[0].getName()
    }
  })
}




リスト6-25
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const pivot = workbook.getPivotTable('pivot1')
  const slcr1 = workbook.addSlicer(pivot, '期間', 
      sheet.getName())
  slcr1.setName('期間スライサー')
  slcr1.setCaption('期間の切替')
  slcr1.setLeft(350)
  slcr1.setTop(25)
  const slcr2 = workbook.addSlicer(pivot, 'チャンネル', 
      sheet.getName())
  slcr2.setName('チャンネルスライサー')
  slcr2.setCaption('チャンネル切替')
  slcr2.setLeft(500)
  slcr2.setTop(25)
}




リスト6-26
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const slcr = workbook.getSlicer('チャンネルスライサー')
  slcr.selectItems(['オンライン'])
}




リスト6-27
function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet()
  const slcr = workbook.getSlicer('チャンネルスライサー')
  const items = slcr.getSlicerItems()
  const c = items.length
  const r = Math.floor(Math.random() * c)
  console.log(items[r].getName())
  for(let i = 0;i < c;i++) {
    if (i == r) {
      items[i].setIsSelected(true)
    } else {
      items[i].setIsSelected(false)
    }
  }
}




リスト7-1
function main(workbook: ExcelScript.Workbook)
{
  const sheet = workbook.getWorksheet('data')
  const d = new Date()
  sheet.getCell(0, 0).setValue(d.toLocaleDateString())
  sheet.getCell(1,0).setValue(d.toLocaleTimeString())
  sheet.getCell(2,0).setValue(d.toLocaleString())
}




リスト7-2
function main(workbook: ExcelScript.Workbook, row: number)
{
  const table = workbook.getTable('posttable')
  const range = table.getRange()
  const data = range.getRow(row).getValues()[0]
  const sheet = workbook.getWorksheet('data')
  sheet.getCell(4,0).setValue(data[0])
  sheet.getCell(5, 0).setValue(data[1])
  sheet.getCell(6, 0).setValue(data[2])
  sheet.getCell(4, 0).setNumberFormat('yyyy/mm/dd')
}




リスト7-3
type post = { date: string, message: string, timestamp: string }


function main(workbook: ExcelScript.Workbook, num: number): post {
  const range = workbook.getTable('posttable').getRange()
  const data = range.getRow(num).getValues()[0]
  return {
    date: String(data[0]),
    message: String(data[1]),
    timestamp: String(data[2])
  }
}




リスト7-4
type post = { date: string, message: string, timestamp: string }


function main(workbook: ExcelScript.Workbook):post[] {
  const table = workbook.getTable('posttable')
  const values = table.getRange().getValues()
  const today = getToday()
  const result:post[] = []
  values.forEach(val=> {
    if (val[0] == today) {
      const data:post = {
        date:String(val[0]), 
        message:String(val[1]), 
        timestamp:String(val[2])
      }
      result.push(data)
    }
  })
  return result
}


function getToday():string {
  var d = new Date()
  var dd = String(d.getDate()).padStart(2, '0')
  var mm = String(d.getMonth() + 1).padStart(2, '0')
  var yyyy = d.getFullYear()
  return yyyy + '-' + mm + '-' + dd //☆
}




リスト7-5
type file = {
  Id: string,
  Name: string,
  NameNoExt: string,
  DisplayName: string,
  Path: string,
  LastModified: string,
  Size: number,
  MediaType: string,
  IsFolder: string,
  ETag: string,
  FileLocator: string,
  LastModifiedBy: string
}
type fdata = [string, string, string, string, number, string]


function main(workbook: ExcelScript.Workbook, files: file[]) {
  const table = workbook.getTable('macrotable')
  files.forEach(val => {
    const f: fdata = [
      val.Name,
      val.DisplayName,
      val.MediaType,
      val.Path,
      val.Size,
      val.LastModified
    ]
    table.addRow(table.getRowCount(), f)
  })
}




リスト7-6
type RSS = {
  id: string,
  title: string,
  primaryLink: string,
  links: string[],
  updatedOn: string,
  publishDate: string,
  summary: string,
  copyright: string,
  categories: string[]
}


function main(workbook: ExcelScript.Workbook, news: RSS[]) {
  const table = workbook.getTable('newstable')
  news.forEach(val => {
    table.addRow(table.getRowCount(), [
      val.title,
      val.publishDate,
      val.summary.replace(/(<([^>]+)>)/g, ''),
      val.primaryLink
    ])
  })
}