スプレッドシートをGASと `/gviz/tq` でHTMLテーブルに変換して加工もする

Last updated at 2025-06-17Posted at 2025-06-15

手順

https://docs.google.com/spreadsheets/d/スプレッドシートID/gviz/tq?tqx=out:html; という URL をベースとして、更にその後ろへ適切なクエリーを & で連結してクエリー URL を構築
- gid=シートID または sheet=シート名 でシート指定
- headers=見出し行数 で見出し行数指定
- range=範囲式 や tq=クエリー文 でデータ加工（必要なら）
その URL を UrlFetchApp.fetch() でフェッチ
成功レスポンスから HTML を取得

const sheetToHTML_fetch = () => {

  const spSheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spSheet.getActiveSheet();

  // 1. URL を構築
  const reqUrl = "https://docs.google.com/spreadsheets/d/"  // 横長過ぎるので記事用に改行
    + spSheet.getId()
    + "/gviz/tq?tqx=out:html;&headers=1"
    + `&gid=${sheet.getSheetId()}`  // または `&sheet=${sheet.getSheetName()}`
  ;

  // 2. URL をフェッチ
  const reqParams = {
    "method": "GET",
    "headers": {
      "Authorization": `Bearer ${ScriptApp.getOAuthToken()}`
    },
    "muteHttpExceptions": true,  // エラー時に完全な HTML を得るため
  };
  const res = UrlFetchApp.fetch(reqUrl, reqParams);

  // 3. 成功レスポンスから HTML を取得
  const statusCode = res.getResponseCode();
  const html = res.getContentText();
  if (statusCode !== 200) {  // muteHttpExceptions を使うならこう、使わないなら try/catch
    throw new Error(`${statusCode}\n\n${html}`);
  }

  // あとは変数 html を好きにいじる
  return html;

};

Spreadsheet Service | Apps Script | Google for Developers
https://developers.google.com/apps-script/reference/spreadsheet
- スプレッドシート操作全般
Chart Tools データソースプロトコル（V0.6）の実装 | Charts | Google for Developers
https://developers.google.com/chart/interactive/docs/dev/implementing_data_source
- tqx=out:html; と tq=クエリー文 に関連
Google スプレッドシート | Charts | Google for Developers
https://developers.google.com/chart/interactive/docs/spreadsheets
- gid=シートID、sheet=シート名、headers=見出し行数、range=範囲式 に関連
Class UrlFetchApp | Apps Script | Google for Developers
https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app
- > fetch(url, params)
Class HTTPResponse | Apps Script | Google for Developers
https://developers.google.com/apps-script/reference/url-fetch/http-response
- レスポンスから色々得る

例

以下、クエリー URL

https://docs.google.com/spreadsheets/d/スプレッドシートID/gviz/tq?tqx=out:html;&headers=1&gid=0

をいちいち全部明記するのはダルいので、先頭～スプレッドシート ID の部分とシート指定（gid または sheet）部分を省略して

/gviz/tq?tqx=out:html;&headers=1

のように表す。

こんなシートがあるとして、

/gviz/tq?tqx=out:html;&headers=1 でこうなる。

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN">
<html>
<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>シート1</title>
</head>
<body>
<table border="1" cellpadding="2" cellspacing="0">
<tr style="font-weight: bold; background-color: #aaa;">
<td>あ</td><td>い</td><td>う</td>
</tr>
<tr style="background-color: #f0f0f0">
<td>&nbsp;</td><td>縦結合セル</td><td>リンク</td>
</tr>
<tr style="background-color: #ffffff">
<td>↑空白セル</td><td>&nbsp;</td><td>セル内
改行</td>
</tr>
<tr style="background-color: #f0f0f0">
<td>12345</td><td>←通常 通貨→</td><td>¥12,345.00</td>
</tr>
</table>
</body>
</html>

色々なめんどさ

大筋はこれで機能するが、現実的には色々とめんどさがある。

得られる HTML の特徴を理解して追加工するのがめんどい

先の例から更に色々試すとこうなる。


`/gviz/tq?tqx=out:html;&headers=0`	`/gviz/tq?tqx=out:html;&headers=2`

`/gviz/tq?tqx=out:html;&headers=1&range=B:C`	`/gviz/tq?tqx=out:html;&headers=1&tq=select%20B,C`

