gasでアクティブなセルを基準として、行にデータを挿入する方法

以下のような配列があった場合、アクティブになったセルを基準として、データを挿入したいです。シートのインデックス0,0(A1)を基準とした場合、A1,B1,C1,D1の順に1,2,3,4とデータを挿入していきたいです。

let arr = [1,2,3,4];

以下が作ってみたコードで、サイドバーのボタンをクリックするとアクティブなセルにデータが入力されるようにしています。しかし、動作しません。
根本的にやり方が間違えている、便利な関数があるなどの情報があれば教えていただけますか？

htmlファイル
1<!DOCTYPE html>
2<html>
3  <head>
4    <base target="_top">
5  </head>
6  <body>
7    <input type="button" value="卵1" id="egg1">
8    <script>
9      let egg1 = document.getElementById("egg1");
10      egg1.addEventListener("click",() =>{
11        google.script.run.insert();
12      });
13
14    </script>
15  </body>
16</html>
17

gsファイル
1/************************************
2メニューに追加する
3************************************/
4function onOpen() {
5  SpreadsheetApp.getUi()
6      .createMenu("サイドバーを開く")
7      .addItem("開く", "openSidebar")
8      .addToUi();
9}
10
11/************************************
12サイドバーの表示
13************************************/
14function openSidebar() {
15    const html = HtmlService.createHtmlOutputFromFile('index')
16                            .setTitle("サイドバー");
17    SpreadsheetApp.getUi().showSidebar(html);
18}
19
20
21let arr = [1,2,3,4];
22
23function insert(){
24let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
25let act_cell = sheet.getActiveCell();
26
27let ui = SpreadsheetApp.getUi();
28
29
30//基準位置、現在位置
31  for(let i=0;i<arr.length;i++){
32sheet.getRange(act_cell.getRowIndex()+[i] , act_cell.getColumn()).setValue(arr[i]);
33  }
34
35}

gsファイルのループを以下に変更すると、アクティブになっているセルには文字が入力されます。

  for(let i=0;i<arr.length;i++){
sheet.getRange(act_cell.getRowIndex(), act_cell.getColumn()).setValue("文字");
  }

行動規範の内容に同意します

回答1件

ベストアンサー

書き込み処理のコードですが、

sheet.getRange(act_cell.getRowIndex()+[i] , act_cell.getColumn()).setValue(arr[i]);

となっているので、これだとアクティブセルの行番号に [i]を文字列として結合してる状態です。

アクティブセルが A30だと、行番号は30 なので、これに i=0 の場合は文字列として後ろに結合されてしまうので、 300行目に書き込まれてしまいます。さらにRowの方に足されているので、結果として

A30を選択してボタンを押す　→　A300,A301,A302,A303 に1,2,3,4が書き込まれる

となっているはずです。

というわけで修正するなら、以下になります。

sheet.getRange(act_cell.getRowIndex() , act_cell.getColumn()+i).setValue(arr[i]);

ただ、GASによるスプレッドシートへの書き込み処理は setValues で2次元配列をまとめてセルに書き込めます。
これと offset （使い方はシート関数と同じ）という関数を使えばforループ使わず、すっきり書けます。

GAS
1//書き込み処理部分
2let arr = [1,2,3,4];
3
4function insert(){
5let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
6let act_cell = sheet.getActiveCell();
7
8let ui = SpreadsheetApp.getUi();
9
10//arrを2次元配列化し、アクティブセルを起点として横方向に書き込み
11act_cell.offset(0,0,1,arr.length).setValues([arr]);
12
13}
14

投稿2021/06/21 01:32

編集2021/06/22 01:30

sawa

総合スコア3002

blendegg

2021/06/21 10:48

回答ありがとうございます。２つ目のコードのほうがスッキリしていて使いやすいですね。ただわからない部分があります。 offset関数の第一引数と第二引数はシフトの度合いですが、第三引数はなんでしょうか？調べても他の数値に変更してみても、よくわかりませんでした。２とすると挿入が機能しなくなります。第三引数は「高さ」を設定するようですが、この意味がわかりません。

sawa

2021/06/21 11:56

高さと幅です高さは今回一行分なんで1で、幅は配列の長さに該当するのでこのように書いてます

blendegg

2021/06/21 21:40

2とした場合は数値が2行に渡って挿入されるかと思いましたが、そういう動作にはならず何も入力されません。2とした場合は配列の方も二次元にしないと動作しないということですか？

sawa

2021/06/22 00:11

2とした場合は配列を二次元にという認識は誤りで、getValuesを使う場合は二次元配列である必要があります。なので高さ1でも[arr] として二次元配列化しています。 setValuesは書き込み先と書き込む配列のサイズ（縦横）が一致していないとエラーとなります。サイドバーのボタンで実行されてるので気づかないだけで、実行ログにはサイズが不一致というエラーがでてるんじゃないでしょうか。なんで高さを2としたいのかわかりませんが、もし2行分同じものを書き込みたいなら [arr,arr]みたいに書き込む配列もサイズを揃えておく必要があります。

blendegg

2021/06/22 01:27

了解しました。ありがとうございました。

行動規範の内容に同意します