Intervalos: trabalhar com a grelha em Scripts do Office

Os exemplos seguintes são scripts simples que pode experimentar nos seus próprios livros. Formam os blocos modulares para soluções maiores. Expanda estes scripts para expandir a sua solução e resolver problemas comuns.

Ler e registar uma célula

Este exemplo lê o valor de A1 e imprime-o na consola do .

function main(workbook: ExcelScript.Workbook) {
  // Get the current worksheet.
  let selectedSheet = workbook.getActiveWorksheet();

  // Get the value of cell A1.
  let range = selectedSheet.getRange("A1");
  
  // Print the value of A1.
  console.log(range.getValue());
}

Ler a célula ativa

Este script regista o valor da célula ativa atual. Se estiverem selecionadas múltiplas células, a célula mais à esquerda será registada.

function main(workbook: ExcelScript.Workbook) {
  // Get the current active cell in the workbook.
  let cell = workbook.getActiveCell();

  // Log that cell's value.
  console.log(`The current cell's value is ${cell.getValue()}`);
}

Adicionar dados a um intervalo

Este script adiciona um conjunto de valores a uma nova folha de cálculo. Os valores começam na célula A1. Os dados utilizados neste script estão predefinidos, mas podem ser obtidos a partir de outros locais dentro ou fora do livro.

function main(workbook: ExcelScript.Workbook) {
  // The getData call could be replaced by input from Power Automate or a fetch call.
  const data = getData();

  // Create a new worksheet and switch to it.
  const newWorksheet = workbook.addWorksheet("DataSheet");
  newWorksheet.activate();

  // Get a range matching the size of the data.
  const dataRange = newWorksheet.getRangeByIndexes(
    0,
    0,
    data.length,
    data[0].length);

  // Set the data as the values in the range.
  dataRange.setValues(data);
}

function getData(): string[][] {
  return [["Abbreviation", "State/Province", "Country"],
          ["AL", "Alabama", "USA"],
          ["AK", "Alaska", "USA"],
          ["AZ", "Arizona", "USA"],
          ["AR", "Arkansas", "USA"],
          ["CA", "California", "USA"],
          ["CO", "Colorado", "USA"],
          ["CT", "Connecticut", "USA"],
          ["DE", "Delaware", "USA"],
          ["DC", "District of Columbia", "USA"],
          ["FL", "Florida", "USA"],
          ["GA", "Georgia", "USA"],
          ["HI", "Hawaii", "USA"],
          ["ID", "Idaho", "USA"],
          ["IL", "Illinois", "USA"],
          ["IN", "Indiana", "USA"],
          ["IA", "Iowa", "USA"],
          ["KS", "Kansas", "USA"],
          ["KY", "Kentucky", "USA"],
          ["LA", "Louisiana", "USA"],
          ["ME", "Maine", "USA"],
          ["MD", "Maryland", "USA"],
          ["MA", "Massachusetts", "USA"],
          ["MI", "Michigan", "USA"],
          ["MN", "Minnesota", "USA"],
          ["MS", "Mississippi", "USA"],
          ["MO", "Missouri", "USA"],
          ["MT", "Montana", "USA"],
          ["NE", "Nebraska", "USA"],
          ["NV", "Nevada", "USA"],
          ["NH", "New Hampshire", "USA"],
          ["NJ", "New Jersey", "USA"],
          ["NM", "New Mexico", "USA"],
          ["NY", "New York", "USA"],
          ["NC", "North Carolina", "USA"],
          ["ND", "North Dakota", "USA"],
          ["OH", "Ohio", "USA"],
          ["OK", "Oklahoma", "USA"],
          ["OR", "Oregon", "USA"],
          ["PA", "Pennsylvania", "USA"],
          ["RI", "Rhode Island", "USA"],
          ["SC", "South Carolina", "USA"],
          ["SD", "South Dakota", "USA"],
          ["TN", "Tennessee", "USA"],
          ["TX", "Texas", "USA"],
          ["UT", "Utah", "USA"],
          ["VT", "Vermont", "USA"],
          ["VA", "Virginia", "USA"],
          ["WA", "Washington", "USA"],
          ["WV", "West Virginia", "USA"],
          ["WI", "Wisconsin", "USA"],
          ["WY", "Wyoming", "USA"],
          ["AB", "Alberta", "CAN"],
          ["BC", "British Columbia", "CAN"],
          ["MB", "Manitoba", "CAN"],
          ["NB", "New Brunswick", "CAN"],
          ["NL", "Newfoundland and Labrador", "CAN"],
          ["NT", "Northwest Territory", "CAN"],
          ["NS", "Nova Scotia", "CAN"],
          ["NU", "Nunavut Territory", "CAN"],
          ["ON", "Ontario", "CAN"],
          ["PE", "Prince Edward Island", "CAN"],
          ["QC", "Quebec", "CAN"],
          ["SK", "Saskatchewan", "CAN"],
          ["YT", "Yukon Territory", "CAN"]];
}

