Forum Discussion
Update Script to AddColumn and Enter Text
I am using the following script to update a sheet in an Excel workbook.
I also have the need to initiate AddColumn (Column Title: UpdateType), and add to that column - on every line where there is data, the word "Continuous".
setValue("EmployeeID");
delete(ExcelScript.DeleteShiftDirection.up);
setNumberFormatLocal("m/d/yyyy");
// Set number format for range X:X on selectedSheet
setNumberFormatLocal("m/d/yyyy");
// Set number format for range AG:AH on selectedSheet
setNumberFormatLocal("m/d/yyyy");
// Create a table with format on range A1:AP25 on selectedSheet
Continuous_Tbl.setPredefinedTableStyle("TableStyleLight8");
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") }
