0

I wrote a custom function in Apps Script which constructs a query using the function arguments and calls BigQuery service (for which I enabled using an API key) supplying the query. But when I used the function in the spreadsheet, it always returned server error.

error: We're sorry, a server error occurred. Please wait a bit and try again.

Here is my code (it works when I run it in the debugger by supplying the variables manually):

function GetAge(first_name, last_name) {
  var select_text = "SELECT first_name, last_name, age FROM Testing.FullNames WHERE ";
  var filter_text = "first_name = '" + first_name + "' AND last_name= '" + last_name + "' ";
  var group_text = "GROUP BY 1,2;";
  var query_text = select_text + filter_text + group_text;

  var query = {'query': query_text};
  var response = BigQuery.Jobs.query('<My Project Id>', query);

  var value = response.getRows()[0].getF()[2].getV();

  return value;
}
Improve this question

3 Answers 3

Reset to default
1

The v2 API is enabled? And when first attempting to run, were you prompted to complete the OAuth flow?

Note - once you get past this initial error, you'll need to change your query parameters. The query call just takes a String as the 2nd parameter:

var response = BigQuery.Jobs.query('project_id', query_text);
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I think the v2 API has been enabled, but the online documentation is still v1. I tried your syntax, but it seems that v2's BigQuery.Jobs.query takes the second argument to be a Javascript object instead of a string. The outstanding problem is still that I constantly get Server Error when try to call the function in spreadsheet.
The v2 BigQuery.Jobs.query method definitely has two required parameters: the project ID and the query text. The third parameter is an optional JS object with any additional parameters you wish to pass. I'd suggest checking the API version you've selected for your script!
Hi jcondit, I tried to use v2 API, but still no luck. I suspect there is something to do with authorization when calling from custom functions. Not sure why this problem persists.
Hey Jarod, I just wanted to let you know that I believe this is a bug. A bug that is a lot older than I thought. I have my fears that it may never be fixed if its been almost a year. I submitted a bug report that can be found here
1

The BigQuery services v2beta1 is limited to a subset of trusted testers at this time. If you instead using the v2 version (which takes a string query) it should work.

Improve this answer

3 Comments

Hi Eric, I shifted the API version to v2 and passed the query_text directly, but it still didn't work. I tried passing an object with my API key to the function as well, but no luck. I suspect the problem is due to authorization issue. It seems that when called directly from custom function, the authorization doesn't get triggered.
From reading the BigQuery docs, it looks like the query() action requires authorization, and custom functions can't use any service that requires authorization. This is normally surfaced using a better error message. If you run a query from a menu item or trigger then it should work correctly.
Thanks, Eric. This is what I suspected as well. I will try to confirm with the BigQuery team.
0

There's a tutorial about how to make calls to the BigQuery API from Google Apps Script here.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.