Alterar uma célula adjacente

Este script obtém células adjacentes com referências relativas. Tenha em atenção que se a célula ativa estiver na linha superior, parte do script falha porque referencia a célula acima da célula atualmente selecionada.

function main(workbook: ExcelScript.Workbook) {
  // Get the currently active cell in the workbook.
  let activeCell = workbook.getActiveCell();
  console.log(`The active cell's address is: ${activeCell.getAddress()}`);

  // Get the cell to the right of the active cell and set its value and color.
  let rightCell = activeCell.getOffsetRange(0,1);
  rightCell.setValue("Right cell");
  console.log(`The right cell's address is: ${rightCell.getAddress()}`);
  rightCell.getFormat().getFont().setColor("Magenta");
  rightCell.getFormat().getFill().setColor("Cyan");

  // Get the cell to the above of the active cell and set its value and color.
  // Note that this operation will fail if the active cell is in the top row.
  let aboveCell = activeCell.getOffsetRange(-1, 0);
  aboveCell.setValue("Above cell");
  console.log(`The above cell's address is: ${aboveCell.getAddress()}`);
  aboveCell.getFormat().getFont().setColor("White");
  aboveCell.getFormat().getFill().setColor("Black");
}

Alterar todas as células adjacentes

Este script copia a formatação na célula ativa para as células vizinhas. Tenha em atenção que este script só funciona quando a célula ativa não está numa extremidade da folha de cálculo.

function main(workbook: ExcelScript.Workbook) {
  // Get the active cell.
  let activeCell = workbook.getActiveCell();

  // Get the cell that's one row above and one column to the left of the active cell.
  let cornerCell = activeCell.getOffsetRange(-1,-1);

  // Get a range that includes all the cells surrounding the active cell.
  let surroundingRange = cornerCell.getResizedRange(2, 2)

  // Copy the formatting from the active cell to the new range.
  surroundingRange.copyFrom(
    activeCell, /* The source range. */
    ExcelScript.RangeCopyType.formats /* What to copy. */
    );
}

Alterar cada célula individual num intervalo

Este script percorre o intervalo atualmente selecionado. Limpa a formatação atual e define a cor de preenchimento em cada célula para uma cor aleatória.

function main(workbook: ExcelScript.Workbook) {
  // Get the currently selected range.
  let range = workbook.getSelectedRange();

  // Get the size boundaries of the range.
  let rows = range.getRowCount();
  let cols = range.getColumnCount();

  // Clear any existing formatting.
  range.clear(ExcelScript.ClearApplyTo.formats);

  // Iterate over the range.
  for (let row = 0; row < rows; row++) {
    for (let col = 0; col < cols; col++) {
      // Generate a random hexadecimal color code.
      let colorString = `#${Math.random().toString(16).substr(-6)}`;

      // Set the color of the current cell to that random hexadecimal code.
      range.getCell(row, col).getFormat().getFill().setColor(colorString);
    }
  }
}

