スプレッドシートのGASでサイドバーへ指定範囲の値を表示させたい

##やりたいこと
Googleスプレッドシートを使っています。

見出しのある1行目は固定表示してスクロールしても表示され続ける設定にしていますが、
それとは別に、BA列～BC列（このファイルの最終列）までを
スクロールしても固定表示させたいと思っています。

イメージとしてはWebサイトでいうサイドバーのようなものです。

逆に言えば下にスクロールしたときに、
・1行目がスクロールせず固定表示
・BA～BC列（ファイル最終列）までスクロールせず固定表示
・A2～AZ2だけがスクロールされていく
といったことをしたいと考えています。

具体的には
BA2:BC10のセルを
下へスクロールしても固定表示をさせたいのです。

ただ、スプレッドシートの固定表示機能で
10行目まで固定にしてしまうと、
BA～BC列以外も固定表示となってしまい実用的ではありません。

##試してみたこと
スプレッドシートの固定表示設定ではこのようなことはできないため、
GASでBA2:BC10セルの値（これはリアルタイムで更新されていきます）を
GASで作ったサイドバーへ転写できたら、
擬似的にBA2:BC10セルの値を下へスクロールしても固定表示できるのでは、と考えました。

しかし検索してみましたが、
指定された範囲の値をサイドバーへリアルタイムに転写（表示）させる方法がヒットしませんでした。

##知りたいこと
GASで作ったスプレッドシートのサイドバーへ
BA2:BC10セルの最新の値を常時更新される形で転写（表示）させる方法があれば
教えていただければ幸いです。

よろしくお願いいたします。

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

回答1件

ベストアンサー

サイドバーにスプレッドシートの内容を常に反映させることはできますが、実用には問題があります。

サイドバーの幅が 300ピクセル固定のため、コンテンツ内容によっては横スクロールが必要
サイドバーのコンテンツの部分更新ができない(全体を再設定する必要がある)
サイドバーのコンテンツ更新時にフォーカスがサイドバーに奪われ、A1セルが選択＆スクロールされる。

特に３が致命的で、どこかのセルを変更する度に選択中のセルがA1になってしまうので使いづらすぎました。

JavaScript
1/** 「起動時」トリガーに設定するメソッド */
2function onOpenByTrigger(e) {
3  showSidebar();
4}
5
6/** 「編集時」トリガーに設定するメソッド */
7function onEditByTrigger(e) {
8  showSidebar();
9}
10
11/** サイドバーの表示(更新) */
12function showSidebar() {
13  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート1");
14  const values = sheet.getDataRange().getValues();
15
16  const template = HtmlService.createTemplateFromFile("Sidebar");
17  template.values = JSON.stringify(values);
18  const htmlOutput = template.evaluate();
19  SpreadsheetApp.getUi().showSidebar(htmlOutput);
20}

HTML
1<!DOCTYPE html>
2<html>
3  <head>
4    <base target="_top" />
5    <title>SpreadsheetSidebar</title>
6    <script>
7      window.addEventListener("DOMContentLoaded", () => {
8        const values = JSON.parse("<?= values ?>");
9        const table = document.querySelector("table");
10
11        values.forEach(rowValue => {
12          const tr = document.createElement("tr");
13          table.appendChild(tr);
14          rowValue.forEach(value => {
15            const td = document.createElement("td");
16            tr.appendChild(td);
17            td.textContent = value;
18          });
19        });
20      });
21    </script>
22  </head>
23  <body>
24    <h3>サイドバー</h3>
25    <table border="1"></table>
26  </body>
27</html>

(追記)
getDataRange はデータが入っているセルすべてを含む範囲を取得するので、特定範囲が欲しければ、getRangeを使ってください。

JavaScript
1//const values = sheet.getDataRange().getValues();
2//        ↓
3//列番号を指定する場合1
4const values = sheet.getRange(53, 2, 9, 3).getValues();
5//A1Notaion を指定する場合
6const values = sheet.getRange("BA2:BC10").getValues();

また、HTMlファイル（Sidebar.html）ですが

例えばテーブルタグの装飾にCSSは利用できるのでしょうか？

すべて利用きるかまではわかりませんが、CSSは適用できます。

投稿2020/07/18 03:32

編集2020/07/19 07:29

draq

総合スコア2577

kikuchi33

2020/07/19 06:38

お忙しいところご回答いただき、ありがとうございました。具体的なコードまでありがとうございます。投稿いただいた内容を元にテストをしていたのですがどうしてもサイドバーが表示されずにいてお返事が遅くなってしまいました。（「シート1」の変更を見逃しておりました）大変申し訳ございませんでした。お陰様でサイドバーを表示させることができました！なるほど（3）はたしかにケースによっては致命的ですね…。ただ今回のケースですとサイドバーに表示させたい範囲が限定されているため（3）については問題がなさそうです。ありがとうございます。このコードですと、指定シートの全てのセル情報がテーブルタグとしてサイドバーに表示されるようなのですが、サイドバーに表示させる範囲をBA2:BC10のような形で指定することはできるものでしょうか？コードを見ておりますが、どこをどう編集したらいいものかがわかりませんでした。また、HTMlファイル（Sidebar.html）ですが例えばテーブルタグの装飾にCSSは利用できるのでしょうか？お忙しいところ恐れ入りますが、お手すきの際に引き続きお知恵をお貸しいただければ幸いです。よろしくお願いいたします。

kikuchi33

2020/07/19 08:42

ご回答ありがとうございました。ご投稿いただいたコードで範囲指定をすることができました！お忙しいところ誠にありがとうございました。複数の別の範囲をサイドバーへ反映させるために以下のようにコードを修正してみました。（合わせてサイドバーのタイトルを「サイドバー」とするための修正も他の検索でヒットした記事のコードを参考にしてみましたが、これであっていますでしょうか）一応動作はしているようなのですが、もしお時間がございましたら修正箇所がないか見ていただければ幸いです。 ```JavaScript /** 「起動時」トリガーに設定するメソッド */ function onOpenByTrigger(e) { showSidebar(); } /** 「編集時」トリガーに設定するメソッド */ function onEditByTrigger(e) { showSidebar(); } /** サイドバーの表示(更新) */ function showSidebar() { const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート1"); const values = sheet.getRange("BA2:BC10").getValues(); const values2 = sheet.getRange("A1:B2").getValues(); const template = HtmlService.createTemplateFromFile("Sidebar"); template.values = JSON.stringify(values); template.values2 = JSON.stringify(values2); const htmlOutput = template.evaluate().setTitle('サイドバー') ; SpreadsheetApp.getUi().showSidebar(htmlOutput); } ``` ```html <!DOCTYPE html> <html> <head> <base target="_top" /> <title>SpreadsheetSidebar</title> <script> window.addEventListener("DOMContentLoaded", () => { const values = JSON.parse("<?= values ?>"); const table = document.querySelector(".table"); values.forEach(rowValue => { const tr = document.createElement("tr"); table.appendChild(tr); rowValue.forEach(value => { const td = document.createElement("td"); tr.appendChild(td); td.textContent = value; }); }); const values2 = JSON.parse("<?= values2 ?>"); const table2 = document.querySelector(".table2"); values2.forEach(rowValue => { const tr = document.createElement("tr"); table2.appendChild(tr); rowValue.forEach(value => { const td = document.createElement("td"); tr.appendChild(td); td.textContent = value; }); }); }); </script> </head> <body> <h1>AAA</h1> <table class="table"></table> <h1>BBB</h1> <table class="table2"></table> </body> </html> ``` よろしくお願いいたします。