BigQuery Access for New Google Spreadsheets

1
votes

SOLVED: Turns out that it was an authentication problem in the cloud console. When I am clicking the link in the Advanced services panel, it switches to my main login account, when opening the console. For anyone encountering a similar problem, I would recommend logging out of all other accounts.

Original question. Due to the removal of limitations in the old Google Sheets, I am trying to migrate code to a New Google Sheet. However, I am unable to run my code, because I cannot enable this for the spreadsheet.

(For any of you who dont know what I mean, this is New Google Spreadsheets:http://googleblog.blogspot.nl/2013/12/new-google-sheets-faster-more-powerful.html)

What I try first:
Resources>Advances Google Services>BigQuery API "on"
Then:
I click on the link in "These services must be enabled in the Google Developers Console". This bring me to cloud.google.com with the error "The project you requested is unavailable".

Image can be found here: i44.tinypic.com/117zcld.png

When I perform the same actions in an old Google Sheet, there is no problem at all. I read the list of unsupported features and I see some apps script features are not yet available, but they are not specified: https://support.google.com/drive/answer/3543688?hl=en&ref_topic=20322 Does this mean that BigQuery API access is not yet available for New Google Spreadsheets? If so, do you have any information as to when this might be available in the future?

Kind regards, René

1
google-apps-scriptgoogle-sheetsgoogle-bigquery

1 Answers

2
votes

These are the steps I followed:

  • Created a new Google Spreadsheet, following the link you provided.
  • Went to the script manager, turned on BigQuery.
  • Followed link to the Google Developers Console, it worked.
  • Turned on BigQuery there too.
  • Wrote a small script.
  • It worked!

Not sure why your specific spreadsheet is failing though. Can you try creating a new one?

My test script:

function quickBigQueryTest() {
  var resource = {
      query: 'select 1 x',
      timeoutMs: 1000
    };
  var queryResults = BigQuery.Jobs.query(resource, 'project number');
  Logger.log(queryResults);
  var sheet = SpreadsheetApp.getActiveSheet();
  var headers = queryResults.schema.fields.map(function(field) {
    return field.name;
  });
  Logger.log(headers);
  sheet.appendRow(headers);
}