Requesting a BigQuery API from Google Spreadsheet generates an Error

Question

I am trying to fetch data from Bigquery and show them into my spreadsheet using the App script. First, I created a Spreadsheet file in my G-drive and then put my code into the script editor.

Here is the code I have used to get all the datasets from Bigquery:

    function getAllDataSets(filter){
  try{
    let req_for_datasets = BigQuery.Datasets.list(PROJECT_ID);
    let datasets = req_for_datasets.datasets;
    let list = [];
    datasets.forEach( obj => {
      if(obj.datasetReference.datasetId.indexOf(filter)>-1) 
        list.push(obj.datasetReference.datasetId);
    });
    return list;
  }catch(e){
    return [];
  }
}

this script works fine and I can see the result while running my script through Code Editor. I try to use this script in onOpen() or onEdit() to be able to get data when the spreadsheet gets opened. but using the spreadsheet I receive this message:

GoogleJsonResponseException: API call to bigquery.tables.list failed with error: Request is missing required authentication credential. Expected OAuth 2 access token, login cookie or other valid authentication credential.

this is the code I have put in onOpen function:

    function onOpen(){
  let ui = SpreadsheetApp.getUi();
  let method_list = ["SUM", "AVG", "COUNT"];
  //Adding a Custom menu
  ui.createMenu("Media Budget")
    .addItem("Facebook", "makeQuery")
    .addSeparator()
    .addItem("Google Ads", "makeQuery")
    .addToUi();

    
  //Getting all datasets from the specified project
  let dataset_list = getAllDataSets("dw_sandbox_");
  Browser.msgBox(dataset_list);
  //Creating dropdown list cell
  let cell = SHEET.getRange("B1");
  applyValidationToCell(dataset_list, cell);
  
}

other than that if I try to execute the function using the custom menu in the spreadsheet everything works fine as well.

It would be really appreciated if you can help me.

Answer 1

If you can perform a request when running code manualy, but not on simple onOpen or onEdit trigger - the reason is authorization issues

See restrictions:

Because simple triggers fire automatically, without asking the user for authorization, they are subject to several restrictions:

...

They cannot access services that require authorization. For example, a simple trigger cannot send an email because the Gmail service requires authorization, but a simple trigger can translate a phrase with the Language service, which is anonymous.

Workaround

• Option a)

  Rather than running the request onOpen, implement it into a separate function that will be called when chosen from custom menu

  Sample:

function onOpen(){
  let ui = SpreadsheetApp.getUi();
  let method_list = ["SUM", "AVG", "COUNT"];
  //Adding a Custom menu
  ui.createMenu("Media Budget")
  .addItem("Facebook", "makeQuery")
  .addSeparator()
  .addItem("Google Ads", "makeQuery")
  .addSeparator()
  .addItem("bigQuery", "makeBigQuery")
  .addToUi();  
}

function makeBigQuery(){
  //Getting all datasets from the specified project
  let dataset_list = getAllDataSets("dw_sandbox_");
  Browser.msgBox(dataset_list);
  //Creating dropdown list cell
  let cell = SHEET.getRange("B1");
    applyValidationToCell(dataset_list, cell);
}

• Option b)

  Run your exisitng code on installable instead on simple trigger.

• Installable triggers can run funcitons that reuire auhtorization

• To convert your simple trigger into an installable

  • rename the function from onOpen (otherisw you might run into conflicts from having both simple and installable triggers run simultaneously)
  • Go on Edit -> Current project's triggers - > New trigger - see also here