CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-bigquery
Nodi
Nodi

Reputation: 11

Permission issue when running a bigquery query through Appscript

I'm trying to run a query through App script which a user should be able to run via google sheet gui or button. The query should insert data into table_one from table_two. table_two is an external table (uses google sheets as a source). I can read from and update table_one through app script normally but trying to insert data from table_two returns me the following error message: "GoogleJsonResponseException: API call to bigquery.jobs.query failed with error: Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.".

You can find the code below:

function onOpen(){

var ui = SpreadsheetApp.getUi();
ui.createMenu('Update values')
.addItem('Run query', 'runQuery')
.addToUi();
}

function runQuery() {
  const projectId = 'project';

  const request = {
    query: 'insert into `project.table_one` select * from `project.table_two` where Date is not null;',
    useLegacySql: false
  };
  let queryResults = BigQuery.Jobs.query(request, projectId);
  const jobId = queryResults.jobReference.jobId;

  let sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
  }
  
}

In addition to Bigquery I've added Drive and Sheets services to the appscript project but that didn't help much.

Upvotes: 0

Views: 509

Answers (1)

Nodi
Nodi

Reputation: 11

Found the fix in some related questions including this one.

  • Added google drive api to the cloud project and added scopes to manifest file.
  • Enable Show "appsscript.json" manifest file in editor in app script project settings.
  • Add scopes: "oauthScopes": ["https://www.googleapis.com/auth/drive", "https://www.googleapis.com/auth/bigquery"]

Upvotes: 1

Related Questions