先の例も含めて、得られる HTML には特徴が見出だせる。

最低限見やすいように属性・スタイル付けがされる
見出しのクセが強い
- 別に <th> タグになるとかではなく、ただ背景色が濃いだけ
- 行数を 0 にすると、空白セルしか無い虚無の行が強制挿入される
- 行数を複数にすると、その範囲の行の値がスペース区切りで 1 つの見出しセルの値となる
  - ということで行数は 1 が最も無難
空白セルはノーブレイクスペース   になる
セル結合は破棄され、左上以外は空白セル扱いになる
セル内改行は <br> ではなく LF になる
表示形式（通貨とか）は反映されるが、書式（中央揃えとか）やリンクは破棄される
ページタイトルは大抵シート名になるが、range=B:C のようにした場合だけ "B:C" のような範囲式になる

で、HTML テーブルとなる <table>～</table> 部分だけが欲しい場合を考えると、

テーブルより外側のタグは全て邪魔
勝手に付く各種属性も全て邪魔
完全に失われるセル結合や書式については仕方無いが、セル内改行については復元の余地がある

という訳で加工したくなるのは必至。

文字列として追加工

正規表現で頑張る。

// table タグだけを抽出、余計な属性と空白セルの NBSP を削除、セル内改行で行送り
const htmlTable = /<table[ >].+?<\/table>/s.exec(html)[0]  
  .replace(/ style=".+?"|&nbsp;/g, "")
  .replace(/<table.*?>/, '<table style="white-space: pre-wrap;">')
;

結果

背景も境界線も完全に真っ白の素朴過ぎるテーブルが得られるが、さすがに成果が確認しづらいので body { background: lightgray; } td { background: white; } の CSS を別途適用してスクショした。

<table style="white-space: pre-wrap;">
<tr>
<td>あ</td><td>い</td><td>う</td>
</tr>
<tr>
<td></td><td>縦結合セル</td><td>リンク</td>
</tr>
<tr>
<td>↑空白セル</td><td></td><td>セル内
改行</td>
</tr>
<tr>
<td>12345</td><td>←通常 通貨→</td><td>¥12,345.00</td>
</tr>
</table>

セル内改行については、下手に <br> に置換しようとするよりも white-space: pre-wrap; に頼る方が楽だし安全と判断した。
ただ、今回はテーブル部だけを得ることを最終目標に据えたので style 属性で直接指定したが、実際には何らかの HTML 文書の中にテーブルを配置するのが普通だと思うので、その場合 CSS はここではなくその文書の <head> に書く方が当然良いだろう。

何にせよ、所詮は文字列の塊としての一括変換なので柔軟な処理は難しい。

DOM として追加工

GASでちゃんとしたHTMLパーサーが使いたかったのでライブラリにした - Qiita
https://qiita.com/kairi003/items/06fbf2dc8fb5415c7f60
node-html-parser - npm
https://www.npmjs.com/package/node-html-parser

先人に感謝。

ただし、大元の node-html-parser ライブラリの目的はあくまで「速くてそこそこ便利な HTML パース」程度なので、様々な点で本来の DOM 操作と全く同じようにはできない点に注意。
この加工例では attributes と innerText がそう。

const dom = HtmlParser.parse(html);

// table とその中全ての要素を処理
dom.querySelectorAll("table, table *").forEach(elm => {

  // 余計な属性を削除
  // for (const attr of elm.attributes) {  // TypeError: elm.attributes is not iterable
  //   elm.removeAttribute(attr.name);
  // }
  for (const attrName in elm.attributes) {  // attributes はただのオブジェクト
    elm.removeAttribute(attrName);
  }

  // 空白セルの NBSP を削除、セル内改行を br 要素に
  const text = elm.innerText;
  if (text === "\xA0" || text === "&nbsp;") {  // 実際は後者
    elm.textContent = "";  // テキストの設定は innerText ではできない
  } else if (elm.tagName.toLowerCase() === "td" && text.includes("\n")) {
    elm.textContent = text.replaceAll("\n", "<br>");
  }

});

// table 要素の HTML だけを抽出
const htmlTable = dom.querySelector("table").outerHTML;

結果

先程と同じく body { background: lightgray; } td { background: white; } を別途適用。
まぁ、スクショは全く同じになるに決まっている。

