Excel Office Scripts: Add an UpdateType Column to a Table
Question details
The user needs to add an UpdateType column to an Excel table and populate it based on whether each row contains data.
- Product
- Microsoft Excel Office Scripts
- Device & OS
- not provided
- Scenario
- An Excel table named Continuous_Tbl needs a new column that contains Continuous for populated rows and remains blank for empty rows.
- Observed behavior
- The UpdateType column must be created and filled programmatically, with blank table rows excluded from the Continuous value.
Confirm that the table is named Continuous_Tbl and that the workbook is open in Excel for the web or desktop Excel with Office Scripts available. Decide which existing table columns should count as data before the script adds UpdateType.
Use an Office Script to add UpdateType and preserve blank rows
Use this approach when the table should be updated automatically and only rows containing existing data should receive Continuous.
The script reads the table data before adding the new column, checks each row for at least one nonblank value, and then writes either Continuous or an empty string to UpdateType.
In Excel, open the Automate tab and select New Script to launch the Office Scripts code editor.
Paste this script into the editor: function main(workbook: ExcelScript.Workbook) { const table = workbook.getTable("Continuous_Tbl"); const existingValues = table.getRangeBetweenHeaderAndTotal().getValues(); const updateTypeColumn = table.addColumn(ExcelScript.InsertLocation.end); updateTypeColumn.setName("UpdateType"); const updateValues = existingValues.map(row => { const hasData = row.some(value => value !== "" && value !== null); return [hasData ? "Continuous" : ""]; }); if (updateValues.length > 0) { updateTypeColumn.getRangeBetweenHeaderAndTotal().setValues(updateValues); } }
Select Run in the Office Scripts editor, approve any workbook permission prompt, and check Continuous_Tbl. The new UpdateType column should show Continuous for populated rows and remain blank for rows with no existing data.
Switch to WPS Office for Lightweight Excel Workbook Editing
WPS Office is a free, lightweight alternative for opening and editing Excel workbooks when you do not need Microsoft-specific Office Scripts automation. Its familiar spreadsheet interface supports common table, formula, and formatting workflows, while compatible file handling helps make migration straightforward.
- 1. Install WPS Office: Download and install WPS Office, then open WPS Spreadsheets from the application home screen.
- 2. Open the workbook: Select Open from the WPS Office home screen and choose the Excel .xlsx workbook containing the table.
- 3. Edit the table: Use the spreadsheet grid and table tools to add an UpdateType column and enter or fill Continuous values as needed.
Frequently Asked Questions
Where do I run an Office Script in Excel?
Open the workbook in Excel, select the Automate tab, choose New Script, paste the code into the editor, and select Run.
What table name does this script require?
The script expects a table named Continuous_Tbl. Select the table, open the Table Design tab, and check or change its name before running the script.
How does the script identify a blank row?
It checks every existing cell in the row with some(). If all values are empty strings or null, the script writes a blank value to UpdateType; otherwise, it writes Continuous.
Can the script add UpdateType if the column already exists?
No. Running it again will attempt to create another column with the same name. Remove or rename the existing UpdateType column first, or modify the script to find and reuse that column.
Does WPS Office run Excel Office Scripts?
No. Office Scripts are a Microsoft Excel automation feature. WPS Office can open and edit compatible .xlsx workbooks, but Microsoft-specific Office Scripts should be run in Excel.
