※本サイトはプロモーションが含まれています

Google App Script

毎月のアクセスレポートのテキストを自動生成してみた。~②転記した情報を加工して、シートにまとめる~

  /
記事内に商品プロモーションを含む場合があります
制作の流れ
  1. 共通の設定シートを作る
  2. お客様ごとの設定シートを作る
  3. Google Analyticsの情報を転記したスプレッドシートから情報を持ってくる
  4. Gmailの下書きを作成する
もくじ
  1. 共通の設定シートを作る
  2. お客様ごとの設定シートを作る
  3. Google Analyticsの情報を転記したスプレッドシートから情報を持ってくる
  4. Gmailの下書きを作成する
  5. まとめ
  6. 補足

共通の設定シートを作る

まずは、メールを送信するために必要になる共通のテンプレートを作成していきます。

今回メールを作る上で共通なのは「タイトル」と「文章」となり、以下の設定ファイルに記載します。

=”{会社名}
{名前}様

いつもお世話になっております。
xx株式会社です。

“&””&MONTH(TODAY())-1&”月分のアクセスレポートを添付しております。

“&MONTH(TODAY())-1&”月のページビュー数は{先月のページビュー数}回です。
“&MONTH(TODAY())-1&”月の訪問者数は{先月の訪問者数}人です。

デバイス別のアクセスデータは、スマホからのアクセスが{SPのアクセス}、
PCからのアクセスが{PCのアクセス}、タブレットからのアクセスが{TBのアクセス}となっております。

また、当社で最近手掛けたサイトのご紹介をさせていただきます。
○サイト名　URL（x月xx日公開）

何かご不明な点などございましたら、お気軽にご連絡ください。
引き続き、どうぞよろしくお願いいたします。”

お客様ごとの設定シートを作る

メール送信に必要なto,ccはもちろん、メール文章に当て込みたい社名や宛名も設定します。

Google Analyticsの情報を転記したスプレッドシートから情報を持ってくる

ツール > スクリプトエディタに移動し、スクリプトを書けるエディタを表示します。

持ってくる関数を作る

スクリプトエディタに、GAのスプレッドシートからデータを持ってくるプログラムを設定します。

// GAのスプレッドシートからデータを持ってくる
function analyticsCopypast(copyPlace,pastePlace) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var analyticsApp = SpreadsheetApp.openById('シートのID名');
  var sheetName = SpreadsheetApp.getActiveSheet().getName();
  var analyticsSheet = analyticsApp.getSheetByName(sheetName);
  var rng;
  rng = analyticsSheet.getRange(copyPlace).getValues();
  ss.getRange(pastePlace).setValues(rng);//<-同じ範囲を指定する必要がある。
}

転記する関数を作る

GAのスプレッドシートからデータを持ってきたものを転記するプログラムを記載します。

// 持ってきたデータを貼り付け
function copypastAnalyticsData() {
  
  //ユーザー(訪問者)数
  analyticsCopypast("B12","B7");
  
  //ページビュー数
  analyticsCopypast("C12","B6");

  //セッション数
  analyticsCopypast("D12","B9");
  
  //デバイスごとのセッション数
  analyticsCopypast("D16:D18","B10:B12");  
}

実行する関数を作る

スプレッドシート上で実行できるようにメニューを追加するプログラムを追加します。

// スプレッドシートの起動時にメニューに追加する
function onOpen(){ 
  let ssheet = SpreadsheetApp.getActiveSpreadsheet();
  let menu = [{name: '1.データ転記', functionName: 'copypastAnalyticsData'}]; 
  ssheet.addMenu('下書きを作成', menu); // メニューを作成する  
}

Gmailの下書きを作成する

さて、ここから、文章を作成する段階に入っていきます。

文を作成する関数を作る

GAのスプレッドシートで設定したデータを転記して文章を作成します。

function createMessage() {
  // シートを定義
  const selectSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const settingSpreadSeet = selectSpreadSheet.getSheetByName("メール設定");

  const company = selectSpreadSheet.getRange("B1").getDisplayValue();
  const name = selectSpreadSheet.getRange("B2").getDisplayValue();

  const pageView = selectSpreadSheet.getRange("B6").getDisplayValue();
  const pageVisitor = selectSpreadSheet.getRange("B7").getDisplayValue();

  const devicePC = selectSpreadSheet.getRange("C10").getDisplayValue();
  const deviceSP = selectSpreadSheet.getRange("C11").getDisplayValue();
  const deviceTB = selectSpreadSheet.getRange("C12").getDisplayValue();

  let mailText = settingSpreadSeet.getRange("B2").getValues();

  // 文章作成
  mailText = String(mailText); 
  mailText = mailText
                    .replace('\{会社名\}', company)
                    .replace('\{名前\}', name)
                    .replace('\{先月のページビュー数\}', pageView)
                    .replace('\{先月の訪問者数\}',pageVisitor)
                    .replace('\{PCのアクセス\}',devicePC)
                    .replace('\{SPのアクセス\}',deviceSP)
                    .replace('\{TBのアクセス\}',deviceTB);

  // メール作成
  return mailText;
}

メールを送信する関数を作る

