GAS最適化 配列を入れる範囲の定義を変数とインデックス番号で書く方法

Question

実現したいこと

Googleカレンダーから取得したスケジュールをスプレッドシートに入力し、そのシートをもとに一日のスケジュール表を作成しています。

上図のようにカレンダーから取得したデータを、下図のような表に書き込んでいくGASスクリプトを作っています。

スケジュール開始時間とスケジュール表の時間軸を比較し、該当する時間帯の行に詳細を追加していきたいです。自力で組んだスクリプトでも一応正常に動くのですが、速度やデータ量の面でもっと効率よく動かしたいため書き直しています。修正前のコードはあまりにも長く非効率なのでいったん割愛させていただきます補足しました。

発生している問題・分からないこと

API呼出回数の削減のため、getValue、setValueの数を減らして書いたコードです。
[j][0] <= 開始時刻 < [k][0]　で条件指定してインデックス番号から該当する時間帯を特定したところまでは良いのですが、[j][1]と[j][2]にあたるセルをどう定義すればよいのかが分かりません。
var eventRange = sheet.getRange('displayTime[j][1]:displayTime[j][2]');
と入れてあります（もちろん範囲が見つからずエラーを吐きます）が、このように変数j,kあるいは新たに定義した変数を使用して範囲を取得することは可能でしょうか？

該当のソースコード

function settingmacroTest() {

    var sheet = SpreadsheetApp.getActiveSpreadsheet();

    //元データ(Googleカレンダーから取得したイベントデータの配列)
    var datasheet = sheet.getSheetByName('Input');
    //データの抽出先となるシート(スケジュール表)
    var mainsheet = sheet.getSheetByName('main');                      

    //元データ全体[スケジュール名, URL, 開始時刻]を配列に格納
    var allEventData = datasheet.getRange('A2:C').getValues();
    //書き込む要素[スケジュール名, URL]のみを配列に格納
    var eventData = datasheet.getRange('A2:B').getValues();
    //スケジュール表の時間軸＋書き込みたい範囲を配列に格納
    var displayTime = mainsheet.getRange('C7:E51').getValues();        


　 //時間軸と変数を使って、[開始時刻]が[HH:MM]から[HH:MM]の間にあるときに該当の行へsetValuesしたい
    for(var i = 0; i <= allEventData.length; i++){
        //実際に書き込むデータを二次元配列で取得
        var event = eventData[i];                 　　　      
 
        for(var j = 0, k = 2; j <= displayTime.length - 2, k <= displayTime.length; j+=2, k+=2){
           //書き込むスケジュールの開始時刻
           var starttime = allEventData[i][2];
           //比較で使う時間軸その1
           var timeJ = displayTime[j][0];
           //比較で使う時間軸その2
           var timeK = displayTime[k][0];

           //問題の箇所　書き込むべき範囲、すなわちtimeJと同じ行にある隣接2セルを定義したい
           var eventRange = sheet.getRange('displayTime[j][1]:displayTime[j][2]');

             //書き込む予定の開始時刻と同じ時間軸の行を条件指定しsetValues
             if((timeJ <= starttime)&&(starttime < timeK)){
               mainsheet.getRange(eventRange).setValues(event);
             } 

        }

    }

}

試したこと・調べたこと

• teratailやGoogle等で検索した
• ソースコードを自分なりに変更した
• 知人に聞いた
• その他

上記の詳細・結果

時間軸のところのfor文を下記のようにシートの行番号/列番号に置き換えてみたりもしたのですが、配列に格納してあるのでなるべくこの書き方は避けたいと思っています。

---

  for(var j = 7, k = 9; j <= 49, k <= 51; j+=2, k+=2){
   var starttime = allEventData[i][2];
   var timeJ = mainsheet.getRange(j,3).getValue();
   var timeK = mainsheet.getRange(k,3).getValue();
    if((timeJ <= starttime)&&(starttime < timeK)){
      mainsheet.getRange(j,4).setValue(event);
    } 
  }

---

補足

修正前のスクリプトについて、冒頭では割愛しておりましたがざっくりと補足させていただきます。

修正前

・記入するスケジュール名、URLをそれぞれ別で定義していた
・timeJ, timeKをループのたびにgetRange,getValueで取得していた
・j,kは行番号、列番号
・書き込む範囲はD列のj行目、E列のj行目をgetRangeしていた

現段階での修正点

前提：API呼出回数を削減する
・スケジュール名、URLはまとめて配列に格納
・j,kはインデックス番号
・書き込む範囲は配列displayTime[j][1],[j][2]

Comments

[YAmaGNZ]

ループの初期値を変更して
mainsheet.getRange(j,4)
じゃなくて現在のループで
mainsheet.getRange(j+6,4)
とかって計算してはダメなのですか？

[退会済みユーザー]

