Apps Script to BigQuery Fails: "Unexpected token [ProjectID]^"

Google Cloud Data Analytics
1 
Hello,

I'm encountering a persistent error when trying to call `BigQuery.Jobs.query()` from Google Apps Script, and I'm hoping someone can shed some light on a potential cause or solution.

**Problem:**
The API call fails with the error: 
`GoogleJsonResponseException: API call to bigquery.jobs.query failed with error: Unexpected token. [ACTUAL_MALFORMED_PROJECT_ID_FROM_ERROR]^` 
(e.g., `eloquent-optics-4608^`)

The strange part is that the Project ID referenced in the API error message appears to be a malformed or truncated (to 20 characters) version of the actual, correct Project ID that my Apps Script code is using.

**Apps Script Code (Simplified Test Case):**
I'm using the following simple function to test:

```javascript
function simpleBigQueryTest() {
  const projectID = 'your_ACTUAL_correct_project_id_string'; // e.g., '**eloquent-optics-460821-d9**'
  Logger.log(`Attempting simple query on project: "${projectID}"`);

  let sqlQuery = 'SELECT 1 + 1 AS result;';

  try {
    const request = {
      query: sqlQuery,
      useLegacySql: false
    };

    let queryResults = BigQuery.Jobs.query(projectID, request); 
    const jobId = queryResults.jobReference.jobId;
    Logger.log(`BigQuery Job ID created: ${jobId} for project ${projectID}`);

    // Simplified polling
    while (!queryResults.jobComplete) {
      Utilities.sleep(1000);
      queryResults = BigQuery.Jobs.getQueryResults(projectID, jobId);
    }

    if (queryResults.errors && queryResults.errors.length > 0) {
      Logger.log(`Query FAILED. Errors: ${JSON.stringify(queryResults.errors)}`);
    } else if (queryResults.rows) {
      Logger.log(`Query SUCCEEDED. Result: ${queryResults.rows[0].f[0].v}`);
    }
  } catch (e) {
    Logger.log(`Exception during BigQuery test. Project ID was: "${projectID}". Query attempted: "${sqlQuery}". Error: ${e.toString()} - Stack: ${e.stack}`);
  }
}

Execution Log Snippet Showing the Error: Info Attempting simple query on project: “eloquent-optics-460821-d9” Error Exception during BigQuery test. Project ID was: “eloquent-optics-460821-d9eloquent-optics-4608”. Query attempted: “SELECT 1 + 1 AS result;”. Error: GoogleJsonResponseException: API call to bigquery.jobs.query failed with error: Unexpected token. shortidalgotest^ …

Troubleshooting Steps Already Taken:

  • Confirmed the projectID variable in Apps Script exactly matches my actual, correct GCP Project ID string.
  • The BigQuery API is enabled in the GCP project.
  • The Apps Script project has been explicitly linked to the correct GCP Project Number in Apps Script settings (“Project Settings” > “Google Cloud Platform (GCP) Project”).
  • The OAuth Consent Screen for the GCP project has been configured (User Type: External, App Status: Testing).
  • My Google account (the one running the script) has been added as a “Test User” on the OAuth Consent Screen.
  • I have successfully gone through the script authorization flow, granting necessary permissions (including for BigQuery).
  • The appsscript.json manifest file explicitly includes “https://www.googleapis.com/auth/bigquery” in oauthScopes.
  • The issue occurs even with brand new GCP projects and new Apps Script projects.
  • Crucially, connecting to the same BigQuery tables from the same Google Sheet using the built-in Google Sheets Data Connector (Data > Data connectors > Connect to BigQuery) works perfectly.
  • I previously had an auto-generated “Apps Script” OAuth 2.0 Client ID with a warning triangle on the GCP Credentials page, which I deleted. The issue persists even after this and going through re-authorization.
  • Trying to pass the Project Number (instead of Project ID string) to BigQuery.Jobs.query() resulted in a different error (“Cannot parse as CloudRegion”).

Despite these steps, the API call from Apps Script consistently fails with the “Unexpected token” error referencing a malformed version of my Project ID.

Any insights or suggestions on what might be causing this specific behavior or how to further diagnose it would be greatly appreciated.

Thank you!