auto fill in office script api

Hi, I am trying to auto fill a row of data that changes every month. right now the script is as below. I have bolded where I need help. the auto fill range is set to go to d11203 but the script will ...

excel

Formulas and Functions

Macros and VBA

Office Scripts

SergeiBaklan

Aug 30, 2023

blairwebb

If I understood correctly initially you have some data in column D. You shift it to the right, applied some formulae and formats and autofill D on the size of initial data. If so that could be

function main(workbook: ExcelScript.Workbook) {
    const sheet = workbook
      .getActiveWorksheet();

    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)
}

Forum Discussion

auto fill in office script api

Share

Resources