Forum Discussion
blairwebb
Aug 30, 2023Copper Contributor
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 ...
- 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
MVP
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)
}
nsalahdeen
Sep 26, 2024Copper Contributor
Hello SergeiBaklan
Thank you so much for your input.
I also have a similar issue. I am working on an incident report, and the number of incidents changes monthly.
Below is what I want to achieve:
I inserted 4 new columns between columns D and I: column E: Time taken, column F: 12 Hr Timeline, column G: 12 Hr Status, and column H: AHT.
I did some formulas that are working but my challenge is autofill for the 4 columns in case I have an incident number of more than 106 columns.
Please I need to autofill columns E, F, G, and H.
This is my current script but stops at 106.
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Insert at range E:E on selectedSheet, move existing cells right
selectedSheet.getRange("E:E").insert(ExcelScript.InsertShiftDirection.right);
// Insert at range E:E on selectedSheet, move existing cells right
selectedSheet.getRange("E:E").insert(ExcelScript.InsertShiftDirection.right);
// Insert at range E:E on selectedSheet, move existing cells right
selectedSheet.getRange("E:E").insert(ExcelScript.InsertShiftDirection.right);
// Insert at range E:E on selectedSheet, move existing cells right
selectedSheet.getRange("E:E").insert(ExcelScript.InsertShiftDirection.right);
// Set range E1:H1 on selectedSheet
selectedSheet.getRange("E1:H1").setValues([["Time taken","12 Hr Timeline","12 Hr Status","AHT"]]);
// Auto fit the rows of all cells on selectedSheet
selectedSheet.getRange().getFormat().autofitRows();
// Set height of row(s) at all cells on selectedSheet to 21.75
selectedSheet.getRange().getFormat().setRowHeight(21.75);
// Auto fit the columns of all cells on selectedSheet
selectedSheet.getRange().getFormat().autofitColumns();
// Set range E2 on selectedSheet
selectedSheet.getRange("E2").setFormulaLocal("=C2-B2");
// Auto fill range
selectedSheet.getRange("E2").autoFill("E2:E106", ExcelScript.AutoFillType.fillDefault);
// Set range F2 on selectedSheet
selectedSheet.getRange("F2").setFormulaLocal("=0.5");
// Auto fill range
selectedSheet.getRange("F2").autoFill("F2:F106", ExcelScript.AutoFillType.fillDefault);
// Set range G2 on selectedSheet
selectedSheet.getRange("G2").setFormulaLocal("=IF(E2 <=F2,\"On Target\",\"Not On Target\")");
// Auto fill range
selectedSheet.getRange("G2").autoFill("G2:G106", ExcelScript.AutoFillType.fillDefault);
// Set range H2 on selectedSheet
selectedSheet.getRange("H2").setFormulaLocal("=IF(E2<0.166664,\"1.5\",IF(E2<0.333328,\"1\",IF(E2<0.5,\"0.7\",IF(E2>=0.5,\"0.5\"))))");
// Auto fill range
selectedSheet.getRange("H2").autoFill("H2:H106", ExcelScript.AutoFillType.fillDefault);
// Auto fill range
selectedSheet.getRange("J2").autoFill("J2:J103", ExcelScript.AutoFillType.fillDefault);
- SergeiBaklanSep 26, 2024MVP
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\"))))") }
- nsalahdeenSep 26, 2024Copper Contributor
- SergeiBaklanSep 26, 2024MVP
nsalahdeen , you are welcome