How to run a saved Big Query script from file in G

Question

I have a long BigQuery script that I want to execute from file in Google App Script service. I can save it in BigQuery, in Google Docs, BigQuery view or anywhere which allows me to run it from App Script. The goal is to schedule its running in Google App script, manually copy pasting it like in BigQuery documentation here is not option, too much things to be sanitized and long Standard SQL code would make the JavaScript very hard reading.

How can I execute a BigQuery script from Google App Script?

Duplicate to some extent with Run a saved bigquery query from Google Apps Script?

Answer 1

You can leverage the Apps Script's Advanced Service for BigQuery. Note that the documentation is pretty sparse and with good reason. Apps Script Advanced Services are simply wrappers for their corresponding APIs.

So check out the BigQuery REST API documentation and get familiar with the data payloads passed to the various endpoints. They are analogous to resource constructors in the Apps Script Advanced Service.

EDIT

The BigQuery API has endpoints you can use to schedule jobs, but you're going to have to put some work in to sanitize the data. If you don't want to paste your queries directly in apps script, maybe you can store it as a file in google drive and load it dynamically using the Drive API.