Forum Discussion
Update Script to AddColumn and Enter Text
You may continue the scripts as
function main(workbook: ExcelScript.Workbook) { // ======= let selectedSheet = workbook.getWorksheet("Continuous - New & Changes"); // Set range B1 on selectedSheet selectedSheet.getRange("B8"). setValue("EmployeeID"); // Delete range 1:7 on selectedSheet selectedSheet.getRange("1:7"). delete(ExcelScript.DeleteShiftDirection.up); // Set number format for range I:Q on selectedSheet selectedSheet.getRange("I:Q"). setNumberFormatLocal("m/d/yyyy"); // Set number format for range X:X on selectedSheet selectedSheet.getRange("X:X"). setNumberFormatLocal("m/d/yyyy"); // Set number format for range AG:AH on selectedSheet selectedSheet.getRange("AG:AH"). setNumberFormatLocal("m/d/yyyy"); // Create a table with format on range A1:AP25 on selectedSheet let Continuous_Tbl = workbook.addTable(selectedSheet.getRange("A1:AP25"), true); Continuous_Tbl.setName("Continuous_Tbl"); Continuous_Tbl.setPredefinedTableStyle("TableStyleLight8"); //======== const values = Continuous_Tbl .getRangeBetweenHeaderAndTotal() .getValues() let col: string[] = [] col.push("") for (let row of values) { let emptyRow = true; for (let cell of row) { if (cell.toString().length > 0) { emptyRow = false } } col.push(emptyRow ? "" : "Continuous" ) } Continuous_Tbl.addColumn(-1, col, "UpdateType") }
Hi Phishdawg,
To update your script to add a column and enter text, you can use the following code:
function main(workbook: ExcelScript.Workbook) {
// Get the worksheet named "Continuous - New & Changes"
let selectedSheet = workbook.getWorksheet("Continuous - New & Changes");
// Set the value "EmployeeID" in cell B8
selectedSheet.getRange("B8").setValue("EmployeeID");
// Delete rows 1 to 7 on the selected sheet
selectedSheet.getRange("1:7").delete(ExcelScript.DeleteShiftDirection.up);
// Set the date format for ranges I:Q, X:X, AG:AH on the selected sheet
selectedSheet.getRange("I:Q").setNumberFormatLocal("m/d/yyyy");
selectedSheet.getRange("X:X").setNumberFormatLocal("m/d/yyyy");
selectedSheet.getRange("AG:AH").setNumberFormatLocal("m/d/yyyy");
// Create a table with the format in the range A1:AP25 on the selected sheet
let Continuous_Tbl = workbook.addTable(selectedSheet.getRange("A1:AP25"), true);
Continuous_Tbl.setName("Continuous_Tbl");
Continuous_Tbl.setPredefinedTableStyle("TableStyleLight8");
// Add a new column titled "UpdateType" and set the value "Continuous" for every data row
let updateTypeColumn = Continuous_Tbl.addColumn();
updateTypeColumn.getNameRange().values = "UpdateType"; // Set the column title
updateTypeColumn.getDataBodyRange().setValue("Continuous"); // Set "Continuous" for all data rows
}
script starts by obtaining the worksheet named "Continuous - New & Changes" within the Excel workbook.
the it sets the value "EmployeeID" in cell B8 on the selected sheet.
Rows 1 to 7 on the selected sheet are deleted using delete(ExcelScript.DeleteShiftDirection.up).
specific ranges (I:Q, X:X, AG:AH) are formatted as dates with the "m/d/yyyy" format.
A new table is created on the selected sheet, spanning from A1 to AP25, with a predefined style.
A new column called "UpdateType" is added to the table, and the title is set in the header row.
The value "Continuous" is set in the "UpdateType" column for all data rows in the table.
Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.
If the post was useful in other ways, please consider giving it Like.
Kindest regards,
Leon Pavesic
(LinkedIn)
The following -
.getNameRange().
.getDataBodyRange().
are kicking back the following error -
Property 'getNameRange' does not exist on type 'TableColumn'. (2339)
Property 'getDataBodyRange' does not exist on type 'TableColumn'. (2339)
let updateTypeColumn = Continuous_Tbl.addColumn();
updateTypeColumn.getNameRange().values = "UpdateType";
updateTypeColumn.getDataBodyRange().setValue("Continuous");
You may continue the scripts as
function main(workbook: ExcelScript.Workbook) { // ======= let selectedSheet = workbook.getWorksheet("Continuous - New & Changes"); // Set range B1 on selectedSheet selectedSheet.getRange("B8"). setValue("EmployeeID"); // Delete range 1:7 on selectedSheet selectedSheet.getRange("1:7"). delete(ExcelScript.DeleteShiftDirection.up); // Set number format for range I:Q on selectedSheet selectedSheet.getRange("I:Q"). setNumberFormatLocal("m/d/yyyy"); // Set number format for range X:X on selectedSheet selectedSheet.getRange("X:X"). setNumberFormatLocal("m/d/yyyy"); // Set number format for range AG:AH on selectedSheet selectedSheet.getRange("AG:AH"). setNumberFormatLocal("m/d/yyyy"); // Create a table with format on range A1:AP25 on selectedSheet let Continuous_Tbl = workbook.addTable(selectedSheet.getRange("A1:AP25"), true); Continuous_Tbl.setName("Continuous_Tbl"); Continuous_Tbl.setPredefinedTableStyle("TableStyleLight8"); //======== const values = Continuous_Tbl .getRangeBetweenHeaderAndTotal() .getValues() let col: string[] = [] col.push("") for (let row of values) { let emptyRow = true; for (let cell of row) { if (cell.toString().length > 0) { emptyRow = false } } col.push(emptyRow ? "" : "Continuous" ) } Continuous_Tbl.addColumn(-1, col, "UpdateType") }- Thank you!
Not to be a pain but, as your time allows, can you provide comments to each of the commands in your edit?
It will help understand and learn.
Thank You!!We get all values (2D array) from the created table and initiate empty array of string type (1D array).
Push method adds another value to such array.
Next we iterate values array by row within initial value indicates it is empty (
let emptyRow = true;and next iterate each such row by cells. If at least one cell in the rows is not empty we change emptyRow on false. Above is based on that sample Count blank rows on sheets - Office Scripts | Microsoft Learn
Depends on row is empty or not we add another value to string array using short form of if/then/else
col.push(emptyRow ? "" : "Continuous" )Finally we add new column to the table using this ExcelScript.Table interface - Office Scripts | Microsoft Learn method with values from the generated string array.
