Tried seeing if there's a general megathread for noob questions but couldn't find one so hoping for some discussion. I built a tool for our organisation that tracks social sentiment (we're a non-profit with a lot of engagement on FB specifically) and also keeps track of our social stats. I'm pretty happy with it but have bolded the things that are definitely not optimal and would like some feedback on. The workflow currently is:
Script runs twice a day that scrapes our latest posts and newest comments
Attaches those to several googlesheets
Shiny then obviously reads these sheets every time the dashboard is read
For the social stats we connect to a separate API and again, that data is stored in several google sheets.
My main concern is that we are constantly hammering Google Sheets with loading in data, saving data etc. and I'd assume that in other places you would use a database like AWS to do this? We do a similar thing with dashboards that use Google Analytics data where it does need to be stored somewhere (as the data will eventually be deleted from GA due to GDPR) but GS isn't the most optimal location. If anyone works with scraping social data / GA data and then stores it separately so they can manipulate it for things in a shiny app, would you be able to tell me how you handle the storage issue in your organisation? thanks!