setBQDataSource is a GappS function to build/update your Big Query connections.

setBQDataSource.js

/////////////////////////////////////////////////////////////////////////////////
//setBQDataSource is a GappS function to build/update your Big Query connections.
//Ideal if you need to update or create multiple connections, making mantainers life easier.
//Imagination, life is your creation!
/////////////////////////////////////////////////////////////////////////////////
function setBQDataSource(ProjID,Query,SheetName,refreshAllLinkedObjects=true) {
  SpreadsheetApp.enableBigQueryExecution(); //.enableAllDataSourcesExecution();
  ///////////////
  var ss = SpreadsheetApp.getActive();
  ///////////////
  var dataSourceSpec = SpreadsheetApp.newDataSourceSpec()
                                .asBigQuery()
                                .setProjectId( ProjID )
                                .setRawQuery( Query )
                                .build();
  ///////////////
  var sheet_data = ss.getSheetByName( SheetName );
  if (!sheet_data){
    Logger.log(`Sheet ${SheetName} didn't exist: Creating Sheet.`)
    ss.insertDataSourceSheet(dataSourceSpec).asSheet().setName( SheetName );
  } else {
    Logger.log(`Sheet ${SheetName} did exist: Updating DataSource.`)
    sheet_data.asDataSourceSheet().getDataSource().updateSpec(dataSourceSpec,refreshAllLinkedObjects);
  }
}