2021.9.15

Googleスプレッドシートでできるいろんなこと

はじめに

Googleのツールであるスプレッドシートはその使いやすさと汎用性の高さからビジネスシーンで多く使われており、私もガントチャートやテスト項目書の作成などでよく利用しています。
しかし、スプレッドシートには表計算ツール単体としての用途だけでなく、様々なツールや機能を合わせることで生産性効率を格段に上げる万能ツールへと進化します！
今回はスプレッドシートを応用することで日頃のビジネスシーンで役立つ使い方の一部をご紹介します！！

スプレッドシートで作るGoogle ChatリマインドBOT

スプレッドシートはメニューバーの[ツール]→[スクリプトエディタ]からAppScriptというプログラミングコードを書くことができます。これらを使用してGoogle Chatで自動的に決まった日時にリマインドを流すBOTを作ることができます。
ここでは毎週決まった曜日にリマインドを流すBOTの作り方を紹介します。

作成手順

1. BOT作成用のスプレッドシートを用意し、「BOT_remind」「BOT_config」という名前のシートを用意します。

2. 「BOT_config」シートに曜日と値の対応表を作成します。

3. 「BOT_remind」シートに下図のような表を作成し、E2セルに

=IFERROR(VLOOKUP(C2,BOT_config!$A$2:$B$8,2,FALSE),"")

を入力します。

＊各項目にリマインド内容の設定を入力する。

A列：時間
B列：分
C列：曜日
D列：リマインドするメッセージ

4. [ツール]→[スクリプトエディタ]を開いて下記コードをコピーして貼り付ける。

const date = new Date();
const week = date.getDay();
const hour = date.getHours();
const minutes = date.getMinutes();
 
function remindBOT() {  
  const sheet = SpreadsheetApp.getActive().getSheetByName('BOT_remind');
  const rowLast = sheet.getLastRow()-2;
  const remind = sheet.getRange(3, 1, rowLast, 5).getValues();
  for(var i = 0; i < remind.length; i++){
    const re_hour = remind[i][0];
    const re_minutes = remind[i][1];
    const re_week = remind[i][4];
    const re_message = remind[i][3];
    const Blank = sheet.getRange(2, 5)
    if(hour == re_hour && minutes == re_minutes){
　　　　if(re_week == 7){
        messagePOST(re_message);
      }else if(week == re_week){
        messagePOST(re_message);
      }
      Logger.log('applicable');
    }else{
      Logger.log('not applicable');
    }
  }
}
 
function messagePOST(message) {
  const thdID = 00000000;
    const url = 'webhookのURL';//取得したwebhookのURLを入れる
    url += "&threadKey=" + thdID;
    const re_message = { 'text': message }
    const options = {
      'method': 'POST',
      'headers': {
        'Content-Type': 'application/json; charset=UTF-8'
      },
      'payload': JSON.stringify(re_message)
    };
    const result = UrlFetchApp.fetch(url, options);
    Logger.log(result);
}

5. BOTを追加したいチャットルームメニューから[Webhookを管理]でBOTの名前を入力し、取得したWebhookをコードの[webhookのURL]部分に貼り付ける。

6. AppScriptメニューからトリガーの作成を行う。
トリガー作成時の設定は以下のように設定する。
＊トリガー設定の初期作成時にアクセス許可を求められるので許可する。

以上の設定でチャット内で自動的にリマインドを流してくれるようになります。
「BOT_remind」シートの表に追加していくことで複数のリマインドを管理することができます。

スプレッドシート×Googleフォームで自己紹介スライド自動生成

在宅ワークやオンラインイベントが多くなっている現在ではスライドに自己紹介を記入することも多いこと思います。そこで、Googleフォームで自己紹介内容を収集しスライドを自動生成する方法を紹介します。

作成手順

1. Googleフォームを作成する。
今回は下記の項目で作成してみます。

氏名
社員番号
プロフィール画像
所属部署
勤務地
出身地
入社日
関わってきた案件
スキル分野
今後について
趣味
好きなもの
ひとこと

2. フォームの回答をスプレッドシートに連携する。
＊回答タブからスプレッドシートのマークを押す

3. スライドのテンプレートシートを用意する。
テキストボックスでフォームの質問項目名を{}で囲んで配置します。

4. 連携したスプレッドシートでスクリプトエディタを開き下記コードを貼り付けて、スライドのURL、スプレッドシートのURL、シート名を書き換える。

function initSlide() {
  var url = 'スライドのURL';
  var slide = SlidesApp.openByUrl(url);
  var slide0 = slide.getSlides();
  for(var i = 2; i < slide0.length; i++){
    Logger.log("削除");
    slide0[i].remove();
  }
}
 
function generateSlidesFromSpreadsheet() {
  initSlide();
  var sheet = SpreadsheetApp.openByUrl('スプレッドシートのURL').getSheetByName('シート名');
  var slide = SlidesApp.openByUrl('スライドのURL');
 
  var datarange = sheet.getDataRange().getValues();
  var template = slide.getSlides()[1];
 
  for(var i=1;i<datarange.length;i++){
    var newpage = slide.appendSlide(template);
    for(var j=0;j<datarange[0].length;j++){
      newpage.replaceAllText('{'+datarange[0][j]+'}', datarange[i][j]);
    }
 
    var getimg = newpage.getImages();
 
    var image = datarange[i][3].replace('open?','uc?export=view&');
    Logger.log(image);
    try{
      getimg[0].replace(image);
    }catch(e){
      Logger.log("image false");
    }
  }
}

＊画像ファイルを格納しているフォルダの公開設定をしておかないと画像の反映がうまくいかないことがある。これでも画像の反映に失敗する場合は手動で置き換える。

5. 実行する関数を選択でgenerateSlidesFromSpreadsheetを選択し[実行]ボタンを押すとスライドが生成される。