SOLVED

auto fill in office script api

Copper Contributor

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

}

7 Replies
best response confirmed by blairwebb (Copper Contributor)
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)
} 
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)
}


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

} 

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

@blairwebb , you are welcome, glad to help

@Sergei Baklan 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!

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

1 best response

Accepted Solutions
best response confirmed by blairwebb (Copper Contributor)
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)
} 

View solution in original post