Simple Report Script

adwords-report.js

/**
 * Simple Report Script
 */
 
 
 
/**
 * Date Settings
 */
var date = new Date(),
 
    year  = date.getFullYear(),
    month = date.getMonth(),
 
    last  = new Date(year,month,0),
    lastYear  = last.getFullYear(),
    lastMonth  = last.getMonth()+1,
    
    DURING    = "LAST_MONTH";
 
 
/**
 * SpreadSheet Settings
 */
var TITLE = lastYear+"年"+lastMonth+"月のアドワーズレポート";
 
 var driveId = "GOOGLE DRIVE ID";
 
// http://hanatsunami.blogspot.jp/2013/08/gas-google-apps-script.html
 function createSs(ssName, folderId) {
   var ssId = SpreadsheetApp.create(ssName).getId();
   var file = DriveApp.getFileById(ssId);
   var folder = DriveApp.getFolderById(folderId)
   folder.addFile(file);
   DriveApp.getRootFolder().removeFile(file);
   var ss = SpreadsheetApp.openById(ssId);
   return ss;
}
 
var SPREADSHEET = createSs(TITLE,driveId);
 
 
/**
 * Sheet Settings
 */


var DEFAULT = {
  
      sheet: "基本レポート",
      query: 'SELECT Month,Impressions,Clicks,Ctr,Cost,AverageCpc,EstimatedTotalConversions,CostPerEstimatedTotalConversion,EstimatedTotalConversionRate,AveragePosition ' +
             'FROM ACCOUNT_PERFORMANCE_REPORT ' +
             'WHERE Impressions > 0 ' +
             'DURING '+DURING,
      header:["月","表示回数","クリック数","クリック率","費用","クリック単価","CV","CV単価","CV率","平均掲載順位"],
      sort  :[4,8]
 
    },

    ACCOUNT = {
  
      sheet: "アカウントレポート",
      query: 'SELECT Month, Device,AdNetworkType1,AdNetworkType2,Impressions,Clicks,Ctr,Cost,AverageCpc,EstimatedTotalConversions,CostPerEstimatedTotalConversion,EstimatedTotalConversionRate,AveragePosition ' +
             'FROM ACCOUNT_PERFORMANCE_REPORT ' +
             'WHERE Impressions > 0 ' +
             'DURING '+DURING,
      header:["月","デバイス","配信タイプ","配信先","表示回数","クリック数","クリック率","費用","クリック単価","CV","CV単価","CV率","平均掲載順位"],
      sort  :[6,10]
 
    },
 
    CAMPAIGN = {
  
      sheet: "キャンペーンレポート",
      query: 'SELECT Month, CampaignName, Impressions,Clicks,Ctr,Cost,AverageCpc,EstimatedTotalConversions,CostPerEstimatedTotalConversion,EstimatedTotalConversionRate,AveragePosition ' +
             'FROM CAMPAIGN_PERFORMANCE_REPORT ' +
             'WHERE Impressions > 0 ' +
             'DURING '+DURING,
      header:["月","キャンペーン名","表示回数","クリック数","クリック率","費用","クリック単価","CV","CV単価","CV率","平均掲載順位"],
      sort  :[4,8]
    },
    
    KEYWORD = {
      
      sheet: "キーワードレポート",
      query: 'SELECT Month, KeywordText,Impressions,Clicks,Ctr,Cost,AverageCpc,ConversionsManyPerClick,CostPerConversionManyPerClick,ConversionRateManyPerClick,AveragePosition ' +
             'FROM KEYWORDS_PERFORMANCE_REPORT ' +
             'WHERE Impressions > 0 ' +
             'DURING '+DURING,
      header:["月","キーワード","表示回数","クリック数","クリック率","費用","クリック単価","CV","CV単価","CV率","平均掲載順位"],
      sort  :[4,8]
    },
    
   DEVICE = {
  
      sheet: "デバイスレポート",
      query: 'SELECT Month, Device,AdNetworkType1,Impressions,Clicks,Ctr,Cost,AverageCpc,EstimatedTotalConversions,CostPerEstimatedTotalConversion,EstimatedTotalConversionRate,AveragePosition ' +
             'FROM ACCOUNT_PERFORMANCE_REPORT ' +
             'WHERE Impressions > 0 ' +
             'DURING '+DURING,
      header:["月","デバイス","配信タイプ","表示回数","クリック数","クリック率","費用","クリック単価","CV","CV単価","CV率","平均掲載順位"],
      sort  :[5,9]
 
    }
