Office Scripts Architecture and Working with ExcelScript API
- Running TypeScript in Excel (Web vs. Desktop) and Security Sandbox
- Script Entry Point:
main Function and Workbook Context Object
- Manipulating Sheets, Dynamic Ranges (
Range) and Addressing Cells
- Reading and Writing Values: Working with 2D Arrays (
getValues, setValues) and Formulas
- Searching, Sorting and Applying Filters Using Code
Advanced Data Structures and Visualizations
- Working with Structured Tables (
Table, TableColumn, TableRow)
- Automating PivotTables (
PivotTable) and Configuring Fields
- Programmatically Managing Number Formatting, Styles and Conditions (Conditional Formatting)
- Creating and Dynamically Configuring Charts
- Working with Modern Comments (
Comment) and Notes (Note)
Optimizing Performance and Working with Big Data
- Office Scripts runtime limits: 120 s timeout and data transfer limits
- Batching: minimizing API calls and data processing in memory
- Using TypeScript array methods (
map, filter, reduce) for fast transformations
- Handling runtime errors (
try-catch), data validation and defensive approaches
- Diagnostics, working with the console (
console.log) and debugging limits in the Code Editor
Calling external REST APIs and web integration
- Asynchronous model,
Promise and using the native fetch() function
- Authentication when calling external endpoints (API keys, Bearer tokens) and security aspects
- Parsing, validating and typing responses in JSON format
- Transforming data from external web services (e.g. currency rates, stock market data) and writing to a workbook
- Sending calculated reports and data structures to external webhooks
Python in MS Excel and Advanced Data Analysis
- Code insertion using
=PY() and loading table data via xl() object
- Data processing and transformation using pandas library (DataFrames, Series)
- Creating analytical visualizations in cells using matplotlib and seaborn libraries
- Security isolation: explaining network blocking of external APIs in Python in Excel and combining with TypeScript
Office.js (Office Add-ins)
- Limit comparison: Office Scripts vs. Office Add-ins vs. Python in Excel
- Add-in architecture: application manifest, sidebar (Taskpane) and
Excel.run context
- Creating custom table functions (Custom Functions) for formulas in Excel
- Management, sharing on OneDrive/SharePoint and central enterprise distribution of add-ins
- Can I develop locally in VS Code?
Practical scenarios and complex examples from real practice
- Automated import and cleaning of inconsistent data from corporate REST API into a structured table
- Generation of management report: calculation of aggregations, creation of a pivot table and dynamic chart in one click
- Bulk processing of text feedback from customers using direct LLM API calls (sentiment analysis and automatic tagging)
- Advanced predictive analysis and statistical visualization of sales trends using Python (pandas + seaborn)
- Creation of a custom table function in Office.js for verification of IČO / DIČ or live currency conversion directly in the formula
- Sending approved data reports to a corporate webhook (e.g. MS Teams or internal CRM)
