BigQuery - Querying spreadsheet - Missing scopes

[Mikael]

Hi, 

I have an issue with oauth-scopes and bigquery. I've gotten this (querying a google spreadsheet from bigquery) to work in the (non google ads) scripts environment by manually setting oauthScopes but since this doesn't seem to be an option in ads-scripts i'm not sure how to best proceed. 

The error message is: GoogleJsonResponseException: API call to bigquery.jobs.getQueryResults failed with error: Access Denied: BigQuery BigQuery: Permission denied while getting Drive credentials.

function main() {
  const sheetUrl = "https://docs.google.com/spreadsheets/d/............."
  const projectId = "......"

  let queryResults = BigQuery.Jobs.insert({
    configuration: {
      query:{
        query: "SELECT * FROM test",
        useLegacySql: false,
        tableDefinitions: {
          "test": {
            sourceUris: [sheetUrl],
            autodetect: true,
            sourceFormat: "GOOGLE_SHEETS",
          }
        }
      },
    }
  }, projectId);
  const jobId = queryResults.jobReference.jobId;

  // Check on status of the Query Job.
  let sleepTimeMs = 500;
  while (!queryResults.jobComplete) {
    Utilities.sleep(sleepTimeMs);
    sleepTimeMs *= 2;
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId);
  }

  // Get all the rows of results.
  // Append the headers.
  let rows = queryResults.rows;
  while (queryResults.pageToken) {
    queryResults = BigQuery.Jobs.getQueryResults(projectId, jobId, {
      pageToken: queryResults.pageToken
    });
    rows = rows.concat(queryResults.rows);
  }

  if (!rows) {
    Logger.log('No rows returned from query: ' + query);
    rows = [];
  }
  Logger.log(rows)
}

The above code works in regular scripts after editing the manifest to contain:

  "oauthScopes": [

    "https://www.googleapis.com/auth/bigquery",

    "https://www.googleapis.com/auth/drive", 

    "https://www.googleapis.com/auth/spreadsheets"

  ],

Suggestions?

[Google Ads Scripts Forum Advisor]

Hello Mikael,

Thank you for reaching out to us. However, it appears that your concern is more on Google Apps Scripts rather than anything specific to Google Ads Scripts. With this, we would recommend raising this to App Scripts team via this link instead.

Regards,

Teejay Wennie Google Ads Scripts Team

 

ref:_00D1U1174p._5004Q2asZXv:ref

[Mikael]

I would disagree. 

I can get this to work in Google Apps Scripts since they allow me to specify the scopes i authorize the scripts for (aka, not a problem in apps scripts)

I can NOT get this working in google Ads scripts due the a lack of this capability (manually specify which scopes the script need). 

Another way to phrase it: The BigQuery API does not work in Ads-Script when querying external (sheet) data tables but does work in App-scripts. 

Best Regards

Mikael

[Google Ads Scripts Forum Advisor]

Hi Mikael,

Thank you for pointing this out. Please do note that although Google Ads Scripts is based on Google App Scripts, I'm afraid that not all App Scripts feature would work/supported on Google Ads Scripts. I could submit a feature request regarding this. Before I can proceed, could you please provide your business use case (or benefit) with regard to this feature/functionality?

[Mikael]

Though there is a lot of support for extracting data from through GAQL/AWQL/and entity iterators; there is a huge limitation in processing any larger amount of data in the limited scripting environment scripts offer. Offloading heaver calculations and potentially even ML jobs to Bigquery offers a great balance where on can use scripts for data extractions and mutate operations while doing heavy lifting in another system system suitable for "big" data processing without having the need to provisioning servers for API integrations. Combined with the BigQuery - Google Ads Data transfer one only really need to use scripts for mutations as most of the data can already be found in bigquery. 

The specific use case for google sheets is that sheets is that it act as a user-configuration layer where lists of dynamic values can be provided without needing to build complex UI but rather reusing and interface many are already comfortable with have plugs into many existing ecosystems.

[Google Ads Scripts Forum Advisor]

Hello Mikael,

I’m James, a colleague of Teejay. Allow me to assist you further.

Thank you for providing the required information. I will now forward this to our internal team for validation. Take note that there’s no guarantee that this kind of functionality will be supported anytime soon within Google Ads Scripts. In the meantime, please follow our blog for updates and new releases regarding this.

Regards,

James Howell Google Ads Scripts Team

ref:_00D1U1174p._5004Q2asZXv:ref

[Mikael]

For anyone reading this thread. I did stumble upon a workaround. 

Basically the google ads scripts can be found in https://script.google.com/ (searched by name). There i could manually add the manifest and add the missing scopes. After which I hade to re-auth the script in google ads but it worked to query google sheets afterwards.

One has to be careful though, the google ads script UI seems to keep a local copy of the script so changes under script.google.com and google ads does not sync automatically (UI wise). 

[Google Ads Scripts Forum Advisor]

Hi Mikael,
 

Thank you for getting back to us. I work with James & Teejay and allow me to assist you here.
 

Moving forward to your concern, As my colleague "Teejay" mentioned to you that "Google Ads Scripts is based on Google App Scripts, I'm afraid that not all App Scripts feature would work/supported on Google Ads Scripts." Additionally, one of my colleagues "James" created a feature request for this issue and right now there's no significant updates yet to be shared with, however I assure you that someone from our team will keep this thread posted for any updates. In the meantime, please follow our blog for updates and new releases regarding this.
 

Regards,

Darwin Google Ads Scripts Team

ref:_00D1U1174p._5004Q2asZXv:ref