;
 
/**
 * Sort Sheet
 */
var SHEET_ARR = [DEFAULT,ACCOUNT,CAMPAIGN,KEYWORD,DEVICE];
 
 
/**
 * Header Color
 */
var COLOR = "#f4f4f4";
 
 
/**
 * Replace settings
 * arr[0] replace arr[1]
 */
var REPLACE_ARR = [
  
    ['Mobile devices with full browsers','スマホ'],
    ['Computers','PC'],
    ['Tablets with full browsers','タブレット'],
    ['Search Network','サーチネットワーク'],
    ['Google search','Google'],
    ['Search partners','他'],
    ['Display Network','ディスプレイ']
 
];

/**
 * Mail Setting
 * Send to multiple mail
 * ex.
 * [email protected],[email protected]
 */
 
 var MAIL = "[email protected]"
 
/**
 * Report Class
 * @constructor
 * @classdesc Report
 * @param {object} param - param
 */
function Report(settings){
  
  this.settings = settings;
 
  SPREADSHEET.insertSheet(this.settings.sheet);
  var report = AdWordsApp.report(this.settings.query);
  report.exportToSheet(SPREADSHEET.getSheetByName(this.settings.sheet));
 
}
 
 
/**
 * Sort Spreadsheet
 */
Report.prototype.sort = function(){
  
  this.settings.sort.forEach(function(row){
    SPREADSHEET.sort(row,false);
  })
 
}
 
/**
 * Replace Header
 */
Report.prototype.setHeader = function() {
 
  var header = SPREADSHEET.getSheetByName(this.settings.sheet).getRange(1, 1, 1, this.settings.header.length);
  header.setValues([this.settings.header]);
  header.setBackgroundColor(COLOR);
  header.setHorizontalAlignment("center");
  header.setFontWeight("bold");
  
}
 
/**
 * Replace the word
 */
// http://stackoverflow.com/questions/26480857/how-do-i-replace-text-in-a-spreadsheet-with-google-apps-script
function replaceInSheet(sheet, to_replace, replace_with) {
  //get the current data range values as an array
  var values = sheet.getDataRange().getValues();
 
  //loop over the rows in the array
  for(var row in values){
 
    //use Array.map to execute a replace call on each of the cells in the row.
    var replaced_values = values[row].map(function(original_value){
      //日付や数字が文字列に変換されるので除外
      
      return original_value.toString() === to_replace ? original_value.toString().replace(to_replace,replace_with) : original_value;
      
    });
 
    //replace the original row values with the replaced values
    values[row] = replaced_values;
  }
 
  //write the updated values to the sheet
  sheet.getDataRange().setValues(values);
}
 
 
/**
 * Loop replace the word
 */
function replaceWord(){
 
  REPLACE_ARR.forEach(function(arr){
    replaceInSheet(SPREADSHEET.getActiveSheet(),arr[0],arr[1]);
  })
 
}
 
function mail() {
  var address = MAIL,
      title   = TITLE + "ができました",
      message = TITLE + "ができました\nSheet URL: " + SPREADSHEET.getUrl();
  MailApp.sendEmail(address,TITLE,message);
}
 
/**
 * Fire this script
 */
function main() {
  
  SHEET_ARR.forEach(function(sheet){
 
    var report = new Report(sheet);
    
    replaceWord();
    
    report.sort();
    report.setHeader();
    
  });
  
  SPREADSHEET.setActiveSheet(SPREADSHEET.getSheets()[0]);
  SPREADSHEET.deleteActiveSheet();
  
  Logger.log("Sheet URL " + SPREADSHEET.getUrl());
  
  mail();
}