Yahoo! JAPAN Ads Script | Developer Center

const   REPORT_FIELDS_YSA   =   ['ACCOUNT_ID',   'ACCOUNT_NAME',   'IMPS',   'CLICKS', 'CLICK_RATE',     'COST',     'AVG_CPC',     'CONVERSIONS',     'CONV_RATE',     'COST_PER_CONV', 'IMPRESSION_SHARE'];

const   REPORT_FIELDS_YDA   = ['ACCOUNT_ID',   'ACCOUNT_NAME',   'IMPS',   'CLICKS', 'CLICK_RATE',     'COST',     'AVG_CPC',     'CONVERSIONS',     'CONV_RATE',     'COST_PER_CONV', 'IMPRESSION_SHARE'];

/*
■Script summary
This script outputs any of the search account reports of "This month （excluding today）", "Last month" or "Month before the last".
If you run the script with "This month （excluding today）" on 1st day of the month, it will output "Last month" report.
■How to use
1. This script is for Yahoo! JAPAN Ads Search Ads accounts.
２． Set each constant as follows
・SPREAD_SHEET_ID： Spreadsheet ID
・SPREAD_SHEET_NAME： Sheer name
・MONTHS_AGO： Choose 0 for "This month （excluding today）", 1 for "Last month" and 2 for "Month before the last".
・REPORT_FIELDS_YSA： Edit this array according to the fields you would like to check. 
You can check available fields names from the link below.
https://ads-developers.yahoo.co.jp/reference/ads-search-api/v13/ReportDefinitionService/reports/v13/ACCOUNT.csv
■Limitation
This script outputs the results of accounts whose delivery settings are turned on.
*/
/*
■Script summary
This script outputs any of the search account reports of "This month （excluding today）", "Last month" or "Month before the last".
If you run the script with "This month （excluding today）" on 1st day of the month, it will output "Last month" report.
■How to use
1. This script is for Yahoo! JAPAN Ads Search Ads accounts.
２． Set each constant as follows
・SPREAD_SHEET_ID： Spreadsheet ID
・SPREAD_SHEET_NAME： Sheer name
・MONTHS_AGO： Choose 0 for "This month （excluding today）", 1 for "Last month" and 2 for "Month before the last".
・REPORT_FIELDS_YSA： Edit this array according to the fields you would like to check. 
You can check available fields names from the link below.
https://ads-developers.yahoo.co.jp/reference/ads-search-api/v13/ReportDefinitionService/reports/v13/ACCOUNT.csv
■Limitation
This script outputs the results of accounts whose delivery settings are turned on.
*/
const SPREAD_SHEET_ID = 'SpreadsheetID';
const SHEET_NAME = 'Sheet name';
let MONTHS_AGO = 0;
const REPORT_FIELDS_YSA = [
  'ACCOUNT_ID',
  'ACCOUNT_NAME',
  'IMPS', 
  'CLICKS',
  'CLICK_RATE',
  'COST',
  'AVG_CPC',
  'CONVERSIONS',
  'CONV_RATE', 
  'COST_PER_CONV',
  'IMPRESSION_SHARE',
];
const managerAccountId = AdsUtilities.getCurrentAccountId();
function main() {
  if (MONTHS_AGO < 0 || MONTHS_AGO > 2 || Number.isInteger(MONTHS_AGO) == false) {
    throw new Error('Specify MONTHS_AGO as an integer between 0 to 2');
  }
  writeToSpreadsheet();
}
function writeToSpreadsheet() {
  let accountIds = getAccountIds();
  if (accountIds.length == 0) return;
  accountIds = getAccountIdsActive(accountIds);
  if (accountIds.length == 0) return;
  let reportData = getSearchReport(accountIds);
  let sh = SpreadsheetApp.openById(SPREAD_SHEET_ID).getSheetByName(SHEET_NAME);
  // Paste the reportData into spreadsheet if the report has more than 2 rows.
  if (reportData.length > 1) {
    sh.clear();
    sh.getRange(1, 1).setValues(reportData);
  }
}
//Get account IDs for search ads linked to MCC
function getAccountIds() {
  let accountIds = [];
  let num = 0;
  while (true) {
    const accountLinks = Search.AccountLinkService.get({
      mccAccountId: mccAccountId,
      accountStatuses: ['SERVING'],
      numberResults: 500,
      startIndex: num * 500 + 1,
    }).rval;
    if (accountLinks.totalNumEntries == 0) break;
    for (let i = 0; i < accountLinks.values.length; i++) {
      accountIds.push(accountLinks.values[i].accountLink.accountId);
    }
    num++;
    if (num * 500 >= accountLinks.totalNumEntries) break;
  }
  return accountIds;
}
//Identify account IDs whose deliveryStatus is active.
function getAccountIdsActive(accountIds) {
  let accountIdsActive = [];
  let num = 0;
  while (true) {
    const accounts = Search.AccountService.get({
      accountIds: accountIds.slice(num * 200, Math.min((num + 1) * 200, accountIds.length)),
    }).rval;
    for (let i = 0; i < accounts.values.length; i++) {
      let account = accounts.values[i].account;
      if (account.deliveryStatus == 'ACTIVE') {
        accountIdsActive.push(account.accountId);
      }
    }
    num++;
    if (num * 200 >= accountIds.length) break;
  }
  return accountIdsActive;
}
function getSearchReport(accountIds) {
  let reportData = [];
  let dateString = getDateString();
  for (let i = 0; i < accountIds.length; i++) {
    const reports = AdsUtilities.getSearchReport({
      accountId: accountIds[i],
      dateRange: {
        startDate: dateString.startDateString,
        endDate: dateString.endDateString,
      },
      reportType: 'ACCOUNT',
      fields: REPORT_FIELDS_YSA,
      reportDateRangeType: 'CUSTOM_DATE',
    }).reports[0].rows;
    reportData = reportData.concat(reports);
  }
  return reportData;
}
//Get the start and end dates of the report
function getDateString() {
  let jstNow = new Date(Date.now() + ((new Date().getTimezoneOffset() + (9 * 60)) * 60 * 1000));
  let day = Utilities.formatDate(jstNow, 'GMT', 'd');
  if (MONTHS_AGO == 0 && day == 1) {
    MONTHS_AGO = 1;
  }
  let startDateString;
  let endDateString;
  if (MONTHS_AGO == 0) {
    jstNow.setDate(jstNow.getDate() - 1);
    endDateString = Utilities.formatDate(jstNow, 'GMT', 'yyyyMMdd');
    jstNow.setDate(1);
    startDateString = Utilities.formatDate(jstNow, 'GMT', 'yyyyMMdd');
  } else {
    jstNow.setDate(1);
    jstNow.setMonth(jstNow.getMonth() - MONTHS_AGO);
    startDateString = Utilities.formatDate(jstNow, 'GMT', 'yyyyMMdd');
    jstNow.setMonth(jstNow.getMonth() + 1);
    jstNow.setDate(jstNow.getDate() - 1);
    endDateString = Utilities.formatDate(jstNow, 'GMT', 'yyyyMMdd');
  }
  return { startDateString: startDateString, endDateString: endDateString };
}