Forum Discussion
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();
// 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"]]);
}
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.
7 Replies
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?
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
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.
