GASであれやこれを自動化する

これは何？

• Google App ScriptでGoogle SheetsやSlackを制御するためのスクリプト例です
  • 経常業務に掛かる稼働の軽減を図ることを目的としています

GAS x Google Sheets

Google Sheets（Googleスプレッドシート）に反映させる処理をGASで制御する
※ Google Sheets（Googleスプレッドシート）はGoogle LLCの商標です

条件に合う場合のみ別カラムのセルに値を入れる処理

• シート名で処理を反映させるシートを指定する
• 2行目から最終行にかけて同じ処理を繰り返す
  • この例では1行目は項目名が入力されているものとしている
  • C列のセルに[Aさん、Bさん、Cさん、Dさん、Eさん]のいずれかの場合、E列にチェックを入れる

function myFunction() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("シート名")
  var lastRow = sheet.getLastRow();
  
  for(var i = 2; i < lastRow; i++){
    var reporter = sheet.getRange(i, 4).getValue();
    if(reporter.match(/Aさん|Bさん|Cさん|Dさん|Eさん/)){
      sheet.getRange(i, 5).setValue("");
    }else{
      sheet.getRange(i, 5).setValue("✅");
    };
  }
}

Googleスプレッドシート上の見え方

→ E列で条件に合致するセルのみチェックを入れる

日付表示を変換する処理

• E列に日時がyyyy/MM/dd HH:mm:ssの形式で表示されている
• シート名で処理を反映させるシートを指定する
• 2行目から最終行にかけて同じ処理を繰り返す
  • E列の値を取得する
  • E列の値をyyyy/mm/ddに変換する

function myFunction() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("シート名");
  var lastRow = sheet.getLastRow();

  for(var i = 2; i <= lastRow; i++){
    var created_at = sheet.getRange(i, 5).getValue();
    var created_at2 = Utilities.formatDate(created_at, 'JST', 'yyyy/MM/dd')
    sheet.getRange(i, 5).setValue(created_at2);
  };
}

異なるカラム間の日付差分を表示する処理

• シート名で処理を反映させるシートを指定する
• 2行目から最終行にかけて同じ処理を繰り返す
  • B列とC列の値を取得する
  • B列とC列の差分を日付で換算してD列に格納する
    • 1日未満の場合は0日とする

function calculateDays() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("シート名");
  var lastRow = sheet.getLastRow();

  // ヘッダー行を除いて、各行のB列とC列の差を計算
  for (var row = 2; row <= lastRow; row++) {
    var createDateCell = sheet.getRange("B" + row);
    var firstResponseDateCell = sheet.getRange("C" + row);
    var daysDifferenceCell = sheet.getRange("D" + row);

    var createDate = createDateCell.getValue();
    var firstResponseDate = firstResponseDateCell.getValue();

    if (createDate && firstResponseDate) {
      var daysDifference = Math.floor((firstResponseDate - createDate) / (1000 * 60 * 60 * 24));
      daysDifferenceCell.setValue(daysDifference);
    }
  }
}

Googleスプレッドシート上の見え方

→ D列に日数の差分が入る

カウント関数を自動実行する処理

• C列に担当者のカラム
• E列は遭遇回数を入れる予定のカラム（null）

function countTextOccurrencesPerRow() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("シート名");

  var dataRange = sheet.getRange("C2:C" + sheet.getLastRow());
  var data = dataRange.getValues();
  var outputData = [];

  for (var i = 0; i < data.length; i++) {
    var textToCount = data[i][0];
    var count = 0;

    for (var j = 0; j < data.length; j++) {
      if (data[j][0] === textToCount) {
        count++;
      }
    }

    outputData.push([count]);
  }

  var outputRange = sheet.getRange(2, 5, outputData.length);
  outputRange.setValues(outputData);
}

Googleスプレッドシート上での反映

→ C列の担当者を元に一致するレコード上のE列のセルに集計結果を埋める

装飾を自動で反映させる処理

• シート名で処理を反映させるシートを指定する
• 1行目の背景色を灰色に設定
• フォントタイプを「M PLUS 1p」に設定
• 2行目以降の背景色を白に設定
• フォントの垂直方向の配置を中央「middle」に設定
• 全てのセルに枠線を引く
• 1行目上下の枠線のみ2pxに設定

function decorateSpreadsheet() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("シート名");
  
  // 1行目の背景色を設定
  var firstRowRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
  firstRowRange.setBackground('#b7b7b7');
  firstRowRange.setFontWeight('bold');
  firstRowRange.setFontSize(11);
  
  // フォントタイプを設定
  sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).setFontFamily('M PLUS 1p');

  // 2行目以降の背景色を設定
  var otherRowsRange = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
  otherRowsRange.setBackground('#ffffff');
  
  // 垂直方向の配置を中央に設定
  sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).setVerticalAlignment('middle');
  
  // 全てのセルに枠線を引く
  sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).setBorder(true, true, true, true, true, true);

  // 1行目上下の枠線を2pxに設定
  sheet.getRange(1, 1, 1, sheet.getLastColumn()).setBorder(null, null, true, null, null, null, '#000000', SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
}

