Automatically Back Up an Excel Workbook Connected to Microsoft Forms
Question details
The user needs a reliable strategy to regularly back up an Excel workbook that is actively receiving Microsoft Forms responses and contains custom formulas and user-interface elements.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Safeguarding critical form response data and workbook structure stored in OneDrive or SharePoint without manual intervention.
- Observed behavior
- The user requires an automated process to back up both the incoming form data and the custom workbook sheets, ensuring no data or structural loss occurs over time.
Ensure your Excel workbook is saved in OneDrive for Business or SharePoint Online, and verify that you have access to Power Automate with the necessary permissions to create cloud flows.
Create a Scheduled Power Automate Flow for Separate Backups
Set up a recurring automated workflow to copy your Excel workbook to a designated backup folder at regular intervals, providing isolated backup files.
This method creates an entirely separate copy of your workbook at a fixed interval. It is the safest way to ensure your response data and custom formulas are preserved without risking accidental overwrites in the original file.
Log into Power Automate, click 'Create' from the left menu, and select 'Scheduled cloud flow'. Name your flow and specify your desired backup frequency, such as every 12 hours.
Click 'New step' and search for 'Copy file' using the OneDrive for Business or SharePoint connector, depending on where your original file is stored.
Select your original connected Excel workbook in the 'File to copy' field. Then, choose a secure backup destination folder for the 'Destination folder' field.
In the action settings, configure what happens if a file with the same name exists. To maintain multiple backups, use a dynamic expression like 'utcNow()' in the destination file name to create unique, timestamped files.
Save your flow and click 'Test' in the upper right corner. Run a manual test to verify that the file copies successfully and that destination folder permissions are correct.
Restore from OneDrive or SharePoint Version History
Utilize the built-in version history feature for quick restoration of previous workbook states without the need to generate or manage duplicate files.
Use WPS Office for Free and Lightweight Spreadsheet Management
If you need to analyze, edit, or securely store the backup copies of your Microsoft Forms response data locally, WPS Office provides a free, lightweight, and highly compatible solution. It opens exported Excel workbooks seamlessly while maintaining your formula integrity.
- 1. Download and Install: Get WPS Office for free from the official website and install it on your device.
- 2. Open the Backup File: Launch WPS Spreadsheet and locate the .xlsx backup file generated by your Power Automate flow.
- 3. Analyze Form Responses: Use built-in pivot tables, charts, and formulas to analyze your collected form data securely offline.
Frequently Asked Questions
Will copying the workbook disconnect it from Microsoft Forms?
No. Creating a backup copy using Power Automate leaves the original workbook intact and actively connected to Microsoft Forms. The backup file simply acts as a standalone snapshot of the data at that specific time.
Can I back up the Excel workbook to a different cloud storage provider?
Yes, Power Automate supports various third-party connectors. You can configure the 'Copy file' action or use equivalent actions to save the backup file to Google Drive, Dropbox, or other supported services.
How often should I schedule the backup flow?
This depends entirely on how frequently your form receives responses. For highly active forms, every 12 hours or even every hour might be appropriate. For low-traffic forms, daily or weekly backups may suffice.