メールを送信する関数を設定します。

function createDraft() {
  // シートを定義
  const selectSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const settingSpreadSeet = selectSpreadSheet.getSheetByName("メール設定");
  
  // 数値を定義
  const to = selectSpreadSheet.getRange("B3").getValue();
  const cc = selectSpreadSheet.getRange("B4").getValue();
  const subject = settingSpreadSeet.getRange("B1").getValue();
  const message = createMessage();

  // メールの下書きを作成
  GmailApp.createDraft(to, subject, message, {cc: cc});
}

実行する関数を作る

メニューに追加します。

// スプレッドシートの起動時にメニューに追加する
function onOpen(){ 
  // シートを定義
  var ssheet = SpreadsheetApp.getActiveSpreadsheet(); 
  
  // メニューを追加する
  var menu = [
                      {name: '1.データ転記', functionName: 'copypastAnalyticsData'},
                      {name: '2.下書きを作成', functionName: 'createDraft'}
                    ];
  ssheet.addMenu('下書きを作成', menu);
}

実行するときはデータを転記→下書き作成といったボタンを順番に押すことで、実行されます。

まとめ

今回書いたコードはこちらです。


// 持ってきたデータを貼り付け
function copypastAnalyticsData() {
  
  //ユーザー(訪問者)数
  analyticsCopypast("B12","B7");
  
  //ページビュー数
  analyticsCopypast("C12","B6");

  //セッション数
  analyticsCopypast("D12","B9");
  
  //デバイスごとのセッション数
  analyticsCopypast("D16:D18","B10:B12");  
}

// GAのスプレッドシートからデータを持ってくる
function analyticsCopypast(copyPlace,pastePlace) {
  const selectSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  let sheetName = SpreadsheetApp.getActiveSheet().getName();  
  let analyticsSheet = SpreadsheetApp.openById('シートのID名');
  analyticsSheet = analyticsSheet.getSheetByName(sheetName);
  const analyticsValus = analyticsSheet.getRange(copyPlace).getValues();
  selectSpreadSheet.getRange(pastePlace).setValues(analyticsValus);//<-同じ範囲を指定する必要がある。
}

function createMessage() {
  // シートを定義
  const selectSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const settingSpreadSeet = selectSpreadSheet.getSheetByName("メール設定");

  const company = selectSpreadSheet.getRange("B1").getDisplayValue();
  const name = selectSpreadSheet.getRange("B2").getDisplayValue();

  const pageView = selectSpreadSheet.getRange("B6").getDisplayValue();
  const pageVisitor = selectSpreadSheet.getRange("B7").getDisplayValue();

  const devicePC = selectSpreadSheet.getRange("C10").getDisplayValue();
  const deviceSP = selectSpreadSheet.getRange("C11").getDisplayValue();
  const deviceTB = selectSpreadSheet.getRange("C12").getDisplayValue();

  let mailText = settingSpreadSeet.getRange("B2").getValues();
  // 文章作成
  mailText = String(mailText); 
  mailText = mailText
                    .replace('\{会社名\}', company)
                    .replace('\{名前\}', name)
                    .replace('\{先月のページビュー数\}', pageView)
                    .replace('\{先月の訪問者数\}',pageVisitor)
                    .replace('\{PCのアクセス\}',devicePC)
                    .replace('\{SPのアクセス\}',deviceSP)
                    .replace('\{TBのアクセス\}',deviceTB);

  // メール作成
  return mailText;
}

function createDraft() {
  // シートを定義
  const selectSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  const settingSpreadSeet = selectSpreadSheet.getSheetByName("メール設定");
  
  // 数値を定義
  const to = selectSpreadSheet.getRange("B3").getValue();
  const cc = selectSpreadSheet.getRange("B4").getValue();
  const subject = settingSpreadSeet.getRange("B1").getValue();
  const message = createMessage();

  GmailApp.createDraft(to, subject, message, {cc: cc});
}

// スプレッドシートの起動時にメニューに追加する
function onOpen(){ 
  const selectSpreadSheet = SpreadsheetApp.getActiveSpreadsheet(); // アクティブシート
  let menu = [{name: '1.データ転記', functionName: 'copypastAnalyticsData'},{name: '2.下書きを作成', functionName: 'createDraft'}]; // メニューの項目を作成する      
  selectSpreadSheet.addMenu('下書きを作成', menu); // メニューを作成する  
}

// スプレッドシートの起動時にメニューに追加する
function onOpen(){ 
  // シートを定義
  var ssheet = SpreadsheetApp.getActiveSpreadsheet(); 
  
  // メニューを追加する
  var menu = [
                      {name: '1.データ転記', functionName: 'copypastAnalyticsData'},
                      {name: '2.下書きを作成', functionName: 'createDraft'}
                    ];
  ssheet.addMenu('下書きを作成', menu);
}

補足

書き始めてからかなり時間がたってから公開したので、補足等が不十分かと思います。

ご了承ください。

ABOUT ME
あやおり子
特技はプログラミングのWEBデザイナーです。 WEB制作会社に勤めていた経験を生かして、 ちょっとマニアックな記事を作成しています。
BLOG：https://ayaoriko.com
関連記事