How to export data to google spreadsheet?

[Toms Simanis]

Hello!

So I am new to developing scripts for adwords, also I have been learning Java but now javascript is little bit different.

Therefore qeustion is, I have script for locating different campaign Url's and checking if they are working or not, but the problem is the fact that info is shown on log field and I need to export data to google spreadsheet, how can I do it?

function main() {

var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/1pxtcmSk2nc0lkTV53Qt5KrAIU09BZqXuzPZRX3zyg4M/edit#gid=0';

  var spreadsheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);

  var sheet = spreadsheet.getSheetByName('Sheet1');

  sheet.clear();

  var cell = sheet.getRange("A1:Z1");

  cell.setFontWeight("bold");

  

  var header = [

  'Checed ad',

  'Ad status',

  ];

    sheet.appendRow(header);

  

var campIter = AdWordsApp.campaigns().withCondition("Status = ENABLED").get();

while (campIter.hasNext()) {

 var camp = campIter.next();

  var adIter = camp.ads().withCondition("Status != DISABLED").get();

while (adIter.hasNext()) {

   var ad = adIter.next();

   

   Logger.log('Now checking ad #' + ad.getId());

   

    var FUrl = ad.urls().getFinalUrl();

  var options = { muteHttpExceptions: true };

    var response = UrlFetchApp.fetch(FUrl, options);

    var responseCode = response.getResponseCode();

  if (responseCode == 404.0) {

    Logger.log(camp.getName());

    Logger.log(ad.getAdGroup().getName());

    Logger.log(destUrl);

    Logger.log(ad.getApprovalStatus());

    Logger.log(response.getResponseCode());

  } else if (responseCode == 200.0) {

 Logger.log('Ad OK -- 200 HTTP Response Code.');

 

  }

    }

}

  

  

}

[Tyler Sidell (AdWords Scripts Team)]

Hi Toms,

Your code is looking good.  You are already setting the correct sheet for var sheet.  In order to export the data to a spreadsheet you would need to use exportToSheet.  Below is an example of the setup:

function main() {
  var today = new Date();
  var beginDate = "20140101";
  var endDate = formatDate(today);
  var accountIterator = MccApp.accounts().get();

  var spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
  var mccAccount = AdWordsApp.currentAccount();
  var i=0;
  while (accountIterator.hasNext()) {
    var account = accountIterator.next();
    // Switch to the account you want to process.
    MccApp.select(account);

    var report = AdWordsApp.report("Select Cost, Impressions, Clicks, ConvertedClicks from BUDGET_PERFORMANCE_REPORT during " + beginDate + ", " + endDate);
    var sheet = spreadsheet.getActiveSheet();
    //Name sheet by account name
    if(i == 0){
      spreadsheet.renameActiveSheet("Report "+account.getName());
    }
    else if(i != 0 && !spreadsheet.getSheetByName("Report "+account.getName())){
      spreadsheet.insertSheet("Report "+account.getName());
    }
    else if(i != 0 && spreadsheet.getSheetByName("Report "+account.getName())){
      spreadsheet.setActiveSheet(spreadsheet.getSheets()[i]);
    }
   
    report.exportToSheet(spreadsheet.getActiveSheet());
    i++;

    var range = sheet.getRange("A2:G30");
    range.sort(7);
  }
}


function formatDate(dt) {
    var day = dt.getDate();
    day = (day > 9) ? day : "0" + day;
    var month = dt.getMonth() + 1;
    month = (month > 9) ? month : "0" + month;
    var year = dt.getFullYear();
    return "" + year + month + day;
}

Thanks,

Tyler Sidell
AdWords Scripts Team

[Toms Sīmanis]

Thanks for reply!

But I have a question about using Date and format Date function , what does it change on using this script for exporting data?

[Tyler Sidell (AdWords Scripts Team)]

Hi Toms,

When you set dates in the DURING section, the report only pulls data between that time frame.  So in the above example, the report that we export to the spreadsheet will only include the Cost, Impression, and Click data from 20140101 to today.

Thanks,
Tyler Sidell
AdWords Scripts Team