Microsoft Ads（Bing Ads）Scriptを使って、スプレッドシートに配信レポートを出力する方法

業務効率化
公開日：2024年8月14日更新日：2024年8月15日

Microsoft Ads（Bing Ads）は、BtoBマーケティングにおいて非常に重要なチャネルの一つです。

Statcounterによると、2024年5月にBingは、日本における検索エンジンのシェアランキングで2位となり、これまでGoogleに次いで2位だったYahoo!は3位に低下しました。

そしてMicrosoft Adsは、Google広告やMeta広告からキャンペーン設定等をインポートすることが可能となっており、既にGoogleやMetaで広告を配信している広告主にとっては、簡単な設定ですぐ配信が開始できる「ちょうどいい」広告媒体でもあります。

しかしながら、まだGoogle・Yahooに比べ利用企業が少ないため、運用のTipsなども少ない状況です。

そのため今回は、苦戦しがちなMicrosoft Ads Scriptを使って、デイリーレポートをGoogleスプレッドシートに出力する方法について紹介したいと思います。

Microsoft Ads Scriptを使った自動化が一筋縄ではいかない理由

Google広告やYahoo広告は、Googleスプレッドシートへの出力に関する認証プロセスが用意されているため、スクリプトさえ書ければ比較的簡単にデータをエクスポートすることができます。

例えると、Google広告やYahoo広告はスプレッドシートへの入場パスを用意しているのに対し、Microsoft Adsの場合は専用の入場パスが用意されていません。

アクセス権（入場パス）が必要

なので、こちら側でスプレッドシートへの入場パスを用意する必要があります。

そのため、Microsoft Ads Scriptを使って、Googleスプレッドシートにアカウント内のデータを出力するためには以下のプロセスが必要です。

スプレッドシートへの入場パスを用意する
入場パスを永続的に使えるようにする
Microsoft Adsの実績を取得する
入場パスを使って、スプレッドシートに書き込みを行う

今回の手順では、Google Cloud Platformのアカウントが必要になるので注意してください。

定期実行に関してはMicrosoft Ads Scriptの機能として備わっているので、上記手順を踏まえて、お好みのタイミングで実行させてください。

実装の準備

Google Cloud Platformの設定

まずGoogle Cloud Platformのプロジェクトを作成します。

次に検索バーからGoogle Sheets APIを検索し、クリックします。

Sheets APIの有効化

まだAPIが有効化されていない場合、有効にするボタンを押します。

Sheets APIの有効にするボタンをクリック

同様の手順を、Google Drive APIでもおこないます。

これで初期設定は完了です。

OAuth 同意画面の設定

次に、Google Cloud PlatformでOAuth同意画面の設定をおこないます。このプロセスは、入場パスを発行する前の下準備です。

APIの設定と同じく、検索窓からAPIとサービスを検索し、クリックします。

APIとサービス

次に左メニューから、OAuth同意画面を選択します。

英語の画面に遷移するので、Externalをクリックして、次に進みます。

OAuth同意画面のExternalを選択

いくつか設定項目がありますが、必須項目は以下3つのみです。

アプリ名
ユーザーサポートメール
連絡先メールアドレス

アプリ名は分かりやすいもので大丈夫です。メールアドレスも普段利用しているものを設定しましょう。

基本情報を入力したら次はスコープの設定をおこないます。

スコープの選択

スコープは、先ほど有効にした以下2つのAPIを追加しましょう。

Google Sheets API
Google Drive API

最後に、テストユーザーの追加ですが、これも念の為、普段利用しているものを設定しておきましょう。

これでOAuth同意画面の設定は完了です。

OAuth 2.0 クライアントIDの発行

次にOAuthのクライアントIDを発行します。

APIとサービスの画面上部にある認証情報の作成から、OAuthクライアントIDをクリックします。

OAuthクライアントIDの作成

まずはじめに「アプリケーションの種類」を選ぶ必要がありますが、こちらはウェブアプリケーションを選択しましょう。

次に名前を自由入力できますが、これは管理をするためのものなので、自由に名前をつけて構いません。

ここでは「MicrosoftAds」としておきます。

OauthクライアントIDの詳細設定

他の「承認済みの JavaScript 生成元」と「承認済みのリダイレクト URI」は入力せずに作成ボタンを押しましょう。

すると「OAuthクライアントを作成しました」というモーダルが表示されるので、表示されたクライアントIDとクライアントシークレットを保存しておきましょう。

クライアントID・クライアントシークレットの取得

