How do I get Office Scripts to place formula down a column when data constantly changes?

[ToddRay]

The "Auto Fill Range" does not work out when new data is greater than the original data rows.

function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// Auto fit the columns of all cells on selectedSheet
selectedSheet.getRange().getFormat().autofitColumns();
// Delete range 1:4 on selectedSheet
selectedSheet.getRange("1:4").delete(ExcelScript.DeleteShiftDirection.up);
// Insert at range B:B on selectedSheet, move existing cells right
selectedSheet.getRange("B:B").insert(ExcelScript.InsertShiftDirection.right);
// Insert at range D:D on selectedSheet, move existing cells right
selectedSheet.getRange("D:D").insert(ExcelScript.InsertShiftDirection.right);
// Insert at range F:F on selectedSheet, move existing cells right
selectedSheet.getRange("F:F").insert(ExcelScript.InsertShiftDirection.right);
// Insert at range J:J on selectedSheet, move existing cells right
selectedSheet.getRange("J:J").insert(ExcelScript.InsertShiftDirection.right);
// Insert at range N:N on selectedSheet, move existing cells right
selectedSheet.getRange("N:N").insert(ExcelScript.InsertShiftDirection.right);
// Insert at range N:N on selectedSheet, move existing cells right
selectedSheet.getRange("N:N").insert(ExcelScript.InsertShiftDirection.right);
// Insert at range Q:Q on selectedSheet, move existing cells right
selectedSheet.getRange("Q:Q").insert(ExcelScript.InsertShiftDirection.right);
// Insert at range Q:Q on selectedSheet, move existing cells right
selectedSheet.getRange("Q:Q").insert(ExcelScript.InsertShiftDirection.right);
// Insert at range Q:Q on selectedSheet, move existing cells right
selectedSheet.getRange("Q:Q").insert(ExcelScript.InsertShiftDirection.right);
// Insert at range Y:Y on selectedSheet, move existing cells right
selectedSheet.getRange("Y:Y").insert(ExcelScript.InsertShiftDirection.right);
// Insert at range Y:Y on selectedSheet, move existing cells right
selectedSheet.getRange("Y:Y").insert(ExcelScript.InsertShiftDirection.right);
// Insert at range Y:Y on selectedSheet, move existing cells right
selectedSheet.getRange("Y:Y").insert(ExcelScript.InsertShiftDirection.right);
// Insert at range Y:Y on selectedSheet, move existing cells right
selectedSheet.getRange("Y:Y").insert(ExcelScript.InsertShiftDirection.right);
// Set number format for range Y:Y on selectedSheet
selectedSheet.getRange("Y:Y").setNumberFormatLocal("General");
// Set number format for range Z:Z on selectedSheet
selectedSheet.getRange("Z:Z").setNumberFormatLocal("0");
// Set number format for range AB:AB on selectedSheet
selectedSheet.getRange("AB:AB").setNumberFormatLocal("0");
// Set number format for range AA:AA on selectedSheet
selectedSheet.getRange("AA:AA").setNumberFormatLocal("m/d/yyyy;@");
// Set range B1 on selectedSheet
selectedSheet.getRange("B1").setFormulaLocal("=RIGHT(A1,LEN(A1)-FIND(\" \", A1))");
// Auto fill range
selectedSheet.getRange("B1").autoFill("B1:B5654", ExcelScript.AutoFillType.fillDefault);
// Set range D1 on selectedSheet
selectedSheet.getRange("D1").setFormulaLocal("=RIGHT(C1,LEN(C1)-21)");
// Auto fill range
selectedSheet.getRange("D1").autoFill("D1:D5654", ExcelScript.AutoFillType.fillDefault);
// Set range F1 on selectedSheet
selectedSheet.getRange("F1").setFormulaLocal("=RIGHT(E1,LEN(E1)-22)");
// Auto fill range
selectedSheet.getRange("F1").autoFill("F1:F5654", ExcelScript.AutoFillType.fillDefault);
// Set range J1 on selectedSheet
selectedSheet.getRange("J1").setFormulaLocal("=RIGHT(I1,LEN(I1)-13)");
// Auto fill range
selectedSheet.getRange("J1").autoFill("J1:J5654", ExcelScript.AutoFillType.fillDefault);
// Set range N1 on selectedSheet
selectedSheet.getRange("N1").setFormulaLocal("=MID(M1,FIND(\"(\",M1)+1, FIND(\")\",M1)-FIND(\"(\",M1)-1)");
// Auto fill range
selectedSheet.getRange("N1").autoFill("N1:N5654", ExcelScript.AutoFillType.fillDefault);
// Set range O1 on selectedSheet
selectedSheet.getRange("O1").setFormulaLocal("=IF(OR(ISNUMBER(SEARCH(\"full\",N1)),ISNUMBER(SEARCH(\"partical\",N1))),\"Active\",IF(ISERROR(N1),\"Promotional\",\"Hold\"))");
// Auto fill range
selectedSheet.getRange("O1").autoFill("O1:O5654", ExcelScript.AutoFillType.fillDefault);
// Set range Q1 on selectedSheet
selectedSheet.getRange("Q1").setFormulaLocal("=IF(P1=\"\",L1,P1)");
// Auto fill range
selectedSheet.getRange("Q1").autoFill("Q1:Q5654", ExcelScript.AutoFillType.fillDefault);
// Set range R1 on selectedSheet
selectedSheet.getRange("R1").setFormulaLocal("=CONCAT(K1,\" \",Q1,\" \",U1)");
// Auto fill range
selectedSheet.getRange("R1").autoFill("R1:R5654", ExcelScript.AutoFillType.fillDefault);
// Set range Y1 on selectedSheet
selectedSheet.getRange("Y1").setFormulaLocal("=NETWORKDAYS(W1,X1)");
// Auto fill range
selectedSheet.getRange("Y1").autoFill("Y1:Y5654", ExcelScript.AutoFillType.fillDefault);
// Set range Z1 on selectedSheet
selectedSheet.getRange("Z1").setFormulaLocal("=DATEDIF(W1,X1,\"D\")/7");
// Auto fill range
selectedSheet.getRange("Z1").autoFill("Z1:Z5654", ExcelScript.AutoFillType.fillDefault);
// Set range AA1 on selectedSheet
selectedSheet.getRange("AA1").setFormulaLocal("=TODAY()");
// Auto fill range
selectedSheet.getRange("AA1").autoFill("AA1:AA5654", ExcelScript.AutoFillType.fillDefault);
// Set range AB1 on selectedSheet
selectedSheet.getRange("AB1").setFormulaLocal("=IFS(W1>AA1,Z1,X1>AA1,(X1-AA1)/7,X1<AA1, \"Complete\")");
// Auto fill range
selectedSheet.getRange("AB1").autoFill("AB1:AB5654", ExcelScript.AutoFillType.fillDefault);
// Insert at range 1:1 on selectedSheet, move existing cells down
selectedSheet.getRange("1:1").insert(ExcelScript.InsertShiftDirection.down);
// Set range B1 on selectedSheet
selectedSheet.getRange("B1").setValue("OFFICE");
// Set range D1 on selectedSheet
selectedSheet.getRange("D1").setValue("STUDIO");
// Set range F1 on selectedSheet
selectedSheet.getRange("F1").setValue("PROJECT MANAGER");
// Set range J1:L1 on selectedSheet
selectedSheet.getRange("J1:L1").setValues([["CHARGE TYPE","PROJECT NUMBER","PROJECT NAME"]]);
// Set range O1 on selectedSheet
selectedSheet.getRange("O1").setValue("STAGE");
// Set range Q1:S1 on selectedSheet
selectedSheet.getRange("Q1:S1").setValues([["PROJECT NAME / PHASE","UNIQUE ID","ORDER"]]);
// Set range S2 on selectedSheet
selectedSheet.getRange("S2").setFormulaLocal("=ROW(A1)");
// Auto fill range
selectedSheet.getRange("S2").autoFill("S2:S5655", ExcelScript.AutoFillType.fillDefault);
// Set range T1:AB1 on selectedSheet
selectedSheet.getRange("T1:AB1").setValues([["PHASE NUMBER","TASK NAME","TASK NUMBER","START","FINISH","DURATION","NUMBER OF WEEKS","TODAY","REMAINING WEEKS"]]);
}

[#2]

@ToddRay 

That could be like

    const dataSize = selectedSheet
        .getRange("A:A")
        .getUsedRange()
        .getRowCount()

    const addresses = ["B1", "D1", "F1"]

    selectedSheet.getRange("B1").setFormulaLocal("=RIGHT(A1,LEN(A1)-FIND(\" \", A1))")
    selectedSheet.getRange("D1").setFormulaLocal("=RIGHT(C1,LEN(C1)-21)");
    selectedSheet.getRange("F1").setFormulaLocal("=RIGHT(E1,LEN(E1)-22)");

    addresses
        .map( x =>
            selectedSheet
                .getRange(x)
                .autoFill(selectedSheet
                    .getRange(x)
                    .getResizedRange(dataSize - 1, 0)
                    , ExcelScript.AutoFillType.fillDefault)
        
          )

I only didn't catch entire logic. If to run the script second time, it shifts all columns to the right and fill previous columns again from scratch. Thus result depends on how many times you run the script. Is that by design?

[#3]

Thank you, Sergei, for your assistance here. I planned to run the script once, then the next time it would be run, it would be from a new data set, "Get Data - From File." The newly imported data would always vary in total rows. This is why I needed a solution to fill the formula down the column regardless of rows but dependent on adjacent cell values. Your provided solution seems like it would work, I get one error notification on the last line, and I am unsure how to address it. Line #23: ',' expected

[#4 · Accepted solution]

@ToddRay 

I modified entire script including the part above. It runs without any errors on empty file with some values in column A

function main(workbook: ExcelScript.Workbook) {

  const selectedSheet = workbook.getActiveWorksheet()

  selectedSheet
    .getRange()
    .getFormat()
    .autofitColumns()


    // Delete range 1:4 on selectedSheet
    selectedSheet.getRange("1:4").delete(ExcelScript.DeleteShiftDirection.up);

    selectedSheet.getRange("B:B").insert(ExcelScript.InsertShiftDirection.right);
    selectedSheet.getRange("D:D").insert(ExcelScript.InsertShiftDirection.right);
    selectedSheet.getRange("F:F").insert(ExcelScript.InsertShiftDirection.right);
    selectedSheet.getRange("J:J").insert(ExcelScript.InsertShiftDirection.right);
    selectedSheet.getRange("N:N").insert(ExcelScript.InsertShiftDirection.right);
    selectedSheet.getRange("N:N").insert(ExcelScript.InsertShiftDirection.right);
    selectedSheet.getRange("Q:Q").insert(ExcelScript.InsertShiftDirection.right);
    selectedSheet.getRange("Q:Q").insert(ExcelScript.InsertShiftDirection.right);
    selectedSheet.getRange("Q:Q").insert(ExcelScript.InsertShiftDirection.right);
    selectedSheet.getRange("Y:Y").insert(ExcelScript.InsertShiftDirection.right);
    selectedSheet.getRange("Y:Y").insert(ExcelScript.InsertShiftDirection.right);
    selectedSheet.getRange("Y:Y").insert(ExcelScript.InsertShiftDirection.right);
    selectedSheet.getRange("Y:Y").insert(ExcelScript.InsertShiftDirection.right);
    
    
    selectedSheet.getRange("Y:Y").setNumberFormatLocal("General");
    selectedSheet.getRange("Z:Z").setNumberFormatLocal("0");
    selectedSheet.getRange("AB:AB").setNumberFormatLocal("0");
    selectedSheet.getRange("AA:AA").setNumberFormatLocal("m/d/yyyy;@");
    

    const dataSize = selectedSheet
        .getRange("A:A")
        .getUsedRange()
        .getRowCount()

  const addresses = ["B1", "D1", "F1", "J1", "N1", "O1", "Q1", "R1", "Y1", "Z1", "AA1", "AB1", "S1"]

    selectedSheet.getRange("B1").setFormulaLocal("=RIGHT(A1,LEN(A1)-FIND(\" \", A1))")
    selectedSheet.getRange("D1").setFormulaLocal("=RIGHT(C1,LEN(C1)-21)")
    selectedSheet.getRange("F1").setFormulaLocal("=RIGHT(E1,LEN(E1)-22)")
    selectedSheet.getRange("J1").setFormulaLocal("=RIGHT(I1,LEN(I1)-13)")
    selectedSheet.getRange("N1").setFormulaLocal("=MID(M1,FIND(\"(\",M1)+1, FIND(\")\",M1)-FIND(\"(\",M1)-1)")
    selectedSheet.getRange("O1").setFormulaLocal("=IF(OR(ISNUMBER(SEARCH(\"full\",N1)),ISNUMBER(SEARCH(\"partical\",N1))),\"Active\",IF(ISERROR(N1),\"Promotional\",\"Hold\"))")
    selectedSheet.getRange("Q1").setFormulaLocal("=IF(P1=\"\",L1,P1)")
    selectedSheet.getRange("R1").setFormulaLocal("=CONCAT(K1,\" \",Q1,\" \",U1)")
    selectedSheet.getRange("Y1").setFormulaLocal("=NETWORKDAYS(W1,X1)")
    selectedSheet.getRange("Z1").setFormulaLocal("=DATEDIF(W1,X1,\"D\")/7")
    selectedSheet.getRange("AA1").setFormulaLocal("=TODAY()")
    selectedSheet.getRange("AB1").setFormulaLocal("=IFS(W1>AA1,Z1,X1>AA1,(X1-AA1)/7,X1<AA1, \"Complete\")")
    selectedSheet.getRange("S1").setFormulaLocal("=ROW(A1)-1")

    addresses
        .map( x =>
            selectedSheet
                .getRange(x)
                .autoFill(selectedSheet
                    .getRange(x)
                    .getResizedRange(dataSize - 1, 0)
                    , ExcelScript.AutoFillType.fillDefault)
        
          )
    // Insert at range 1:1 on selectedSheet, move existing cells down
    selectedSheet.getRange("1:1").insert(ExcelScript.InsertShiftDirection.down);

    selectedSheet.getRange("B1").setValue("OFFICE");
    selectedSheet.getRange("D1").setValue("STUDIO");
    selectedSheet.getRange("F1").setValue("PROJECT MANAGER");
    selectedSheet.getRange("J1:L1").setValues([["CHARGE TYPE", "PROJECT NUMBER", "PROJECT NAME"]]);
    selectedSheet.getRange("O1").setValue("STAGE");
    selectedSheet.getRange("Q1:S1").setValues([["PROJECT NAME / PHASE", "UNIQUE ID", "ORDER"]]);
    selectedSheet
      .getRange("T1:AB1")
      .setValues(
        [["PHASE NUMBER", "TASK NAME", "TASK NUMBER", "START", "FINISH",
         "DURATION", "NUMBER OF WEEKS", "TODAY", "REMAINING WEEKS"]]);
}

If you still have an error please give more details. "Line #23: ',' expected" says nothing since it's not clear what is in line 23 and why comma is expected in it.

[#5]

Thank you, Sergei! The above-provided script ran perfectly on the initial execution. The only issue I'm now having is when I load the new data a second time through "Get Data - From File," it turns the new data into a table, and the portion of the script that deletes the first four rows will not run. Hindsight being 20/20, I really don't need to delete the first four rows. I think if you modify the script to remove that portion, it will run perfectly every time. I greatly appreciate all of your assistance here. Thank you

[#6]

@ToddRay 

Could you please clarify. You load into the sheet returned by Power Query table, after that run the script on the top. So, you don't need scenario when script could work both on raw data in range and on structured table, correct?

If so you may simply comment (and delete after the testing) this entire block as

/* 
   // Delete range 1:4 on selectedSheet
    selectedSheet.getRange("1:4").delete(ExcelScript.DeleteShiftDirection.up);

    selectedSheet.getRange("B:B").insert(ExcelScript.InsertShiftDirection.right);
    selectedSheet.getRange("D:D").insert(ExcelScript.InsertShiftDirection.right);
    selectedSheet.getRange("F:F").insert(ExcelScript.InsertShiftDirection.right);
    selectedSheet.getRange("J:J").insert(ExcelScript.InsertShiftDirection.right);
    selectedSheet.getRange("N:N").insert(ExcelScript.InsertShiftDirection.right);
    selectedSheet.getRange("N:N").insert(ExcelScript.InsertShiftDirection.right);
    selectedSheet.getRange("Q:Q").insert(ExcelScript.InsertShiftDirection.right);
    selectedSheet.getRange("Q:Q").insert(ExcelScript.InsertShiftDirection.right);
    selectedSheet.getRange("Q:Q").insert(ExcelScript.InsertShiftDirection.right);
    selectedSheet.getRange("Y:Y").insert(ExcelScript.InsertShiftDirection.right);
    selectedSheet.getRange("Y:Y").insert(ExcelScript.InsertShiftDirection.right);
    selectedSheet.getRange("Y:Y").insert(ExcelScript.InsertShiftDirection.right);
    selectedSheet.getRange("Y:Y").insert(ExcelScript.InsertShiftDirection.right);
*/

Moreover, if you work with the table it's better to rebuild entire script to work with table object.

Even more, perhaps you don't need such script at all and everything could be done with Power Query.

[#7]

Thank you again here for all your assistance and advice. The modified script you provided works perfectly. I just needed to go to "Table Tools" and "Convert to Range" upon the second data import. I'm sure there is a simpler method or approach to my process here, but this will work for now. Much appreciated!

[#8]

@ToddRay , you are welcome, great to know finally all works.