Yahoo! JAPAN Ads Script | Developer Center

Change ad deliver setting (on and off) using spreadsheets

Sumarry

Yahoo! JAPAN Ads Script lets you take various actions for your ads by using GoogleSheets.
This page introduces a script to switch ad delivery settings hourly. Specify the ad name and time to start and stop on Google Sheets.
You can receive notifications through email and Slack when this script switches ad delivery settings.

Before you begin

Specify the header of your spreadsheet as follows:

spreadsheetimage1

Enter the target ad with the start and end times in a line.

spreadsheetimage2

Note

This script will switch all ads with ad names that contain a term you have entered in the column B "Ad name" cell.

For example, all ads that have the following ad name will switch (in the second row of the screenshot).

Ad_A
Ad_A(stops on Nov. 25)
New Ad_A_start on Nov.21

Additionally, specify "Hourly" as a "Frequency" in the script management console.

Sample code

Search Ads


/*
■Script summary
This script sets ads' status "Start State" on the specified start date and time, and "End State" on the specified end date and time.
Additionally, the script will notify users ads' status switch via mail or Slack.
■How to use
１．This script is for Yahoo! JAPAN Ads Search Ads accounts.
２．Set each constant as follows
■Constants
・SPREAD_SHEET_ID：SpreadsheetID
・SPREAD_SHEET_NAME：Spreadsheet name
・FLAG_MAIL：Choose true when using mail notification, when not, choose false.
・MAIL_TO：Yahoo! JAPAN BusinessID to be used for mail notification. The script will send a mail to an address which is linked to the Business ID.
・MAIL_TITLE：Mail title
・FLAG_SLACK：Choose true when using Slack notification, when not, choose false.
・URL_FETCH_APP：Slack Webhook URL
・YOUR_USER_NAME：Slack user name to be used for notification.
・YOUR_CHANNEL_ID：Slack channel ID (name) to be used for notification.
・TEST_EXECUTION：Choose true for test run, when not, choose false.
■Headers
Column A：Account ID　//Specify an account ID for changing ads' status
Column B：Ad Name　//Names of the ads to be switched their status by this script
Column C：Start Date　//Specify the date as "yyyy/M/d" format （e.g. 2001/1/1 or 2022/12/31）
Column D：Start Time　//Specify the time as "H:00" format （e.g. 1:00 or 13:00）
Column E：Start State　//On or Off.
Column F：End Date　//Specify the date as "yyyy/M/d" format （e.g. 2001/1/1 or 2022/12/31）
Column G：End Time　//Specify the time as "H:00" format （e.g. 1:00 or 13:00）
Column H：End State　//On or Off
■Limitations
When Start Date, Start Time and End Date, End Time are the same, the script will switch ads' status based on Start Date and Start Time. 
When multiple rows are used for an operation for the same ad, the script will switch its status based on the smaller number row.
*/
const SPREAD_SHEET_ID = 'SpreadsheetID';
const SPREAD_SHEET_NAME = 'Spreadsheet name';
const FLAG_MAIL = false;
const MAIL_TO = ['Yahoo! JAPAN BusinessID'];
let MAIL_TITLE = '[Yahoo! JAPAN Ads Script Notification] Switched ads delivery status';
const FLAG_SLACK = false;
const URL_FETCH_APP = 'Slack Webhook URL';
const YOUR_USER_NAME = 'Slack user name';
const YOUR_CHANNEL_ID = 'Slack channel ID';
const TEST_EXECUTION = true;
const accountId = AdsUtilities.getCurrentAccountId();
let TEXT_MESSAGE_ARRAY = [];
function main() {
  if (TEST_EXECUTION) {
    Logger.log('*This is test run, the ads delivery status have not changed in practice.');
  }
  try {
    switchAds();
  } catch (e) {
    Logger.log(e);
    MAIL_TITLE += ' Script run has failed';
    TEXT_MESSAGE_ARRAY.push('Script run has failed for some reason. ');
  }
  //Notify via Slack or mail.
  if (TEXT_MESSAGE_ARRAY.length > 0) {
    if (TEST_EXECUTION) {
      TEXT_MESSAGE_ARRAY.unshift('*This is test run, the ads delivery status have not changed in practice.');
    }
    if (FLAG_MAIL) {
      sendMail();
    }
    if (FLAG_SLACK) {
      sendSlack();
    }
  }
}
function switchAds() {
  let sh = SpreadsheetApp.openById(SPREAD_SHEET_ID).getSheetByName(SPREAD_SHEET_NAME);
  let dataArray = sh.getDataRange().getValues();
  //When the spreadsheet has only headers
  if (dataArray.length < 2) {
    logAndMessage('Specify ads status switch condition to the following sheet: ' + SPREAD_SHEET_NAME);
    return;
  }
  let adNamePartToSwitch = getAdNamePartToSwitch(dataArray);
  if (Object.keys(adNamePartToSwitch).length == 0) return;
  let adGroupAdArray = getAdGroupAdArray(adNamePartToSwitch);
  if (adGroupAdArray.length == 0) {
    Logger.log('There was no ad to be switched its status');
    return;
  }
  setAdGroupAds(adGroupAdArray);
}
function logAndMessage(text) {
  Logger.log(text);
  TEXT_MESSAGE_ARRAY.push(text);
}
//Get adNamePartToSwitch from dataArray
function getAdNamePartToSwitch(dataArray) {
  let dayString = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy/M/d');
  let timeString = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'H:00');
  let adNamePartToSwitch = {};
  for (let i = 0; i < dataArray.length; i++) {
    //When account ID is not current account's one
    if (Number(dataArray[i][0]) != accountId) continue;
    //When ad name is empty
    let adNamePart = dataArray[i][1];
    if (adNamePart == '') continue;
    //When ad name is already in adNamePartToSwitch
    if (adNamePart in adNamePartToSwitch) continue;
    if (dayString == dataArray[i][2] && timeString == dataArray[i][3]) {
      adNamePartToSwitch = addAdNamePartToSwitch(dataArray[i][4], adNamePartToSwitch, adNamePart, i + 1, 'Start');
      Logger.log(i + 1 + 'row：' + adNamePart + ',' + dataArray[i][2] + ',' + dataArray[i][3] + ',' + dataArray[i][4]);
      //When only end date and time match running time
    } else if (dayString == dataArray[i][5] && timeString == dataArray[i][6]) {
      adNamePartToSwitch = addAdNamePartToSwitch(dataArray[i][7], adNamePartToSwitch, adNamePart, i + 1, 'End');
      Logger.log(i + 1 + 'row：' + adNamePart + ',' + dataArray[i][5] + ',' + dataArray[i][6] + ',' + dataArray[i][7]);
    }
  }
  return adNamePartToSwitch;
}
//Update adNamePartToSwitch
function addAdNamePartToSwitch(switchString, adNamePartToSwitch, adNamePart, rowNum, startOrEndString) {
  if (switchString == 'On') {
    adNamePartToSwitch[adNamePart] = 'ACTIVE';
  } else if (switchString == 'Off') {
    adNamePartToSwitch[adNamePart] = 'PAUSED';
  } else {
    Logger.log('Specify ' + startOrEndString + ' State on ' + rowNum + 'row ('+ adNamePart +') as On or Off ');
  }
  return adNamePartToSwitch;
}
//Get operand for target ads
function getAdGroupAdArray(adNamePartToSwitch) {
  let adGroupAdArray = [];
  let num = 0;
  while (true) {
    const adGroupAds = Search.AdGroupAdService.get({
      accountId: accountId,
      numberResults: 10000,
      startIndex: num * 10000 + 1,
    }).rval;
    for (let i = 0; i < Object.keys(adGroupAds.values).length; i++) {
      let adGroupAd = adGroupAds.values[i].adGroupAd;
      for (let adNamePart in adNamePartToSwitch) {
        let newUserStatus = adNamePartToSwitch[adNamePart];
        //When adNamePart has the ad name
        if (adGroupAd.adName.indexOf(adNamePart) >= 0) {
          //When the ad's status is different status from adNamePart's one
          if (newUserStatus != adGroupAd.userStatus) {
            adGroupAdArray.push({
              campaignId: adGroupAd.campaignId,
              campaignName: adGroupAd.campaignName,
              adGroupId: adGroupAd.adGroupId,
              adGroupName: adGroupAd.adGroupName,
              adId: adGroupAd.adId,
              adName: adGroupAd.adName,
              userStatus: newUserStatus,
            });
            //When the ad's status is the same as adNamePart's one
          } else {
            if (newUserStatus == 'ACTIVE') {
              logAndMessage(adGroupAd.campaignName + '/' + adGroupAd.adGroupName + '/' + adGroupAd.adName + ' is already On');
            } else {
              logAndMessage(adGroupAd.campaignName + '/' + adGroupAd.adGroupName + '/' + adGroupAd.adName + ' is already Off');
            }
          }
          break;
        }
      }
    }
    num++;
    if (num * 10000 >= adGroupAds.totalNumEntries) break;
  }
  return adGroupAdArray;
}
//Switch ads' status
function setAdGroupAds(adGroupAdArray) {
  if (!TEST_EXECUTION) {
    let num = 0;
    while (true) {
      const adGroupAds = Search.AdGroupAdService.set({
        accountId: accountId,
        operand: adGroupAdArray.slice(num * 2000, Math.min((num + 1) * 2000, adGroupAdArray.length)),
      }).rval;
      for (let i = 0; i < Object.keys(adGroupAds.values).length; i++) {
        if (adGroupAds.values[i].operationSucceeded) {
          let adGroupAd = adGroupAdArray[num * 2000 + i];
          if (adGroupAd.userStatus == 'ACTIVE') {
            logAndMessage(adGroupAd.campaignName + '/' + adGroupAd.adGroupName + '/' + adGroupAd.adName + 'has been changed to On');
          } else {
            logAndMessage(adGroupAd.campaignName + '/' + adGroupAd.adGroupName + '/' + adGroupAd.adName + 'has been changed to Off');
          }
        } else {
          Logger.log('Failed to change delivery status of ' adGroupAd.campaignName + '/' + adGroupAd.adGroupName + '/' + adGroupAd.adName);
        }
      }
      num++;
      if (num * 2000 >= adGroupAdArray.length) break;
    }
  } else {
    for (const ad of adGroupAdArray) {
      const newUserStatus = ad.userStatus == 'ACTIVE' ? 'On' : 'Off';
      logAndMessage(ad.campaignName + '/' + ad.adGroupName + '/' + ad.adName + ' has been changed to ' + newUserStatus);
    }
  }
}
function sendMail() {
  MailApp.sendEmail({
    to: MAIL_TO,
    subject: MAIL_TITLE,
    body: TEXT_MESSAGE_ARRAY.join('\n'),
  });
}
function sendSlack() {
  UrlFetchApp.fetch(URL_FETCH_APP,
    {
      method: 'POST',
      contentType: 'application/json',
      payload: {
        text: TEXT_MESSAGE_ARRAY.join('\n'),
        username: YOUR_USER_NAME,
        channel: YOUR_CHANNEL_ID,
      }
    });
}

