Excel Office Script to FillDown

Question

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.

https://learn.microsoft.com/en-us/javascript/api/office-scripts/excelscript/excelscript.range?view=office-scripts#flashFill__ — user16187680, Sep 10 '21 at 11:42

Brian Gonzalez · Accepted Answer · 2023-01-11T18:06:59.753

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.

Thank you @Brian Gonzalez for the code, however, the code did not fill up the blank cells. I Have edited the question with little bit more details, with screenshot maybe you might help again — George Chitechi, Sep 11 '21 at 13:43
Nice!! This worked, thanks a lot. Will just find a way to dynamically select Column [0] and [1] and map to those columns since I will be using this in a flow via power Automate so it will work on Column [0] and [1] — George Chitechi, Sep 11 '21 at 16:06
I am not familiar with PowerAutomate. So unfortunately I can't help you there. I have updated my code to be more dynamic however. So perhaps you can modify it to your needs in PowerAutomate. — Brian Gonzalez, Sep 11 '21 at 16:43
Thank you . Am ok with Power Automate. All my requirements met — George Chitechi, Sep 11 '21 at 21:58

Excel Office Script to FillDown

1 Answers1