ADF Copy Activity using Google Ads Dataset - Preview Data error

Question

ADF Copy Activity using Google Ads Dataset - Preview Data error

Terry Shelton 45

I recently configured a Google Ads linked service that successfully connects to the Google Ads API. I created a dataset of type Google Ads, and it uses the new linked service that connects to the Google Ads API as seen here:

User's image

I created a Copy Activity that uses the new dataset as the Source Dataset. When I click on the Preview Data link on the Copy Activity Source tab, I receive an error as seen here:

User's image

What is causing this error, and what needs to be done to successfully Preview the data from the Google Ads source dataset?

Terry Shelton 45 Reputation points

2025-03-17T18:20:55.6233333+00:00

@Mamadou Bhoye Sow I'm getting the error that I show in my question above. Have you been able to use ADF to successfully query any Google Ads API data using Copy or Lookup activities?
Chandra Boorla 11,575 Reputation points Microsoft External Staff

2025-03-17T18:54:47.6533333+00:00

@Terry Shelton

As I understand that you are encountering an error when attempting to preview data. The error message indicates an "Invalid JSON payload received," specifically mentioning an unknown name 'returnTotalResultsCount'. Here are a few steps you can take to address this issue:

Check API Version and Fields - Ensure that you are using the correct version of the Google Ads API. Verify that the fields you are querying, such as campaign.name, are valid and available in the API version you are using. Google Ads API updates can sometimes deprecate or change fields.

Review Query Syntax - Double-check the query syntax in your Copy Activity. Ensure that it matches the expected format for the Google Ads API. Your query SELECT campaign.name FROM campaign looks standard, but it's worth confirming against the API documentation.

API Configuration - Look into the configuration settings of your Google Ads dataset and linked service in Azure Data Factory. Verify that all authentication credentials and parameters are correctly configured.

Unexpected Parameters - The error message refers to 'returnTotalResultsCount', which might be an unexpected parameter. Check if there are any optional settings in ADF that might be adding this to your request and adjust accordingly.

Manual API Testing - Consider using a tool like Postman to manually test the API request outside of ADF. This can help determine if the issue lies with the ADF configuration or the API request itself.

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Thank you.
Chandra Boorla 11,575 Reputation points Microsoft External Staff

2025-03-19T01:09:25.36+00:00

@Terry Shelton

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Thank you.
Chandra Boorla 11,575 Reputation points Microsoft External Staff

2025-03-19T19:09:42.6833333+00:00

@Terry Shelton

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Terry Shelton 45 Reputation points

2025-03-19T23:29:19.0033333+00:00
Hello Chandra Boorla

I used the list you provided to help resolve the issue. I first tested the ADF linked service with different Google Ads API versions. The linked service connects successfully using the Test Connection for versions v17, v18, and v19. Versions prior to v16 fail to connect.

I then used Postman to verify that I could return data from the Google Ads API. I accomplished this with the help of this publication:

https://medium.com/kinandcartacreated/google-authentication-with-postman-12943b63e76a

The author describes a method to authenticate using a Postman pre-request script and Google Ads service account credentials. This is one of 3 methods she describes, but it is the preferred. She references the work of Denis Loginov and this gist available here. In this gist, there are comments by ffeldhaus about using the pre-request script for the Google Cloud Platform and the modifications required in the pre-request script. He references a modified gist that contains a pre-request script that I used in my Postman collection here. I added this Scope to this script for the Google Ads API:

'https://www.googleapis.com/auth/adwords'

I created a POST request in my Postman collection with this URL:

https://googleads.googleapis.com/v19/customers/*insert 10 digit Google Ads customer id*/googleAds:search

I then added my GAQL query to the POST Body as raw JSON:

{ "query": "SELECT campaign.id, campaign.name, campaign.status FROM campaign ORDER BY campaign.id" }

I also had to add two Header Keys to the POST.

developer-token (set the key value to the Google Ads developer token)

login-customer-id (set to the 10 digit Google Ads manager account customer id - this is different than the 10 digit Google Ads customer account id in the POST request URL)

Also, make sure you remove the dashes/hyphens from both of the 10 digit ids.

Next I created a Postman Environment that contains the following variables:

serviceAccountKey

jsrasign

tokenExpiresAt

accessToken

Make sure you associate the Environment with the Collection so the environment variables can be used by the Collection.

I pasted the Google Ads service account JSON credentials file contents into the serviceAccountKey variable value. NOTE: the private_key value in the JSON credentials file must have all occurrences of \n removed. When we created the key for the service account in Google Ads, there were several occurrences of \n in the private_key value.

