Adwords API calls using VBA possible? if Yes could you please send me a sample http request format to pull a report.

[shreedha...@accenture.com]

Hi Team,

I am trying to pull a report from Adwords by API calls using Excel VBA with Windows HTTP request.

But I am arriving at below error:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?><reportDownloadError><ApiError><type>ReportDownloadError.MISSING_PARAMETER</type><trigger>Missing report definition</trigger><fieldPath></fieldPath></ApiError><ApiError><type>ReportDownloadError.INVALID_PARAMETER</type><trigger>Missing client information</trigger><fieldPath></fieldPath></ApiError></reportDownloadError>

and the code i tried as below

Sub TestAPI()

Dim objHTTP As Object

Dim result As String

Dim Xml As Variant

Dim Fs As FileSystemObject

Dim FileData() As Byte

Dim Arr() As String

Dim St As String

   Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")

   URL = "https://adwords.google.com/api/adwords/reportdownload/v201509"

   

   objHTTP.Open "POST", URL, False

   

   objHTTP.setRequestHeader "Authorization", "Bearer 3756320"

   objHTTP.setRequestHeader "DEVELOPER_TOKEN", "NegtkJjSr7jcrQSdeI4Nzg"

   objHTTP.setRequestHeader "CLIENT_CUSTOMER_ID", "328-664-6765"

   

   'objHTTP.RequestBody "__rdxml", Xml

       

   '  Set Fs = New FileSystemObject

  'Set Xml = Fs.OpenTextFile("C:\Users\ajkumar\Desktop\Shreedhar\New Text Document.xml")

   'St = Xml.ReadAll

   ' "<fields>Id</fields>" & _

  ' "<predicates>" & _

      "<field>Status</field>" & _

      "<operator>IN</operator>" & _

      "<values>ENABLED</values>" & _

      "<values>PAUSED</values>" & _

    "</predicates>" & _

' "<fields>CampaignId</fields>" & _

   St = "<reportDefinition xmlns=" & """" & "https://adwords.google.com/api/adwords/cm/v201509" & """" & ">" & _

      "<selector>" & _

   "<fields>Impressions</fields>" & _

    "<fields>Clicks</fields>" & _

    "<fields>Cost</fields>" & _

  "</selector>" & _

  "<reportName>Account Performance</reportName>" & _

  "<reportType>ACCOUNT_PERFORMANCE_REPORT   </reportType>" & _

  "<dateRangeType>LAST_7_DAYS</dateRangeType>" & _

  "<downloadFormat>CSV</downloadFormat>" & _

"</reportDefinition>"

   objHTTP.send St

   result = objHTTP.responseText

End Sub

Your guidance and help would be really helpful.

Regards,

Shreedhar Bakale

[Michael Cloonan (AdWords API Team)]

Hello,

Our .NET client library does support VB, although I'm not sure if that's quite the same as using VBA from excel.

However, a client library is not required if you formulate your request correctly. Can you please log the full XML, rather than showing the code that generates the XML, because then it would be easy to take a look and see where the formatting you're using may differ from the correct formatting that would result in a successful request.

Regards,

Mike, AdWords API Team

[shreedha...@accenture.com]

Hello Mike,

Please find attached xml attached.

I need one help can you please share sample HTTP request of Dot Net code for pulling a report from API with sample XML file. I will try following that format to VBA Excel and try again.

The error message is as below:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?><reportDownloadError><ApiError><type>ReportDownloadError.MISSING_PARAMETER</type><trigger>Missing report definition</trigger><fieldPath></fieldPath></ApiError><ApiError><type>ReportDownloadError.INVALID_PARAMETER</type><trigger>Missing client information</trigger><fieldPath></fieldPath></ApiError></reportDownloadError>

Regards,

Shreedhar

[Michael Cloonan (AdWords API Team)]

Hello,

Based on the error message, it seems like you may not be setting your HTTP headers correctly, even if the XML is valid. Here's an example of what a set of valid HTTP headers looks like, in addition to a valid report request XML:

