Google Data Studio is not fetching new data from Google Sheet, because Google Sheet is not open, and formulas will not run to update data.
Normally when opening the Google Sheet, it takes around 5 minutes to finish running all the formulas. (it's a very large file, with many formulas)
Trigger set to run every 10 minutes.
function wakupSheet(){
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
Utilities.sleep(100000);
}
As per answer from Rubén "The script will not cause the formulas recalculation as it's not doing any change to the spreadsheet".
What If I were to create a loop that changes the date in a cell 10x with a 1 minute sleep (totalling 10 minutes of the sheet being active updating the date, and giving the sheet 10 minutes to run all the formulas needed).
function wakeupSheet() {
for (i=0; i<10; r++) {
changeDate();
Utilities.sleep(60000);
}
}
function changeDate() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
var date = Utilities.formatDate(new Date(), "GMT-7", "MMMM dd, yyyy' @ 'HH:mm:ss");
var endDate = date
sheet.getRange(4, 1).setValue("The data was last refreshed on: " + endDate);
}
The script will not cause the formulas recalculation as it's not doing any change to the spreadsheet, by the other hand and more importa is that using the results of formulas from a spreadsheet that takes more than few seconds to recalculate as the data source for Google Data Studio or any other similar tool usually is a very bad idea because they don't offer way to control how much time wait for the recalculation to finish and Google Sheets doesn't include a way to be certain that there isn't a recalculation in progress or if the last recalculation has finished other than polling a "witness" (a cell that you know that will change at the end of every recalculation).
Considering the above it's very likely that your "best" option is to transfer the job done by the formulas to the script rather than using something like Utilities.sleep(100000)