auto fill in office script api

blairwebb 

If to remove first rows with zero (not blank or empty string) in column D and after that add durations as before, that could be

function main(workbook: ExcelScript.Workbook) {

    const sheet = workbook
      .getActiveWorksheet();

//here we remove rows with 0 in column D

  const range = sheet
    .getRange("D:D")
    .getUsedRange()

    const values = sheet
      .getRange("D:D")
      .getUsedRange()
      .getValues()

    let deleteRows: number[] = [];
    let k: number = 0;

    values.forEach( v => {
      if ( v[0] == 0) {
        deleteRows.push(k);
      }
      k++;
    })

  for (let n = deleteRows.length-1; n > -1; n--) {
    range
      .getCell(deleteRows[n], 0)
      .getEntireRow().delete(ExcelScript.DeleteShiftDirection.up);

  }

// next shift columns to the right
// and insert durations

    const dataSize = sheet
      .getRange("D:D")
      .getUsedRange()
      .getRowCount() -1

    sheet
      .getRange("D:D")
      .insert(ExcelScript.InsertShiftDirection.right)

    sheet
      .getRange("D1:D2")
      .setFormulasLocal([["Duration_Minutes"], ["=(E2/60)"]])

    sheet
      .getRange("D:E")
      .setNumberFormatLocal("General")

    sheet
      .getRange("D2")
      .setNumberFormatLocal("0.00")
    
    sheet
      .getRange("D2")
      .autoFill(
          sheet
            .getRange("D2")
            .getResizedRange(dataSize - 1, 0)
          , ExcelScript.AutoFillType.fillDefault)

} 

ejotuftsdpt 

If "number of rows in the column change periodically" you need to define such range. You may use getUsedRange() before applying other steps or to use other logic. But in any case such logic is to be defined: how do I know how many rows in the column? If have an answer what is the logic is you may apply proper OfficeScript function(s).

nsalahdeen 

That could be like

function main(workbook: ExcelScript.Workbook) {

	const sheet = workbook.getActiveWorksheet()
	sheet.getRange("E:H").insert(ExcelScript.InsertShiftDirection.right)

	sheet.getRange("E1:H1")
		.setValues([["Time taken", "12 Hr Timeline", "12 Hr Status", "AHT"]])

	const format = sheet.getRange().getFormat()
		format.autofitRows()
		format.setRowHeight(21.75)
		format.autofitColumns()

	const size = sheet.getUsedRange().getRowCount()-2
	const fill = (cell: string, formula: string) => {
		sheet.getRange(cell).setFormulaLocal(formula)
		sheet.getRange(cell).autoFill(
			sheet.getRange(cell)
			.getResizedRange(size,0), ExcelScript.AutoFillType.fillDefault)
	}

	fill("E2", "=C2-B2")
	fill("F2", "= 0.5")
	fill("G2", "=IF(E2 <=F2,\"On Target\",\"Not On Target\")")
	fill("H2", "=IF(E2<0.166664,\"1.5\",IF(E2<0.333328,\"1\",IF(E2<0.5,\"0.7\",IF(E2>=0.5,\"0.5\"))))")

}