Google AppScript large multi-line query string (BigQuery) issue

Question

I have been perplexed by this issue for a long time. I work with pretty massive multi-line bigquery sql strings. When I go to schedule them using Google AppScript, I have to spend hours trying to format them because:

• They are multi line (~ 1000 lines)

• And to use them in appscript, I have to convert them in a format like this

  var queryString = " -- MEASUREMENTS AT DAY LEVEL " queryString += "---------------------------------------------------------------------------------------------------------------------------- " queryString += "-- ** All marketing pages and whether logged in or out " queryString += "-- UNION " queryString += "-- All marketing pages irrespective of logged in or out " queryString += "---------------------------------------------------------------------------------------------------------------------------- " queryString += "\n SELECT 'day' as measurement_period,"

(This question has an example appscript that runs a SQL. That SQL has been formatted to be in one line. In my case it will be multi-line)

Any suggestions how I could possibly avoid this and: 1. Either just copy paste a code block? 2. Or use a "saved query" from my bigquery account?

Thank you!

Answer 1

It looks like the backslash is the line continuation character, so if you end each line with a backslash, that should work to make a really long queryString.

i.e.

var queryString = "SELECT * FROM Foo;\

SELECT * FROM Bar"

There's probably some limit to how long the queryString can be, but I don't know what that is.

Seems like there should be a way to use a saved query, but I have not found it.