Forum Discussion

Copper Contributor

Jul 18, 2023

Solved

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

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

Jul 18, 2023

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.

SergeiBaklan

Diamond Contributor

Jul 18, 2023

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?

ToddRay

Copper Contributor

Jul 18, 2023

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

SergeiBaklan

Diamond Contributor

Jul 18, 2023

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.

ToddRay

Copper Contributor

Jul 19, 2023

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

SergeiBaklan

Diamond Contributor

Jul 19, 2023

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.