Display Ads


/*
■Script summary
This script sets ads' status "Start State" on the specified start date and time, and "End State" on the specified end date and time.
Additionally, the script will notify users ads' status switch via mail or Slack.
■How to use
１．This script is for Yahoo! JAPAN Ads Search Ads accounts.
２．Set each constant as follows:
■Constants
・SPREAD_SHEET_ID：SpreadsheetID
・SPREAD_SHEET_NAME：Spreadsheet name
・FLAG_MAIL：Choose true when using mail notification, when not, choose false.
・MAIL_TO：Yahoo! JAPAN BusinessID to be used for mail notification. The script will send a mail to an address which is linked to the Business ID.
・MAIL_TITLE：Mail title
・FLAG_SLACK：Choose true when using Slack notification, when not, choose false.
・URL_FETCH_APP：Slack Webhook URL
・YOUR_USER_NAME：Slack user name to be used for notification.
・YOUR_CHANNEL_ID：Slack channel ID (name) to be used for notification.
・TEST_EXECUTION：Choose true for test run, when not, choose false.
■Headers
Column A：Account ID　//Specify an account ID for changing ads' status
Column B：Ad Name　//Names of the ads to be switched their status by this script
Column C：Start Date　//Specify the date as "yyyy/M/d" format （e.g. 2001/1/1 or 2022/12/31）
Column D：Start Time　//Specify the time as "H:00" format （e.g. 1:00 or 13:00）
Column E：Start State　//On or Off.
Column F：End Date　//Specify the date as "yyyy/M/d" format （e.g. 2001/1/1 or 2022/12/31）
Column G：End Time　//Specify the time as "H:00" format （e.g. 1:00 or 13:00）
Column H：End State　//On or Off
■Limitations
When Start Date, Start Time and End Date, End Time are the same, the script will switch ads' status based on Start Date and Start Time. 
When multiple rows are used for an operation for the same ad, the script will switch its status based on the smaller number row.
*/
const SPREAD_SHEET_ID = 'SpreadsheetID';
const SPREAD_SHEET_NAME = 'Spreadsheet name';
const FLAG_MAIL = false;
const MAIL_TO = ['Yahoo! JAPAN BusinessID'];
let MAIL_TITLE = '[Yahoo! JAPAN Ads Script Notification] Switched ads delivery status';
const FLAG_SLACK = false;
const URL_FETCH_APP = 'Slack Webhook URL';
const YOUR_USER_NAME = 'Slack user name';
const YOUR_CHANNEL_ID = 'Slack channel ID';
const TEST_EXECUTION = true;
const accountId = AdsUtilities.getCurrentAccountId();
let TEXT_MESSAGE_ARRAY = [];
function main() {
  if (TEST_EXECUTION) {
    Logger.log('*This is test run, the ads delivery status have not changed in practice.');
  }
  try {
    switchAds();
  } catch (e) {
    Logger.log(e);
    MAIL_TITLE += ' Script run has failed';
    TEXT_MESSAGE_ARRAY.push('Script run has failed for some reason. ');
  }
  //Notify via Slack or mail.
  if (TEXT_MESSAGE_ARRAY.length > 0) {
    if(TEST_EXECUTION) {
      TEXT_MESSAGE_ARRAY.unshift('*This is test run, the ads delivery status have not changed in practice.');
    }
    if (FLAG_MAIL) {
      sendMail();
    }
    if (FLAG_SLACK) {
      sendSlack();
    }
  }
}
function switchAds() {
  let sh = SpreadsheetApp.openById(SPREAD_SHEET_ID).getSheetByName(SPREAD_SHEET_NAME);
  let dataArray = sh.getDataRange().getValues();
  //When the spreadsheet has only headers
  if (dataArray.length < 2) {
    logAndMessage('Specify ads status switch condition to the following sheet: ' + SPREAD_SHEET_NAME);
    return;
  }
  let adNamePartToSwitch = getAdNamePartToSwitch(dataArray);
  if (Object.keys(adNamePartToSwitch).length == 0) return;
  let adGroupAdArray = getAdGroupAdArray(adNamePartToSwitch);
  if (adGroupAdArray.length == 0) {
    Logger.log('There was no ad to be switched its status');
    return;
  }
  setAdGroupAds(adGroupAdArray);
}
function logAndMessage(text) {
  Logger.log(text);
  TEXT_MESSAGE_ARRAY.push(text);
}
//Get adNamePartToSwitch from dataArray
function getAdNamePartToSwitch(dataArray) {
  let dayString = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy/M/d');
  let timeString = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'H:00');
  let adNamePartToSwitch = {};
  for (let i = 0; i < dataArray.length; i++) {
    //When account ID is not current account's one
    if (Number(dataArray[i][0]) != accountId) continue;
    //When ad name is empty
    let adNamePart = dataArray[i][1];
    if (adNamePart == '') continue;
    //When ad name is already in adNamePartToSwitch
    if (adNamePart in adNamePartToSwitch) continue;
    if (dayString == dataArray[i][2] && timeString == dataArray[i][3]) {
      adNamePartToSwitch = addAdNamePartToSwitch(dataArray[i][4], adNamePartToSwitch, adNamePart, i + 1, 'Start');
      Logger.log(i + 1 + 'row：' + adNamePart + ',' + dataArray[i][2] + ',' + dataArray[i][3] + ',' + dataArray[i][4]);
      //When only end date and time match running time
    } else if (dayString == dataArray[i][5] && timeString == dataArray[i][6]) {
      adNamePartToSwitch = addAdNamePartToSwitch(dataArray[i][7], adNamePartToSwitch, adNamePart, i + 1, 'End');
      Logger.log(i + 1 + 'row：' + adNamePart + ',' + dataArray[i][5] + ',' + dataArray[i][6] + ',' + dataArray[i][7]);
    }
  }
  return adNamePartToSwitch;
}
//Update adNamePartToSwitch
function addAdNamePartToSwitch(switchString, adNamePartToSwitch, adNamePart, rowNum, startOrEndString) {
  if (switchString == 'オン') {
    adNamePartToSwitch[adNamePart] = 'ACTIVE';
  } else if (switchString == 'オフ') {
    adNamePartToSwitch[adNamePart] = 'PAUSED';
  } else {
    Logger.log('Specify ' + startOrEndString + ' State on ' + rowNum + 'row ('+ adNamePart +') as On or Off');
  }
  return adNamePartToSwitch;
}
//Get operand for target ads
function getAdGroupAdArray(adNamePartToSwitch) {
  let adGroupAdArray = [];
  let num = 0;
  while (true) {
    const adGroupAds = Display.AdGroupAdService.get({
      accountId: accountId,
      numberResults: 10000,
      startIndex: num * 10000 + 1,
    }).rval;
    for (let i = 0; i < Object.keys(adGroupAds.values).length; i++) {
      let adGroupAd = adGroupAds.values[i].adGroupAd;
      for (let adNamePart in adNamePartToSwitch) {
        let newUserStatus = adNamePartToSwitch[adNamePart];
        //When adNamePart has the ad name
        if (adGroupAd.adName.indexOf(adNamePart) >= 0) {
          //When the ad's status is different status from adNamePart's one
          if (newUserStatus != adGroupAd.userStatus) {
            adGroupAdArray.push({
              campaignId: adGroupAd.campaignId,
              campaignName: adGroupAd.campaignName,
              adGroupId: adGroupAd.adGroupId,
              adGroupName: adGroupAd.adGroupName,
              adId: adGroupAd.adId,
              adName: adGroupAd.adName,
              userStatus: newUserStatus,
            });
            //When the ad's status is the same as adNamePart's one
          } else {
            if (newUserStatus == 'ACTIVE') {
              logAndMessage(adGroupAd.campaignName + '/' + adGroupAd.adGroupName + '/' + adGroupAd.adName + ' is already On');
            } else {
              logAndMessage(adGroupAd.campaignName + '/' + adGroupAd.adGroupName + '/' + adGroupAd.adName + ' is already Off');
            }
          }
          break;
        }
      }
    }
    num++;
    if (num * 10000 >= adGroupAds.totalNumEntries) break;
  }
  return adGroupAdArray;
}
//Switch ads' status
function setAdGroupAds(adGroupAdArray) {
  if (!TEST_EXECUTION) {
    let num = 0;
    while (true) {
      const adGroupAds = Display.AdGroupAdService.set({
        accountId: accountId,
        operand: adGroupAdArray.slice(num * 2000, Math.min((num + 1) * 2000, adGroupAdArray.length)),
      }).rval;
      for (let i = 0; i < Object.keys(adGroupAds.values).length; i++) {
        if (adGroupAds.values[i].operationSucceeded) {
          let adGroupAd = adGroupAdArray[num * 2000 + i];
          if (adGroupAd.userStatus == 'ACTIVE') {
            logAndMessage(adGroupAd.campaignName + '/' + adGroupAd.adGroupName + '/' + adGroupAd.adName + 'has been changed to On');
          } else {
            logAndMessage(adGroupAd.campaignName + '/' + adGroupAd.adGroupName + '/' + adGroupAd.adName + 'has been changed to Off');
          }
        } else {
          Logger.log('Failed to change delivery status of ' adGroupAd.campaignName + '/' + adGroupAd.adGroupName + '/' + adGroupAd.adName);
        }
      }
      num++;
      if (num * 2000 >= adGroupAdArray.length) break;
    }
  } else {
    for (const ad of adGroupAdArray) {
      const newUserStatus = ad.userStatus == 'ACTIVE' ? 'On' : 'Off';
      logAndMessage(ad.campaignName + '/' + ad.adGroupName + '/' + ad.adName + ' has been changed to ' + newUserStatus);
    }
  }
}
function sendMail() {
  MailApp.sendEmail({
    to: MAIL_TO,
    subject: MAIL_TITLE,
    body: TEXT_MESSAGE_ARRAY.join('\n'),
  });
}
function sendSlack() {
  UrlFetchApp.fetch(URL_FETCH_APP,
    {
      method: 'POST',
      contentType: 'application/json',
      payload: {
        text: TEXT_MESSAGE_ARRAY.join('\n'),
        username: YOUR_USER_NAME,
        channel: YOUR_CHANNEL_ID,
      }
    });
}

Developer Center

Related sites: