Forum Discussion

Scott4850's avatar
Scott4850
Copper Contributor
Apr 15, 2024
Solved

Office Script Loop using Range (cell) array?

I'm trying to write an efficient solution for the code at the bottom.  Sheets contain just column headers and at least 1 row of data.  If there are more than 1 row, then A-D columns get filled down. ...
  • SergeiBaklan's avatar
    Apr 15, 2024

    Scott4850 

    And if define cells as range

    function main(workbook: ExcelScript.Workbook) {
      const selectedSheet = workbook.getActiveWorksheet()
    
      const fillRows = selectedSheet
        .getUsedRange()
        .getRowCount() - 2  //-2 = subtract header and current row
    
      //const cells = ["A2", "B2", "C2", "D2"]
      const firstRow = selectedSheet.getRange("A2:D2")
     
      if (fillRows > 0) {
    
        for (let n in firstRow.getValues()[0]) {
          let cell = firstRow.getCell(0, +n)
          cell
            .getResizedRange(fillRows, 0)
            .copyFrom(cell, ExcelScript.RangeCopyType.values)
    
        }
    
    /*
      for (let cell of cells) {
          selectedSheet
            .getRange(cell)
            .getResizedRange(fillRows, 0)
            .copyFrom(cell, ExcelScript.RangeCopyType.values)
      }
    */
      }
    }
    

Resources