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を選択し[実行]ボタンを押すとスライドが生成される。

スプレッドシートで使える便利なアドオン

スプレッドシートシートにはアドオンをインストールすることで様々な便利機能を追加することができます。
その中でもおすすめのアドオンをいくつか紹介していきます。
*アドオンの追加方法は、メニューバーの[アドオン]→[アドオンの取得]から気になったアドオンを探して追加することができる。

・Template Gallery
ビジネスシーンや普段使いもできるような様々な書式テンプレートが用意されているアドオンです。アドオンの中からいつでも好きな書式を使用して手早く資料を作成することが可能になります。

・Mapping Sheets
スプレッドシートに入力した内容からマップを作成してくれるアドオンです。
デモシートに地図にしたい情報を入力することで目的地の地図を見ることができます。
これを使って営業所やオフィスの確認などが簡単にできます。

・Projectsheet Planning
ガントチャートのフォーマットが瞬時に生成されます。
期限を入力することでガントチャートのグラフも自動で生成されるため、手間いらずでプロジェクトのスケジュール管理が行えます。

・Google Analytics
Google Analytics 公式のアドオンで、Google Analyticsと連携し、レポートを作成することができます。見たいデータのレポートを自動で出力する設定もでき、グラフなどの出力も可能です。

今回紹介した内容以外にもスプレッドシートの活用術は多く存在します。
是非、様々な機能に触れて自分に合った生産性向上の仕組みを築き上げていきましょう!

この記事を書いた人

知念 央樹

知念 央樹

2020年度新卒入社。Webアプリ開発を担当。
好きなアーティストはBUMP OF CHICKEN。
最近は家にこもりがち。。

おすすめ記事

タグ

2020新卒バトンAdobe IllustratorBIツールBOTCCDLab.CSSCSV事例DockerDXECExcelExcel関数GitGoogleAnalyticsGoogleスプレッドシートGoogleデータポータルLT会MembersDinerOJTPhotoshopPythonRubySDGsSEOSimilarWebSlackSNSSocial Art JapanプロジェクトSQLUIUXUXリサーチVSCodeWebディレクションWebディレクターWebマーケティングWeb解析Well-beingWordPressアクセシビリティアナリティクスウェビナーウェビナー運用エシカルエシカルファッションエンジニアオウンドメディアオンラインイベントお悩み相談室キャリアクライアントワークコーディングコミュニケーションコンテンツマーケティングコンペサービスサイト構造サステイナブルサンプルスウェーデンスキルアップセミナーソーシャルアーティストソーシャルクリエイターチームビルディングツールデータディレクションディレクターデザイナーデザインデンマークトンマナナレッジブームの裏側フレームワークプログラミングプログラミング教育フロントエンドマーケティングマネジメントスキルミーティングメタバースメンバーズメディカルマーケティングカンパニーメンバーズルーツカンパニーユーザーテストライティングラボ活動リモートワークショップワークスタイル事例事例紹介仕事術仙台仙台オフィス分析効率化勉強会動画北欧医療業界品質管理地方金融企業基本情報技術者試験広告運用提案数学新卒研修新規構築機械学習気候変動海洋プラスチック問題生産性向上産学連携研修社会課題社会課題調査競技プログラミング脱炭素自動化ツール色彩検定製薬業界試験対策資格開発環境障がい者雇用食の問題