Intervalos: trabalhar com a grade em Scripts do Office
Os exemplos a seguir são scripts simples para você experimentar em suas próprias pastas de trabalho. Eles formam os blocos de construção para soluções maiores. Expanda esses scripts para estender sua solução e resolver problemas comuns.
Ler e registrar uma célula
Este exemplo lê o valor de A1 e o imprime no console.
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
Esse script registra o valor da célula ativa atual. Se várias células forem selecionadas, a célula superior esquerda será registrada.
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 planilha. Os valores começam na célula A1. Os dados usados neste script são predefinidos, mas podem ser provenientes de outros locais dentro ou fora da pasta de trabalho.
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
Esse script obtém células adjacentes usando referências relativas. Observe que, se a célula ativa estiver na linha superior, parte do script falhará porque faz referência à célula acima da selecionada no momento.
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
Esse script copia a formatação na célula ativa para as células vizinhas. Observe que esse script só funciona quando a célula ativa não está em uma borda da planilha.
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 em um intervalo
Esse script faz loops sobre o intervalo selecionado no momento. Ela limpa a formatação atual e define a cor de preenchimento em cada célula como 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
Esse script obtém todas as células em branco no intervalo usado da planilha atual. Em seguida, realça todas essas 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 ser avaliada. O valor é o resultado dessa expressão.
Fórmula única
Esse script define a fórmula de uma célula e exibe 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()}`);
}
Manipular um #SPILL! erro retornado de uma fórmula
Esse script transpõe o intervalo "A1:D2" para "A4:B7" usando a função TRANSPOSE. Se o transpose resultar em um #SPILL erro, ele limpará o intervalo de destino e aplicará a fórmula novamente.
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 por seus valores de resultado
Esse script substitui todas as células da planilha atual que contém uma fórmula pelo resultado dessa fórmula. Isso significa que não haverá fórmulas depois que o script for executado, somente 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
Damos as boas-vindas às sugestões para novos exemplos. Se houver um cenário comum que ajude outros desenvolvedores de script, conte-nos na seção Comentários na parte inferior da página.