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