Google Apps Script からFacebook Ads データに連携

詳細情報をご希望ですか?

製品についての詳細情報や無償トライアルをご案内します:

API Server


CData Connect Server を使用してGoogle Apps Script のFacebook Ads データにアクセスします。



Google Apps Script を使用すると、Google Sheets やGoogle Docs を含むGoogle ドキュメント内にカスタム機能を作成できます。CData Connect Server を使用すると、Facebook Ads を含むCData でサポートされている240+ ソースのMySQL インターフェースを取得できます。MySQL プロトコルは、Google Apps Script のJDBC サービスを通じてネイティブにサポートされているため、Connect Server を利用することで、Google ドキュメント内のライブFacebook Ads データにアクセスできます。

この記事では、Connect Server でFacebook Ads の仮想データベースを作成する方法を示し、Google Spreadsheet でFacebook Ads データを処理するためのサンプルスクリプトを提供します。

Facebook Ads データの仮想MySQL データベースを作成する

CData Connect Server は、シンプルなポイントアンドクリックインターフェースを使用してデータソースに接続し、API を生成します。

  1. Connect Server にログインし、[Databases]をクリックします。
  2. [Available Data Sources]から[Facebook Ads]を選択します。
  3. Facebook Ads はOAuth を使用して認証します。 Test Database をクリックして、Facebook Ads で認証します。
  4. [Privileges]->[ Add]とクリックし、適切な権限を持つ新しいユーザー(または既存のユーザー) を追加します。

仮想データベースが作成されたら、すべてのMySQL クライアントからFacebook Ads に接続できるようになります。

Apps Script を用いてのFacebook Ads データへの接続

この時点で、Connect Server でFacebook Ads の仮想データベースを構成しておく必要があります。あとは、Google Apps Script を使ってConnect Server にアクセスし、Google Sheets でサービスを操作するだけです。

このセクションでは、スプレッドシートにFacebook Ads データを入力するためのスクリプト(スクリプトを呼び出すメニューオプション付き)を作成します。サンプルスクリプトを作成し、様々な部分について説明しました。記事の最後に、生のスクリプトを表示できます。

1.空のスクリプトを作成

Google Sheet のスクリプトを作成するには、Google Sheets メニューから[Tools][Script editor]をクリックします。

2.クラス変数を宣言

スクリプトで作成された関数で使用できるようにいくつかのクラス変数を作成します。

//replace the variables in this block with real values as needed
var address = 'CONNECT_SERVER_URL:port';
var user = 'CONNECT_USER';
var userPwd = 'CONNECT_PASSWORD';
var db = 'facebookadsdb';
var serverSslCert = '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----';
var clientSslCert = '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----';
var clientSslKey  = '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----';

var dbUrl = 'jdbc:mysql://' + address + '/' + db + '?useSSL=true';

3.メニューオプションを追加

この関数は、Google Sheet にメニューオプションを追加し、UI を使用して関数を呼び出すことができるようにします。

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name:'Write data to a sheet', functionName: 'connectToFacebookAdsData'}
  ];
  spreadsheet.addMenu('Facebook Ads Data', menuItems);
} 

4.ヘルパー関数の記述

この関数は、スプレッドシートの最初の空の行を検索するために使用します。

/*
 * Finds the first empty row in a spreadsheet by scanning an array of columns
 * @return The row number of the first empty row.
 */
function getFirstEmptyRowByColumnArray(spreadSheet, column) {
  var column = spreadSheet.getRange(column + ":" + column);
  var values = column.getValues(); // get all data in one call
  var ct = 0;
  while ( values[ct] && values[ct][0] != "" ) {
    ct++;
  }
  return(ct+1);
}

5.Facebook Ads データをスプレッドシートに書き込む関数を記述

以下の関数では、Google Apps Script のJDBC 機能を使用してFacebook Ads を記述し、Connect Server に接続してSELECT データを取得し、スプレッドシートにデータを入力します。スクリプトを実行すると、以下の2つの入力ボックスが表示されます。

最初のものは、データを保持するシートの名前を入力するようにユーザーに要求します。(スプレッドシートがない場合、関数によって作成されます。)

もう一つは、読み込むFacebook Ads テーブルの名前を入力するようユーザーに要求します。無効なテーブルを選択するとエラーメッセージが表示され、関数が終了します。

この関数は、メニューオプションとして使用するように設計されていますが、スプレッドシートの式として使用するように拡張できます。

/*
 * Reads data from a specified Facebook Ads 'table' and writes it to the specified sheet.
 *    (If the specified sheet does not exist, it is created.)
 */
