ラベル スプレッドシート の投稿を表示しています。 すべての投稿を表示
ラベル スプレッドシート の投稿を表示しています。 すべての投稿を表示

2021/03/18

【スプレッドシート】Importrange関数とQuery関数の融合


こんにちは!

Query関数の処理の速さが病みつきになっているtknriaです。

前回は、スプレッドシートのデータを他のスプレッドシートで参照する関数としてQuery関数をご紹介しました。

今回は、Importrange関数を組み合わせることで、他のスプレッドシートを参照して一部を抽出する方法を紹介します。


Query関数の引数にImportrange関数を使う


例えばスプレッドシートA内のsheet1の範囲A1:C3のデータを、別のスプレッドシートB内のsheet2で参照することを想定します。

これを行うために、まずは、B内のsheet2のセルにAのデータを引用してくる必要があります。

そこで、Bのsheet2に、

 =Importrange("スプレッドシートAのキー","'sheet1'!A1:C3")

と入力すれば良いです。

ここから、A列とC列のみを抽出するので、

 =Query('sheet1'!A1:C3, "select A,C")

と組み合わせます。


それがこちらです。

 =Query(Importrange("スプレッドシートAのキー","'sheet1'!A1:C3"), "select Col1, Col3)"


おわかりでしょうか。

下記のQuery関数において、

 =Query('sheet1'!A1:C3, "select A,C")

1つ目の引数の「'sheet1'!A1:C3」が「Importrange(~~)」に置き換わって、

2つ目の引数の「"select A,C"」は「"select Col1, Col3"」に変わっています。


前者については、参照する範囲を変更しているだけなので、問題ないかと思いますが、

後者は要注意です。


1つ目の引数の範囲が、同じスプレッドシート内だったら、抽出したい列名を直接アルファベットで指定すればよかったですが、

別のスプレッドシートを参照する場合には、指定されたの範囲の中の何列目かを指定する必要があります。

今回は、A列~C列の3列のなかのA列とC列(1列目と3列目)を抽出するというものだったので、「Col1, Col3」と指定しなければなりません。


少し慣れが必要なところではありますが、何度か実際に使ってみるなかで感覚的にわかってくると思うので、ぜひトライしてみてください。


億劫なあとがき


いかがでしたか。

例えば、同じ営業部内でも、商材によって管理しているスプレッドシートが別、ただ数字はまとめて管理したい、というようなケースがあると思います。

実際に私の部署はそうしています。
知っておいてほしい数字と知らなくていい数字があるので、その切り分けにとてもやりやすいです。

そういった場合には、今回のようにImportrange関数とQuery関数の合わせ技が有効です。


2021/03/17

【スプレッドシート】Query関数を使ってシートを一部分のみ参照したい



こんにちは!

もうすぐ年度がかわりますが、引き続きスプレッドシートを多用しそうで嬉しいtknriaです。


以前、スプレッドシートのデータを他のスプレッドシートで参照する関数としてIMPORTRANGE関数をご紹介しました。

億劫な細道|別のスプレッドシートの値を参照したい


今回は、あるデータを参照して、条件付きで引用してくる、という関数を紹介します。



Query関数を使ってみよう


その関数はQuery関数です。

例えばスプレッドシートA内のsheet1の範囲A1:C3のデータを、同じA内のsheet2で参照することを想定します。

これを行うためには、sheet2のセルに、

 =Arrayformula('sheet1'!A1:C3)

と入力すれば良いです。

同様に、Query関数を使うと、

 =Query('sheet1'!A1:C3)

となります。

これだけなら、Query関数の特徴はそれほど目立たないのですが、ここからが本領発揮です。


Query関数を使って指定した列だけ抜粋する


上記の例で、A列とC列だけを抜きだしたい場合、

 =Arrayformula('sheet1'!A1:A3)

 =Arrayformula('sheet1'!C1:C3)

と分けて抽出する必要があります。

これが、Queryを使うと、

 =Query('sheet1'!A1:C3, "select A,C")

と1つの関数で書けてしまいます。


Query関数を使って条件に合う値をもつ列を行ごと抜粋する


具体例でいうと、営業マンが3人(D~Fさん)いて、それぞれの受注案件をリスト化していることを想定します。

ここから、Dさんの案件のみを抽出する場合、スプレッドシートのフィルタ機能を使うこともできますが、Query関数を使っても可能です。

sheet1のB列に担当営業の名前が入っているものとすると、

 =Query('sheet1'!A1:C3, "where B='D'")

とすれば、Dさんの案件のみを抽出することができます。


億劫なあとがき


いかがでしたか。

Query関数でできることは他にもありますが、今日はここまで。


2019/10/21

【GAS】スプレッドシートからGmailを送りたい



こんにちは!仕事時間の約7割はスプレッドシートを操作しているtknria(@tknria0304)です!

以前に「Gmailに届いたメール内容をスプレッドシートに反映したい」という記事をご紹介しました。
今回はその逆、スプレッドシートからGmailを送っちゃおうという内容です。

この作業を簡単にしようと思うと、
 ・スプレッドシートから必要情報を読み込む
 ・Gmailでメールを作成し送信する
というステップを踏めば実現することができます。

では早速、その具体的な方法をご紹介させていただきます。


スプレッドシートからGmailを送ろう


スプレッドシートからGmailを送る方法は簡単です。

宛先・件名・本文を決めて、「MailApp.sendEmail」で送る。

実はたったこれだけなんです。
これを実現するのは以下のコードです。

function SendGmail() {
  var recipient = "xxxx.xxxx@xxx.co.jp"   // 宛先
  var subject = "テストメールの件名"   // 件名
  var body = "tknriaです。\nテストです。"   // 本文
  MailApp.sendEmail(recipient, subject, body);
}

このコードでは、宛先・件名・本文を直接設定しています。

これを、例えばスプレッドシートの「テスト」シートの「D2」セルに入力された宛先に送る、としたい場合は、

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('ここに取得したいシート名');
var recipient = sheet.getRange(2,4).getValue();

とすれば良いです。

ぜひ、いろいろいじってみながら感覚をつかんでください。


億劫なあとがき


いかがでしょうか。
これとトリガー設定を組み合わせることで、定型のメールを望んだタイミングで定期的に送ることが可能です。

トリガー設定については後日、ご紹介できればと思います。



関連記事

・これからGASを始めたい!という方にはこちらの記事がオススメ
 → 億劫な細道「GAS まずGASを使ってみたい

・VBAやGASについてもっとざっくり知りたい方はこちらの記事がオススメ
 → 億劫な細道「VBAやGASを用いた業務効率化のススメ

2018/11/22

【GAS】Gmailに届いたメール内容をスプレッドシートに反映したい



こんにちは!Gmailもスプレッドシートも生活必需品になってしているtknria(@tknria0304)です!

立場上、部下や後輩から定型フォーマットの報告メールを送ってもらうのですが、これをひとつずつ開いて内容を確認するのがとても手間です。

この作業を簡単にしようと思うと、
 ・Gmailを検索する
 ・メールの本文を解析する
 ・必要な部分だけを抽出する
 ・スプレッドシートに書き込む
というステップを踏めば実現できます。

「Gmailを検索」ってスプレッドシート上からできるの??

そんなことを思っていましたが、いろいろ試してみるとできちゃいました。

ということで、今回はGmailからメール内容を引用してスプレッドシートに反映する方法をご紹介します。



Gmailとスプレッドシートを連携しよう


まずはスプレットシート上に、ボタンを用意し、スクリプトエディタを開いてください。
(わからない方は以前ご紹介した「まずGASを使ってみたい」という記事を参考にしてみてください。)

そして以下のコードをそのままコピペしてください。

function checkHRS() {

  var out_START = 5;  // 出力のスタート行を指定する

  // 報告メールに付けているラベル名を設定。ここでは「報連相」というラベルをメールに付けているとします。
  var Label = "報連相";

  // Gmail上で検索し、今回の報連相を取得
    var this_threads = GmailApp.search('label:' + Label);  // 日

  // このシート上に報連相の内容を出力
  for(var n in this_threads){
    var thd = this_threads[n];
    var msgs = thd.getMessages();
    var msg = msgs[msgs.length-1];
    var from = msg.getFrom();
    var date = msg.getDate();
    var title = msg.getSubject();
    var body = msg.getBody();
 
    sheet.getRange(out_START,1).setValue(date);
    sheet.getRange(out_START,2).setValue(from);
    sheet.getRange(out_START,3).setValue(title);
    sheet.getRange(out_START,4 + i).setValue(body);
    out_START++;
    Utilities.sleep(500);
  }
}

設定として、報告メールには「報連相」というラベルが付いているものとします。
このメールを、GmailApp.search()で検索して取得しています。

上記ではラベルだけで検索していますが、報告メールは毎日、毎週送ってもらうと思うので、日付も指定したくなります。
その場合は、

    var this_threads = GmailApp.search('label:' + Label + ' after:2018/11/21 before:2018/11/24');

というように、追加で条件を指定することができます。
(上記であれば、受信日が「2018年11月21日~24日」のメール、という条件を追加しています)

試しに、Gmailで「報連相」というラベル付きのメールをいくつか用意してテストしてみてください。
このコードをベースにすれば、いろんなことができるので、そちらも模索してみてください。


億劫なあとがき


いかがでしょうか。
余談ですが、コード内にある「HRS」というのは、以前某企業に勤めていたときに使われていた用語で、「報連相(HouRenSou)」を指します。
そのときの名残で未だに使ってしまいます笑



関連記事

・これからGASを始めたい!という方にはこちらの記事がオススメ
 → 億劫な細道「GAS まずGASを使ってみたい

・VBAやGASについてもっとざっくり知りたい方はこちらの記事がオススメ
 → 億劫な細道「VBAやGASを用いた業務効率化のススメ

2018/09/19

【スプレッドシート】別のスプレッドシートの値を参照したい



こんにちは!スプレッドシートが業務で不可欠なくらい活用しているtknriaです!

KPIの管理や、営業活動の把握など、さまざまな場面でスプレッドシートを使っています。

そのなかで、あるスプレッドシートのデータを他のスプレッドシートで参照したい、と思うことも出てきています。

単純にコピペをしてもいいのですが、もしそのデータに変更が加わったときにリアルタイムで対応することは困難です。

また、スプレッドシートによっては共有設定が異なっていて、閲覧不可能なものも含まれているかもしれません。

そういった状況を勘案して、それでもデータを共有したい場合に、すごく都合の良い関数があったのでご紹介します。



IMPORTRANGE関数を使ってみよう


結論からいうと、その関数とはIMPORTRANGE関数です。

例えばスプレッドシートAの範囲A1:C3のデータを、スプレッドシートBで参照することを想定します。

これを行うためには、スプレッドシートBのセルに、

 =IMPORTRANGE("スプレッドシートAのキー","A1:C3")

と入力すれば良いです。

一般化すると、

 =IMPORTRANGE("スプレッドシートキー","範囲を示す文字列")

となります。

初めて参照する場合は、アクセス権の許可を求めるボタンが表示されますが、次回以降はすぐに呼び出すことができます。


これを活用することで、スプレッドシートを簡易データベースとして運用することもできます。オーナーアカウント以外にはその値を編集できないよう設定すれば、データの信ぴょう性を確保することも可能です。



億劫なあとがき


KPI管理や売掛管理などを行う部署では特に活用できます。