office-scripts

Import cell value to script Autofill

I am working on trying to automate the length of an autofill based on the value in cell C6. The cell C6 looks at row 9 and finds the last instance column where there is a value with the following formula =SUBSTITUTE(ADDRESS(1,LOOKUP(1E+307,COLUMN(9:9)*IF(9:9="","",1)),4),"1", "")

I have the following code linked to the button:

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();


//part that isnt working
Dim val as Long
val = Range("C6").Value


    // Auto fill range
        selectedSheet.getRange("D11:D26").autoFill("D11:'val'26"), ExcelScript.AutoFillType.fillDefault;
}

I would like the script to pull the value from C6 which will be a column lettering that is 3 leters, such as MXT and insert it where i have typed 'val' in the auto fill section.

I tried to use:

Dim val as Long
val = Range("C6").Value

And val as my variable in the autofill portion.

Solution

  • Get the content of cell C6 with getText() and use D11:${val}26 as autofilling range.

    function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();

  const val: string = selectedSheet.getRange("C6").getText();
  
  // Auto fill range
  selectedSheet.getRange("D11:D26").autoFill(`D11:${val}26`, ExcelScript.AutoFillType.fillDefault);
}

    Microsoft documentation:

    ExcelScript.Range getText() interface