Not getting Authorization dialog for App Script accessing BigQuery Service

Question

I am trying to fix some App Scripts which use the BigQuery "Advanced Service" to install some BigQuery jobs. The first new script I ran popped a BigQuery authorization dialog, and everything worked fine, now running hourly. The second new script ran initially, but is now failing with:

{
 "error": {
  "errors": [
   {
    "domain": "global",
    "reason": "required",
    "message": "Login Required",
    "locationType": "header",
    "location": "Authorization"
   }
  ],
  "code": 401,
  "message": "Login Required"
 }
}

This began about 12 hours after the initial authorization, so it seems like something expired. But, I can't figure out how to trigger the authorization dialog to appear. Also, I expected an authorization would be required for each distinct function, yet the second one initially ran without asking for BigQuery permissions.

Additional Info: Here is the .gs file I am executing:

function runQuery() {
  var projectId = redacted;

  var configuration = {
    "query": {
      "useQueryCache": false,
      "destinationTable": {
          "projectId": projectId,
          "datasetId": redacted,
          "tableId": redacted
        },
      "writeDisposition": "WRITE_TRUNCATE",
      "createDisposition": "CREATE_IF_NEEDED",
      "allowLargeResults": true,
      "query": redacted
    }
  };
  var job = {
    "configuration": configuration
  };

  var jobResult = BigQuery.Jobs.insert(job, projectId);
  var jobId = jobResult.jobReference.jobId;
  Logger.log(jobResult);
}

The first time I ran a very similar query, I got a dialog prompting me for BigQuery permissions, as documented here: https://developers.google.com/apps-script/guides/services/authorization I subsequently ran this same script successfully (with slightly different query config). But, then, about 12 hours after first run, I started getting the authorization return quoted above. I thought it might refresh with time, but still get same failure.

@noogui, not that I know of. My understanding is the App Scripts - BigQuery Advanced Service integration handles authorization for you, as I experienced initially, when it prompted me for permission. — user1055568
Please include the code where you authorize your script - login, etc. People can't help you if you just say "script" because we've no idea how you're implementing it. — noogui
@noogui I had edited original to include more detail. Thanks. — user1055568
Could you provide the query that you are running? I would suggest you to use library, in this way you can manage your access in your scirpt. — enle lin

user1055568 user1055568 · Accepted Answer · 2018-04-07T19:07:23

Turns out this has been working all along. The authorization error was being generated by my attempt to look at the job results, not by the job itself. Doh!

Not getting Authorization dialog for App Script accessing BigQuery Service

1 Answers