auto fill in office script api

[blairwebb]

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 need to run on sheets that I do not know the end of the range so it is unusable unless I can fill the data all the way down. can someone please help me? All I've found is VBA script help and I keep getting errors when I add in code because it is different. I did this from the record actions in the automate tab. Thank you! 

function main(workbook: ExcelScript.Workbook) {

let selectedSheet = workbook.getActiveWorksheet();

// Insert at range D:D on selectedSheet, move existing cells right

selectedSheet.getRange("D:D").insert(ExcelScript.InsertShiftDirection.right);

// Set range D1:D2 on selectedSheet

selectedSheet.getRange("D1:D2").setFormulasLocal([["Duration_Minutes"],["=(E2/60)"]]);

// Set number format for range D:E on selectedSheet

selectedSheet.getRange("D:E").setNumberFormatLocal("General");

// Set number format for range D2 on selectedSheet

selectedSheet.getRange("D2").setNumberFormatLocal("0.00");

// Auto fill range

selectedSheet.getRange("D2").autoFill("D2:D11203", ExcelScript.AutoFillType.fillDefault);

}

[#2 · Accepted solution]

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

[#3]

Thank you so much that works perfectly Sergei! That works perfectly thank you so much Sergei. I also have many cells that have a zero in them in column D and I want to delete the entire row if there is a 0 in column D. I have found a VBA for this but there's much in terms of excel script to do it and I need it to be a office script and I can't do a filter and delete because then it deletes the column header which I need for the data. could you help with this? this is what I have this so far but it just returns errors and the other one I had was just deleting the entire workbook. Thank you so much for your help!

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

const dataSize = sheet
.getRange("D:D")
.getUsedRange()
.getRowCount() - 1
let selectedSheet = workbook.getActiveWorksheet();
// Toggle auto filter on selectedSheet
selectedSheet.getAutoFilter().apply(selectedSheet.getRange());
// Apply values filter on selectedSheet
selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 4, { filterOn: ExcelScript.FilterOn.values, values: ["0"] });
// Delete all cells on selectedSheet

selectedSheet
.getRange("D2")
.delete(
.getRange(dataSize - 1, 0)
, ExcelScript.Delete.up)
}

[#4]

@blairwebb 

If to remove first rows with zero (not blank or empty string) in column D and after that add durations as before, that could be

function main(workbook: ExcelScript.Workbook) {

    const sheet = workbook
      .getActiveWorksheet();

//here we remove rows with 0 in column D

  const range = sheet
    .getRange("D:D")
    .getUsedRange()

    const values = sheet
      .getRange("D:D")
      .getUsedRange()
      .getValues()

    let deleteRows: number[] = [];
    let k: number = 0;

    values.forEach( v => {
      if ( v[0] == 0) {
        deleteRows.push(k);
      }
      k++;
    })

  for (let n = deleteRows.length-1; n > -1; n--) {
    range
      .getCell(deleteRows[n], 0)
      .getEntireRow().delete(ExcelScript.DeleteShiftDirection.up);

  }

// next shift columns to the right
// and insert durations

    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)

} 

[#5]

You are truly God sent! thank you so much Sergei, I couldn't have completed this project without you. Thank you.

[#6]

@blairwebb , you are welcome, glad to help

[#7]

@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?

  // Auto fill range

  selectedSheet.getRange("L2").autoFill("L2:L751", ExcelScript.AutoFillType.fillDefault);

Thanks, in advance!

[#8]

@ejotuftsdpt 

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

[#9]

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

[#10]

@nsalahdeen 

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\"))))")

}

[#11]

@SergeiBaklan 

I sincerely appreciate this, it worked perfectly!!!.

[#12]

@nsalahdeen , you are welcome

[#13]

@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);

[#14]

@nsalahdeen 

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

 }

[#15]

@SergeiBaklan This is well noted.

Thank you once again, you are a lifesaver.

I sincerely appreciate your effort.

[#16]

@nsalahdeen , glad it helped