I'm looking for a way to set up an Excel spreadsheet in SharePoint that updates its data from NetSuite every 30 minutes without needing to open the file since it's quite large. I've explored options like Power Automate and Power Query, but haven't found a solution that fits this specific use case. Any advice or pointers would be greatly appreciated!
4 Answers
I’m not sure how this works with SharePoint, but I have a local setup where I schedule a `.bat` file to open the spreadsheet. It runs a VBA script that refreshes the data, saves the updated file automatically, and only does so when triggered by a specific flag. You might consider that approach if you can adapt it for your SharePoint environment.
You might be able to leverage APIs from your third-party service to pull the data into Power Automate, parse it as JSON, and directly update your Excel sheet. This way, the data could refresh on its own without much hassle, depending on how large your dataset is.
Honestly, it sounds like you should think about moving away from Excel and into a more robust BI system. It'll save you headaches down the line!
You might want to check out Power Automate workflows; they can help with automating this kind of task. However, if you're able, I recommend considering a different approach for managing large datasets. It might be time to upgrade to a proper BI solution like Power BI instead of relying on Excel for heavy lifting. But if Excel is your only option, good luck with it!
Thanks for your input! I suggested looking into VBA, but they want to keep everything in SharePoint.