Was wondering if there is a way to write excel office script to fill down values in excel sheet. Similar to what we achieve using 'go to special' in the legacy excel Or if we can convert below VBA to excel office script.
Sub FillDown()
For each cell in Selection
if cell = "" Then
cell.FillDown
End if
Next
End Sub
Edit
Below is a screenshot of what I want to achieve. Image of dataset
I have so far used below code to get empty cells, now i need to get value above empty cells and iterate each empty cell filling with Value above.
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).getAddress();
console.log(blankCells)
//I want to iterate on each blank cell and take the value above it into this blank celles //
}
Thank you.
From your updated example, it looks like you're trying to set formulas to the values in column B based on the row you're on when that cell is empty. I have updated the code to be more dynamic.
I don't believe that fill down is currently implemented in Office Scripts. But you can give this code a try:
function main(workbook: ExcelScript.Workbook) {
let ws: ExcelScript.Worksheet = workbook.getActiveWorksheet();
let tableName: string = "Table1";
let tableColumn: string = "Office";
let columnFormula: string = "=B";
let inputRange: ExcelScript.Range = workbook.getTable(tableName).getColumn(tableColumn).getRange();
let inputFormulas: string[][] = inputRange.getFormulas();
let newVals: string[][] = inputFormulas.map((cell,row)=>{
if (cell.toString() === "") {
return [columnFormula + (row + 1)];
} else {
return [cell.toString()];
}
})
inputRange.setFormulas(newVals);
}
This code starts by getting a range of cells from a column (Office) in a table (Table1). It then uses this range to get an array of formulas and iterates through the array. If the value of a given element in the array is an empty string, it returns the concatenated string "=B" and the sum of the row of a given cell + 1. If the value of a given array is not empty, that original value is returned. After all of the elements in the array have been iterated, it sets the values in the original range to the values in the updated array. So it's not quite filldown exactly, but it should be similar.