GAS x Slack

Slackの処理をGASで制御する

土日祝を除外したリマインダー

• WebhookURLを取得する
• 参照する「日本の祝日」カレンダーを指定する
  • 祝日の場合は送らないように定義する
  • 年末年始の12/27-01/03も除外する
• メッセージ文とリンクを指定する
• 投稿先のチャンネルとユーザーグループを指定する
• 送信時刻を指定する
  • 別途トリガーを指定時刻の1時間前付近で設定しておく

function sendNotification() {
  // send mention automatically
  var slackWebhookUrl = 'https://hooks.slack.com/services/xxx';

  // 祝日を定義
  function isHoliday(date) {
    var calendarId = 'ja.japanese#[e-mail]';
    var calendar = CalendarApp.getCalendarById(calendarId);
    var events = calendar.getEventsForDay(date);
    return events.length > 0;
  }

  // 年末年始を定義
  function isYearEnd(date) {
    var month = date.getMonth() + 1; // getMonth() returns 0-11
    var day = date.getDate();
    return (month === 12 && day >= 27) || (month === 1 && day <= 3);
  }

  // 投稿内容を定義
  var mentionMessage = ' メッセージ文 （ <https://xxxxxx|テキスト> ）';

  // 現在の日時等を取得
  var now = new Date();
  var dayOfWeek = now.getDay();
  var currentTime = Utilities.formatDate(now, 'JST', 'HH:mm');

  // 祝日と年末年始を除いた平日に通知されるようにセット
  if ( 0 < dayOfWeek && dayOfWeek < 6  ) {
    // send mention without holiday
    if (!isHoliday(now)&&!isYearEnd(now)) {
      var payload = {
        text: "<!subteam^ユーザーグループID>" + mentionMessage,
        channel: '#チャンネル名',
      };
      var options = {
        method: 'post',
        contentType: 'application/json',
        payload: JSON.stringify(payload),
      };
      UrlFetchApp.fetch(slackWebhookUrl, options);
    }
  }
  deleteTrigger()
}

// タイマーをセットする
function setReminder(){
  var time = new Date();
  time.setHours(11);
  time.setMinutes(00);
  time.setSeconds(00);
  ScriptApp.newTrigger('sendNotification').timeBased().at(time).create();
}

// トリガーの削除する（溜まると発火しなくなるため）
function deleteTrigger() {
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() == "sendNotification") {
      ScriptApp.deleteTrigger(triggers[i]);
    }
  }
}

毎週水曜日のリマインダー

• WebhookURLを取得する
• 参照する「日本の祝日」カレンダーを指定する
  • 祝日の場合は送らないように定義する
  • 年末年始の12/27-01/03も除外する
• メッセージ文とリンクを指定する
• 投稿先のチャンネルとユーザーグループを指定する
• 送信時刻を指定する
  • 別途トリガーを送信時刻の1時間前付近で設定しておく
  • トリガーを削除する（トリガーが溜まり過ぎると動かなくなるため）

function sendNotification() {
  // send mention automatically
  var slackWebhookUrl = 'https://hooks.slack.com/services/xxxx';

  // designate time and day of week
  var notificationDay = 3; // 0:Sun, 1:Mon, ..., 6:Sat

  // check holiday
  function isHoliday(date) {
    var calendarId = 'ja.japanese#[e-mail]';
    var calendar = CalendarApp.getCalendarById(calendarId);
    var events = calendar.getEventsForDay(date);
    return events.length > 0;
  }

 // check YearEnd
  function isYearEnd(date) {
    var month = date.getMonth() + 1; // getMonth() returns 0-11
    var day = date.getDate();
    return (month === 12 && day >= 27) || (month === 1 && day <= 3);
  }

  // content
  var mentionMessage = ' <https://xxxx|テキスト>を確認して完了報告をしてください！';

  // get action date
  var now = new Date();
  var dayOfWeek = now.getDay();
  var currentTime = Utilities.formatDate(now,'JST','HH:mm');

  // set Wednesday
  if (dayOfWeek === notificationDay && currentTime) {
    // send mention without holiday
    if (!isHoliday(now)&&!isYearEnd(now)) {
      var payload = {
        text: "<!subteam^ユーザーグループID>" + mentionMessage,
        channel: '#チャンネル名',
      };
      var options = {
        method: 'post',
        contentType: 'application/json',
        payload: JSON.stringify(payload),
      };
      UrlFetchApp.fetch(slackWebhookUrl, options);
    }
  }
  deleteTrigger()
}

// Set Trigger
function setReminder(){
  var time = new Date();
  time.setHours(11);
  time.setMinutes(00);
  time.setSeconds(00);
  ScriptApp.newTrigger('sendNotification').timeBased().at(time).create();
}

function deleteTrigger() {
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() == "sendNotification") {
      ScriptApp.deleteTrigger(triggers[i]);
    }
  }
}

毎月月初○営業日目のリマインダー

• WebhookURLを取得する
• 参照する「日本の祝日」カレンダーを指定する
  • 祝日だった場合は翌営業日に送るように定義する
