Given a Google Sheet which has data coming from BigQuery via the Sheets data connector for BigQuery (as described here), is it possible to force a refresh of this BigQuery data via the Sheets API? It is possible to do so through the Sheets UI (has a refresh button), but I want to do it via some other service invoking an API.
0
votes
1 Answers
0
votes
You can use Apps Script to interact with the data connector in your Spreadsheet.
Considerations
From the Sheet API you are not able to manipulate the DataSource. You can use Apps Script instead. It's relatively easy to set up a script that refreshes you BigQuery data.
Approach
If you want to do this operation with an external API you will have to use Apps Script as a proxy to achieve your goal. You can deploy your Apps Script as a API Executable and trigger its functions from an external service.
Apps Script
/** @OnlyCurrentDoc */
function refresh() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.getRange('A1').activate();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Data Sheet 1'), true);
SpreadsheetApp.enableAllDataSourcesExecution();
spreadsheet.getCurrentCell().getDataSourceTables()[0].refreshData();
};
Your External Service
// [...] Oauth and other set ups...
public static void main(String[] args) throws IOException {
// ID of the script to call. Acquire this from the Apps Script editor,
// under Publish > Deploy as API executable.
String scriptId = "ENTER_YOUR_SCRIPT_ID_HERE";
Script service = getScriptService();
// Create an execution request object.
ExecutionRequest request = new ExecutionRequest()
.setFunction("refresh");
try {
// Make the API request.
Operation op =
service.scripts().run(scriptId, request).execute();
// [...] Error handling...
