hi everyone,
In the desktop version you can use the autofill down option for the blanks rows but in the online Excel version this is not available.
Can anyone help me with an office script that auto fills column A and B, based on the values from the row above?
thanks in advance.
I found this code via internet, however i am get "0'' as result in the empty columns, can any one advise how the code should be populated?
Hmm, I'm not able to reproduce this error, but what seems to be happening is that either getAddress() is returning null, or the regex isn't able to make a match. If you're interested in debugging this, you can add some logs like so (replace line 12):
console.log(column.getName());
let address = inputRange.getAddress();
console.log(address);
let match = columnRegex.exec(address);
console.log(match);
let columnFormula = "=" + match[1];
and see which of these is failing.
hi Michelle,
this works, thank you so much!! it does give an error when it is finished:
Try this script - it should autofill all columns in the given table.
function main(workbook: ExcelScript.Workbook) {
let ws: ExcelScript.Worksheet = workbook.getActiveWorksheet();
let table = workbook.getTable("Table2");
const columnRegex = /!([A-Z]+)\d/;
table.getColumns().forEach(column => {
let inputRange: ExcelScript.Range = column.getRange();
let inputFormulas: string[][] = inputRange.getFormulas();
let startRow = inputRange.getRowIndex();
let columnFormula = "=" + columnRegex.exec(inputRange.getAddress())[1];
let newVals: string[][] = inputFormulas.map((cell, row) => {
if (row > 1 && cell.toString() === "") {
return [columnFormula + (startRow + row)];
} else {
return [cell.toString()];
}
})
inputRange.setFormulas(newVals);
})
}
Do note that because you're filling the cells with formulas that reference the above cells, every time you change - say - A2, all the values in A3:A18 will change as well. If you just wanted to copy the values in the cells once, you could use this code:
function main(workbook: ExcelScript.Workbook) {
let ws: ExcelScript.Worksheet = workbook.getActiveWorksheet();
let table = workbook.getTable("Table2");
table.getColumns().forEach(column => {
let inputRange: ExcelScript.Range = column.getRange();
let startRow = inputRange.getRowIndex();
let rowCount = inputRange.getRowCount();
for (let row = 2; row < rowCount; row++) {
let cell = inputRange.getCell(row, 0);
if (cell.getValue().toString() === "") {
cell.setValue(inputRange.getCell(row - 1, 0).getValue());
}
}
})
}
it worked now by changing the B to A, thanks!! However, I want to apply the same to column B, when I change back to B it doesn't work. Can I combine column A and B in one script?
I believe that's because the script starts the cell formulas with "=B", but the data that you want copied is in column A. Try changing the columnFormula variable to "=A". Let me know if that helps!
hi Michelle, thank you so much for looking into this, however when i try to run your modified script i am getting this result:
Hi,
Thanks for your question! I suspect the issue you're running into is that the code you're using assumes the table begins on row 1. This change to the script should allow it to work regardless of which row the table starts at:
function main(workbook: ExcelScript.Workbook) {
let ws: ExcelScript.Worksheet = workbook.getActiveWorksheet();
let tableName: string = "Table2";
let tableColumn: string = "Spec.";
let columnFormula: string = "=B";
let inputRange: ExcelScript.Range = workbook.getTable(tableName).getColumn(tableColumn).getRange();
let inputFormulas: string[][] = inputRange.getFormulas();
let startRow = inputRange.getRowIndex(); // NEW
let newVals: string[][] = inputFormulas.map((cell, row) => {
if (cell.toString() === "") {
return [columnFormula + (startRow + row)]; // MODIFIED
} else {
return [cell.toString()];
}
})
inputRange.setFormulas(newVals);
}
Hopefully that helps! Let me know if you have any questions.
Best,
Michelle
Following
Michael E. Gernaey
497
Super User 2025 Season 2
David_MA
436
Super User 2025 Season 2
Riyaz_riz11
244
Super User 2025 Season 2