Report request to: 'https://adwords.google.com/api/adwords/reportdownload/v201509'
HTTP headers: [Content-Type: application/x-www-form-urlencoded, Authorization: [REDACTED], User-Agent: [REDACTED], clientCustomerId: [REDACTED], developerToken: [REDACTED], skipReportHeader: false, skipReportSummary: false, skipColumnHeader: false, includeZeroImpressions: false]
<reportDefinition>
  <selector>
    <fields>CampaignId</fields>
    <fields>AdGroupId</fields>
    <fields>Id</fields>
    <fields>Criteria</fields>
    <fields>CriteriaType</fields>
    <fields>FinalUrls</fields>
    <fields>Impressions</fields>
    <fields>Clicks</fields>
    <fields>Cost</fields>
    <predicates>
      <field>Status</field>
      <operator>IN</operator>
      <values>ENABLED</values>
      <values>PAUSED</values>
    </predicates>
  </selector>
  <reportName>Last+7+days+CRITERIA_PERFORMANCE_REPORT</reportName>
  <reportType>CRITERIA_PERFORMANCE_REPORT</reportType>
  <dateRangeType>LAST_7_DAYS</dateRangeType>
  <downloadFormat>CSV</downloadFormat>
</reportDefinition>

You need to specify the Authorization, User-Agent, clientCustomerId, and developerToken headers in order for the request to work. If you do not have these, you can use the Get Started guide to learn more. This guide will help you set up all of those pieces of data except the access token. Instead, it will set you up with a refresh token, which can be used to generate access tokens. Our OAuth 2.0 guide also has instructions for how to generate an access token from a refresh token.

Once you get this all set up, please give it another try. If it fails again, please include both the HTTP request headers and body in the log so that I can make sure that everything is present.

Regards,

Mike, AdWords API Team

[Michael Cloonan (AdWords API Team)]

Hello,

The User-Agent is a custom string defined by you that can uniquely identify your application. It is useful for troubleshooting, for example, so that if someone needs to look through logs they are able to identify which requests were made with your application vs. some other piece of code.

Regards,

Mike, AdWords API Team

On Thursday, December 17, 2015 at 12:06:35 AM UTC-5, shreedha...@accenture.com wrote:

Hi Mike,

Thank you mike.  Yup its seems like report headers are not correct.

Please tell me what is User-Agent?

I got rest of the parameters. 

Regards,

Shreedhar

[Michael Cloonan (AdWords API Team)]

Hello,

Here are some problems with the current state of your headers:

1. You should use "clientCustomerId", not "CLIENT_CUSTOMER_ID"
2. You should use "developerToken", not "DEVELOPER_TOKEN"
3. The clientCustomerId is a 10-digit number, which represents the id of the account you want to access with the request. It can be in the format XXXXXXXXXX or XXX-XXX-XXXX. It looks like you've instead used your OAuth client id, which is a different concept. Please see this section of the Get Started guide for more information on the two uses of the word "client".
4. You shouldn't be URL-escaping any of these fields, as they don't contain illegal characters. developerToken has alphanumeric characters and - and _, and is case sensitive.
5. Your access token doesn't look long enough; please make sure that you are generating this correctly.

I am going to delete your post since it contains private, sensitive information. If you want, we can continue this discussion privately. You can respond to me only using the drop down arrow at the top right of my post and choosing "Reply to Author", and then you can include private information again.

Regards,

Mike, AdWords API Team

On Thursday, December 17, 2015 at 7:45:03 AM UTC-5, shreedha...@accenture.com wrote:

Hi Mike,

I understood there is a problem with request headers. But I am still getting MISSING CLIENT INFORMATION as the error.

Could you please give an example of all request headers with what kind of values need to be set for those.

I have tried setting customer id in 2 below patterns, but still getting above error.

a. objHTTP.setRequestHeader "CLIENT_CUSTOMER_ID", "311277555846-g506fkkku4h99immi16r60tvam3dc61s.apps.googleusercontent.com" 

b. bjHTTP.setRequestHeader "CLIENT_CUSTOMER_ID", "xxx-xxx-xxxx"

My code in VBA is as below for setting request headers

Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")

   URL = "https://adwords.google.com/api/adwords/reportdownload/v201509"

   

   objHTTP.Open "POST", URL, False

   

   objHTTP.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"

   objHTTP.setRequestHeader "Authorization", "Bearer 7636419%0d%0a"

   objHTTP.setRequestHeader "DEVELOPER_TOKEN", "NegtkJjSr7jcrQSdeI4Nzg%0d%0a"

   objHTTP.setRequestHeader "CLIENT_CUSTOMER_ID", "311277555846-g506fkkku4h99immi16r60tvam3dc61s.apps.googleusercontent.com" 

   

   objHTTP.setRequestHeader "skipReportHeader", "false"

   objHTTP.setRequestHeader "skipReportSummary", "false"

   objHTTP.setRequestHeader "skipColumnHeader", "false"

   objHTTP.setRequestHeader "includeZeroImpressions", "false"

Regards,

Shreedhar