Perhaps you could use service account with OAuth2 to authenticate your script and access BigQuery on behalf of a backend identity, rather than the active user. This approach allows you to centralize IAM permissions and avoid granting BigQuery access to every user individually.

First need to create a service account in Google Cloud and assign it the necessary roles, then generate a JSON key for that service account, and use it in your Apps Script to manually build a JWT which you'll send to Google’s OAuth2 token endpoint to exchange for an access token, and finally, with that token, you can make authorized BigQuery API requests as the service account you created. Take a look below how it should work:

// Save service account json credentials for test purposes
const SERVICE_ACCOUNT_JSON = {
  "type": "service_account",
  "project_id": "your-project-id",
  "private_key_id": "your-private-key",
  "private_key": "your-private-key",
  "client_email": "[email protected]",
  "client_id": "1234567890",
  ...
};

// Create manually a JWT
function createJWT(sa) {
  const header = {
    alg: "RS256",
    typ: "JWT"
  };

  const iat = Math.floor(Date.now() / 1000);
  const exp = iat + 3600;

  const claimSet = {
    iss: sa.client_email,
    scope: "https://www.googleapis.com/auth/bigquery",
    aud: "https://oauth2.googleapis.com/token",
    exp: exp,
    iat: iat
  };

  const base64Header = Utilities.base64EncodeWebSafe(JSON.stringify(header));
  const base64Claim = Utilities.base64EncodeWebSafe(JSON.stringify(claimSet));
  const signatureInput = `${base64Header}.${base64Claim}`;

  const signatureBytes = Utilities.computeRsaSha256Signature(signatureInput, sa.private_key);
  const base64Signature = Utilities.base64EncodeWebSafe(signatureBytes);

  return `${signatureInput}.${base64Signature}`;
}

// Exchange JWT for access token
function getAccessToken() {
  const jwt = createJWT(SERVICE_ACCOUNT_JSON);

  const response = UrlFetchApp.fetch('https://oauth2.googleapis.com/token', {
    method: 'post',
    payload: {
      grant_type: 'urn:ietf:params:oauth:grant-type:jwt-bearer',
      assertion: jwt
    }
  });

  return JSON.parse(response.getContentText()).access_token;
}

// Call BigQuery through Google APIs using service account token
function runQuery() {
  const accessToken = getAccessToken();
  const projectId = 'your-project-id';

  const queryRequest = {
    query: 'SELECT name FROM `your_dataset.your_table` LIMIT 5',
    useLegacySql: false
  };

  const response = UrlFetchApp.fetch(`https://www.googleapis.com/bigquery/v2/projects/${projectId}/queries`, {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(queryRequest),
    headers: {
      Authorization: 'Bearer ' + accessToken
    }
  });

  const result = JSON.parse(response.getContentText());
  Logger.log(result);
}

By this way you'll be able to use service account token for every call to BigQuery, keeping IAM permissions only for service account being used on this script. On this approach, everyone with access to the document (and App Script), will have access to run the script (and may edit it), so please be aware of it. Did this solve your issue?

Note: Please, avoid using hardcoded sensive data, like SERVICE_ACCOUNT_JSON , here I'm just giving an example for test purposes.

Reference:

• Create JWT with REST