• メッセージ文とリンクを指定する
• この例では月初5営業日と定義する
• 投稿先のチャンネルとユーザーグループを指定する

function sendNotification() {
  // send mention automatically
  var slackWebhookUrl = 'https://hooks.slack.com/services/xxx';

  var today = new Date();

  // 参照するGoogleカレンダーを定義
  var calendarId = 'ja.japanese#[e-mail]';
  var calendar = CalendarApp.getCalendarById(calendarId);
  var holidays = calendar.getEventsForDay(today);

  // content
  var mentionMessage = ' メッセージ<#チャンネルID|テキスト>';

  // 月初5営業日目を計算
  var businessDays = 0;
  var date = new Date(today.getFullYear(), today.getMonth(), 1);
  while (businessDays < 5) {
    if (date.getDay() != 0 && date.getDay() != 6 && calendar.getEventsForDay(date).length == 0) {
      businessDays++;
    }
    if (businessDays < 5) {
      date.setDate(date.getDate() + 1);
    }
  }

  if (today.getDay() == 0 || today.getDay() == 6 || holidays.length > 0) {
    return;
  }

  // 今日が月初5営業日目かどうかを確認
  if (today.getDate() != date.getDate()) {
    return;
  }

  var payload = {
    channel: '#チャンネル名',
    text: "<!subteam^ユーザーグループID>" + mentionMessage,
  };

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

  UrlFetchApp.fetch(slackWebhookUrl, options);
}

特定の曜日と時間に2人1組のペアに通知を送る

• WebhookURLを取得する
• 参照する「日本の祝日」カレンダーを指定する
  • 祝日だった場合は前営業日に送るように定義する
• メッセージ文とリンクを指定する
• この例では毎週木曜日の午前09:00を通知時間とする
• 投稿先のペアを指定する
• 一度通知されたペアは全てのペアが終わるまで次の通知は回ってこない

function sendNotification() {
  // send mention automatically
  var slackWebhookUrl = 'https://hooks.slack.com/services/*****';

  // designate time and day of week
  var notificationDay = 4; // 0:Sun, 1:Mon, ..., 6:Sat

  // check holiday
  function isHoliday(date) {
    var calendarId = 'ja.japanese#[e-mail]';
    var calendar = CalendarApp.getCalendarById(calendarId);
    var events = calendar.getEventsForDay(date);
    return events.length > 0;
  }

  // check YearEnd
  function isYearEndHoliday(date) {
    var month = date.getMonth() + 1; // getMonth() returns 0-11
    var day = date.getDate();
    return (month === 12 && day >= 28) || (month === 1 && day <= 3);
  }

  function isBusinessDay(date) {
    var day = date.getDay();
    if (day == 0 || day == 6) return false;
    var holidays = getHolidays();
    for (var i = 0; i < holidays.length; i++) {
      if (holidays[i].getTime() == date.getTime()) {
        return false;
      }
    }
    return true;
  }

  function getPreviousBusinessDay() {
    var date = new Date();
    while (!isBusinessDay(date)) {
      date.setDate(date.getDate() - 1);
    }
    return date;
  }

  function getAssignedPair() {
    var pairs = [
      ["<@U********>", "<@U********>"],
      ["<@U********>", "<@U********>"],
      ["<@U********>", "<@U********>"],
      ["<@U********>", "<@U********>"],
      ["<@U********>", "<@U********>"]
    ];
    var scriptProperties = PropertiesService.getScriptProperties();
    var lastPairIndex = scriptProperties.getProperty("lastPairIndex");
    if (lastPairIndex === null || lastPairIndex >= pairs.length - 1) {
      lastPairIndex = -1;
    }
    var nextPairIndex = parseInt(lastPairIndex, 10) + 1;
    scriptProperties.setProperty("lastPairIndex", nextPairIndex.toString());
    return pairs[nextPairIndex];
  }

  // content
  var pair = getAssignedPair();
  var mentionMessage = pair[0] + pair[1] + "テキスト";

  // get action date
  var now = new Date();
  var dayOfWeek = now.getDay();
  var currentTime = Utilities.formatDate(now,'JST','HH:mm');

  // set Thursday 09:00
  if (dayOfWeek === notificationDay && currentTime) {
    // send mention without holiday
    if (!isHoliday(now)&&!isYearEndHoliday(now)) {
      var payload = {
        text: mentionMessage,
        channel: '#new_notification',
      };
      var options = {
        method: 'post',
        contentType: 'application/json',
        payload: JSON.stringify(payload),
      };
      UrlFetchApp.fetch(slackWebhookUrl, options);
    }
  }
  deleteTrigger()
}

// Set Trigger
function setReminder(){
  var time = new Date();
  time.setHours(09);
  time.setMinutes(00);
  time.setSeconds(00);
  ScriptApp.newTrigger('sendNotification').timeBased().at(time).create();
}

function deleteTrigger() {
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() == "sendNotification") {
      ScriptApp.deleteTrigger(triggers[i]);
    }
  }
}

最後に

経常業務を楽にし、本来掛けるべきことに労力を費やせるようになることを祈ります！