Example

The syntax for Office Scripts and Office Add-ins is different


Office Scripts

function main(workbook: ExcelScript.Workbook) { 
  let sampleSheet = setUpSample(workbook);

  sampleSheet
    .getRange("A2:C2")
    .getFormat()
    .getFill()
    .setColor("FFC000");

  sampleSheet
    .getRange("A3:C3")
    .getFormat()
    .getFill()
    .setColor("yellow");

  let newTable = workbook.addTable(sampleSheet.getRange("A1:C5"), true);

  newTable.getSort().apply([
    {key: 0, ascending: true}
  ]);
}

function setUpSample(workbook: ExcelScript.Workbook): ExcelScript.Worksheet {
  let sheet = workbook.addWorksheet();
  let range = sheet.getRange("A1:C5");
  range.setValues([
    ["Fruit", "2018", "2019"],
    ["Oranges", "1000", "1200"],
    ["Lemons", "800", "900"],
    ["Limes", "600", "500"],
    ["Grapefruits", "900", "700"]
  ]);
  range.select();
  return sheet;
}

Office Add-ins / Script Lab

$("#setup").click(() => tryCatch(main)); 

async function main() {
  await Excel.run(async (context) => {

    setUpSample();

    let workbook = context.workbook;
    workbook.load("Worksheets");
    await context.sync();

    let sampleSheet = workbook.worksheets.getItem("Sample");
    let dataRange1 = sampleSheet.getRange("A2:C2");
    dataRange1.load(["Format/Fill"]);
    dataRange1.format.fill.color = "FFC000";
    let dataRange2 = sampleSheet.getRange("A3:C3");
    dataRange2.load(["Format/Fill"]);
    dataRange2.format.fill.color = "yellow";
    let newTable = workbook.tables.add(sampleSheet.getRange("A1:C5"), true);
    newTable.load(["Sort"]);
    await context.sync();

    newTable.sort.apply([
      {key: 0, ascending: true}
    ]);
  });
}

async function setUpSample() {
  await Excel.run(async (context) => {
    let workbook = context.workbook;
    let sheet = workbook.worksheets.add("Sample");
    let range = sheet.getRange("A1:C5");
    range.values = [
      ["Fruit", "2018", "2019"],
      ["Oranges", "1000", "1200"],
      ["Lemons", "800", "900"],
      ["Limes", "600", "500"],
      ["Grapefruits", "900", "700"]
    ];
    range.select();
    await context.sync();
  });
}

async function tryCatch(callback) {
  try {
    await callback();
  } catch (error) {
    console.error(error);
  }
}


© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext