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.

 

I tried using a loop like this inside the if block, but not working:

 

 

for( let str in ["A2", "B2", "C2". "D2"]){...}

 

 

 

let fillRows = selectedSheet.getUsedRange().getRowCount()-2;  //-2 = subtract header and current row
	if(fillRows > 0) {
		selectedSheet.getRange("A2").getResizedRange(fillRows, 0).copyFrom("A2", ExcelScript.RangeCopyType.values);
		selectedSheet.getRange("B2").getResizedRange(fillRows, 0).copyFrom("B2", ExcelScript.RangeCopyType.values);
		selectedSheet.getRange("C2").getResizedRange(fillRows, 0).copyFrom("C2", ExcelScript.RangeCopyType.values);
		selectedSheet.getRange("D2").getResizedRange(fillRows, 0).copyFrom("D2", ExcelScript.RangeCopyType.values);
	}

 

 

 

 

excel
Office Scripts
  • SergeiBaklan's avatar
    SergeiBaklan
    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)
  }
*/
  }
}

4 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    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)
  }
*/
  }
}
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 15, 2024

    Scott4850 

    If you'd like to define cells separately that could be

    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"]

  if (fillRows > 0) {

  for (let cell of cells) {
      selectedSheet
        .getRange(cell)
        .getResizedRange(fillRows, 0)
        .copyFrom(cell, ExcelScript.RangeCopyType.values)
  }

  }
}

Resources