Can a Google Spreadsheet be recalculated if it's not manually opened?

Question

We have some Google Spreadsheets that use the ImportHtml() to pull in some real messy tables. Then on on an alternate page we access the above data and pull it into a nice cleanly formatted table via spreadsheet QUERY() and other functions so that subsequently a batch process from our database can import it.

Our problem is that we need the Google Spreadsheets to refresh and populate without manually opening them because if not, the database batch import fails.

We know that there are probably more robust ways to do this but it makes it easy enough for our less tech proficient people to do because it liberates them to build these type of data tools themselves.

From what we've read, running SpreadsheetApp.flush() via a G-Apps Script might not force all the pages to recalculate.

Is there another way to easily do it?

Answer 1

If you are using the new (Q2 2014) spreadsheets you can try the Recalculation option found under [File] » [Spreadsheet Settings ...]

I believe it will work in your case. It depends what you mean by "on an alternate page". If you mean a 2nd sheet in the same spreadsheet you may be in luck. If you mean an alternate workbook, I think you lose.

In our case, we have several users with workbooks having ImportRange() calls to a master workbook, which is updated by Google Apps Script activity. The new Recalculation feature does NOT cause the remote users' workbooks to pull from the master the way a manual edit would.

Answer 2

In the [File] » [Spreadsheet Settings ...] you can chose to have recalculations done "at change + every hour". But unfortunately this is only works when the spreadsheet is open = viewed in a browser. So it will not do a recalculation for you when the file is not open.