コメントありがとうございます。ご指摘いただいた(j+6,4)というのがどこを指すのか分かりかねるのですが、getRangeの引数はインデックス番号でもよいということでしょうか？その場合どのように記述するのでしょうか？
今回の修正はAPI呼出回数の削減が前提ですので、変数j,kはインデックス番号であることが望ましいです。ループの度にgetRange,getValueを使うのは修正前のコードで使用している手法で、それを避けるために配列とインデックス番号を使用しています。

[YAmaGNZ]

＞この書き方は避けたい
というのはfor文の初期値等を変更したくないと読んだのですが合っていますか？

上記で合っているのであれば
//問題の箇所　書き込むべき範囲、すなわちtimeJと同じ行にある隣接2セルを定義したい
という部分で指定する際にｊ等をもとに計算すれば行、列のインデックスが求められるのではないですか？
別に計算結果がインデックスとなればいいだけですので変数ｊやｋがインデックスそのものである必要はありません。
例えばdisplayTime[0][0]はC7のセルの値が入っている場所になりますよね。
であれば0+7とすれば実際の行を求めることが出来ます。
なのでdisplayTime[j][0]なのであればj+7が実際に書き込むセルの行番号になります。
なのでgetRange(j+7,3,1,2)とすれば起点のセルから１行、２列の領域を取得できます。
実際の計算が私が書いた値となるかについては私が適当に書いているので正解ではないかもしれませんが
現状のｊやｋの変数の値から実際のセルの行、列番号が計算できるという話です。

[退会済みユーザー]

こちらの説明が下手で大変恐縮ですが、上記でおっしゃっている方法では表記方法ミスのエラーが出ることを確認しているため、インデックス番号で範囲を取得する方法について質問を投稿しております。

[YAmaGNZ]

どこを表記方法ミスすることを考えていらっしゃるのかが私には分かりません。
私の理解が足りていないので、ミスを発見するのがデバッグですから、それで修正すればいいのではないかと思ってしまいます。

例えばj+7の７の部分を計算ミスしていて思った結果が得られないから7という固定値を使いたくない
というのであれば、offset = mainsheet.getRange('C7').getRow()といった感じで変数に入れoffset + jといった計算にするなり手はあると思います。
頻繁にフォーマットが変化する為、こういった定数を使うと修正場所が多くて修正漏れが発生するなどの考えなのであれば、変数にoffset = 7;といった感じで入れてそれを利用すれば修正箇所は減ります。

[atlanticSalmon]

> もっと効率よく動かしたい

ループ内でgetRange(...).getValue()やgetRange(...).setValue()をせずに、
全て配列上で処理して記入する配列を作成してから
一括で全ての予定を記入する方法は避けたいのでしょうか？

[atlanticSalmon]

ところで、この質問の予定の転記は、何かの課題なのですか？
前にも、
同じような「オフィス」と書かれて複数行の時間帯別になっているシートに
同じように予定を振り分ける質問を見たように思います。

[退会済みユーザー]

ご指摘のものと同一のシートですが、何かの課題をやっているわけではありません。事情により仕事上必要になったため作成しており、独学で自分なりに勉強しながら組んでみたスクリプトを実用性のあるものにブラッシュアップしたかっただけです。とはいえ課題でないことを証明することは不可能ですので、不正利用のご指摘につきまして通報等していただければ幸いです。
プライバシーを詮索されたように感じてしまいましたが、いただいたコメントが私自身の説明不足や不手際によるものであることは承知しております。配列ですべて処理する方法についてご存じでしたらお伺いしたいところでしたが、質問内容・利用目的が不適切だったようですのでご回答には及びません。この度は大変申し訳ございませんでした。

Answer 1

なぜか退会なさったのですね。
作ってしまったので参考までに記入しておきます。

function scheduleMake() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  //元データ(Googleカレンダーから取得したイベントデータの配列)
  const dataSheet = ss.getSheetByName('Input');
  //データの抽出先となるシート(スケジュール表)
  const mainSheet = ss.getSheetByName('main');

  // スケジュール表を配列に取得
  const range = mainSheet.getRange('C7:E51');
  const events = range.getDisplayValues(); // 時刻を文字列として取得

  //元データ全体[スケジュール名, URL, 開始時刻]を配列に格納
  const values = dataSheet.getDataRange().getValues();
  values.splice(0, 1);
  for (const rowData of values) {
    const [event, url, time] = rowData;
    // 格納すべき時刻の文字列を生成
    time.setMinutes(time.getMinutes() < 30 ? 0 : 30);
    eventTime = Utilities.formatDate(time, 'JST', 'H:mm');
    // 時刻の文字列が一致した行に記入
    const index = events.findIndex(v => v[0] === eventTime);
    if (index >= 0) {
      events[index][1] = events[index][1] ? events[index][1] + '\n' + event : event;
      events[index][2] = events[index][2] ? events[index][2] + '\n' + url : url;
    } else {
      console.log(`時刻が不明な予定があります。 ${event}`)
    }
  }
  // 一括して書き戻し
  range.setValues(events);
}