kinuasa

//ワークシート関数実行
async function main(context: Excel.RequestContext) {
  let sheet = context.workbook.worksheets.getActiveWorksheet();
  let range = sheet.getRange("A1:A4");
  range.values = [[10], [20], [30], [40]];
  let ave = context.workbook.functions.average(range); //AVERAGE関数実行
  ave.load("value");
  await context.sync();
  console.log(ave.value); //2020.05時点ではエラー
}

kinuasa

//Power Automateパラメーターテスト(2)
async function main(
  context: Excel.RequestContext,
  sender: string
) {
  let sheet = context.workbook.worksheets.getFirst();
  sheet.getRange("A1").values = [[sender]];
  return `こんにちは、${sender}さん！`;
}

kinuasa

//A列の最後のセルを選択
function main(workbook: ExcelScript.Workbook) {
  let sheet = workbook.getActiveWorksheet();
  let lastRange = sheet.getRange("A:A").getLastCell();
  lastRange.select();
}

kinuasa

//UsedRange範囲内でA列最後のセルを選択
function main(workbook: ExcelScript.Workbook) {
  let sheet = workbook.getActiveWorksheet();
  let usedRange = sheet.getUsedRange();
  let intRange = usedRange.getIntersection("A:A");
  let lastRange = intRange.getLastCell();
  lastRange.select();
}

kinuasa

//A列最後のデータが入ったセルを選択
function main(workbook: ExcelScript.Workbook) {
  let sheet = workbook.getActiveWorksheet();
  let usedRange = sheet.getUsedRange();
  let intRange = usedRange.getIntersection("A:A");
  let blankRangeAreas = intRange.getSpecialCells(ExcelScript.SpecialCellType.blanks);
  let blankRange = blankRangeAreas.getAreas()[blankRangeAreas.getAreaCount() - 1];
  let lastRange = blankRange.getRowsAbove(1);
  lastRange.select();
}

kinuasa

//シート上のすべての図形を回転する
function main(workbook: ExcelScript.Workbook) {
  let sheet = workbook.getActiveWorksheet();
  sheet.getShapes().forEach((shape) => {
    shape.incrementRotation(60); //60度ずつ回転
    //shape.setRotation(60); //絶対的な回転角度を指定することも可能
  });
}

kinuasa

//3列目を和暦に変換
function main(workbook: ExcelScript.Workbook) {
  let sheet = workbook.getFirstWorksheet();
  sheet.getRange("C:C").setNumberFormatLocal("ggge年m月d日");
}

kinuasa

//Power Automateから実行
function main(workbook: ExcelScript.Workbook) {
  let sheet = workbook.getWorksheet("Sheet1");
  if (sheet !== void 0) {
    sheet.getRange("A1").setValue("Power AutomateからOffice Scriptsを実行してみました。");
  }
}

kinuasa

//グラフの追加とタイトル等設定
function main(workbook: ExcelScript.Workbook) {
  let sheet = workbook.getActiveWorksheet();

  //グラフの元データ作成
  let range = sheet.getRange("A1:B5");
  range.setValues([
    ["商品", "価格"],
    ["リンゴ", "  100"],
    ["バナナ", "200"],

kinuasa

//テーブルのn列目・n行目のみ処理する
function main(workbook: ExcelScript.Workbook) {
  let sheet = workbook.getActiveWorksheet();
  let table = sheet.getTable("tblSample");
  table.getColumn(1).getRange().setNumberFormatLocal("ggge年m月d日(aaa)"); //テーブルの1列目の書式設定
  table.getRangeBetweenHeaderAndTotal().getRow(9).getFormat().getFont().setBold(true); //テーブルの10行目を太字設定
}