HTML は、セル内改行を <br> で表したことで余計な white-space 指定が消せるようになり、大変シンプルになった。

<table>
<tr>
<td>あ</td><td>い</td><td>う</td>
</tr>
<tr>
<td></td><td>縦結合セル</td><td>リンク</td>
</tr>
<tr>
<td>↑空白セル</td><td></td><td>セル内<br>改行</td>
</tr>
<tr>
<td>12345</td><td>←通常 通貨→</td><td>¥12,345.00</td>
</tr>
</table>

柔軟な処理という点で、文字列の塊を自力でどうにかするより遥かにマシ。

シートの直接加工ができないのがめんどい

HTML を取得する直前に GAS でシートの内容を変更してみる。

  const sheetToHTML_fetch = () => {

    const spSheet = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = spSheet.getActiveSheet();

+   // 0. シートを編集
+   sheet.getRange("A1").setValue("ん");

    // 1. URL を構築
    const reqUrl = "https://docs.google.com/spreadsheets/d/"  // 横長過ぎるので記事用に改行
      + spSheet.getId()
      + "/gviz/tq?tqx=out:html;&headers=1"
      + `&gid=${sheet.getSheetId()}`  // または `&sheet=${sheet.getSheetName()}`
    ;

    // 2. URL をフェッチ
    const reqParams = {
      "method": "GET",
      "headers": {
        "Authorization": `Bearer ${ScriptApp.getOAuthToken()}`
      },
      "muteHttpExceptions": true,  // エラー時に完全な HTML を得るため
    };
    const res = UrlFetchApp.fetch(reqUrl, reqParams);

    // 3. 成功レスポンスから HTML を取得
    const statusCode = res.getResponseCode();
    const html = res.getContentText();
    if (statusCode !== 200) {  // muteHttpExceptions を使うならこう、使わないなら try/catch
      throw new Error(`${statusCode}\n\n${html}`);
    }

    // あとは変数 html を好きにいじる
    return html;

  };

実行後にシートの方を見ると当然変更されていて、A1 の「あ」が「ん」になっている。

では取得した HTML の方は…

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN">
<html>
<head>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>シート1</title>
</head>
<body>
<table border="1" cellpadding="2" cellspacing="0">
<tr style="font-weight: bold; background-color: #aaa;">
<td>あ</td><td>い</td><td>う</td>
</tr>
<tr style="background-color: #f0f0f0">
<td>&nbsp;</td><td>縦結合セル</td><td>リンク</td>
</tr>
<tr style="background-color: #ffffff">
<td>↑空白セル</td><td>&nbsp;</td><td>セル内
改行</td>
</tr>
<tr style="background-color: #f0f0f0">
<td>12345</td><td>←通常 通貨→</td><td>¥12,345.00</td>
</tr>
</table>
</body>
</html>

変わってない！
A1 にあたるセルが「あ」のまま。