Obter grupos de células com base em critérios especiais

Este script obtém todas as células em branco no intervalo utilizado da folha de cálculo atual. Em seguida, realça todas as células com um fundo amarelo.

function main(workbook: ExcelScript.Workbook) {
    // Get the current used range.
    let range = workbook.getActiveWorksheet().getUsedRange();
    
    // Get all the blank cells.
    let blankCells = range.getSpecialCells(ExcelScript.SpecialCellType.blanks);

    // Highlight the blank cells with a yellow background.
    blankCells.getFormat().getFill().setColor("yellow");
}

Fórmulas

Os intervalos têm valores e fórmulas. A fórmula é a expressão a avaliar. O valor é o resultado dessa expressão.

Fórmula única

Este script define a fórmula de uma célula e, em seguida, apresenta como o Excel armazena a fórmula e o valor da célula separadamente.

function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();

  // Set A1 to 2.
  let a1 = selectedSheet.getRange("A1");
  a1.setValue(2);

  // Set B1 to the formula =(2*A1), which should equal 4.
  let b1 = selectedSheet.getRange("B1");
  b1.setFormula("=(2*A1)");

  // Log the current results for `getFormula` and `getValue` at B1.
  console.log(`B1 - Formula: ${b1.getFormula()} | Value: ${b1.getValue()}`);
}

Processar um #SPILL! erro devolvido de uma fórmula

Este script transpõe o intervalo "A1:D2" para "A4:B7" com a função TRANSPOR. Se a transpor resultar num #SPILL erro, limpa o intervalo de destino e aplica novamente a fórmula.

function main(workbook: ExcelScript.Workbook) {
  let sheet = workbook.getActiveWorksheet();
  // Use the data in A1:D2 for the sample.
  let dataAddress = "A1:D2"
  let inputRange = sheet.getRange(dataAddress);

  // Place the transposed data starting at A4.
  let targetStartCell = sheet.getRange("A4");

  // Compute the target range.
  let targetRange = targetStartCell.getResizedRange(inputRange.getColumnCount() - 1, inputRange.getRowCount() - 1);

  // Call the transpose helper function.
  targetStartCell.setFormula(`=TRANSPOSE(${dataAddress})`);

  // Check if the range update resulted in a spill error.
  let checkValue = targetStartCell.getValue() as string;
  if (checkValue === '#SPILL!') {
    // Clear the target range and call the transpose function again.
    console.log("Target range has data that is preventing update. Clearing target range.");
    targetRange.clear();
    targetStartCell.setFormula(`=TRANSPOSE(${dataAddress})`);
  }

  // Select the transposed range to highlight it.
  targetRange.select();
}

Substituir todas as fórmulas pelos respetivos valores de resultado

Este script substitui todas as células na folha de cálculo atual que contém uma fórmula pelo resultado dessa fórmula. Isto significa que não haverá fórmulas após a execução do script, apenas valores.

function main(workbook: ExcelScript.Workbook) {
    // Get the ranges with formulas.
    let sheet = workbook.getActiveWorksheet();
    let usedRange = sheet.getUsedRange();
    let formulaCells = usedRange.getSpecialCells(ExcelScript.SpecialCellType.formulas);

    // In each formula range: get the current value, clear the contents, and set the value as the old one.
    // This removes the formula but keeps the result.
    formulaCells.getAreas().forEach((range) => {
      let currentValues = range.getValues();
      range.clear(ExcelScript.ClearApplyTo.contents);
      range.setValues(currentValues);
    });
}

Sugerir novos exemplos

Agradecemos as sugestões de novos exemplos. Se existir um cenário comum que ajude outros programadores de scripts, informe-nos na secção Feedback na parte inferior da página.

Confira também

• "Noções básicas de Range" de Sudhi Ramamurthy no YouTube
• Exemplos de gráficos
• Exemplos e cenários de Scripts do Office
• Tutorial: Criar e formatar uma tabela do Excel