リフレッシュトークンの発行

次に、先ほど取得したクライアントIDとクライアントシークレットを用いてリフレッシュトークンを発行します。

本来はアクセストークンが、いわゆる入場パスなのですが、アクセストークンには有効期限があり、永続的に使えるものではありません。

そのためリフレッシュトークンというものを発行し、リフレッシュトークンを用いて、アクセスが必要な時に都度アクセストークンを発行します。

まずは以下のURLにアクセスします。

https://developers.google.com/oauthplayground/

次に、「Step 1Select & authorize APIs」の中からGoogle Sheets API v4を見つけます。

見つけたらトグルボタンをクリックし、中にあるURLすべてにチェックをいれます。

認証情報の作成

チェックをいれたらAuthorize APIsをクリックします。

すると以下のような認証画面に入るので、自分のアカウントを選択し、その後「許可」ボタンをクリックします。

認証ステップ1

認証ステップ2

認証が完了すると元の画面に戻り、「Authorization code」という項目に値が入っている状態になると思います。

Exchange authorization code for tokensというボタンをクリックすると、リフレッシュトークンとアクセストークンが取得できます。

リフレッシュトークンの取得

今回取得したいのはリフレッシュトークンなので、このリフレッシュトークンを保存して、いよいよスクリプトの実行に移ります。

Microsoft Ads Scriptでの実装

スクリプトの設定画面へのアクセス

Microsoft Adsのデフォルトの画面では、おそらくスクリプト画面にたどり着きづらいと思います。

探し方は左メニューの「ツール」から「スクリプト」と検索しましょう。

MicrosoftAds Script

ピン留めマークがあるので、そちらを有効にしておくと、左メニューからアクセスできるようになります。

「一括操作」のページにアクセスし、「スクリプト」を選択して「スクリプトを作成」ボタンをクリックして、コードを入力していきます。

リフレッシュトークンを用いたアクセストークンの取得

まずはじめに、リフレッシュトークンを用いてアクセストークンを取得します。

先ほど取得したクライアントID・クライアントシークレット・リフレッシュトークンを変数として設定します。

var CLIENT_ID = '********************';
var CLIENT_SECRET = '********************';
var REFRESH_TOKEN = '********************';

見ての通り、直接このような情報をコードに記載することは本来好ましくありませんが、Microsoft Ads Scriptは環境変数の設定などができないので致し方なしです。

Microsoft Adsへのアクセス権の管理には注意を払っておく必要があります。

次にこの情報を用いて、アクセストークンを取得します。

function getAccessToken() {
  var url = 'https://oauth2.googleapis.com/token';
  var payload = {
    client_id: CLIENT_ID,
    client_secret: CLIENT_SECRET,
    refresh_token: REFRESH_TOKEN,
    grant_type: 'refresh_token'
  };

  var options = {
    method: 'POST',
    contentType: 'application/x-www-form-urlencoded',
    payload: Object.keys(payload).map(function(key) {
      return encodeURIComponent(key) + '=' + encodeURIComponent(payload[key]);
    }).join('&')
  };

  var response = UrlFetchApp.fetch(url, options);
  var token = JSON.parse(response.getContentText());
  return token.access_token;
}

クライアントIDやクライアントシークレットなどをグローバル変数にする必然性はないのですが、認証系でエラーが出やすいので、一番わかりやすい場所に配置しています。

取得するデータを決める

アクセストークンの取得の次は、Microsoft Adsからどのようなデータを取得するか決める必要があります。

以下のコードサンプルでは、日付・キャンペーン名・表示回数・クリック・費用としています。

function fetchCampaignData() {
  var data = [['Date', 'Campaign Name', 'Impressions', 'Clicks', 'Spend']];
  var startDate = new Date('2024-07-01');
  var endDate = new Date();
  endDate.setDate(endDate.getDate() - 1);

  var currentDate = new Date(startDate);

  while (currentDate <= endDate) {
    var year = currentDate.getFullYear();
    var month = ('0' + (currentDate.getMonth() + 1)).slice(-2);
    var day = ('0' + currentDate.getDate()).slice(-2);
    var dateString = year + '/' + month + '/' + day;

    var dateFrom = {year: year, month: currentDate.getMonth() + 1, day: currentDate.getDate()};
    var dateTo = {year: year, month: currentDate.getMonth() + 1, day: currentDate.getDate()};

    var campaigns = AdsApp.campaigns().forDateRange(dateFrom, dateTo).get();

    while (campaigns.hasNext()) {
      var campaign = campaigns.next();
      var stats = campaign.getStats();
      data.push([
        dateString,
        campaign.getName(),
        stats.getImpressions(),
        stats.getClicks(),
        stats.getCost()
      ]);
    }

    currentDate.setDate(currentDate.getDate() + 1);
  }
  return data;
}

