cloud Function -> BigQuery: Permission denied while getting Drive credentials. Does work with Cloud console?

This is what I have.

• For my project I have A cloud Function that gets triggered by Pub/Sub.

• The function selects data from BigQuery table to which a Google sheet is connected. So my data is inside Google sheet and BiqQuery is used to query my data.

• The same function the inserts the selected data into another table inside BigQuery. All the selecting and Inserting is done with BigQuery Jobs.

Here is the problem

When the function is triggered I get the following error message.

Error: Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials. at new ApiError (/workspace/node_modules/@google-cloud/common/build/src/util.js:73:15) at Util.parseHttpRespBody (/workspace/node_modules/@google-cloud/common/build/src/util.js:208:38) at Util.handleResp (/workspace/node_modules/@google-cloud/common/build/src/util.js:149:117) at /workspace/node_modules/@google-cloud/common/build/src/util.js:479:22 at onResponse (/workspace/node_modules/retry-request/index.js:228:7) at /workspace/node_modules/teeny-request/build/src/index.js:226:13 at processTicksAndRejections (internal/process/task_queues.js:95:5)

The things that do work

• When the same function is run via Cloud console selecting and inserting into BigQuery table work.

• Querying the Google sheet via BigQuery page inside Google Cloud does also works as it should.

What I have tried

I followed everything inside this: Querying Drive data documentation.

I have have all the required permissions.

Is this a bug or am I doing something wrong?

Edit

I forgot to add my code.

This is the part where I select the data Something happens with the Tag_Data which triggers the catch.

 const Select_Tag_Info_query = `SELECT * FROM \`project.Dataset.table\` where TagId = "${tag}"`;

console.log(Select_Tag_Info_query); // outputs: "SELECT * FROM `pti-tag-copy.ContainerData2.PTI-tags` where TagId = "tag-1"

const Tag_Data = SelectJob(Options(Select_Tag_Info_query));

console.log(`This is Tag-Data: ${Tag_Data}`); // outputs: "This is Tag-Data: [object Promise]"

Tag_Data.catch((error) => {
    console.log(`Something went wrong with selecting tag data from the spreadsheet`);
    console.error(error);
});
//The resolve is not called because of the error above
Promise.resolve(Tag_Data).then(function (Tag_Data) {
    let returned_tag = Tag_Data.TagId;
    let returnd_country = Tag_Data.Country;
    let returned_location = Tag_Data.Location;
    let returned_poleId = Tag_Data.PoleId;

    console.log(`this is tag: ${returned_tag}`);
    console.log(`this is country: ${returnd_country}`);
    console.log(`this is location: ${returned_location}`);
    console.log(`this is poleid: ${returned_poleId}`);
});

    

This is how the BigQuery Jobs function looks like.

function Options(query) {
    const options = {
        configuration: {
            query: {
                query: query,
                useLegacySql: false,
            },
            location: 'EU'
        },
    };

    return options;
}



// This function is for selecting the tag data from spreadsheet via bigquery
async function SelectJob(options) {
    // Run a BigQuery query job.

    console.log(`select job is called.`) // This part is outputed

    const [job] = await bigquery.createJob(options); // something goes wrong here.
    const [rows] = await job.getQueryResults(); 

    console.log(`${rows[0]["TagId"]}`);    // This part is not outputed
    console.log(`${rows[0]["Country"]}`);  // This part is not outputed
    console.log(`${rows[0]["Location"]}`); // This part is not  outputed
    console.log(`${rows[0]["PoleId"]}`);   // This part is not outputed


    if (rows.length < 1) {
        console.log("array is empty");
    } else {
        console.log(`selected tag data from spreadsheet`);

        return {
            TagId: rows[0]["TagId"],
            Country: rows[0]["Country"],
            Location: rows[0]["Location"],
            PoleId: rows[0]["PoleId"]
        }
    }
}

I think something goes wrong at this part of the code inside the Select Job(). because the other console.logs are not outputed. My reason for saying that is because inside BigQuery project history I don't see the query. I only see a red circle with a white ! mark. see the photo BigQuery project history.

const [job] = await bigquery.createJob(options); 
        const [rows] = await job.getQueryResults();