Google Apps Scriptで週次レポートの作成を楽に

背景

以前の記事で週次レポートの可視化部分の更新を楽にするために、可視化シートのセルを1か所更新するだけでグラフが更新されるフォーマットを作成した。このExcelをベースにこれらを資料化する工程についてGoogle Apps Scriptで自動化するコードを作成した。
ExcelなのでVBAが使えるはずだが、Mac版のExcelでグラフをPowerPointにコピペする際にクラッシュしてしまい、どうにも解消できなかったため今回はGASを用いて自動化することにした。

今回使用するスプレッドシート

基本的に上の記事のExcelをそのままスプレッドシートに変換したものを利用する。

Googleスライドでの資料作成のために以下の3つの作業をGASで行う。

• A26セルの更新（9/18 → 9/25）
• 3枚のグラフのコピー
• M19:N21の範囲の数値を取得

テンプレートスライド

週次レポートは簡単に以下の4枚を用意。

このテンプレートをコピーし、ファイル名を作成日に合わせて変更、作成済みレポートのフォルダに格納する。
その後、必要な情報をスプレッドシートからGASで流し込む。具体的には以下の4つの作業を行う。

• 作成日: {yyyyMMdd}
• 売上・客数・客単価の実績: {number}
• 売上・客数・客単価の前週比: {comparison}
• 売上・客数・客単価のグラフをコメントの下に挿入

また作成したレポートは、Slackで送付する。

完成したレポート

作成したGASコード

スプレッドシートやスライドのID、SlackのURLは ***************************** に置換しているのでコードを利用する際は対象のID、URLに要変更。
makeWeeklyReport 資料作成用の関数
notifySlack Slack送付用の関数
getShapeObjectId 資料作成用の関数作成時にスライド内のオブジェクトをIDで取得する必要があるため、各オブジェクトのIDを調べるための関数

makeWeeklyReport

/**
 * レポート作成の流れ
 * 1. テンプレートスライドをコピーし今回作成用ファイルを作成
 * 2. スプレッドシートのグラフ、コメント用の数値を取得
 * 3. 取得したグラフをスライドに貼り付け、コメントに数値を記入
 * 4. 作成したスライドをSlackで送付
 */
