Forum Discussion
Move columns with excel office script
I have a table with a number of columns, and I want to use the column names to reorganize them. Specifically moving the "Property Class" column to position 0 in the table, and the "Property Description" column to position 1. Is anyone able to help with the office script code?
You can use Office Scripts in Excel to accomplish this task. Below is a sample script that moves the "Property Class" column to position 0 and the "Property Description" column to position 1 in the active worksheet:
function main(workbook: ExcelScript.Workbook) { // Get the active worksheet let sheet = workbook.getActiveWorksheet(); // Get the table let table = sheet.getTables()[0]; // Assuming there is only one table in the worksheet // Get the column indices for "Property Class" and "Property Description" let propertyClassIndex = table.getColumnByName("Property Class").getIndex(); let propertyDescriptionIndex = table.getColumnByName("Property Description").getIndex(); // Move "Property Class" column to position 0 table.getColumn(propertyClassIndex).moveToBeginning(); // Move "Property Description" column to position 1 table.getColumn(propertyDescriptionIndex).moveAfter(table.getColumn(0)); }Make sure to save your workbook before running the script in case you need to revert any changes. Adjust the script according to your specific table structure if needed.
Add. Info.: Office Scripts samples and scenarios
The text, steps and the code were created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
- Thank you. The structure of the code is exactly what I was hoping could be accomplished. However, Excel is identifying the following problems with this script.
[13, 41] Property 'moveToBeginning' does not exist on type 'TableColumn'.
[16, 47] Property 'moveAfter' does not exist on type 'TableColumn'.
[18, 1] Cannot find name 'moveToBeginning'.Apparently there is a mistake in the code
.However, we can achieve the desired functionality by manipulating the table's columns array directly.
function main(workbook: ExcelScript.Workbook) { // Get the active worksheet let sheet = workbook.getActiveWorksheet(); // Get the table let table = sheet.getTables()[0]; // Assuming there is only one table in the worksheet // Get the column indices for "Property Class" and "Property Description" let propertyClassIndex = table.getColumnByName("Property Class").getIndex(); let propertyDescriptionIndex = table.getColumnByName("Property Description").getIndex(); // Move "Property Class" column to position 0 moveColumnToPosition(table, propertyClassIndex, 0); // Move "Property Description" column to position 1 moveColumnToPosition(table, propertyDescriptionIndex, 1); } function moveColumnToPosition(table: ExcelScript.Table, columnIndex: number, newPosition: number) { // Remove the column from its current position let column = table.getColumns()[columnIndex]; table.deleteColumn(column); // Insert the column at the new position let columns = table.getColumns(); columns.splice(newPosition, 0, column); }This script defines a helper function moveColumnToPosition() to move a column to a specified position in the table. The main() function then uses this helper function to move the "Property Class" and "Property Description" columns to their desired positions.
Please try running this corrected script, and it should move the columns as intended.
