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

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

ただいまの
回答率

90.60%

  • Google Apps Script

    788questions

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

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

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 211

koromo_t

score 50

 やりたいこと

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行を結合することはできて、

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

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

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

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

 追記1(進行状況)

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

function zenbu(){
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("コメントの成形");
  Logger.log(sh);
  var LastRow = sh.getLastRow();
  Logger.log(LastRow);
  var rng = sh.getRange(LastRow,1).getValue();
  Logger.log(rng);
  var rng2 = sh.getRange(LastRow,2).getValue();
  Logger.log(rng2);
  if(rng != "" , rng2 != ""){
  sample_Range_merge();
  sample_Range_Formula1();
  sample_Range_Formula2();
  }
}

function sample_Range_merge(){
 var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("コメントの成形");
  var LastRow = sh.getLastRow();
  Logger.log(LastRow);
  var rng = sh.getRange(LastRow+2,2,2);
  var rng2 = sh.getRange(LastRow+2,1,2);
  Logger.log(rng);
  rng.merge();
  rng2.merge();
  } 

// 日付の入力(INDIRECT)
function sample_Range_Formula1(){
 var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("コメントの成形");
  var LastRow = sh.getLastRow();
  Logger.log(LastRow);
  var LastRow_Value = sh.getRange(LastRow,1).getValue();
  Logger.log(LastRow_Value);
  var LastRow_Range2 = sh.getRange(LastRow+2,1);
  Logger.log(LastRow_Range2)
  LastRow_Range2.setFormula("=INDIRECT(\"コメント入力用シート!\"&\"R\"&(ROW()+1)/2&\"C\"&column(A$181), FALSE)");
  } 

// 数式の入力
function sample_Range_Formula2(){
 var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("コメントの成形");
  var LastRow = sh.getLastRow();
  Logger.log(LastRow);
  var LastRow_Value = sh.getRange(LastRow,1).getValue();
  Logger.log(LastRow_Value);
  var LastRow_Range = sh.getRange(LastRow,2);
  Logger.log(LastRow_Range)
  LastRow_Range.setFormula("=INDIRECT(\"コメント入力用シート!\"&\"R\"&(ROW()+1)/2&\"C\"&column(B$181), FALSE)");
  }
  • 気になる質問をクリップする

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

+1

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

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

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

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

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


こちらで試したのは、

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/04/21 01:49

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

    キャンセル

  • 2018/05/02 23:49 編集

    回答が至らず、すみません。
    言いたかったのは、「if(rng != "" , rng2 != ""){」(とこれに対応する「}」)を消せば、空白行があっても動くはずだということです。
    つまり
    > if(rng != "" , rng2 != ""){
    > sample_Range_merge();
    > sample_Range_Formula1();
    > sample_Range_Formula2();
    > }

    > sample_Range_merge();
    > sample_Range_Formula1();
    > sample_Range_Formula2();
    という風にするということです(条件をとっぱらう)
    やりたいはずのことについては、別の観点が必要なようなので、すみませんが回答をもう一つ追加します。

    キャンセル

  • 2018/05/03 15:29

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

    キャンセル

+1

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

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

function zenbu(){
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("コメントの成形");
  Logger.log(sh);
  sh.clear();
//  var LastRow = sh.getLastRow();
//  Logger.log(LastRow);
//  var rng = sh.getRange(LastRow,1).getValue();
//  Logger.log(rng);
//  var rng2 = sh.getRange(LastRow,2).getValue();
//  Logger.log(rng2);
  sample_Range_merge();
  sample_Range_Formula1();
  sample_Range_Formula2();
}

function sample_Range_merge(){
 var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("コメントの成形");
  var shref = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("コメント入力用シート");
  var LastRow = shref.getLastRow();
  Logger.log(LastRow);
  for(var i = 0; i <= LastRow; i++) {
    var rng = sh.getRange(i*2+1,2,2);
    var rng2 = sh.getRange(i*2+1,1,2);
    //  Logger.log(rng);
    rng.merge();
    rng2.merge();
  }
} 

// 日付の入力(INDIRECT)
function sample_Range_Formula1(){
 var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("コメントの成形");
  var shref = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("コメント入力用シート");
  var LastRow = shref.getLastRow();
  Logger.log(LastRow);
  for(var i = 1; i <= LastRow; i++) {
    // var LastRow_Value = sh.getRange(i,1).getValue();
    //  Logger.log(LastRow_Value);
    var LastRow_Range2 = sh.getRange(i*2 + 1,1);
    //  Logger.log(LastRow_Range2)
    LastRow_Range2.setFormula("=INDIRECT(\"コメント入力用シート!\"&\"R\"&(ROW()+1)/2&\"C\"&column(A$181), FALSE)");
  }
} 

// 数式の入力
function sample_Range_Formula2(){
 var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("コメントの成形");
  var shref = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("コメント入力用シート");
  var LastRow = shref.getLastRow();
  Logger.log(LastRow);
  for(var i = 1; i <= LastRow; i++) {
    //  var LastRow_Value = sh.getRange(i,1).getValue();
    //  Logger.log(LastRow_Value);
    var LastRow_Range = sh.getRange(i*2+1,2);
    Logger.log(LastRow_Range)
    LastRow_Range.setFormula("=INDIRECT(\"コメント入力用シート!\"&\"R\"&(ROW()+1)/2&\"C\"&column(B$181), FALSE)");
  }
}

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

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

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

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

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/05/03 15:30 編集

    いただいたスクリプトを実行してみたところ、サービスエラー?になりました。
    まるっと書き換えだと問題があるのかもしれません…?

    ちなみに、「入力用シート」といっているものの、
    別のシートから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セルに成形する必要が出てきたりした次第です(笑)。

    キャンセル

  • 2018/05/06 12:26

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

    * 解決方法
    コメントと言われたときに、お問い合わせフォームからのコメントかと思ったんですが、自分で記録していくものであれば一日マックス1行なので、日付トリガーで一行増やす、っていうのは一つの最適解だと思います。

    そんな深遠な考えで回答はしていなかったのですが、もしかしたらそういう意図の部分を書いていただくように質問に対する質問を入れるべきでしたね。適切に回答するのも難しいですね。
    とりあえずやりたい/やれることの路線が見えたようでよかったです。

    キャンセル

  • 2018/05/06 22:52 編集

    通知については、当方の個人的な問題なのかもしれないのですが、
    プッシュ通知をする設定にしているはずなのに、全般的に通知されないんですよね…。
    いつごろまでだったか、以前は通知されていたのですけど。
    ちなみに、サイトに訪れれば、ベルの通知マークのところには通知の数が出るので、
    そこで気づくことはできます。(今回の件についても出ていましたので、
    解決済の記事についても、通知はされるっぽいです?)

    * サービスエラー
    はい、参照元のシートも自分のアカウントにあるスプレッドシートです。

    * 解決方法
    あ、なるほど…!言葉足らずで誤解を招いてしまい、すみません…!
    お問い合わせフォームからのコメントなら、
    シートに編集があるたびに1行増やすという、
    papinianusさんの方法が最適解かもしれませんね!

    改めまして、ご協力ありがとうございました!

    キャンセル

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

  • ただいまの回答率 90.60%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • Google Apps Script

    788questions

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