BigQuery: No OAuth token with Google Drive scope was found

Question

I'm trying to replace the contents of a Google Sheet with a BigQuery query.

I'm in the Google Sheet and using a script based on these: https://greenido.wordpress.com/2013/12/16/big-query-and-google-spreadsheet-intergration/ Accessing BigQuery with Google Spreadsheet

All the script does is pull the info from a BigQuery query and write it to a Google Sheet. But if I just run the script, I end up getting an error: BigQuery: No OAuth token with Google Drive scope was found

What do I need to do to get this to work?

Answer 1

After a lot of Googling, I found the answer. Apparently, even though I need to give my script the authority to manage not just my Google Sheet, but also the authority to manage my Google Drive.

All I have to do is add a function to make Google ask me for permission for the script to manage my Drive.

Here's the function:

function usingAppsScriptsToken ()}
  // we need to provoke a drive dialog
  // DriveApp.getFiles()
}

Note that key ingredient is DriveApp.getFiles(), and it still works even though it's commented. When I run the function, I'll get a pop-up window that says "(your script) would like to View and manage the files in your Google Drive". When I allow it, the data import function works.

Source

Answer 2

No need to add anything to your code.

You simply need to add an additional scope to the dependencies part of the appscript.json file:

"dependencies": {
  "enabledAdvancedServices": [{
    "userSymbol": "BigQuery",
    "serviceId": "bigquery",
    "version": "v2"
  },
  { 
    "userSymbol": "Drive",
    "serviceId": "drive",
    "version": "v2"
  }]
}

In the View menu you could select to Show the manifest file:

Answer 3

Just add

DriveApp.getFiles()

to any function in your project and run it. After your running, you see a window that asks permission to the Drive.

Afterall you can delete this row and everything is will be ok.