質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
Google Apps Script

Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

Q&A

解決済

2回答

422閲覧

1行1セルのリストを、2行1セルのリストに成形したい。

koromo_t

総合スコア60

Google Apps Script

Google Apps ScriptはGoogleの製品と第三者のサービスでタスクを自動化するためのJavaScriptのクラウドのスクリプト言語です。

0グッド

0クリップ

投稿2018/04/17 05:49

編集2018/04/17 16:17

やりたいこと

1行1セルのリストがあります。
これを、GASを使って、別シートに2行1セルのリストに成形したいです。

手動では、以下の通りにやると、期待通りの結果になります。

  1. 「別シート」に2行1セルで結合したリストを下方向のオートフィルで作る
  2. INDIRECT関数とオートフィルを組み合わせ、元のリストを1行飛ばしで呼び出し
=INDIRECT("元のシート!"&"R"&(ROW()+1)/2&"C"&column(B$181), FALSE)

やってみたこと

手動でできることなので、GASでもできるのではないかと思ったのですが、
ちょっと難しくてよくわからなかったです。

まず、B1とB2の2行を結合することはできて、

javaScript

1function sample_Range_merge(){ 2 var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("コメントの成形"); 3 var rng = sh.getRange('B1:B2'); 4 rng.merge(); 5 }

あとは繰り返し文を使えばよさそうということまではわかるのですが、
理解が及ばず、それを最終行まで繰り返すことができませんでした。

仮にここがクリアできたとしても、
GASでINDIRECT関数のようなことをすることについても、
調べてみても、よくわかりませんでした。

みなさまの見解を聞いてみて、私にも理解できそうであれば、
ぜひともやってみたいと思うのですが、
私には理解できそうになければ、手動でもいいかなとも思っています。
よろしくお願いします。

追記1(進行状況)

元のリストが更新されたら、
「別シート」の一番下の行にINDIRECT関数の数式を入力するようなスクリプトを書きました。
でもこれだと、元のリストで空白の日があると、瞬く間に意味をなさなくなってしまうようです。
うーん…。

javaScript

1function zenbu(){ 2 var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("コメントの成形"); 3 Logger.log(sh); 4 var LastRow = sh.getLastRow(); 5 Logger.log(LastRow); 6 var rng = sh.getRange(LastRow,1).getValue(); 7 Logger.log(rng); 8 var rng2 = sh.getRange(LastRow,2).getValue(); 9 Logger.log(rng2); 10 if(rng != "" , rng2 != ""){ 11 sample_Range_merge(); 12 sample_Range_Formula1(); 13 sample_Range_Formula2(); 14 } 15} 16 17function sample_Range_merge(){ 18 var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("コメントの成形"); 19 var LastRow = sh.getLastRow(); 20 Logger.log(LastRow); 21 var rng = sh.getRange(LastRow+2,2,2); 22 var rng2 = sh.getRange(LastRow+2,1,2); 23 Logger.log(rng); 24 rng.merge(); 25 rng2.merge(); 26 } 27 28// 日付の入力(INDIRECT) 29function sample_Range_Formula1(){ 30 var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("コメントの成形"); 31 var LastRow = sh.getLastRow(); 32 Logger.log(LastRow); 33 var LastRow_Value = sh.getRange(LastRow,1).getValue(); 34 Logger.log(LastRow_Value); 35 var LastRow_Range2 = sh.getRange(LastRow+2,1); 36 Logger.log(LastRow_Range2) 37 LastRow_Range2.setFormula("=INDIRECT(\"コメント入力用シート!\"&\"R\"&(ROW()+1)/2&\"C\"&column(A$181), FALSE)"); 38 } 39 40// 数式の入力 41function sample_Range_Formula2(){ 42 var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("コメントの成形"); 43 var LastRow = sh.getLastRow(); 44 Logger.log(LastRow); 45 var LastRow_Value = sh.getRange(LastRow,1).getValue(); 46 Logger.log(LastRow_Value); 47 var LastRow_Range = sh.getRange(LastRow,2); 48 Logger.log(LastRow_Range) 49 LastRow_Range.setFormula("=INDIRECT(\"コメント入力用シート!\"&\"R\"&(ROW()+1)/2&\"C\"&column(B$181), FALSE)"); 50 }

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

