export google ads report to google sheet and accumulation

[jk lee]

Hello, not a major in coding it would be great if you guys can help. 

I'm trying to export google ads report to google sheet. 

since google ads reports can change after the report date, I'm thinking of exporting 30 days data to google sheet every day and accumulate data in the same sheet to use it as a back-data. this is so far I wrote ..  the code is not working .. and stuck how to improve. 

thx if any one helps. 

--------------------------------------------------------------------

function main() {

  var spreadsheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/');

  var ss = spreadsheet.getSheetByName('google_ads:report');

  var beginDate = "20211001";

  var today = new Date();

   

  

  var report = AdsApp.report(

    'SELECT Date, AdNetworkType1, Impressions, Clicks, Cost, AverageCpc, CostPerAllConversion FROM   ACCOUNT_PERFORMANCE_REPORT DURING' + beginDate + ', ' + today);

  

  report.exportToSheet(ss);

  Logger.log("Report available at " + spreadsheet.getUrl());

}

[Sigurd Fabrin]

If you log your beginDate and today variables you will see that they are not using the same date format.

Try smt like: var today = Utilities.formatDate(new Date,AdsApp.currentAccount().getTimeZone(),'yyyyMMdd'); // this will give you today's date formatted like your other date

Another problem is your spacing. Make sure you have exactly one whitespace between words e.g. between DURING and beginDate

Cheers

Sigurd

[Google Ads Scripts Forum Advisor]

Hi Kwan,

Thanks for reaching out. Harry here, from the Google Ads Scripts Team.

I would appreciate it if you can provide where exactly you are having issues with or at least provide the error message you are getting. Nonetheless, I am assuming that it pertains to the query's date. Dates in this regard should be in form of YYYYMMDD so you would have to convert the today variable in this date format where you can use the Utilities.formatDate(date, timeZone, format) method to do so and as mentioned by Sigurd. More information on this matter is described in this link.

Another thing to take note as well is that queries are whitespace sensitive so you can double check your query by storing it in a variable first then log to check if the query's format checks out like the below:

var query = "Select CampaignName from CAMPAIGN_PERFORMANCE_REPORT";
Logger.log(query);
var report = AdsApp.report(query);

@Sigurd: Thanks once again!

Let us know of anything else.  

Thanks,

Harry Cliford Rivera Google Ads Scripts Team

ref:_00D1U1174p._5004Q2R6Ddh:ref

[jk lee]

Thx both @Sigurd and @Harry

improved my script but still having one error. 

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

function main() {

  var spreadsheet = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/');

  var ss = spreadsheet.getSheetByName('google_ads:report');

  var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;

  var now = new Date();

  var from = new Date('October 1, 2021');

  var to = new Date(now.getTime() - 1 * MILLIS_PER_DAY);

  

  var timeZone = AdsApp.currentAccount().getTimeZone();

  var report = AdsApp.report(

    'SELECT Date, AdNetworkType1, Impressions, Clicks, Cost, AverageCpc, CostPerAllConversion' +

    'FROM ACCOUNT_PERFORMANCE_REPORT' + 

    'DURING' + Utilities.formatDate(from, timeZone, 'yyyyMMdd') + ','

            + Utilities.formatDate(to, timeZone, 'yyyyMMdd'));

  

  report.exportToSheet(ss);

  Logger.log("Report available at " + spreadsheet.getUrl());

}

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

InputError: Missing FROM clause: SELECT Date, AdNetworkType1, Impressions, Clicks, Cost, AverageCpc, CostPerAllConversionFROM ACCOUNT_PERFORMANCE_REPORTDURING20211001,20211104 (file CIC.gs, line 11)

I did had what I intended with

 var report = AdsApp.report(

    'SELECT Date, AdNetworkType1, Impressions, Clicks, Cost, AverageCpc, CostPerAllConversion' +

    'FROM ACCOUNT_PERFORMANCE_REPORT' + 'DURING ONE_MONTH' )

so thinking I'm have something wrong with my query? 

2021년 11월 4일 목요일 오후 6시 37분 45초 UTC+9에 adsscripts님이 작성:

[Google Ads Scripts Forum Advisor]

Hi Kwan,

Thanks for coming back. To reiterate, queries are whitespace sensitive that you would supply into the report method and is technically a single line of string. It is better to store the query in into a variable before executing the query to pull a report to validate the query's format. Having the code should address the issue you are encountering where you are missing whitespaces after each of the clauses.

  var query = 
    'SELECT Date, AdNetworkType1, Impressions, Clicks, Cost, AverageCpc, CostPerAllConversion ' +
    'FROM ACCOUNT_PERFORMANCE_REPORT ' + 
    'DURING ' + Utilities.formatDate(from, timeZone, 'yyyyMMdd') + ','
            + Utilities.formatDate(to, timeZone, 'yyyyMMdd');
  Logger.log(query);
  var report = AdsApp.report(query);

Kindly try and let me know how it goes.

[jk lee]

Did change some details but worked great! 

Thx!!!!

2021년 11월 5일 금요일 오후 5시 46분 10초 UTC+9에 adsscripts님이 작성: