广告系列和关键字效果报告

老板要求您汇总的许多每日或每周报告任务都可以使用AdWords脚本自动执行。我的一位读者通过了以下要求脚本的请求，该脚本可以节省他每天的时间，并将其一些绩效报告汇总在一起。也许它也可以节省您一些时间。

以下脚本将构建具有五个标签的Google Spreadsheet：

1. 过去7天的广告系列效果摘要
2. 迄今为止的广告系列效果月份
3. 上个月的广告系列效果
4. 过去7天的关键字效果摘要
5. 过去7天的每日关键字效果

您需要做的就是创建一个 新的Google电子表格 并将网址粘贴到脚本中。然后，您可以安排此脚本每天运行，并在早上开始工作时，将刷新所有数据。

谢谢，
拉斯

/************************************
* Campaign and Keyword Summary Report
* Version: 1.2
* Changelog v1.2 - Fixed INVALID_PREDICATE_ENUM_VALUE
* ChangeLog v1.1 - Removed  api Version from  报告   呼叫 
* Created By:  拉斯  Savage
* FreeAdWordsScripts.com
************************************/
var SPREADSHEET_URL = "PASTE GOOGLE SPREADSHEET URL HERE";

function main() {
  //These names are important. change them with caution
  var tabs = ['camp_perf_7_days','camp_perf_mtd','camp_perf_last_month','keyword_perf_7_days','keyword_perf_7_days_daily'];
  for(var i in tabs) {
    var results = runQuery(tabs[i]);
    writeToSpreadsheet(tabs[i],results);
  }
}

//Helper function to get or  创造  the 电子表格
function getSheet(tab) {
  var s_sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet;
  try {
    sheet = s_sheet.getSheetByName(tab);
    if(!sheet) {
      sheet = s_sheet.insertSheet(tab, 0);
    }
  } catch(e) {
    sheet = s_sheet.insertSheet(tab, 0);
  }
  return sheet
}

//Function to write the rows of the report to the sheet
function writeToSpreadsheet(tab,rows) {
  var to_write =  兑换 RowsToSpreadsheetRows(tab,rows);
  var s_sheet = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = getSheet(tab);
  sheet.clear();
  
  var numRows = sheet.getMaxRows();
  if(numRows < to_write.length) {
    sheet.insertRows(1,to_write.length-numRows); 
  }
  var range = sheet.getRange(1,1,to_write.length,to_write[0].length);
  range.setValues(to_write);
}

//A generic function used to build and run the report query
function runQuery(tab) {
  var API_VERSION = { includeZeroImpressions : false };
  var cols = getColumns(tab);
  var report = getReport(tab);
  var date_range = getDateRange(tab);
  var where = getWhereClause(tab);
  var query = ['select',cols.join(','),'from',report,where,'during',date_range].join(' ');
  var report_iter = AdWordsApp.report(query, API_VERSION).rows();
  var rows = [];
  while(report_iter.hasNext()) { 
    rows.push(report_iter.next());
  }
  return rows;
}
 
//This function will  兑换  row data into a format easily pushed into a 电子表格
function  兑换 RowsToSpreadsheetRows(tab,rows) {
  var cols = getColumns(tab);
  var ret_val = [cols];
  for(var i in rows) {
    var r = rows[i];
    var ss_row = [];
    for(var x in cols) {
      ss_row.push(r[cols[x]]);
    }
    ret_val.push(ss_row);
  }
  return ret_val;
}

//Based on the tab name, this returns the report type to use for the query
function getReport(tab) {
  if(tab.indexOf('camp_') == 0) {
    return 'CAMPAIGN_PERFORMANCE_REPORT';
  }
  if(tab.indexOf('keyword_') == 0) {
    return 'KEYWORDS_PERFORMANCE_REPORT';
  }
  throw new Exception('tab name not recognized: '+tab);
}

//Based on the tab name, this returns the where clause for the query
function getWhereClause(tab) {
  if(tab.indexOf('camp_') == 0) {
    return 'where CampaignStatus = ENABLED';
  }
  if(tab.indexOf('keyword_') == 0) {
    return 'where CampaignStatus = ENABLED and AdGroupStatus = ENABLED and Status = ENABLED';
  }
  throw new Exception('tab name not recognized: '+tab);
}

//Based on the tab name, this returns the columns to  广告 d into the report
function getColumns(tab) {
  var ret_array = [];
  if(tab.indexOf('daily') >= 0) {
    ret_array.push('Date');
  }
  ret_array.push('CampaignName');
  ret_array.push('CampaignStatus');
  
  if(tab.indexOf('keyword_') == 0) {
    ret_array = ret_array.concat(['AdGroupName',
                                  'AdGroupStatus',
                                  'Id',
                                  'KeywordText',
                                  'KeywordMatchType']);
  }
  return ret_array.concat(['Clicks',
                           'Impressions',
                           'Ctr',
                           'AverageCpc',
                           'Cost',
                           'AveragePosition',
                           'Conversions',
                           'ConversionRate',
                           'ConversionValue']);
}

//Based on the tab name, this returns the date range for the data.
function getDateRange(tab) {
  if(tab.indexOf('7_days') >= 0) {
    return 'LAST_7_DAYS';
  }
  if(tab.indexOf('mtd') >= 0) {
    return 'THIS_MONTH';
  }
  if(tab.indexOf('last_month') >= 0) {
    return 'LAST_MONTH';
  }
  throw new Exception('tab name not recognized: '+tab);
}