まず、あまり手を入れない最低限の変更でループするように↓のように書き換えさせていただきました(もしdiffツールが使えるようなら差分を見てください)

こうすることで、例えばスクリプトエディタ画面のメニューの実行→関数の実行からzenbuを選択すれば、コメントの成形シートがまるっと書き換わるはずです(もちろん、コメント入力用シートに空行があっても)

javascript

1function zenbu(){ 2 var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("コメントの成形"); 3 Logger.log(sh); 4 sh.clear(); 5// var LastRow = sh.getLastRow(); 6// Logger.log(LastRow); 7// var rng = sh.getRange(LastRow,1).getValue(); 8// Logger.log(rng); 9// var rng2 = sh.getRange(LastRow,2).getValue(); 10// Logger.log(rng2); 11 sample_Range_merge(); 12 sample_Range_Formula1(); 13 sample_Range_Formula2(); 14} 15 16function sample_Range_merge(){ 17 var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("コメントの成形"); 18 var shref = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("コメント入力用シート"); 19 var LastRow = shref.getLastRow(); 20 Logger.log(LastRow); 21 for(var i = 0; i <= LastRow; i++) { 22 var rng = sh.getRange(i*2+1,2,2); 23 var rng2 = sh.getRange(i*2+1,1,2); 24 // Logger.log(rng); 25 rng.merge(); 26 rng2.merge(); 27 } 28} 29 30// 日付の入力(INDIRECT) 31function sample_Range_Formula1(){ 32 var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("コメントの成形"); 33 var shref = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("コメント入力用シート"); 34 var LastRow = shref.getLastRow(); 35 Logger.log(LastRow); 36 for(var i = 1; i <= LastRow; i++) { 37 // var LastRow_Value = sh.getRange(i,1).getValue(); 38 // Logger.log(LastRow_Value); 39 var LastRow_Range2 = sh.getRange(i*2 + 1,1); 40 // Logger.log(LastRow_Range2) 41 LastRow_Range2.setFormula("=INDIRECT(\"コメント入力用シート!\"&\"R\"&(ROW()+1)/2&\"C\"&column(A$181), FALSE)"); 42 } 43} 44 45// 数式の入力 46function sample_Range_Formula2(){ 47 var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("コメントの成形"); 48 var shref = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("コメント入力用シート"); 49 var LastRow = shref.getLastRow(); 50 Logger.log(LastRow); 51 for(var i = 1; i <= LastRow; i++) { 52 // var LastRow_Value = sh.getRange(i,1).getValue(); 53 // Logger.log(LastRow_Value); 54 var LastRow_Range = sh.getRange(i*2+1,2); 55 Logger.log(LastRow_Range) 56 LastRow_Range.setFormula("=INDIRECT(\"コメント入力用シート!\"&\"R\"&(ROW()+1)/2&\"C\"&column(B$181), FALSE)"); 57 } 58}

ただ、こうなってくると、どういうタイミングでどれくらいの分量のデータを編集するのか、という問題が起こります。
試していただくと分かると思うのですが、上記のスクリプトは(処理内容から考えると)実行に時間がかかりすぎます。コメント入力用シートの行数次第では途中でタイムアウトするかもしれません。

もし、コメント入力用シートが、「データ量が多い」けれども「一行ずつ記入される」(まとめて何行もコピペすることはなく、頻度は多くても一度に一行しか入らない)とか、編集トリガで入力と成形シートが同時並行的にメンテされる、という状況で利用されるものであれば、私が書き換えた方法ではタイムアウトしてデータがおかしくなる危険があります。なので、あえてループにせず、一行ずつ追加する方法(あなた様がもともと書いておられた方法)が適当になる場合もあるかと思いました。

そうではなくて、例えば「データ量は一定量を越えない」(月ごとにファイルもしくはシートが分けられる運用)であるとか「一日一回整理すればよい」(一回の実行に数秒~十数秒待てる)のであれば、ループによる方法も適当かもしれません。

また、関数を動かす方法も、編集をきっかけにするのか、時間なのか、ボタンを配置してそれを押すのか、などどういうタイミングで成形をしたいか、によっても解決方法は異なります。
(もちろん、ループの方法を最適化してもっと早くやるように書くことはできます。ただ、改変可能な、つまり理解可能な方法を提示しようとしています)

そもそも、今回の私のやりたいことは、手動でやればよいことで、
GASを使って実現することが難しいことなのかもしれません…。

こう思わせる回答をしてしまったことは、大変に遺憾です。
しかし、(手間を減らそうという着想を持てる)あなたのような方がこのような単純作業をするのは勿体ないですし、また今のところGASでやりにくいというよりも、要件が明らかになっていないように思います。

もし前回のif文を取る、あるいは今回のループが目的を達しないのであれば、「何をきっかけに」「どれくらいの量のコメントを」「どれくらいの頻度で」「一回あたりどれくらいの時間をかけて」処理するものなのかが想像できるような実際の使い方をご提示いただければと思います。

投稿2018/05/02 15:30

papinianus

総合スコア12705

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

koromo_t

2018/05/03 07:26 編集

いただいたスクリプトを実行してみたところ、サービスエラー?になりました。 まるっと書き換えだと問題があるのかもしれません…? ちなみに、「入力用シート」といっているものの、 別のシートからimportrangeでリンクしているものなので、 それもなにか関係があるのかもしれません。 具体的には、 1日1行、入力用シート(の元のシート)に日記的なものを書いて(書いたり書かなかったり)、 入力用シートへリンクして取得したデータを2行1セルに成形したものを、 さらに別のシートへimportrangeでリンクしている…、 なんかそんな感じです。この説明で正確に伝わるかわかりませんが(笑)。 ややこしくてすみません…(笑)。 あはは…(笑)、お褒めいただき恐縮です…(笑)。 いろいろと考えていただいたにも関わらず、振り回した感じになって申し訳ないのですが、 単純作業といっても、2行1セルに成形したものが足りなくなれば、 そのときにまたオートフィルでビュンとやればいいだけではありますしね…。 ん…? ちょっといま閃いたかもしれません。 そういえば、1日1セルずつ増殖するようにトリガーを設定すれば、 セルが足りなくなるという状況が生まれないような気がしました…?! そうか、1日1回だけ、スクリプトが働くように設定しておけばいいような気がしました! スプレッドシートの具体的な使用法を伝えていれば、 もっと早くここにたどり着いていたのかもしれませんね…。 というか、最初からこういうようなことを指摘?してくださっていた…? テラテイルで質問するようになってしばらく経ちますが、 質問する難しさも日々学んでおります…(笑)。 (これは完全に余談ですが、詳しく書こうとした結果、 余計なことを書いて逆に混乱を招いたり…(笑)) なんとなく、問題は解決したように思います! ご協力、ありがとうございました!! --- 【追記 2018/05/03 16:18】 すごく余談なのですが…、 入力用シートが1行1セルなのは、 https://www.healthcare.omron.co.jp/product/hem/note/index.html こちらの、血圧記録表の生活メモを入力用シートにしているからで、 2行1セルに成形する必要があるのは、 http://kaminsho.jp/sleepdiary.html こちらの備考欄に、血圧記録表に書いたコメントを表示させたかったから、 だったりしますw それぞれ、いろいろ改変しているので、 若干、原型をとどめていないところもありますが、 ベースとしてこれらの表を使わせてもらっているので、 2行1セルに成形する必要が出てきたりした次第です(笑)。
papinianus

2018/05/06 03:26

解決とかいておられるので余談になってしまうのですが(通知されるのかな?)。 * サービスエラー これって参照元のシートも自分のアカウントにあるgoogleドキュメントにあるスプレッドシートですよね?importrangeしてみたのですが、再現できませんでした(といっても非常に単純なデータしかいれてないですけども) 可能性としては、データ量が多くて、[動作制限](https://docs.google.com/macros/dashboard)にかかっているのかなと思います。ぱっと見つけられませんでしたが、単純に一回の処理が長くても停止する仕様だったように記憶します (もしトリガではなく、スクリプトエディタで手動実行しておられるなら、メニューから開いたログの上に、実行トランスクプトというのがあって、エラー情報があるかもしれないし、ないかもしれません) 仮にデータ量による制限だとしたらやはり当初思い描いておられた、一行ずつ増やすという解決が、要件に対して適切な解法だったということになるかと思います。 * 解決方法 コメントと言われたときに、お問い合わせフォームからのコメントかと思ったんですが、自分で記録していくものであれば一日マックス1行なので、日付トリガーで一行増やす、っていうのは一つの最適解だと思います。 そんな深遠な考えで回答はしていなかったのですが、もしかしたらそういう意図の部分を書いていただくように質問に対する質問を入れるべきでしたね。適切に回答するのも難しいですね。 とりあえずやりたい/やれることの路線が見えたようでよかったです。
koromo_t

2018/05/06 13:52 編集

通知については、当方の個人的な問題なのかもしれないのですが、 プッシュ通知をする設定にしているはずなのに、全般的に通知されないんですよね…。 いつごろまでだったか、以前は通知されていたのですけど。 ちなみに、サイトに訪れれば、ベルの通知マークのところには通知の数が出るので、 そこで気づくことはできます。(今回の件についても出ていましたので、 解決済の記事についても、通知はされるっぽいです?) * サービスエラー はい、参照元のシートも自分のアカウントにあるスプレッドシートです。 * 解決方法 あ、なるほど…!言葉足らずで誤解を招いてしまい、すみません…! お問い合わせフォームからのコメントなら、 シートに編集があるたびに1行増やすという、 papinianusさんの方法が最適解かもしれませんね! 改めまして、ご協力ありがとうございました!
guest

0

ベストアンサー

やりたいことを正しく理解できているか分かりませんが、

でもこれだと、元のリストで空白の日があると、瞬く間に意味をなさなくなってしまうようです。

というところでおっしゃっているのは

javascript

1if(rng != "" , rng2 != ""){

のところでrng2 != ""という条件を加えているからではないでしょうか?


こちらで試したのは、

  1. 「コメント入力用シート」「コメント成形」の2シートをもつスプレッドシートを新規作成
  2. コメント入力用シートに、5行分くらい、A列に日付、B列にアルファベットを入力したデータを作る
  3. コメント成形シートで10行分くらい(入力の2倍)、セル結合とindirectを手で設定
  4. スクリプトにzenbu()以下のコードをコピペして、編集時トリガーを設定(このとき、「,rng2!=""」を消す)
  5. コメント入力用シートの最終入力行の2セルをコピーして次の行にペースト

ここまでやったところ、コメント成形シートに結合ブロックが増えるのを確認できました。

投稿2018/04/19 08:41

papinianus

総合スコア12705

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

koromo_t

2018/04/20 16:49

考えてくださってありがとうございます! そうすると、たしかに入力用シートの最終行の次の行に入力した際は反応するのですが、 仮に、最終行の次の行が空白で、最終行の次の次の行に入力すると、反応しないのですよね。 そもそも、今回の私のやりたいことは、手動でやればよいことで、 GASを使って実現することが難しいことなのかもしれません…。
papinianus

2018/05/02 14:50 編集

回答が至らず、すみません。 言いたかったのは、「if(rng != "" , rng2 != ""){」(とこれに対応する「}」)を消せば、空白行があっても動くはずだということです。 つまり > if(rng != "" , rng2 != ""){ > sample_Range_merge(); > sample_Range_Formula1(); > sample_Range_Formula2(); > } を > sample_Range_merge(); > sample_Range_Formula1(); > sample_Range_Formula2(); という風にするということです(条件をとっぱらう) やりたいはずのことについては、別の観点が必要なようなので、すみませんが回答をもう一つ追加します。
koromo_t

2018/05/03 06:29

回答ありがとうございます! いえ、こちらこそ理解が至らずすみません…。 おっしゃる通りに書き換えると、コメント入力用シートが編集されるたびに、 じわじわと、数式が入った2行1セルが増殖していくといった感じですね? これなら、成形したセルが足りないという状況は生まれないのかもしれません。 「自動」にこだわるのであれば、これが最適解なのかもしれないと思いました。 →新たにいただいた回答のコメント欄へ続く。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問