function makeWeeklyReport() {

  // 定数定義
  const templateSlideId = '*****************************'; // テンプレート
  const reportFolderId = '*****************************'; // 作成したレポートの格納場所
  const chartSpreadsheetID = '*****************************'; // データの取得元のスプレッドシート

  // レポート作成日を取得
  const reportMakeDate = new Date();

  // 先週の月曜日を取得（スプレッドシートのデータ更新用）
  let reportTargetWeekDate = new Date(reportMakeDate);
  reportTargetWeekDate.setDate(reportMakeDate.getDate() - 7);
  let reportTargetWeekDateDay = reportTargetWeekDate.getDay();

  while(reportTargetWeekDateDay !== 1){
    reportTargetWeekDate.setDate(reportTargetWeekDate.getDate() - 1);
    reportTargetWeekDateDay = reportTargetWeekDate.getDay();
  };
  const reportTargetWeekMonday = reportTargetWeekDate;

  const formattedReportMakeDate = Utilities.formatDate(reportMakeDate, 'Asia/Tokyo', 'yyyyMMdd');
  const formattedReportTargetWeekMonday = Utilities.formatDate(reportTargetWeekMonday, 'Asia/Tokyo', 'yyyy/MM/dd');

  Logger.log('レポート作成日: ' + formattedReportMakeDate);
  Logger.log('レポート対象週の月曜日: ' + formattedReportTargetWeekMonday);

  // ----------------------------------------------------------------------------------------------------------------------
  // レポート用ファイルの作成
  // テンプレートスライドのコピー
  const sourceFile = DriveApp.getFileById(templateSlideId);
  const newFile = sourceFile.makeCopy();
  const newFileName = '週次レポート_{yyyyMMdd}'.replace('{yyyyMMdd}', formattedReportMakeDate);
  newFile.setName(newFileName);
  newFile.moveTo(DriveApp.getFolderById(reportFolderId));
  const newFileId = newFile.getId();

  // コピーしたファイルを編集用のスライドとして設定
  const presentation = SlidesApp.openById(newFileId);

  // ----------------------------------------------------------------------------------------------------------------------
  // 1ページ目（タイトルスライドの作成日の記入）
  let shapeId = 'i1';
  let shape = presentation.getPageElementById(shapeId).asShape();
  let text = shape.getText().asString();
  
  text = text.replace('{yyyyMMdd}', formattedReportMakeDate);  
  shape.getText().setText(text);

  // ----------------------------------------------------------------------------------------------------------------------
  // 2ページ目以降（売上・客数・客単価グラフをスプレッドシートからコピペ & 実績値と前週比の値をスプレッドシートからコピペ）
  // 対象のスプレッドシートとシートを取得
  const ssChart = SpreadsheetApp.openById(chartSpreadsheetID);
  const sheetChart = ssChart.getSheetByName('グラフ');

  // スプレッドシートのグラフに反映する基準データを直近の月曜日に変更
  sheetChart.getRange('A26').setValue(reportTargetWeekMonday);

  // 売上・客数・客単価のグラフを取得
  const charts = sheetChart.getCharts();

  // スライドに貼り付け
  for(let i=0; i<charts.length; i++){
    const slide = presentation.getSlides()[i+1];
    const position = {left: -90, top: 95};
    const size = {width: 900, height: 300};
    const chart = charts[i];
    slide.insertSheetsChart(
      chart,
      position.left,
      position.top,
      size.width,
      size.height
    );
  };

  // 実績値と前週比の値をスプレッドシートから取得
  const sales = sheetChart.getRange('M19:N19').getDisplayValues(); // getValue()では数値の書式が変わるためgetDisplayValue()を使用
  const customers = sheetChart.getRange('M20:N20').getDisplayValues();
  const avgRevenuePerCustomer = sheetChart.getRange('M21:N21').getDisplayValues();

  const shapeIdNumberSets = [
    {shapeId:'g287e460ee90_0_126', number:sales},
    {shapeId:'g287e460ee90_0_127', number:customers},
    {shapeId:'g287e460ee90_0_128', number:avgRevenuePerCustomer}
    ];

  // スライドのコメントのテキストに取得した数値を入力（置換）
  for(let shapeIdNumberSet of shapeIdNumberSets){
    let shape = presentation.getPageElementById(shapeIdNumberSet.shapeId).asShape();
    let comment = shape.getText().asString();
    comment = comment.replace('{number}', shapeIdNumberSet.number[0][0]).replace('{comparison}', shapeIdNumberSet.number[0][1]);
    shape.getText().setText(comment);
  };

  Logger.log('週次レポートが作成されました。');
  
  // ----------------------------------------------------------------------------------------------------------------------
  // Slackで送付
  newFile.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW); // スライドの共有設定
  const reportUrl = '<https://docs.google.com/presentation/d/' + newFileId + '/edit?usp=drive_link>';
  notifySlack(reportUrl);

  Logger.log('週次レポートがSlackで送付されました。');

}

notifySlack

/**
 * Slackに作成したレポートのリンクを通知する
 * @param {string} reportUrl - 作成したレポート（Googleスライド）のURL
 */
function notifySlack(reportUrl) {
  const text = '週次レポートを送付します。\n';
  const payload = {
    'username': '週次レポート',
    'icon_emoji': ':bar_chart:',
    'text': text + reportUrl,
    'channel': '#weekly_report',
    'unfurl_links': true
  };

  const options = {
    'method': 'post',
    'contentType': 'application/json',
    'payload': JSON.stringify(payload)
  };

  const url = '*****************************';
  UrlFetchApp.fetch(url, options);

}

getShapeObjectId

/**
 * テンプレートのスライドすべてについてスライド番号とその中のオブジェクトのID・内容を取得して表示する
 * テンプレートの改修時に使用する
 */
function getShapeObjectId(){
  const presentationId = '*****************************';
  const presentation = SlidesApp.openById(presentationId);
  const slides = presentation.getSlides();

  for (let i = 0; i < slides.length; i++){
    let slide = slides[i];
    let shapes = slide.getShapes();
    Logger.log('スライド番号: ' + i);

    for (let j = 0; j < shapes.length; j++){
      let shape = slide.getShapes()[j];  
      Logger.log('[%s]: %s',shape.getObjectId(), shape.getText().asString());
    };
  };
}

参考

• ビジネスパーソンに贈る業務効率化大全 〜Google Apps Scriptによる業務の自動化〜
  https://www.udemy.com/course/gas_for_operational_efficiency/
• GASでGoogleスライドのプレゼンテーションを操作するはじめの一歩
  https://tonari-it.com/gas-slides-presentation-introductin/
• Google Apps Script(GAS)でvar,let,constの変数宣言の違いと使い分けを解説
  https://auto-worker.com/blog/?p=2096