Adwords save and send CSV file

[Viet]

Sorry for the trouble but as I'm having issues saving to a spreadsheet format (too many rows), can someone help me change the below code to save the file in CSV file format please? Thanks!

function main() { 

var REPORT_NAME = 'AdWords_Keyword_Report_2014_02_Historical_'; 

if(DriveApp.getFilesByName(REPORT_NAME).hasNext()) { 

Logger.log("File found"); 

var existingReport = DriveApp.getFilesByName(REPORT_NAME).next(); 

var spreadSheet = SpreadsheetApp.openByUrl(existingReport.getUrl()); 

} else { 

Logger.log("File not found. Creating new file."); 

var spreadSheet = SpreadsheetApp.create(REPORT_NAME+ Utilities.formatDate(new Date(), "PST", "yyyy-MM-dd HH:mm")); 

Logger.log("New file created"); 

} 

  

var mccSheet = spreadSheet.getActiveSheet();

  

Logger.log("Initiating remote script");

var REPORT_TYPE = 'KEYWORDS_PERFORMANCE_REPORT';  

Logger.log("Report type set");

var FILTER = 'Impressions > 0';

//var DATE_RANGE = 'LAST_7_DAYS';

var DATE_BEGIN = '20170201';

var DATE_END = '20170630';

Logger.log("Report date set");

//https://developers.google.com/adwords/api/docs/appendix/reports/keywords-performance-report

var COLUMN_NAMES = [

  'Date',

  'AccountDescriptiveName',

  'CampaignName',

  'AdGroupName',

  'Criteria',

  'KeywordMatchType', 

  'Impressions',

  'Clicks',

  'Ctr',

  'Cost',

  'AverageCost', 

  'AverageCpm',

  'AverageCpc',

  'CpcBid',

  'TopOfPageCpc',

  'ActiveViewImpressions',

  'ActiveViewCtr',

  'ActiveViewCpm',

  'Conversions',

  //'ConversionRate',

  'CostPerConversion',

  'BounceRate',

  'QualityScore',  

  'AveragePosition',

  'AveragePageviews',

  'AverageTimeOnSite',

  'Interactions',

  'InteractionTypes', 

  'AdNetworkType2',

  'SearchPredictedCtr',

  'SearchImpressionShare',

  'SearchExactMatchImpressionShare',

  

];

  

Logger.log("Columns set");

var COLUMNS = COLUMN_NAMES.join(',');

mccSheet.clear();

mccSheet.appendRow(COLUMN_NAMES);

var adwordsSheet = spreadSheet.insertSheet();

var accountIterator = MccApp.accounts().get();

Logger.log("Querying and exporting data");

while (accountIterator.hasNext()) {

var account = accountIterator.next();   

MccApp.select(account);

Logger.log("Querying data for " + account.getName());

var REPORT = AdWordsApp.report(

'SELECT ' +

COLUMNS +

' FROM ' +

REPORT_TYPE +

' WHERE Status IN [REMOVED, ENABLED, PAUSED]' +

' AND ' +

FILTER +

' DURING ' +

//DATE_RANGE

          DATE_BEGIN+" ,"+DATE_END

);

    

REPORT.exportToSheet(adwordsSheet);

adwordsSheet.deleteRow(1);

var rowNumber = adwordsSheet.getLastRow();

var rangeToCopy = adwordsSheet.getDataRange();

mccSheet.insertRowAfter(mccSheet.getLastRow());

rangeToCopy.copyTo(mccSheet.getRange(mccSheet.getLastRow() + 1, 1));

Logger.log("Data successfully added to file(" + rowNumber + " rows)");

}

spreadSheet.deleteSheet(adwordsSheet);

Logger.log("File update complete(" + (mccSheet.getLastRow() - 1) + " rows)");

 MailApp.sendEmail(

    'emaila...@company.com',

    'My CSV File',

    '',

    {attachments:[{fileName: 'foo.csv', mimeType: 'text/csv', content: csv}]}

  );

}

  

[Vincent Racaza (AdWords Scripts Team)]

Hi Viet,

Below is the equivalent script which will output the data in a csv file and send it to the email you specified:

