On my Google Sheet I have a code that I use to:
- Pull an employees information from a BigQuery table
- Add it to a 'Notes' tab on the sheets file
- Then another part of the code adds an import range formula at the last row of the 'notes' tab to pull more information from a separate Google Sheet
I am trying to cut out the need for the second part of the formula and my idea was to make a second BigQuery table using the separate Google Sheet with the additional info as the source, then query each table through either a saved view of both tables or just query each table and input data separately.
The issue that I am running into is that whenever I try to run the new query I am hit with this error whenever it involves the Google Sheets table:
GoogleJsonResponseException: API call to bigquery.jobs.query failed with error: Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.
I have tried querying a saved view that combines both tables and I have tried querying just the second Google Sheets sourced table but both give me the same error. I run into no errors when I query only the original BigQuery table. My guess is it that I have to add in some sort of google drive/sheets/bigquery credentials into my code to make it work but I am not sure where to start and my google-fu hasnt turned up anything useful so far
Here is the code I use to the info, its pretty standard and that I want to say it's pulled straight from Google's Documentation with the only change being adding an 'employee' variable which is determined on the sheet and changes which associate to query for:
function loadEmployee() {
// Clear sheet
var clearSpreadsheet = SpreadsheetApp.getActive().getSheetByName('Notes').getRange("A2:M");
clearSpreadsheet.clearContent();
// Replace this value with the project ID listed in the Google
// Cloud Platform project.
var projectId = 'xxxxx';
// Sets Employee Variable to query
var ss = SpreadsheetApp.getActiveSpreadsheet();
var name = ss.getSheetByName("Gold Belt Dashboard");
var range = name.getRange("a1");
var cell = range.getCell(1,1);
var employee = cell.getValue();
/* Begin BigQuery Section */
// bigQuery DML goes here
var request = {
query: 'SELECT * from project.dataset.combined_table where eid = '+employee+' order by date desc',
useLegacySql: false
};
// Make Query and get jobId
var queryResults = BigQuery.Jobs.query(request, projectId);
var jobId = queryResults.jobReference.jobId;
// Check on status of the Query Job.
var sleepTimeMs = 500;
while (!queryResults.jobComplete) {
Utilities.sleep(sleepTimeMs);
sleepTimeMs *= 2;
queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
}
// Get all the rows of results.
var rows = queryResults.rows;
while (queryResults.pageToken) {
queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
pageToken: queryResults.pageToken
});
rows = rows.concat(queryResults.rows);
}
if (rows) {
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getSheetByName('Notes');
// Append the headers.
var headers = queryResults.schema.fields.map(function(field) {
return field.name;
});
sheet.appendRow(headers);
// Append the results.
var data = new Array(rows.length);
for (var i = 0; i < rows.length; i++) {
var cols = rows[i].f;
data[i] = new Array(cols.length);
for (var j = 0; j < cols.length; j++) {
data[i][j] = cols[j].v;
}
}
sheet.getRange(2, 1, rows.length, headers.length).setValues(data);
} else {
Logger.log('No rows returned.');
}
}Thanks in advance!