実用する場合に、修正すべき点だけ解説をします。

まず最初の変数であるdataは、出力する際のデータの見出しを指定しています。

これは任意の文字列で構いませんが、ここで指定するカラム数が、あとで指定するAPI経由で取得するデータのカラム数と一致していないとエラーが出ます。

var data = [['Date', 'Campaign Name', 'Impressions', 'Clicks', 'Spend']];

例えばコンバージョンのカラムも追加したいという場合は、以下のように変更します。

var data = [['Date', 'Campaign Name', 'Impressions', 'Clicks', 'Spend', 'Conversions']];

次に、データ取得の範囲を設定します。

現在のコードだと、2024年7月1日～今日までのデータにしています。

var startDate = new Date('2024-07-01');
var endDate = new Date();

データ量に応じて変更してください。

最後に取得するデータを設定します。

具体的にはcampaign.getStats();でキャンペーン別の実績を取得しており、このStatsオブジェクトで取得できるデータは以下URLで確認ができます。

https://learn.microsoft.com/ja-jp/advertising/scripts/reference/stats

while (campaigns.hasNext()) {
  var campaign = campaigns.next();
  var stats = campaign.getStats();
  data.push([
    dateString, //日付
    campaign.getName(),  //キャンペーン名
    stats.getImpressions(),  //表示回数
    stats.getClicks(),  //クリック数
    stats.getCost()  //費用
  ]);
}

「コンバージョンのデータも追加したい」という場合は、こちらのURLを元にコンバージョンに該当するメソッドを探します。

コンバージョンはstats.getConversions();で取得できるようなので、stats.getConversions();を追記します。

while (campaigns.hasNext()) {
  var campaign = campaigns.next();
  var stats = campaign.getStats();
  data.push([
    dateString,
    campaign.getName(),
    stats.getImpressions(),
    stats.getClicks(),
    stats.getCost(),
    stats.getConversions() //コンバージョン追記
  ]);
}

スプレッドシートの処理を用意する

取得するデータを決めたら、次はスプレッドシートの処理を用意します。

今回は毎日最新情報に更新するために、以下の2つの処理を実行します。

現在のスプレッドシート内の特定のシートの内容を全部削除
新しいデータを出力

最後の行に足す等の処理がないので非常にシンプルで簡単ですが、エラーが発生すると全データ消える可能性があるのが難点です。

まずはスプレッドシートの全削除からです。Sheets APIのclearを用います。

function clearSheet(spreadsheetId, sheetName, accessToken) {
  var url = 'https://sheets.googleapis.com/v4/spreadsheets/' + spreadsheetId + '/values/' + encodeURIComponent(sheetName) + '!A1:E10000:clear';
  var options = {
    method: 'POST',
    headers: {
      'Authorization': 'Bearer ' + accessToken,
    }
  };
  UrlFetchApp.fetch(url, options);
}

var urlの最後で「A1:E10000:clear」としている部分が範囲の指定と指示内容です。

A1:E10000は任意の範囲なので、自由に設定してください。

またSheets APIを利用する場合、アクセストークンが必要なので、引数としてアクセストークンを設定しています。

続いて、取得したデータを貼り付ける関数です。

function writeToSheet(spreadsheetId, sheetName, data, accessToken) {
  var url = 'https://sheets.googleapis.com/v4/spreadsheets/' + spreadsheetId + '/values/' + sheetName + '!A1:append?valueInputOption=USER_ENTERED';
  var options = {
    method: 'POST',
    contentType: 'application/json',
    headers: {
      'Authorization': 'Bearer ' + accessToken,
    },
    payload: JSON.stringify({
      values: data
    })
  };
  var response = UrlFetchApp.fetch(url, options);
  Logger.log(response.getContentText());
}

ポイントはvalueInputOption=USER_ENTEREDにしている点です。

これによって貼付け先の書式などが維持された状態になります。

またこちらでもA1と指定していますが、これも好きな場所に変更して問題ありません。

メインの実行

最後にこれらの関数を実行するコードです。