function connectToFacebookAdsData() {
  var thisWorkbook = SpreadsheetApp.getActive();

  //select a sheet and create it if it does not exist
  var selectedSheet = Browser.inputBox('Which sheet would you like the data to post to?',Browser.Buttons.OK_CANCEL);
  if (selectedSheet == 'cancel')
    return;

  if (thisWorkbook.getSheetByName(selectedSheet) == null)
    thisWorkbook.insertSheet(selectedSheet);
  var resultSheet = thisWorkbook.getSheetByName(selectedSheet);
  var rowNum = 2;

  //select a Facebook Ads 'table'
  var table = Browser.inputBox('Which table would you like to pull data from?',Browser.Buttons.OK_CANCEL);
  if (table == 'cancel')
    return;

  var conn = Jdbc.getConnection(dbUrl, {
    user: user, 
    password: userPwd, 
    _serverSslCertificate: serverSslCert,
    _clientSslCertificate: clientSslCert,
    _clientSslKey:         clientSslKey
  );

  //confirm that var table is a valid table/view
  var dbMetaData = conn.getMetaData();
  var tableSet = dbMetaData.getTables(null, null, table, null);
  var validTable = false;
  while (tableSet.next()) {
    var tempTable = tableSet.getString(3);
    if (table.toUpperCase() == tempTable.toUpperCase()){
      table = tempTable;
      validTable = true;
      break;
    }
  } 
  tableSet.close();
  if (!validTable) {
    Browser.msgBox("Invalid table name: " + table, Browser.Buttons.OK);
    return;
  }

  var stmt = conn.createStatement();

  var results = stmt.executeQuery('SELECT * FROM ' + table);
  var rsmd = results.getMetaData();
  var numCols = rsmd.getColumnCount();

  //if the sheet is empty, populate the first row with the headers
  var firstEmptyRow = getFirstEmptyRowByColumnArray(resultSheet, "A");
  if (firstEmptyRow == 1) {
    //collect column names
    var headers = new Array(new Array(numCols));
    for (var col = 0; col < numCols; col++){
      headers[0][col] = rsmd.getColumnName(col+1);
    }
    resultSheet.getRange(1, 1, headers.length, headers[0].length).setValues(headers);
  } else {
    rowNum = firstEmptyRow;
  }

  //write rows of Facebook Ads data to the sheet
  var values = new Array(new Array(numCols));
  while (results.next()) {
    for (var col = 0; col < numCols; col++) {
      values[0][col] = results.getString(col + 1);
    }
    resultSheet.getRange(rowNum, 1, 1, numCols).setValues(values);
    rowNum++;
  }

  results.close();
  stmt.close();
}
  

この機能が完了すると、Facebook Ads データが入力されたスプレッドシートが作成され、インターネットにアクセスできるあらゆる場所でGoogle Sheets の計算、グラフ化、チャート作成機能を利用できるようになります。


Google Apps Script の完成

//replace the variables in this block with real values as needed
var address = 'CONNECT_SERVER_URL:port';
var user = 'CONNECT_USER';
var userPwd = 'CONNECT_PASSWORD';
var db = 'facebookadsdb';
var serverSslCert = '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----';
var clientSslCert = '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----';
var clientSslKey  = '-----BEGIN CERTIFICATE-----
...
-----END CERTIFICATE-----';

var dbUrl = 'jdbc:mysql://' + address + '/' + db + '?useSSL=true';

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name:'Write table data to a sheet', functionName: 'connectToFacebookAdsData'}
  ];
  spreadsheet.addMenu('Facebook Ads Data', menuItems);
}

/*
 * Finds the first empty row in a spreadsheet by scanning an array of columns
 * @return The row number of the first empty row.
 */
function getFirstEmptyRowByColumnArray(spreadSheet, column) {
  var column = spreadSheet.getRange(column + ":" + column);
  var values = column.getValues(); // get all data in one call
  var ct = 0;
  while ( values[ct] && values[ct][0] != "" ) {
    ct++;
  }
  return(ct+1);
}

/*
 * Reads data from a specified 'table' and writes it to the specified sheet.
 *    (If the specified sheet does not exist, it is created.)
 */
function connectToFacebookAdsData() {
  var thisWorkbook = SpreadsheetApp.getActive();

  //select a sheet and create it if it does not exist
  var selectedSheet = Browser.inputBox('Which sheet would you like the data to post to?',Browser.Buttons.OK_CANCEL);
  if (selectedSheet == 'cancel')
    return;

  if (thisWorkbook.getSheetByName(selectedSheet) == null)
    thisWorkbook.insertSheet(selectedSheet);
  var resultSheet = thisWorkbook.getSheetByName(selectedSheet);
  var rowNum = 2;

  //select a Facebook Ads 'table'
  var table = Browser.inputBox('Which table would you like to pull data from?',Browser.Buttons.OK_CANCEL);
  if (table == 'cancel')
    return;

  var conn = Jdbc.getConnection(dbUrl, {
    user: user, 
    password: userPwd, 
    _serverSslCertificate: serverSslCert,
    _clientSslCertificate: clientSslCert,
    _clientSslKey:         clientSslKey
  );

  //confirm that var table is a valid table/view
  var dbMetaData = conn.getMetaData();
  var tableSet = dbMetaData.getTables(null, null, table, null);
  var validTable = false;
  while (tableSet.next()) {
    var tempTable = tableSet.getString(3);
    if (table.toUpperCase() == tempTable.toUpperCase()){
      table = tempTable;
      validTable = true;
      break;
    }
  } 
  tableSet.close();
  if (!validTable) {
    Browser.msgBox("Invalid table name: " + table, Browser.Buttons.OK);
    return;
  }

  var stmt = conn.createStatement();

  var results = stmt.executeQuery('SELECT * FROM ' + table);
  var rsmd = results.getMetaData();
  var numCols = rsmd.getColumnCount();

  //if the sheet is empty, populate the first row with the headers
  var firstEmptyRow = getFirstEmptyRowByColumnArray(resultSheet, "A");
  if (firstEmptyRow == 1) {
    //collect column names
    var headers = new Array(new Array(numCols));
    for (var col = 0; col < numCols; col++){
      headers[0][col] = rsmd.getColumnName(col+1);
    }
    resultSheet.getRange(1, 1, headers.length, headers[0].length).setValues(headers);
  } else {
    rowNum = firstEmptyRow;
  }

  //write rows of Facebook Ads data to the sheet
  var values = new Array(new Array(numCols));
  while (results.next()) {
    for (var col = 0; col < numCols; col++) {
      values[0][col] = results.getString(col + 1);
    }
    resultSheet.getRange(rowNum, 1, 1, numCols).setValues(values);
    rowNum++;
  }

  results.close();
  stmt.close();
}