googleスプレッドシート

2022.01.21

スプレッドシートの使える関数～Query関数～

こんにちは！おまかせ事務代行YPP　伊澤です。
スプレッドシートの使える関数シリーズ３つ目はQuery関数を紹介します！

Query関数は、例えば表のデータを絞り込む場合、フィルタ機能を使用することが多いと思います。
でも行が増えるたびにフィルタを付け直す作業は面倒だったりしますよね^^;
そしてデータを間違って書き換えてしまわないか不安という気持ちもあります。。

そんなときにお勧めなのがQuery関数です。
数式を一度設定してしまえば自動更新をしてくれるので、データを誤って触る心配もないです。

いろいろ使えそう！！？Query関数でできること

Query関数でできること、慣れてしまえばやれることは沢山ありそうです！
ざっと出来ることを紹介しますね＾＾

①列の順番を入れ替える

数式は　=QUERY(範囲,”select 列”)
今回はA列とB列を入れ替えたいので以下のようにします。
=QUERY(A1:C20,”select B,A,C”)
特に列の入れ替えを行わない場合は
=QUERY(A1:C20,”select *”)　とします。

②データの抽出をする

数式は　=QUERY(範囲,”select 列 where 抽出する列=’抽出したい文字列'”)
今回はAとB列を入れ替えて、C列の「伊澤」だけを抽出したいので以下のようにします。
=QUERY(A1:C20,”select B,A,C where C=’伊澤'”)
上記と同様に、列の入れ替えを行わない場合は
=QUERY(A1:C20,”select * where C=’伊澤'”)　とします。

③別のシートへ表示させる

①②では、同じシートにデータを抽出して反映させましたが、別のシートへ反映させることも出来ます。

数式は　=QUERY(‘シート名’!範囲,”select 列 where 抽出する列=’抽出したい文字列'”)
今回、反映元のシート名が「シート1」なので、以下のようにします。
=QUERY(‘シート1’!A:C,”select B,A,C where C=’メンバー'”)
また、範囲を「A1:C20」ではなく「A:C」にすることで列全体を反映するので、元データに新しい行が追加されれは自動的に更新されます。

④別のスプレットシートへ表示させる

「使えるのは同じスプレッドシートだけ？？」と思った方もいるかもしれませんが、前回お伝えしたImportrange関数と組み合わせることで別のスプレッドシートへデータを反映させることができます。

これができたら、例えばYPPの稼働実績を予め自分だけが見られるシートへ反映させておいて、自分の稼働分だけ集計なんかもできちゃいます！

Query関数とImportrange関数を組み合わせて実際のデータを別のスプレットシートへ反映させています。

数式は
=Query(IMPORTRANGE(“スプレッドシートキー”,”!A10:G”),”select Col1,Col2,Col4,Col5,Col7 where Col4 =’YPP 事務所'”)
範囲を「A10:G」としているのは実際の表が10行目から始まっているためです。
「A:G」と表記しても問題ないですが、表と関係ない文字が１行目に集約されてしまうので、綺麗に表示させたい場合は表が始まる行数を入力することをおすすめします！
また、列の表記も先ほどとは異なっていることにお気づきでしょうか？
「select Col1,Col2,Col4,Col5,Col7」とさっきまで「A,B,C・・・」としていたのに違います＾＾；
はっきりした理由はわかりませんが、Importrange関数を組み合わせるときは列を「A,B,C・・・」だとうまくいかず、「Col1,Col2,Col3,Col4・・・」という表記でうまく反映することが出来ました。

Query関数を活用しよう！

若干とっつきにくいかもしれませんが、慣れればいろんな使い方が出来ると思います！
まずは稼働実績表をご自身のスプレッドシートへ反映するところから試してみてください＾＾

スプレッドシートを使いこなして、業務効率化を目指しましょう☆

引用

https://pumpuppineapple.com/query5/

合わせて読んでください＾＾

スプレッドシートの使える関数～Importrange関数～

スプレッドシートの使える関数～ArrayFormula関数～

＜YPPがLINE公式アカウントに登場！＞
お得な情報を受け取るには、以下のリンクから友だち追加してください。

2022.01.20

スプレッドシートの使える関数～Importrange関数～

こんにちは！おまかせ事務代行YPP　伊澤です。

前回のArrayFormula関数に引き続き、スプレッドシート独自の便利な関数を紹介します。

スプレッドシートの使える関数～ArrayFormula関数～

今回は　Importrange関数　です！
Importrange関数は他シートのデータを取り込むことができます。

他のスプレッドシートのデータを自動で取り込む！Importrange関数

複数シートで同じデータを管理している場合、Importrange関数を使えば、元データが更新されると取り込んだデータも自動で更新されるので、他のシートの情報を書き換える手間がなくなります。

使い方

数式は、
＝IMPORTRANGE(“スプレッドシートキー”, “!範囲”)
今回はデータ元であるシートのA～D列を反映させたいので範囲は「!A:D」とします。
※このときにダブルコーテーションで括るのをお忘れなく！（範囲の前には “!” を入れます）
※「スプレッドシートキー」は、データの読み込み元となるスプレッドシートの URL の内、「d/」の後の部分のことです。
スプレッドシートの住所をイメージすればわかりやすいです。

注意点

ArrayFormula関数と同様、Importrange関数も表示範囲内に文字を入力するとエラーになってしまうので気を付けましょう。

Importrange関数を使えば、同じデータを複数のシートで管理する手間がなくなります。
二度手間をなくすことでミスも防げて効率化にもつながりそうです＾＾

是非、いろんな現場で活用してみてください☆

引用

IMPORTRANGE関数とは？Googleスプレッドシートで外部データをインポートする方法

＜YPPがLINE公式アカウントに登場！＞
お得な情報を受け取るには、以下のリンクから友だち追加してください。

2022.01.19

スプレッドシートの使える関数～ArrayFormula関数～

こんにちは！おまかせ事務代行YPP　伊澤です。
題名を見て？と思った方も多いと思いますが、Googleスプレッドシート独自の関数があるってご存知でしたか？
数年前まではスプレッドシートはExcelよりも機能面では劣る・・・というイメージがありましたが
Excel以上に便利な機能も実は探せばたくさんあるんです♪

その中で今日はArrayFormula関数を紹介したいと思います！

データ軽量化に！ArrayFormula関数

Arrayformulaで関数を括ってあげれば、オートフィルを使うことなく数式を対象の列や行に反映させることが出来ます。
最初のセルにのみ数式を入力すれば良いので、データの軽量化にもつながります。
ショートカットキーもあるので是非覚えておきたい関数です。

OS	ショートカットキー
Windows	Ctr＋Shft＋Enter
Mac	Command＋Shft＋Enter

使い方

行が増えるたびに数式をコピーして反映させて・・・
というのは地味に大変ですが、ArrayFormula関数で最初の関数を括っておけばその必要もありません。

Arrayformula関数を適用させるには、数式を見て判別してほしい範囲を全て選ぶ必要があるので・・・

数式が「=IF(I2-H2<>0,I2-H2,””)」　の場合は・・・
=ArrayFormula(IF(I2:I-H2:H<>0,I2:I-H2:H,””))　とします。
指定したセルにそれぞれ注目してほしいのですが、「I2:I」はI2セル以降のI列全体を見るという意味となり、行が増えても数式を追加で入力することなくついてきてくれます。
I18セルまでの反映させるなら「I2:I18」というように表記します。（他のセルも同じように範囲を指定します）

注意点

Arrayformula関数が適用されている範囲に文字列を入力してしまうと数式がエラーとなってしまう。（画像参照）
Arrayformula関数はすべての関数に適用するわけではない。（基本的な関数は使えますが、Index、Match関数の組み合わせ、Sumifs、Countifs、Ifs関数など未だ原因が解明されていないエラーなどもあります。）

読み込みが遅くなってきたな・・・
というスプレッドシートがありましたら、是非ArrayFormula関数の活用を検討してみてください＾＾

引用

https://aitrigger.co.jp/blog/efficient/arrayformula_useful_function/

＜YPPがLINE公式アカウントに登場！＞
お得な情報を受け取るには、以下のリンクから友だち追加してください。

2021.12.18

【事務効率化】ChatWorkの予約投稿を活用しよう～動作確認編～

こんにちは！おまかせ事務代行YPP　伊澤です。

前回紹介した【事務効率化】ChatWorkの予約投稿を活用しよう～AppsScript設定編～ではGoogleスプレッドシートを使ってAppsScriptの設定を行いました。

【事務効率化】ChatWorkの予約投稿を活用しよう～AppsScript設定編～

皆さんスムーズに設定できましたでしょうか？

今日は早速動作確認をしていきたいと思います！

スプレッドシートへ設定を行う

最初に用意したスプレッドシートへ投稿したい内容を入力します。

これで準備OKです。
入力した投稿日のトリガーで設定した時間帯に投稿されます。
※ルームIDの確認方法は以下をお読みください↓

ルームIDの確認方法

「グループチャットの設定」を開いたら左下に表示されている「ルームID」をコピーすればOKです

実行してみた！結果・・・

エデュタも保存して、トリガーも設定して、投稿内容も入力して、これで投稿されるのを待つのみ！！！

・・・・と思ったら投稿されない＾＾；

解決策

またまたGASを最初に動かすにはGoogleさんにアクセス許可を与えないとダメみたいです。

画面キャプチャがなくて申し訳ないのですが、実行後、「このアプリは Google で確認されていません」というエラー表示が出てくるので、手順は『AppsScript設定編』でお伝えした流れと一緒です。

画面下の「詳細」をクリック
「××××××［ファイル名］（安全ではないページ）に移動」　をクリック
「××××××［ファイル名］がGoogleアカウントへのアクセスをリクエストしています」という画面が出てくる
「許可」をクリックする

何回も「実行」をクリックしてしまうと同じ投稿がされてしまうので注意してくださいね。(笑)

まとめ

初めてGoogle Apps Script（通称GASと覚えましょう）を使う方はアクセス許可を３回くらい求められるので心が折れそうになりますが、そこをクリアすれば予約投稿が使えるようになります＾＾

予約投稿できるのは、管理者権限のあるグループチャットのみです。
管理者権限がない場合は管理者権限のあるYPP担当者へ「GASの予約投稿を使いたいので・・・」とお気軽にご相談ください＾＾

また、マイチャットや個人チャットへの投稿はできないのでご注意ください。

夜に申し訳ない気持ちでチャットを送信する方の気持ちが少しでも楽になることを願っております☆彡

＜YPPがLINE公式アカウントに登場！＞
お得な情報を受け取るには、以下のリンクから友だち追加してください。

2021.12.07

【事務効率化】ChatWorkの予約投稿を活用しよう～AppsScript設定編～

こんにちは！おまかせ事務代行YPP　伊澤です。

今日はメンバーさんからお聞きしたチャットワークに関する便利機能をご紹介します★
Googleスプレッドシートの「Apps Script」という拡張機能を使えば指定した日にチャットワークのグループチャットへ予約投稿ができるのです！

朝は忙しいから・・・とついつい遅い時間にチャットを送ってしまうというとき、時期尚早だけど忘れないうちに連絡しておきたい！というときなど、是非ご活用ください＾＾

「準備の前の準備」初めてGoogle Apps Scriptを使う方・・・

GoogleドライブからGoogle Apps Scriptを取得します。
※Google Apps ScriptとはGoogleが提供するプログラミング言語で、プログラムをすることで作業の自動化が行えます。Excelで言うマクロと似たイメージです。

スプレッドシートを開いたときにメニューバーに「拡張機能」が表示されます

１．スプレッドシートを用意する

スプレッドシートを新規で作成します。
ファイル名は何でもOKですが、のちのScriptの編集上、シート名は「シート1」のままを推奨します。

以下の必要項目を１行目に入力します。

項目名	入力内容
NO	管理しやすいように連番を付けるのがおすすめです
ルームID	投稿したいグループチャットの「ルームID」を入力 ※確認方法はのちほど説明します
投稿内容	投稿したい内容を入力。TOへ絵文字も指定可能です
投稿日	投稿日を「yyyy/mm/dd」で入力する
実行日時	スクリプト内で自動で記録するので空白でOK

２．Apps Scriptの設定を行う

最初に入力されているコードは全削除して、以下のコードをコピーして貼りつけます。

// 【編集対象】Chatwork API Token
const token = '●●●';

function myFunction() {

  // スプレットシート読み込み
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('シート1');
  const lastRow = sheet.getLastRow()

  // ChatworkAPIクライント作成
  const client = ChatWorkClient.factory({token: token});

  // 実行日時
  const today = new Date();

  // スプレットシートに記載がある分だけ繰り返す
  for(var i = 2; i <= lastRow; i++) {

    const room_id = sheet.getRange(i, 2).getValue(); // ルームID
    const message = sheet.getRange(i, 3).getValue(); // 投稿内容
    const reservation_date = new Date(sheet.getRange(i, 4).getValue()); // 投稿日
   
    // 同じ日付の場合、メッセージ送信
    if(diffDate(reservation_date,today)){
      client.sendMessage({
        room_id: room_id,
        body: message
      });

      // 実行日時の記録
      sheet.getRange(i, 5).setValue(new Date());
    }
  }
  
}

// 日付を比較する（同じ場合、trueを返す）
function diffDate(date1, date2){
  if(date1.getFullYear() == date2.getFullYear()){
    if(date1.getMonth() == date2.getMonth()){
      if(date1.getDate() == date2.getDate()){
        return true;
      }
    }
  }
  return false;
}

2行目の　const token = ‘●●●’;　の●●●のところには自分のトークンを入力します。
※トークンの確認方法は次で説明します。
※シート名を変更した場合は6行目の　const sheet = ss.getSheetByName(‘シート1’);　のシート名1のところも変更しましょう。

３．トークンの確認方法

自分のチャットワークにログインして、トークンを発行します。

先ほどのApps Scriptへ戻って●●●の部分へコピーしたトークンを貼り付けてプロジェクトを保存します。

【補足】個人契約ではない場合は管理者の承認が必要なため、こんな画面も表示されます

スクリプトIDは以下をコピーして貼り付けてください。

1nf253qsOnZ-RcdcFu1Y2v4pGwTuuDxN5EbuvKEZprBWg764tjwA5fLav

スクリプトID

・バージョン　→　最新バージョン（一番大きな数字）
・ID　→　変更なし（ChatWorkClient）

上記を確認したら「追加」をクリックします。

４．トリガーの設定をする

トリガーとは・・・ITの分野では、きっかけになる出来事が起こったら自動的に特定の処理を起動するソフトウェアの仕組みなどをこのように呼ぶようです。
ここでは毎日決まった時間にスクリプトを動かしChatworkへ通知するための設定を行います。
https://e-words.jp/w/%E3%83%88%E3%83%AA%E3%82%AC%E3%83%BC.html