function main() {
  var sheetId = '************************************';
  var sheetName = '******';

  var accessToken = getAccessToken();
  if (!accessToken) {
    Logger.log('アクセストークンの取得に失敗しました');
    return;
  }

  var data = fetchCampaignData();
  if (!data || data.length === 0) {
    Logger.log('データがありません');
    return;
  }

  clearSheet(sheetId, sheetName, accessToken);
  writeToSheet(sheetId, sheetName, data, accessToken);
}

sheetIdではスプレッドシートの以下のハイライトした部分を指定します。

https://docs.google.com/spreadsheets/d/********************************************/edit?usp=sharing

sheetNameではスプレッドシート内のシート名を直接入力します。（デフォルトでは「シート1」となっている部分です）

あとはこれまで作成してきた関数を順番に実行していきます。

コードの全文

最後に、今回紹介したコードの全文です。

途中で伝えたように、このコード内に直接クライアントIDやクライアントシークレット、リフレッシュトークンを記述する必要があるので、Microsoft Adsへのアクセス権限には細心の注意をはらっておきましょう。

var CLIENT_ID = '********************';
var CLIENT_SECRET = '********************';
var REFRESH_TOKEN = '********************';

function getAccessToken() {
  var url = 'https://oauth2.googleapis.com/token';
  var payload = {
    client_id: CLIENT_ID,
    client_secret: CLIENT_SECRET,
    refresh_token: REFRESH_TOKEN,
    grant_type: 'refresh_token'
  };

  var options = {
    method: 'POST',
    contentType: 'application/x-www-form-urlencoded',
    payload: Object.keys(payload).map(function(key) {
      return encodeURIComponent(key) + '=' + encodeURIComponent(payload[key]);
    }).join('&')
  };

  var response = UrlFetchApp.fetch(url, options);
  var token = JSON.parse(response.getContentText());
  return token.access_token;
}

function fetchCampaignData() {
  var data = [['Date', 'Campaign Name', 'Impressions', 'Clicks', 'Spend']];
  var startDate = new Date('2024-07-01');
  var endDate = new Date();
  endDate.setDate(endDate.getDate() - 1);

  var currentDate = new Date(startDate);

  while (currentDate <= endDate) {
    var year = currentDate.getFullYear();
    var month = ('0' + (currentDate.getMonth() + 1)).slice(-2);
    var day = ('0' + currentDate.getDate()).slice(-2);
    var dateString = year + '/' + month + '/' + day;

    var dateFrom = {year: year, month: currentDate.getMonth() + 1, day: currentDate.getDate()};
    var dateTo = {year: year, month: currentDate.getMonth() + 1, day: currentDate.getDate()};

    var campaigns = AdsApp.campaigns().forDateRange(dateFrom, dateTo).get();

    while (campaigns.hasNext()) {
      var campaign = campaigns.next();
      var stats = campaign.getStats();
      data.push([
        dateString,
        campaign.getName(),
        stats.getImpressions(),
        stats.getClicks(),
        stats.getCost()
      ]);
    }

    currentDate.setDate(currentDate.getDate() + 1);
  }
  return data;
}

function clearSheet(spreadsheetId, sheetName, accessToken) {
  var url = 'https://sheets.googleapis.com/v4/spreadsheets/' + spreadsheetId + '/values/' + encodeURIComponent(sheetName) + '!A1:E10000:clear';
  var options = {
    method: 'POST',
    headers: {
      'Authorization': 'Bearer ' + accessToken,
    }
  };
  UrlFetchApp.fetch(url, options);
}

function writeToSheet(spreadsheetId, sheetName, data, accessToken) {
  var url = 'https://sheets.googleapis.com/v4/spreadsheets/' + spreadsheetId + '/values/' + sheetName + '!A1:append?valueInputOption=USER_ENTERED';
  var options = {
    method: 'POST',
    contentType: 'application/json',
    headers: {
      'Authorization': 'Bearer ' + accessToken,
    },
    payload: JSON.stringify({
      values: data
    })
  };
  var response = UrlFetchApp.fetch(url, options);
  Logger.log(response.getContentText());
}


function main() {
  var sheetId = '********************';
  var sheetName = '*****;

  var accessToken = getAccessToken();
  if (!accessToken) {
    Logger.log('アクセストークンの取得に失敗しました');
    return;
  }

  var data = fetchCampaignData();
  if (!data || data.length === 0) {
    Logger.log('データがありません');
    return;
  }

  clearSheet(sheetId, sheetName, accessToken);
  writeToSheet(sheetId, sheetName, data, accessToken);
}