How to Automatically Record Daily Excel Values with Office Scripts
Question details
The user needs to automatically copy a daily updated value from a source table and append it to a history table at a specific time each day.
- Product
- Excel for the Web / Power Automate
- Device & OS
- not provided
- Scenario
- Automating daily data logging in an Excel workbook to track historical changes without manual data entry.
- Observed behavior
- A dynamic value needs to be captured alongside the current date and automatically inserted into a separate historical record table daily at a scheduled time.
Ensure you have access to Excel for the web with Office Scripts enabled, an active Microsoft 365 business or enterprise license, and a Power Automate account to create scheduled cloud flows.
Use Office Scripts and Power Automate to Schedule Daily Records
Combine a custom Office Script to handle the data transfer within Excel and a Power Automate flow to automatically trigger the script on a daily schedule.
Office Scripts can read and update ranges within Excel for the web. By utilizing the script to grab the current date and dynamic value from your source table, you can append a new row to a designated history table.
Since Office Scripts alone cannot run on a timer, Microsoft Power Automate is used to create a Scheduled Cloud Flow that executes the script at your required daily time (e.g., 10:30 p.m.).
Open your workbook in Excel for the web. Navigate to the 'Automate' tab on the ribbon and click 'New Script' to open the Code Editor.
Write a script that reads the target cell value from your source table, generates the current date, and utilizes the `table.addRow()` method to append this combined data as a new row in your history table. Save the script with a recognizable name.
Log into Microsoft Power Automate and click 'Create' > 'Scheduled cloud flow'. Name your flow and set the starting date, time (e.g., 10:30 PM), and set the recurrence to '1' and 'Day'.
Click 'New step', search for 'Excel Online (Business)', and select the 'Run script' action. Choose the SharePoint or OneDrive location of your file, select the specific workbook, and select the Office Script you created earlier from the dropdown.
Save your Power Automate flow and click 'Test' in the upper right corner to verify that the script executes successfully and the data is correctly appended to your history table.
Automate Your Worksheets Locally with WPS Office
If you don't have access to the enterprise Microsoft 365 accounts required for Power Automate and Office Scripts, WPS Office offers a powerful, completely free alternative. You can use advanced built-in VBA and JS Macros to automate daily data recording and streamline your spreadsheet workflows locally without complex cloud setups or expensive subscriptions.
- 1. Download and Install: Download WPS Office for free from the official website and install it on your device.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and seamlessly open your existing .xlsx or .xlsm files.
- 3. Use Local Macros: Navigate to the 'Tools' tab to access the JS Macro or VBA Editor to write local scripts for daily data logging.
Frequently Asked Questions
Can I run Office Scripts on desktop versions of Excel?
Office Scripts were originally designed for Excel for the Web, but they can now be viewed and run from the 'Automate' tab in recent builds of Excel for Windows and Mac for enterprise users. However, automated scheduling via Power Automate remains a cloud-based operation.
Why is my Power Automate flow failing to run the Office Script?
Flows often fail if the Excel file is not saved in a supported cloud location (OneDrive for Business/SharePoint), if the file is currently locked for editing by another desktop process, or if the account running the flow lacks the appropriate permissions to access the workbook.
Does WPS Office support Microsoft Office Scripts?
WPS Office does not support Microsoft's proprietary TypeScript-based Office Scripts. Instead, it provides robust support for industry-standard VBA Macros and modern JS Macros (JavaScript) for comprehensive, offline spreadsheet automation.