In the Postman collection Authorization, select Bearer Token for the Auth Type, and then insert {{accessToken}} as the Token value.

I believe this is everything I had to do to get the POST request to execute successfully and return a list of campaigns.

For the ADF Linked Service, make sure you insert the Client Customer ID and the Login Customer ID without any dashes/hyphens. Also, when testing my Copy Activity, I found that the only version of the Google Ads API version in the linked service that allowed the Copy Activity to work is v17. When I used v18 or v19, I received my original error regarding the returnTotalResultsCount invalid argument. This indicates that the ADF Google Ads linked service has not been updated to work with v18 or v19 at this time.

I hope this helps others trying to use the ADF Google Ads linked service to retrieve data with the Google Ads API.
Chandra Boorla 11,575 Reputation points Microsoft External Staff

2025-03-20T01:20:37.22+00:00

@Terry Shelton

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer.

Accepted answer

0 additional answers

Your answer

Terry Shelton 45 Reputation points

2025-03-17T18:20:55.6233333+00:00

@Mamadou Bhoye Sow I'm getting the error that I show in my question above. Have you been able to use ADF to successfully query any Google Ads API data using Copy or Lookup activities?
Chandra Boorla 11,575 Reputation points Microsoft External Staff

2025-03-17T18:54:47.6533333+00:00

@Terry Shelton

As I understand that you are encountering an error when attempting to preview data. The error message indicates an "Invalid JSON payload received," specifically mentioning an unknown name 'returnTotalResultsCount'. Here are a few steps you can take to address this issue:

Check API Version and Fields - Ensure that you are using the correct version of the Google Ads API. Verify that the fields you are querying, such as campaign.name, are valid and available in the API version you are using. Google Ads API updates can sometimes deprecate or change fields.

Review Query Syntax - Double-check the query syntax in your Copy Activity. Ensure that it matches the expected format for the Google Ads API. Your query SELECT campaign.name FROM campaign looks standard, but it's worth confirming against the API documentation.

API Configuration - Look into the configuration settings of your Google Ads dataset and linked service in Azure Data Factory. Verify that all authentication credentials and parameters are correctly configured.

Unexpected Parameters - The error message refers to 'returnTotalResultsCount', which might be an unexpected parameter. Check if there are any optional settings in ADF that might be adding this to your request and adjust accordingly.

Manual API Testing - Consider using a tool like Postman to manually test the API request outside of ADF. This can help determine if the issue lies with the ADF configuration or the API request itself.

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Thank you.
Chandra Boorla 11,575 Reputation points Microsoft External Staff

2025-03-19T01:09:25.36+00:00

@Terry Shelton

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Thank you.
Chandra Boorla 11,575 Reputation points Microsoft External Staff

2025-03-19T19:09:42.6833333+00:00

@Terry Shelton

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Chandra Boorla 11,575 Reputation points Microsoft External Staff

2025-03-20T01:20:37.22+00:00

@Terry Shelton

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer.

Answer 1

@Terry Shelton

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer.

Issue:

ADF Copy Activity using Google Ads Dataset - Preview Data error

Solution:

Use API Version v17 in ADF – ADF’s Google Ads linked service only works with v17. Versions v18 and v19 fail with the returnTotalResultsCount error.

Verify API Access via Postman

Authenticate using Google Ads service account credentials.
Use the OAuth scope: 'https://www.googleapis.com/auth/adwords'.
Send a POST request to https://googleads.googleapis.com/v17/customers/{customer_id}/googleAds:search with a GAQL query in the JSON body.

Fix ADF Linked Service Settings

Ensure Client Customer ID and Login Customer ID are entered without dashes/hyphens.
Set developer-token and login-customer-id headers correctly.

Check JSON Credentials

Remove \n characters from the private_key field in the service account JSON file.

This should resolve the issue and allow ADF to successfully copy data from Google Ads.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

Hope this helps. Do let us know if you have any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Terry Shelton 45 Reputation points

2025-03-20T21:45:29.7033333+00:00

@Chandra Boorla

I accepted your answer. Your summary of my long comment that I submitted on 3/19/2025 at 6:29pm Central time is more concise, but may not have the detail that someone would need. For anyone reading this, please read my long comment with full detail of what I did to get both Postman and Azure Data Factory to connect and query data.

One more clarification, a better example of the URL in Postman that I used is below where I use a fake customer id. In Postman, I actually did use v19 as I said in my comment, but the ADF Copy Activity would only work with me using v17 in the ADF Google Ads linked service.

https://googleads.googleapis.com/v17/customers/0123456789/googleAds:search

Share via

ADF Copy Activity using Google Ads Dataset - Preview Data error

0 additional answers

Your answer