Constrói uma tabela de lista de notas a partir da planilha de participantes de uma chamada no Teams.

Montar Lista de Notas.EXCEL.yaml

name: Montar Lista de Notas
description: >-
  Constrói uma tabela de lista de notas a partir da planilha de participantes de
  uma chamada no Teams.
host: EXCEL
api_set: {}
script:
  content: |
    const clear = document.getElementById("clear");
    clear.addEventListener("click", async () => {
      await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getItemOrNullObject("Alunos");
        if (sheet) sheet.delete();
        context.workbook.worksheets.add("Alunos");
        await context.sync();
      });
    });

    const load = document.getElementById("load");
    load.addEventListener("click", async () => {
      await Excel.run(async (context) => {
        const sheet1 = context.workbook.worksheets.getFirst();
        const sheet2 = context.workbook.worksheets.getItemOrNullObject("Alunos");
        if (!sheet2) return;

        const range1 = sheet1.getRange("A3").getExtendedRange("Down");
        range1.load(["values", "rowCount"]);
        await context.sync();

        sheet2.getRange("A1:B1").values = [["Nome", "Nota"]];
        const range2 = sheet2.getRange(`A2:A${range1.rowCount + 1}`);
        range2.values = range1.values;
        range2.removeDuplicates([0], false);

        sheet2.activate();
        await context.sync();
      });
    });

    const format = document.getElementById("format");
    format.addEventListener("click", () => {
      Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getItemOrNullObject("Alunos");
        if (!sheet) return;

        const rangeAB = sheet.getRange("A1:B1").getExtendedRange("Down");
        rangeAB.format.autofitColumns();
        rangeAB.format.horizontalAlignment = "Left";
        rangeAB.format.verticalAlignment = "Center";
        rangeAB.load("values");
        await context.sync();

        rangeAB.values = rangeAB.values.map((row) => [toTitleCase(row[0]), row[1]]);

        const table = sheet.tables.add(rangeAB, true);
        table.name = "Alunos";
        table.style = "TableStyleMedium15";
        table.sort.apply([{ key: 0, ascending: true }]);

        const rangeB = sheet.getRange("B2").getExtendedRange("Down");
        rangeB.load("numberFormat");
        await context.sync();

        const conditional = rangeB.conditionalFormats.add("CellValue");
        conditional.cellValue.format.font.color = "Red";
        conditional.cellValue.rule = { formula1: "=6", operator: "LessThan" };
        rangeB.numberFormat = rangeB.numberFormat.map(() => ["0.0"]);
        rangeB.format.columnWidth = 70;

        sheet.freezePanes.freezeRows(1);
        await context.sync();
      });
    });

    const random = document.getElementById("random");
    random.addEventListener("click", () => {
      Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getItemOrNullObject("Alunos");
        if (!sheet) return;

        const rangeB = sheet.getRange("B2").getExtendedRange("Down");
        rangeB.load("values");
        await context.sync();

        rangeB.values = rangeB.values.map((row) => [getRandomNumber()]);
        await context.sync();
      });
    });

    function toTitleCase(value) {
      return value
        .trim()
        .toLowerCase()
        .replace(/\b[a-z]/g, (letter) => letter.toUpperCase())
        .replace(/\b(das?|de|dos?|e)\b/gi, (word) => word.toLowerCase());
    }

    function getRandomNumber(max = 10) {
      return Math.round(Math.random() * max);
    }
  language: typescript
template:
  content: |-
    <button id="clear" class="ms-Button">
      <span class="ms-Button-label">Limpar</span>
    </button>

    <button id="load" class="ms-Button">
      <span class="ms-Button-label">Carregar Alunos</span>
    </button>

    <button id="format" class="ms-Button">
      <span class="ms-Button-label">Formatar Tabela</span>
    </button>

    <button id="random" class="ms-Button">
      <span class="ms-Button-label">Sortear Notas</span>
    </button>
  language: html
style:
  content: ''
  language: css
libraries: |
  https://appsforoffice.microsoft.com/lib/1/hosted/office.js
  @types/office-js

  [email protected]/dist/css/fabric.min.css
  [email protected]/dist/css/fabric.components.min.css

  [email protected]/client/core.min.js
  @types/core-js