Forum Discussion
auto fill in office script api
- Aug 30, 2023
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) }
SergeiBaklan I'm working on something similar, but I'm not sure what the input is for dataSize in Row 31 of the code. Everything is working fine, but how do I change "L2:L751" to fill down when the number of rows in the column change periodically?
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).