Forum Discussion
Solved
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 ...
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) }
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);
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\"))))")
}nsalahdeen , you are welcome
SergeiBaklan Sorry to disturb you again.
I tried to modify the code to filter column J value and change "In Progress" & "Pending" to "Assigned", and "Closed" to "Resolved" and any column J cell with "Canceled" should be deleted as an entire Row.
The script successfully deletes the entire rows with "Canceled" but doesn't change the other values.I also got an error that says:
Line 65: Range setValues: The argument is invalid or missing or has an incorrect format.
Please assist.
Below is my code:// Rename worksheet to "TM INC by Eng"sheet1.setName("TM INC by Eng");const usedRange = sheet.getUsedRange();const values = usedRange.getValues();for (let i = values.length - 1; i >= 0; i--) {if (values[i][9] === "Canceled") {sheet.getRange(`A${i + 1}:Z${i + 1}`).delete(ExcelScript.DeleteShiftDirection.up);} else if (values[i][9] === "In Progress") {values[i][9] = "Assigned";} else if (values[i][9] === "Closed") {values[i][9] = "Resolved";} else if (values[i][9] === "Pending") {values[i][9] = "Assigned";}}usedRange.setValues(values);