function main() {
 
  Logger.log("Initiating remote script");

   
    var csv =  COLUMN_NAMES.join(',');

   
    Logger.log("Columns set");
    var COLUMNS = COLUMN_NAMES.join(',');

   
    var accountIterator = MccApp.accounts().get();

    Logger.log("Querying and exporting data");
   
    while (accountIterator.hasNext()) {
    var account = accountIterator.next();  
    MccApp.select(account);
    Logger.log("Querying data for " + account.getName());
   
    var REPORT = AdWordsApp.report(
    'SELECT ' +
    COLUMNS +
    ' FROM ' +
    REPORT_TYPE +
    ' WHERE Status IN [REMOVED, ENABLED, PAUSED]' +
    ' AND ' +
    FILTER +
    ' DURING ' +
    //DATE_RANGE
    DATE_BEGIN+" ,"+DATE_END
    );

    var rows = REPORT.rows();
   
    while (rows.hasNext()) {
       var row = rows.next();
       var result = [row['Date'], row['AccountDescriptiveName'], row['CampaignName'], row['AdGroupName'], row['Criteria'], row['KeywordMatchType'], row['Impressions'],
       row['Clicks'], row['Ctr'], row['Cost'], row['AverageCost'], row['AverageCpm'], row['AverageCpc'], row['CpcBid'], row['TopOfPageCpc'], row['ActiveViewImpressions'],
       row['ActiveViewCtr'], row['ActiveViewCpm'],row['Conversions'], row['CostPerConversion'], row['BounceRate'], row['QualityScore'], row['AveragePosition'],
       row['AveragePageviews'], row['AverageTimeOnSite'], row['Interactions'], row['InteractionTypes'],row['AdNetworkType2'], row['SearchPredictedCtr'], row['SearchImpressionShare'],
       row['SearchExactMatchImpressionShare']];
       csv += '\n' + result.join(',');
     }
}

  MailApp.sendEmail(
    'INSERT_EMAIL_ADDRESS_HERE',

    'My CSV File',
    '',
    {attachments:[{fileName: 'foo.csv', mimeType: 'text/csv', content: csv}]}
  );
}

Also, please take note that there is also a limit on the mail attachment's size. You can attach up to 25 MB per message.

Thanks,

Vincent Racaza

AdWords Scripts Team

[Viet]

This is great! Works like a charm so thank you very much for it!

Viet

[Viet]

Sorry but is there a way to change the filetype from CSV to xlsx? i tried changing the mimeType to 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' but it hasnt worked. thanks.

[Vincent Racaza (AdWords Scripts Team)]

Hi Viet,

I was able to attach an xlsx file using this mimeType (application/vnd.openxmlformats-officedocument.spreadsheetml.sheet):

{attachments:[{fileName: 'foo.xlsx', mimeType: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet', content: csv}]}

Could you confirm if you also changed the file extension? If you can confirm that both the file extension and the mimeType are correct, and it has not worked still, then please provide to me your CID and the name of the script so I can check (Reply privately to author). Also, please provide a sample mail which has an incorrect attachment.

[Caitlin M]

I keep getting an error from line 58: ReferenceError: "MccApp" is not defined. Any idea how to correct this, please? Thank you! 

[Thea Vega (AdWords Scripts Team)]

Hi Caitlin,

One possible reason as to why you are encountering the "ReferenceError: "MccApp" is not defined." is because the script uses the accountSelector and fetches accounts. If the script uses the said selector and is implemented on a client account-level, then the said error would be encountered. 

With this, could you confirm if you are using your script on an MCC-level instead of a client account-level? If you could confirm this, please create a new forum thread with your concern. You may then provide your CID and script name via Reply privately to author in the new thread so I can further investigate.

Thanks,

Thea

AdWords Scripts Team

[jaja...@graphicproducts.com]

Hi,

thank you for this!

Is it possible to download the csv instead of emailing it?

Thanks!

[googleadsscrip...@google.com]

Hello,

This is more related to Apps Scripts. You can find support for Apps Scripts related topics here.

Regards,

Matt 

Google Ads Scripts Team
=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~
Also find us on our blog and discussion group:
    http://googleadsdeveloper.blogspot.com/search/label/adwords_scripts
    https://developers.google.com/google-ads/scripts/community/
=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~

--
-- You received this message because you are subscribed to the Google Groups AdWords Scripts Forum group. Please do not reply to this email. To post to this group or unsubscribe please visit https://developers.google.com/adwords/scripts/community.
---
You received this message because you are subscribed to the Google Groups "Google Ads Scripts Forum" group.
To unsubscribe from this group and stop receiving emails from it, send an email to adwords-scripts+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/adwords-scripts/d841f670-a141-4c2e-aefa-29a49e93c66c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.