📎 This article includes 1 downloadable practice file ↓
In this article
Tables make scripts reliable: they grow with data and columns can be found by name, not position.
Used range and tables
const used = sheet.getUsedRange();
const table = sheet.addTable(used.getAddress(), true); // true = has headers
table.setName("Sales");Columns by name
const amount = table.getColumnByName("Amount");
const values = amount.getRangeBetweenHeaderAndTotal().getValues();Sort, filter, add rows
table.getSort().apply([{ key: amount.getIndex(), ascending: false }]);
table.getColumnByName("Region").getFilter().applyValuesFilter(["North"]);
table.addRow(-1, ["New customer", 5000]); // -1 = at the end💡 Check for missing tables or columns (if (!table) …) and log a clear message. Scripts run unattended from Power Automate, so silent failures are hard to diagnose.
Where people go wrong
| Problem | Fix |
|---|---|
| Script breaks when columns move | Use getColumnByName |
| addTable fails | Range already a table |
Practice
Put a small list with an Amount column on a sheet and run the script twice.
📎 Practice files for this article
- 📝Lesson 3 Office ScriptPaste into Excel on the web > Automate > New Script.⬇ TS · 710 B
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
✨ Ask AI about this article
Stuck on a step? Ask a question and the AI answers using this article.
Free · AI can be wrong