Spreadsheet macros & scripts
Write and fix VBA, Office Scripts or Apps Script that automate workbook tasks correctly.
20 project-authored proposals · taxonomy v1.1 · 0 new benchmark runs. Verifiers and partial-credit rules below are designs, not executed results.
- Typical applications
- Excel (VBA, Office Scripts) · Google Sheets (Apps Script) · LibreOffice Basic
- Class GUI-need label
HY· Hybrid
Task proposals · 20
Expand to inspect the complete original wording and proposed checks. Original proposal text remains English in both interfaces.
R04.03.01.06-T01 Fix a VBA consolidation macro that drops rows
Original instruction
fixtures/monthly_ops.xlsm has macro ConsolidateMonths that copies 12 monthly sheets into 'Summary', but it skips each sheet's last row and stops at the first blank row. Fix it so all data rows are copied (blank rows inside data skipped, not terminating), headers once, and a 'Month' column added. Save as out/monthly_ops.xlsm.
- Platforms
win· Windowsmac· macOS- Applications
- Excel (VBA)
- Interfaces
desk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commandstype· Text & forms- Work product
WP06· Code/engineering- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
Grader runs ConsolidateMonths via COM on the saved file and on 2 hidden workbooks (different row counts, blank rows, a 13th non-month sheet to ignore); Summary must equal the expected table exactly (values + Month column).
Proposed partial credit
40% all rows copied on fixture; 20% blank rows handled; 15% Month column; 25% hidden workbooks pass
R04.03.01.06-T02 Office Script to flag and sort overdue tasks
Original instruction
In Excel for the web (test M365 tenant), write an Office Script 'FlagOverdue' saved to OneDrive that, on the active workbook's table 'Tasks', fills rows whose Due < today and Status <> 'Done' with #FFC7CE, sorts by Due ascending, and adds a total row counting overdue items. Run it on fixtures/tasks.xlsx.
- Platforms
web· Any OS (web)- Applications
- Excel (Office Scripts)
- Interfaces
brow· Browser- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
G2· GUI-essential- Interaction primitives
code· Code & commandstype· Text & formsclick· Point & click- Work product
WP06· Code/engineering- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
Grader runs the saved .osts via Power Automate 'Run script' on tasks.xlsx and 2 hidden workbooks with a frozen date: fills on exactly the overdue rows, sort order, total-row formula result; fixture workbook already processed.
Proposed partial credit
40% correct rows highlighted; 25% sorted; 20% total row count; 15% script saved and runnable
R04.03.01.06-T03 Apps Script custom function and edit-timestamp trigger
Original instruction
In the test Google Sheet 'Leave Tracker', add Apps Script: a custom function WORKDAYS_BETWEEN(start, end, holidays) that counts Mon-Fri days inclusive excluding a holiday range, and an onEdit trigger that writes the current timestamp to column D when column C of sheet 'Requests' changes (not on other sheets/columns).
- Platforms
web· Any OS (web)- Applications
- Google Sheets (Apps Script)
- Interfaces
brow· Browser- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
G2· GUI-essential- Interaction primitives
code· Code & commandstype· Text & forms- Work product
WP06· Code/engineering- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
Grader (Sheets API as owner) writes 12 test cases into cells using WORKDAYS_BETWEEN and compares results; edits C5 on Requests (D5 timestamp within 60 s), edits B5 and C5 on another sheet (no timestamp).
Proposed partial credit
50% custom function correct on all cases; 35% trigger fires on correct column only; 15% no errors in executions log
R04.03.01.06-T04 LibreOffice Basic: export every sheet to UTF-8 CSV
Original instruction
In fixtures/regions.ods, write a LibreOffice Basic macro ExportSheetsCSV in the document's Standard library that saves each sheet as <sheetname>.csv (UTF-8, comma, double-quoted text, formulas as values) next to the file, and bind it to a toolbar button labelled 'Export CSVs'. Save the document.
- Platforms
lin· Linuxwin· Windows- Applications
- LibreOffice Calc (Basic)
- Interfaces
desk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commandsclick· Point & click- Work product
WP06· Code/engineering- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
Grader runs the macro headless (soffice macro:///) on the saved file and on a hidden .ods with non-ASCII sheet names: CSV byte-compare with expected (encoding, quoting); toolbar button with that label in the document's toolbar config (Configurations2).
Proposed partial credit
55% CSV output exact; 20% hidden file with unicode names; 25% toolbar button bound
R04.03.01.06-T05 VBA UserForm for validated order entry
Original instruction
In fixtures/orders.xlsm design a UserForm 'frmOrder' with fields Date (validated dd/mm/yyyy), Customer (combo from named range Customers), Qty (integer 1-999) and Notes, plus Save and Cancel. Save appends a row to table tblOrders; invalid input shows a message and saves nothing. Add a sheet button 'New order' that opens it.
- Platforms
win· Windows- Applications
- Excel (VBA)
- Interfaces
desk· Desktop GUI- GUI need · task setting
G2· GUI-essential- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
drag· Drag & dropclick· Point & clickcode· Code & commandstype· Text & forms- Work product
WP06· Code/engineering- Conditions
- None declared
- Proposed difficulty
- 5 / 5
Proposed verifier
Grader drives the form via UI Automation: 3 valid and 4 invalid entries; tblOrders gains exactly 3 correct rows; combo items equal Customers. Trust access to the VBA project object model is disabled by policy (AccessVBOM=0), so code/forms cannot be injected via COM; vbaProject.bin provenance must be EXCEL.EXE (Sysmon).
Proposed partial credit
30% form controls and combo source; 35% valid saves exact; 25% invalid input rejected; 10% sheet button opens form
R04.03.01.06-T06 Speed up a slow VBA macro on 200k rows
Original instruction
fixtures/ledger.xlsm macro TagTransactions loops cell by cell over 200,000 rows and takes ~10 minutes. Rewrite it with array reads/writes (and screen updating/calculation off during the run, restored after) so it finishes in under 5 seconds and produces identical output.
- Platforms
win· Windows- Applications
- Excel (VBA)
- Interfaces
desk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commands- Work product
WP06· Code/engineering- Conditions
- None declared
- Proposed difficulty
- 4 / 5
Proposed verifier
Grader runs the original and new macro via COM on a hidden 200k-row copy: outputs (values and fills) identical; new runtime < 5 s on the grader VM; Application.ScreenUpdating and Calculation restored to original afterwards (also after an injected error).
Proposed partial credit
40% identical output; 40% runtime under 5 s; 20% app state restored even on error
R04.03.01.06-T07 Apps Script daily digest trigger and custom menu
Original instruction
In the test Google Sheet 'Sales Log', write Apps Script that (a) adds a menu 'Reports > Send digest now' and (b) installs a time-driven trigger running sendDigest daily at 07:00 in the sheet's timezone. sendDigest emails (via MailApp to the test inbox in fixtures/inbox.txt) yesterday's total and top 3 reps.
- Platforms
web· Any OS (web)- Applications
- Google Sheets (Apps Script) · Gmail
- Interfaces
brow· Browser- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
AP· API-sufficient- Interaction primitives
code· Code & commandsclick· Point & click- Work product
WP06· Code/engineering- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
Grader: ScriptApp triggers list (via Apps Script API as owner) has one daily trigger at hour 7 for sendDigest; menu present after reload; grader invokes sendDigest with a frozen date and the sandbox inbox receives a mail with exact total and top-3 list.
Proposed partial credit
30% trigger configured exactly; 20% menu works; 50% digest content correct
R04.03.01.06-T08 Lock shipped rows with an audited Worksheet_Change handler
Original instruction
In fixtures/fulfilment.xlsm add a Worksheet_Change handler on sheet Orders that undoes any edit to a row whose Status is 'Shipped' (except the Notes column) and logs every accepted change (timestamp, user, cell, old value, new value) to sheet Audit. Multi-cell pastes must be handled.
- Platforms
win· Windows- Applications
- Excel (VBA)
- Interfaces
desk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commands- Work product
WP06· Code/engineering- Conditions
- None declared
- Proposed difficulty
- 4 / 5
Proposed verifier
Grader scripts 10 edits through COM with events on (incl. a 3x2 paste spanning shipped and open rows): shipped cells revert, Notes edits allowed, Audit rows equal expected (old/new values exact), no recursion errors.
Proposed partial credit
40% shipped rows protected; 15% Notes exception; 30% audit log exact; 15% multi-cell paste handled
R04.03.01.06-T09 Port a VBA report macro to Office Scripts
Original instruction
fixtures/region_report.xlsm has VBA macro BuildRegionReport. Port it to an Office Script 'BuildRegionReport' (Excel for the web, test tenant) that produces the same 'Report' sheet (values, number formats, conditional formatting) from the 'Data' sheet, and save it to OneDrive.
- Platforms
web· Any OS (web)win· Windows- Applications
- Excel (Office Scripts) · Excel (VBA)
- Interfaces
brow· Browserdesk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
HY· Hybrid- Interaction primitives
code· Code & commandsmulti· Cross-app- Work product
WP06· Code/engineering- Conditions
multiapp· Multi-app- Proposed difficulty
- 4 / 5
Proposed verifier
Grader runs the VBA via COM and the Office Script via Power Automate on 3 hidden workbooks; Report sheets compared cell by cell (values, number formats) and conditional-format rules equal.
Proposed partial credit
50% values identical on all workbooks; 25% number formats; 25% conditional formatting
R04.03.01.06-T10 Make an Apps Script survive the 6-minute limit
Original instruction
The bound script in the test sheet 'Inventory Sync' processes 60,000 rows and times out. Refactor processAll to use batched getValues/setValues and resume from a checkpoint saved in PropertiesService via a chained time-based trigger, so a full run completes and cleans up its triggers. Output must match the original logic.
- Platforms
web· Any OS (web)- Applications
- Google Sheets (Apps Script)
- Interfaces
brow· Browser- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
G2· GUI-essential- Interaction primitives
code· Code & commands- Work product
WP06· Code/engineering- Conditions
- None declared
- Proposed difficulty
- 5 / 5
Proposed verifier
Grader starts processAll on a fresh 60k-row copy: no execution exceeds 6 min (executions log), the run finishes within 30 min, output column equals expected for all rows, no leftover triggers or checkpoint properties at the end.
Proposed partial credit
30% batched I/O; 35% checkpoint and resume work; 25% output exact; 10% triggers cleaned up
R04.03.01.06-T11 LibreOffice Basic macro to highlight cross-column duplicates
Original instruction
In fixtures/contacts.ods write a Basic macro MarkDupes that highlights (background #FFEB9C) every cell in column A whose value also appears in column C, comparing case-insensitively and ignoring leading/trailing spaces. Run it and save.
- Platforms
lin· Linuxwin· Windowsmac· macOS- Applications
- LibreOffice Calc (Basic)
- Interfaces
desk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commands- Work product
WP06· Code/engineering- Conditions
- None declared
- Proposed difficulty
- 2 / 5
Proposed verifier
Saved file: exactly the 14 expected A cells have background 0xFFEB9C; grader re-runs the macro headless on a hidden file and checks the highlighted set.
Proposed partial credit
60% correct cells highlighted in the fixture; 40% hidden file passes
R04.03.01.06-T12 Robust VBA CSV importer with error log
Original instruction
fixtures/importer.xlsm macro ImportFolder reads every CSV in a chosen folder into sheet Data but crashes on malformed files. Add error handling so malformed files (wrong column count, bad dates) are skipped and listed on sheet 'Import Log' with the reason, valid files are imported, and a summary MsgBox is suppressed when run with argument silent:=True.
- Platforms
win· Windowsmac· macOS- Applications
- Excel (VBA)
- Interfaces
desk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commands- Work product
WP06· Code/engineering- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
Grader runs ImportFolder silent on a hidden folder of 9 CSVs (3 malformed): Data equals the 6 valid files concatenated; Import Log lists the 3 with correct reason codes; no runtime error dialogs (COM call returns).
Proposed partial credit
40% valid files imported exactly; 35% malformed files logged with reasons; 25% silent mode works
R04.03.01.06-T13 Record a macro and put it on the Quick Access Toolbar
Original instruction
In Excel, use the macro recorder to record 'FormatReport' (bold header row, freeze top row, autofit columns, apply currency format to column E) into the Personal Macro Workbook with relative references so it works on any sheet. Then add it to the Quick Access Toolbar with the 'table' icon.
- Platforms
win· Windows- Applications
- Excel (VBA)
- Interfaces
desk· Desktop GUI- GUI need · task setting
G2· GUI-essential- GUI need · ordinary access
G2· GUI-essential- Interaction primitives
click· Point & clickkey· Shortcuts- Work product
WP06· Code/engineering- Conditions
- None declared
- Proposed difficulty
- 2 / 5
Proposed verifier
PERSONAL.XLSB contains FormatReport; grader runs it on 2 hidden sheets and checks formatting; Excel.officeUI QAT entry references PERSONAL.XLSB!FormatReport (GUI-only property: written by Excel's customization dialog). AccessVBOM=0 blocks COM code injection; Sysmon detects officeUI writes by non-Excel processes -> 0.
Proposed partial credit
50% macro works on hidden sheets; 20% stored in Personal workbook; 30% QAT button present
R04.03.01.06-T14 Apps Script sidebar that builds a regional summary
Original instruction
In the test sheet 'Field Sales', build an Apps Script HTML sidebar (menu 'Tools > Region summary') with a region dropdown populated from the Regions sheet and a 'Build' button that creates/overwrites a sheet 'Summary_<region>' with totals by product and month.
- Platforms
web· Any OS (web)- Applications
- Google Sheets (Apps Script)
- Interfaces
brow· Browser- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
G2· GUI-essential- Interaction primitives
code· Code & commandsclick· Point & click- Work product
WP06· Code/engineering- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
Grader opens the sidebar via a browser driver, picks 2 regions and clicks Build: resulting sheets equal expected pivots; server function also called directly through the Apps Script API for a third region; dropdown options equal Regions list.
Proposed partial credit
25% sidebar and menu; 15% dropdown populated; 60% summaries correct for 3 regions
R04.03.01.06-T15 VBA macro that builds a pivot table from a table
Original instruction
In fixtures/sales.xlsm write macro BuildPivot that creates (or rebuilds) a PivotTable on sheet 'Pivot' from table tblSales: rows Region then Rep, columns Quarter, values Sum of Revenue (format #,##0) and Count of OrderID, with a slicer on Channel. openpyxl cannot do this, so it must be a real PivotCache.
- Platforms
win· Windows- Applications
- Excel (VBA)
- Interfaces
desk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
G2· GUI-essential- Interaction primitives
code· Code & commands- Work product
WP06· Code/engineering- Conditions
- None declared
- Proposed difficulty
- 4 / 5
Proposed verifier
Grader runs BuildPivot twice via COM (idempotence) on the file and a hidden variant: one PivotTable whose pivotTableDefinition XML has the field layout and number format; values equal expected; one slicer bound to Channel.
Proposed partial credit
40% pivot layout exact; 30% values and formats; 15% slicer; 15% re-run idempotent
R04.03.01.06-T16 Analyze and neutralize a suspicious inherited macro
Original instruction
fixtures/vendor_invoice.xlsm came from an old vendor. Without enabling or running macros, determine what its Workbook_Open/Auto_Open code does, write ~/out/macro_report.md listing every URL, file path and shell command it uses, and save a clean macro-free copy out/vendor_invoice.xlsx with the same cell values.
- Platforms
lin· Linuxwin· Windows- Applications
- oletools (olevba) · Excel
- Interfaces
term· Terminaldesk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commandsclick· Point & click- Work product
WP01· Document- Conditions
- None declared
- Proposed difficulty
- 3 / 5
Proposed verifier
Report lists the 2 URLs, 1 dropped file path and the PowerShell command (exact strings, deobfuscated from Chr()/StrReverse); grader sandbox telemetry shows the macro never executed; xlsx has no vbaProject part and identical cell values.
Proposed partial credit
50% indicators extracted exactly; 20% macro never executed; 30% clean copy correct
R04.03.01.06-T17 Port an Excel VBA macro to native LibreOffice Basic
Original instruction
Port macro CleanAddresses from fixtures/addresses.xlsm to LibreOffice Basic using the UNO API (no 'Option VBASupport 1'), stored in fixtures/addresses.ods, so it trims, proper-cases street names, normalizes 9 US state names to codes and splits ZIP+4. Output must equal the Excel version's.
- Platforms
lin· Linuxwin· Windows- Applications
- LibreOffice Calc (Basic) · Excel (VBA)
- Interfaces
desk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commandsmulti· Cross-app- Work product
WP06· Code/engineering- Conditions
multiapp· Multi-app- Proposed difficulty
- 4 / 5
Proposed verifier
Grader runs the Excel macro (COM) and the Basic macro (headless soffice) on 2 hidden datasets: outputs identical; Basic module contains no VBASupport option and uses ThisComponent/UNO calls.
Proposed partial credit
30% trim and case; 30% state normalization; 25% ZIP split; 15% no VBA-compat mode
R04.03.01.06-T18 Generate 50 PDF invoices from a template sheet with VBA
Original instruction
In fixtures/billing.xlsm write macro MakeInvoices that, for each row of tblClients, fills sheet 'Template' (name, address, line items from tblLines by client id, totals with 8.25% tax), exports it as out/invoices/INV-<id>-<yyyymm>.pdf, and restores the template afterwards.
- Platforms
win· Windowsmac· macOS- Applications
- Excel (VBA)
- Interfaces
desk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commands- Work product
WP06· Code/engineering- Conditions
- None declared
- Proposed difficulty
- 4 / 5
Proposed verifier
Grader runs MakeInvoices via COM: 50 PDFs with exact names; pdftotext of each contains the right name, line count and total (to the cent); Template sheet equals its original after the run.
Proposed partial credit
25% 50 files named correctly; 50% invoice contents and totals correct; 25% template restored
R04.03.01.06-T19 Minimal OAuth scopes for an Apps Script project
Original instruction
The Apps Script bound to the test sheet 'Team Roster' requests full Drive and Gmail scopes but only reads this spreadsheet and sends one email. Set explicit oauthScopes in appsscript.json to the minimum needed, switch DriveApp usage to SpreadsheetApp.getActive(), and keep the 'Notify' menu working.
- Platforms
web· Any OS (web)- Applications
- Google Sheets (Apps Script)
- Interfaces
brow· Browser- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
G2· GUI-essential- Interaction primitives
code· Code & commands- Work product
WP06· Code/engineering- Conditions
- None declared
- Proposed difficulty
- 2 / 5
Proposed verifier
Manifest oauthScopes exactly {spreadsheets.currentonly, script.send_mail, script.container.ui}; no DriveApp/GmailApp references remain; grader authorizes as a test user and runs Notify: email arrives in the sandbox inbox and roster unchanged.
Proposed partial credit
45% scope set exact; 25% Drive/Gmail calls removed; 30% Notify still works
R04.03.01.06-T20 Excel add-in with a custom ribbon tab
Original instruction
Package the macros in fixtures/tools.xlsm as an add-in out/TeamTools.xlam with a custom ribbon tab 'Team' (group 'Clean', buttons 'Trim cells' and 'Fix dates' with callbacks), install the add-in in Excel, and confirm both buttons work on fixtures/messy.xlsx.
- Platforms
win· Windows- Applications
- Excel (VBA) · Office RibbonX Editor
- Interfaces
desk· Desktop GUI- GUI need · task setting
HY· Hybrid- GUI need · ordinary access
CL· CLI-sufficient- Interaction primitives
code· Code & commandsclick· Point & clickmulti· Cross-app- Work product
WP06· Code/engineering- Conditions
multiapp· Multi-app- Proposed difficulty
- 5 / 5
Proposed verifier
xlam contains customUI14.xml with tab Team, group Clean, 2 buttons whose onAction callbacks exist in the VBA project; Excel registry Add-ins entry (OPEN key) points at it; grader clicks both buttons via UI Automation on a hidden messy file and checks results.
Proposed partial credit
30% xlam with valid ribbon XML; 20% callbacks exist; 15% installed; 35% both buttons work
Mapped research references
Existing benchmark-to-class links from the research catalog. They are not record-level annotations, executions or product endorsements.