最初はラグか何かのせいかと思ったが、Utilities.sleep() で 1 秒待とうが 1 分待とうが同様に反映されなかったので、何らかの理由で根本的に無理なのだと思われる。

  const sheetToHTML_fetch = () => {

    const spSheet = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = spSheet.getActiveSheet();

    // 0. シートを編集
    sheet.getRange("A1").setValue("ん");
+   Utilities.sleep(60000);  // これでも無理

    // 以下略...

ということで、シートを同時に加工するのは諦めるしか無い。

権限がめんどい

ここまでコードの違いによる挙動の変化に焦点を当ててきたが、そもそも初回実行時にはどうしても権限の承認が必要になる。

コードを保存して初回実行すると、

こうなって、

そしてようやく実行できる。

承認後、プロジェクトの「概要」→「プロジェクトの OAuth スコープ」に表示される権限はこの 2 つ。

Connect to an external service
- https://www.googleapis.com/auth/script.external_request
- /gviz/tq の使用で特に必要になる権限
See, edit, create, and delete all your Google Sheets spreadsheets
- https://www.googleapis.com/auth/spreadsheets
- GAS によるスプレッドシート操作一般の権限

まぁ、GAS の各種権限は必要になった時点で自動的にこのような承認を要求されるので、それに従っておけば基本的には問題にならない。
基本的には。

`@OnlyCurrentDoc` でエラーになる謎

ここで、まだ権限の承認をしたことの無い新たなスプレッドシートを用意して、そこに最初の例と同じ sheetToHTML_fetch 関数を記述してみる。
ただし、先頭には /** @OnlyCurrentDoc */ を付記する。

+ /** @OnlyCurrentDoc */
+
  const sheetToHTML_fetch = () => {

    const spSheet = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = spSheet.getActiveSheet();

    // 以下略...

Google サービスの承認 | Apps Script | Google for Developers
https://developers.google.com/apps-script/guides/services/authorization
- > スプレッドシート、ドキュメント、スライド、フォームの手動承認スコープ

この場合の要求スコープは、スプレッドシート操作一般の方が少し狭いものになる。

Connect to an external service
- https://www.googleapis.com/auth/script.external_request
View and manage spreadsheets that this application has been installed in
- https://www.googleapis.com/auth/spreadsheets.currentonly ←ちょっと違う

権限は狭い方が安全なのでそれは良い。
では、その状態で同様に実行してみると…

エラー。
初回だけとかではなく、何度やり直しても同じエラー。

ではエラーとなったレスポンスの HTML を閲覧してやろうじゃないかと、ログから HTML をコピーして適当に error.html のような形で保存して開こうとすると、Chrome では about:blank#blocked というのが代わりに開かれてブロックされてしまう。
VS Code のライブプレビュー機能なら開けたので見てみるとこのようになっていたが、"Sign in" をクリックしても何が起きる訳でもない。

ということでエラー時に完全な HTML を得る価値は無いので、reqParams の "muteHttpExceptions": true は削除しても良いだろう。

    // 2. URL をフェッチ
    const reqParams = {
      "method": "GET",
      "headers": {
        "Authorization": `Bearer ${ScriptApp.getOAuthToken()}`
      },
-     "muteHttpExceptions": true,  // エラー時に完全な HTML を得るため

代わりに try/catch は必須だが、もしそれもしないと次のようなエラーになる。

Exception: Request failed for https://docs.google.com returned code 401. Truncated server response: <!DOCTYPE html><style nonce="EX4Ez-Sa1HlVFQqBBLLI7g">body{height:100%;margin:0;width:100%}@media (max-height:350px){.button{font-size:10px}.button-... (use muteHttpExceptions option to examine full response)

処理内容によるのかもしれないが、少なくとも私の今回のコードではこうなった。
そして私にはエラーになる原理がさっぱりわからない。

自分のスプレッドシートで起きる分には、/** @OnlyCurrentDoc */ を一旦削除してから実行することでより広い権限を承認すれば、その後また /** @OnlyCurrentDoc */ を復元しても動くようになることがわかっている。
しかし、プラグイン等の形で配布する場合にはどう解決できるのか、そもそも同じエラーになるのか、といった点については全然詳しくないので想像もつかない。

他にも変な挙動がめんどい

既に見出し周りは割と変だったが、もっと変な気がする挙動もある。

空行が消失

こんなシートが、

/gviz/tq?tqx=out:html;&headers=1 でこうなる。

空の列は保たれるのに対して、空の行は存在が消える。
不具合というより単に仕様かもしれないが、とにかく消えるし、消えさせない方法は見当たらない。

空白セルと表示形式ありのセルの間のセルが消失

こんなシートが、

/gviz/tq?tqx=out:html;&headers=1 でこうなる。

D3 にあたるセルが空白になっている。
これはさすがに不具合だと思う。（どこに報告すれば良いんだ？）

もう単純に行と列で二重ループして自力で HTML を組んだ方が楽なのでは？

そうだね！

const sheetToHTML_loop = () => {

  const spSheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spSheet.getActiveSheet();

  const numRows = sheet.getMaxRows(), numCols = sheet.getMaxColumns();
  const allRange = sheet.getRange(1, 1, numRows, numCols);

  // セル結合を破棄する場合
  const allValues = allRange.getDisplayValues();
  const htmlTable = allValues.reduce(
    (joinedHTML, row, rowIdx) => joinedHTML + row.reduce(
      (joinedRow, value) => {
        const tag = rowIdx === 0 ? "th" : "td";
        value = value.replaceAll("\n", "<br>");
        return `${joinedRow}<${tag}>${value}</${tag}>`;
      },
      "  <tr>\n    "
    ) + "\n  </tr>\n",
    "<table>\n  <caption>セル結合を破棄</caption>\n"
  ) + "</table>";

  // セル結合を再現する場合
  // 他にもやろうと思えば様々なことを再現できる
  let htmlTable_merge = "<table>\n  <caption>セル結合を再現</caption>";
  for (let rowIdx = 1; rowIdx <= numRows; rowIdx++) {  // 範囲系のインデックスは 1 始まり
    htmlTable_merge += "\n  <tr>\n    ";
    const cellTag = rowIdx === 1 ? "th" : "td";
    for (let colIdx = 1; colIdx <= numCols; colIdx++) {
      const cell = allRange.getCell(rowIdx, colIdx);
      const value = cell.getDisplayValue().replaceAll("\n", "<br>");
      if (! cell.isPartOfMerge()) {
        htmlTable_merge += `<${cellTag}>${value}</${cellTag}>`;
      } else {
        const mergedRange = cell.getMergedRanges()[0];
        if (cell.getA1Notation() === mergedRange.getCell(1, 1).getA1Notation()) {
          const rowSpan = mergedRange.getHeight(), colSpan = mergedRange.getWidth();
          let cellTagOp = cellTag;
          if (rowSpan > 1) { cellTagOp += ` rowspan="${rowSpan}"`; }
          if (colSpan > 1) { cellTagOp += ` colspan="${colSpan}"`; }
          htmlTable_merge += `<${cellTagOp}>${value}</${cellTag}>`;
        }
      }
    }
    htmlTable_merge += "\n  </tr>";
  }
  htmlTable_merge += "\n</table>";

  const style = "<style>body { background: lightgray; } table { display: inline-block; } th, td { background: white; }</style>";
  const html = `<!DOCTYPE html>\n<html>\n<head>\n${style}\n</head>\n<body>\n${htmlTable}\n${htmlTable_merge}\n</body>\n</html>`;
  return html;

};

<!DOCTYPE html>
<html>
<head>
<style>body { background: lightgray; } table { display: inline-block; } th, td { background: white; }</style>
</head>
<body>
<table>
  <caption>セル結合を破棄</caption>
  <tr>
    <th>あ</th><th>い</th><th>う</th>
  </tr>
  <tr>
    <td></td><td>縦結合セル</td><td>リンク</td>
  </tr>
  <tr>
    <td>↑空白セル</td><td></td><td>セル内<br>改行</td>
  </tr>
  <tr>
    <td>12345</td><td>←通常 通貨→</td><td>¥12,345.00</td>
  </tr>
</table>
<table>
  <caption>セル結合を再現</caption>
  <tr>
    <th>あ</th><th>い</th><th>う</th>
  </tr>
  <tr>
    <td></td><td rowspan="2">縦結合セル</td><td>リンク</td>
  </tr>
  <tr>
    <td>↑空白セル</td><td>セル内<br>改行</td>
  </tr>
  <tr>
    <td>12345</td><td>←通常 通貨→</td><td>¥12,345.00</td>
  </tr>
</table>
</body>
</html>

余談：投稿のきっかけ

【GAS】GASとHTML間のデータ受け渡しが難しかった話 - Qiita
https://qiita.com/moyomoyomoyo/items/adb7fe10c084db59a690

こちらの記事にコメントした際の下調べで知った /gviz/tq について、自分の記事として整理して残しておきたかったから。
コメントではなく自分の記事にするとなるとまた調査のギアが 1 段階上がり、勉強になった。

おわり

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

スプレッドシートをGASと `/gviz/tq` でHTMLテーブルに変換して加工もする

手順

例

色々なめんどさ

得られる HTML の特徴を理解して追加工するのがめんどい

文字列として追加工

結果

DOM として追加工

結果

シートの直接加工ができないのがめんどい

権限がめんどい

@OnlyCurrentDoc でエラーになる謎

他にも変な挙動がめんどい

空行が消失

空白セルと表示形式ありのセルの間のセルが消失

もう単純に行と列で二重ループして自力で HTML を組んだ方が楽なのでは？

余談：投稿のきっかけ

`@OnlyCurrentDoc` でエラーになる謎