Login Required error accessing BigQuery from Google Apps Script in a Sheet

Question

I have created a very simple Google Apps Script taken from the documentation here:

https://developers.google.com/apps-script/advanced/bigquery

I modified that script to use my own project id and to query a table in BigQuery in that project. When running the script from the script editor it works fine - however when I try to call it from my Sheet it first says "Loading" and then after a while says "Error: Login Required". I was never asked to authenticate nor give permission for anything. I tried packaging the script as a Sheets add-on for my organization and when I add the add-on it does ask for permission, but again I try to run the query I get the login required error. From the BigQuery console I can see that indeed no query was executed and from the Stackdriver Logging console I can see the login required errors.

{
 insertId:  "1qpzv6cfv6fswq"  
 jsonPayload: {
  context: {
   reportLocation: {
    filePath:  "Code"     
    functionName:  "runQuery"     
    lineNumber:  86     
   }
  }
  message:  "Login Required
    at runQuery(Code:86)"   
  serviceContext: {
   service:  "blah"    
  }
 }
 labels: {
  script.googleapis.com/process_id:  "blah"   
  script.googleapis.com/project_key:  "blah"   
  script.googleapis.com/user_key:  "blah"   
 }
 logName:  "projects/sk-data-platform/logs/script.googleapis.com%2Fconsole_logs"  
 receiveTimestamp:  "2018-03-31T20:20:53.173034608Z"  
 resource: {
  labels: {
   function_name:  "runQuery"    
   invocation_type:  "custom function"    
   project_id:  "sk-data-platform"    
  }
  type:  "app_script_function"   
 }
 severity:  "ERROR"  
 timestamp:  "2018-03-31T20:20:52.169Z"  
}

All of the documentation I have seen indicates that Google Sheets should handle the authentication flow for me and the script should run in the context of the logged in user.

In this case my user is the owner of the Google Project so I have access to everything. Very confused at this point.

I have been seeing similar problem, though I get the Login error even when I run from the gs script editor. stackoverflow.com/questions/49478149/… — user1055568

Ben Ben · Accepted Answer · 2018-04-04T22:43:25

Ok, so the problem I was having was that I was trying to execute the function as a Sheets custom function. What I wasn't aware of is that custom functions cannot talk to a host of google services (BigQuery among them). The error was extremely unhelpful. If I bind the function to a menu then click the menu item it will execute properly.

Unfortunately this does not allow me to build a sheet where users can fill arbitrary ranges of cells with custom queries - which is what I was looking for.

Login Required error accessing BigQuery from Google Apps Script in a Sheet

1 Answers