I'm working on creating reports for my company where I connect many different data sources together for one cohesive report. I've found Google Data Studio (GDS) is a great tool for this because I can import data from many different sources. How I'm accomplishing this is I created a rows and column report in Sugar with all the data fields I want in GDS. From there I take that excel document and I add all the data to a Google Sheet then set that sheet as a source in GDS. After this the source is in the report and I can create pretty well any chart, table I want and all is well in the world.
I have 2 questions with this though and I have no idea where to look for guidance.
1) Is there a way to schedule this report so that on the day of the schedule it will just dump the data into a Google sheet automatically? I was thinking that to do this I'd set the schedule in Sugar to just run the report so the data is refreshed. Then in Google Sheets I could use the script editor to create a script that will go to Sugar, find the report and pull in the data set. I've attempted this a little bit but haven't been able to get past the access token AND run the URL Fetch code.
After playing with this I also realized that even if I can get the data into Google Sheets I will likely have duplicate data if a lead or contact was updated recently, so now I'm not even sure if this is the right approach.
2) If I decide that creating an API isn't the right solution to updating my data for GDS and I stick with updating the data manually should I, update the entire data set each week or just attempt to find the new and modified data. If I just find the new and/or modified data what kind of logic would I use to merge any duplicates? OR am I thinking too hard on this and missing a much easier solution?
Thanks for any assistance!!