Fix an Excel Office Script Formula That Does Not Run Automatically
Question details
The user needs an Office Script to run an INDEX, SMALL, and IF formula automatically in Excel, but the recorded script contains invalid or incorrectly formatted references so the formula does not execute.
- Product
- Microsoft Excel / Office Scripts
- Device & OS
- not provided
- Scenario
- The user recorded or edited an Office Script to insert and fill an INDEX, SMALL, and IF formula, and wants it to run automatically without manual entry.
- Observed behavior
- The formula works when entered manually but fails in an Office Script because the recorder generated invalid or incorrectly formatted references.
Before editing the script, open the workbook in Excel for the web and confirm that the source ranges ($D$2:$D$10000, $E$2:$E$10000), the criteria cell ($I2), and the output range M2:M14 match the actual data layout. Also make sure you have permission to run and edit Office Scripts in the workbook.
Set the Formula Manually with Valid A1-Style References
Use this method when the recorded Office Script produces invalid or relative references, so the formula does not run automatically.
The Office Script recorder sometimes captures malformed or relative references that do not resolve when the script runs. Replacing the generated statement with an explicit setFormulaLocal call and a valid A1-style formula makes the script deterministic.
Open the workbook in Excel for the web and confirm that columns D, E, I, and M match the ranges shown in the code. For example, the source data should be in D2:D10000 and E2:E10000, the criteria in I2, and the output in M2:M14.
In Excel for the web, go to Automate > All Scripts, select the script that is not running automatically, and click Edit to open the Code Editor.
Inside the main function, keep the active worksheet reference and set the formula manually. Use: sheet.getRange("M2").setFormulaLocal("=IFERROR(INDEX($E$2:$E$10000,SMALL(IF($I2=$D$2:$D$10000,ROW($E$2:$E$10000)-1,"" ),ROW()-1)),"")");
Add the autoFill statement immediately after the formula assignment: sheet.getRange("M2").autoFill("M2:M14", ExcelScript.AutoFillType.fillDefault);
Click Save script, then Run once. Confirm that the formula is placed in M2:M14 and calculates automatically without manual entry.
Replace Office Script Dependency with WPS Office for Formula Workbooks
WPS Office is a free, lightweight Office alternative that opens your Excel workbooks, keeps standard formulas and macros familiar, and reduces reliance on the Excel for the web Office Script recorder.
- 1. Download and install WPS Office: Go to the official WPS Office download page, download the installer for Windows or macOS, and complete the installation.
- 2. Open the Excel workbook in WPS Spreadsheets: Launch WPS Office, click Spreadsheets, and open the affected .xlsx workbook. The INDEX, SMALL, and IF formula can be entered or pasted directly into M2 and filled down.
- 3. Use WPS formulas or macros instead of Office Scripts: Replace the Office Script automation with a standard worksheet formula in M2 and drag the fill handle to M14, or use WPS macro tools for more advanced automation.
Frequently Asked Questions
Why does my recorded Office Script create a formula that does not run automatically?
The recorder often generates invalid or relative references. Open the script in the Code Editor, replace the generated statement with an explicit setFormulaLocal call using valid A1-style references, and then use autoFill.
How do I fill the INDEX, SMALL, and IF formula down in Excel Office Script?
After setting the formula in M2, call sheet.getRange("M2").autoFill("M2:M14", ExcelScript.AutoFillType.fillDefault); inside the main function.
Can I use the same INDEX, SMALL, IF formula in WPS Spreadsheets?
Yes. WPS Spreadsheets supports standard Excel-style formulas and opens .xlsx workbooks, so you can enter the formula directly in M2 and fill down without an Office Script.
Do Office Scripts run in WPS Office?
No. Office Scripts are a Microsoft Excel feature for Excel for the web and Microsoft 365. WPS Office does not run Office Scripts, but it provides similar formula and macro features for workbook automation.
What should I check before running the script?
Check that the worksheet ranges match the script (D2:D10000, E2:E10000, I2, M2:M14), that the script is saved, and that you have permission to run Office Scripts.
