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) }
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);You need first replace values in existing range, after that to modify range deleting some rows
function main(workbook: ExcelScript.Workbook) { const sheet = workbook.getActiveWorksheet() const col: ExcelScript.Range = workbook .getActiveWorksheet() .getUsedRange() .getColumn(9) let id: number[] = [] for (let i: number = 0; i < col.getRowCount(); i++) { const cell: ExcelScript.Range = col.getCell(i,0) const value = cell.getValue() value === "In Progress" || value === "Pending" ? cell.setValue("Assigned") : value === "Closed" ? cell.setValue("Resolved") : value === "Canceled" ? id.push(i) : '' } for (let i = id.length - 1; i > -1; i--) { col .getCell(id[i],0) .getEntireRow() .delete(ExcelScript.DeleteShiftDirection.up) } }