The OAuth Client was not found - Google Apps Scrip

问题:

I am trying to use App Script to load BigQuery results into a Google Spreadsheet. Here is my Code

function runQuery() {
var projectId = 'xxxxx';

var request = {
  query: 'select * from ASRLogs.LocationBasedClicks;'
};

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 the result. 
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.create("BigQuery Results");
     var sheet = spreadsheet.getActiveSheet();

     // 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).setValue(data);

     Logger.log('Results spreadsheet created: %s', spreadsheet.getUrl());
} else {
  Logger.log('No rows returned.')
}
}

The error I get is

Error: invalid_client
The OAuth client was not found.
Request Details
- cookie_policy_enforce=false
- scope=https://www.googleapis.com/auth/bigquery https://www.googleapis.com/auth/spreadsheets
- response_type=code gsession
- redirect_uri=https://script.google.com/oauthcallback
- access_type=offline
- approval_prompt=force
- state=ACjPJvHwuS-sspO-j9b5vlH_Ul4VokI3QRANL-gwa7YWxz6-RFelZBuLQ2aiiGldHRgR89sMnvlgpsmSOnlquEY45oTt1IgZHWfoWq5e52Jf6l_G-5yPpPwCf40Dkv_JYR9welQPrQ
- client_id=[e-mail]
- hl=en

Am I missing something ? I have done the following.

1. Enabled BigQuery API in Google Serveices
2. Enabled BigQuery in the developer console for the project.

回答1:

I had this exact same issue yesterday.

I had been copying a document several times, which will in turn copy all the scripts inside of it, appending each with "Copy of". What I had ended up with was "Copy of Copy of Copy of Copy of Copy of ".

As a last resort, I renamed the Script project to "", and hey presto, it came good.

My theory is that since the Script project name is being used as the name of the application requesting authorisation, there is some character limit going on here.

Does renaming the project to a shorter name fix the issue?

回答2:

Check whether your client ID or secret does not contain any spaces in your code - that was my problem which I didn't notice when copying the info from the console

回答3:

Navigate to Consent Screen section in your Google API console (from the sidebar at left), change product name and save changes.