So basically we have 50 employees who use google sheets to update daily data. Like how many customers were seen, the why, and where. This data goes through a process from checking the data, posting it and then sending to be billed and finally billing it. All of this is done in google sheets by updating designated column. Multiple employees come to this sheet, do their job and leave it for the next employee.

On the other hand I use PowerBI to build weekly dashboards. First I started to copy paste data from 50 sheets, transform in excel, clean, and load in powerBi and build my report. This turned nightmare when my manager asked for an update of the same report ( coz not all customers are billed the same day/week and we want to see how many where billed vs not billed and why there were nt billed or whre in the process of billing are we for that particular customer). I had to copy paste values again from google sheet, clean and transform and then send report again. Now imagine having to do this every week. Workload only builds up.

I started connecting google sheets directly to powerbI and refresh it before sending an update and to my surprise all the data was updated and accurate. But I still get overload error which has to do with api overload I believe. Hence, looking for another system to do most of the cleaning and transformation and storing.

Another problem Im trying to solve is if my manager asks for report on quarterly basis, I need to spend a week gathering data and this cannot be done linking google sheets to powerBi coz im sure powerbi is simply gonna shut down which all those spreadsheets and individual sheets inside those soreadsheets

We do not have a database system. And sometimes powerbi throws overload error when I have so many sheets connected.

Im expecting a system that lies between google sheet and powerBI. This system should should be connected to google sheets, and constanly be refreshing data (like streaming data or batch processing atleast 1 refresh every hour) so that we can have a clear number of how many customers were billed in the start of the day vs end. And while this system should transform, clean and store data in a way I would do in powerBI. And then I want powerBI to link to this system to do my analytics without spending much time in transforming while also be able to do quaterly analysis

Ihv asked this question a few times here but because I have limited knowledge of databases, im confused. Ihv been researching alot abt big query, azure and snowflake. But still nt sure which one is perfect for this case.