【超便利！】HTMLにGoogleスプレッドシートからデータを取得して表示する方法

GoogleAppsScript
2021.05.24

こんにちは。まさきです。

今回はGoogleAppsScriptでスプレッドシートからデータを取得するWebAPIを作成します。そしてJavaScriptを使ってHTML上に取得したデータを表示するといったことを行います。

スポンサーリンク

目的

まずはデータを入力したスプレッドシートを用意します。

今回はフォームに上記の内容を表示しようと思います。

次にHTMLを用意します。

このHTMLにJavaScriptのfetch()を使って、非同期でGASのAPIを呼び出し、データを更新します。

更新はスプレッドシートのヘッダー(1列目)とHTMLの要素のname属性で紐づけています。

結論

では結論です。

まずはGASで作成するWebAPIに使用するコードはこちらです。

//ここは各自変更してください。
const SPREAD_SHEET_ID = 'スプレッドシートのID';
const SHEET_NAME = 'シート名';

//GETリクエスト時に呼び出される関数
function doGet(e) {
  //スプレッドシートをIDで取得
  const app = SpreadsheetApp.openById(SPREAD_SHEET_ID);
  //シートをシート名で取得
  const sheet = app.getSheetByName(SHEET_NAME);
  //シートの入力内容を全て配列で取得
  const values = sheet.getDataRange().getValues();
  const data = [];

  //シートの入力内容をオブジェクトに詰め替え
  for(let i=0; i<values.length; i++){
    //ヘッダー部(1行目)はスキップ
    if(i === 0)continue;
    const param = {};
    for(let j=0; j<values[i].length; j++){
      param[values[0][j]] = values[i][j];
    }
    data.push(param);
  }
  //返却情報を生成
  const result = ContentService.createTextOutput();

    //Mime TypeをJSONに設定
    result.setMimeType(ContentService.MimeType.JSON);

    //JSONテキストをセットする
    result.setContent(JSON.stringify(data));

    return result;
}

そして公開し、URLを取得します。

次にHTMLです。

<!DOCTYPE html>
<html lang="ja">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <!--Bootstrap-->
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"
        integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
    <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js"
        integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous">
    </script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js"
        integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous">
    </script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"
        integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous">
    </script>

    <title>GAS API</title>
</head>

<body>
    <div class="container">
    <form action="#" method="POST">
        <div class="form-group ">
            <label for="name">名前</label>
            <input type="text" id="name" name="name" class="form-control">
        </div>
        <div class="form-group">
            <label for="email">メールアドレス</label>
            <input type="text" id="email" name="email" class="form-control">
        </div>
        <div class="form-group">
            <input type="radio" id="sex0" name="sex" value="0" checked><label for="sex0">選択しない</label>
            <input type="radio" id="sex1" name="sex" value="1"><label for="sex1">男</label>
            <input type="radio" id="sex2" name="sex" value="2"><label for="sex2">女</label>
        </div>
        <div class="form-group">
            <label for="age">年齢</label>
            <input type="text" id="age" name="age" class="form-control">
        </div>
        <div class="form-group">
            <input type="submit" value="送信する" class="btn btn-primary">
        </div>
    </form>
</div>
</body>
<script>
    //GASのAPIのURL（各自変更してください。）
    const endpoint =
        "★WebAPIのURL★";
        
        //APIを使って非同期データを取得する
        fetch(endpoint)
        .then(response => response.json())
        /*成功した処理*/
        .then(data => {
            //JSONから配列に変換
            const object = data;
            //inputタグそれぞれに取得したデータを設定
            $('input').each(function (index, element) {
                if (object[0][$(element).attr('name')]) {
                    $(element).val([object[0][$(element).attr('name')]]);
                }
            });
        });
</script>

</html>

このHTMLが先ほどの画面になります。
フォームの作成にはBootstrapを使用しています。

画像に alt 属性が指定されていません。ファイル名: image-13-1024x478.png

では、実際の作成方法を順を追ってご説明していきます！

Googleスプレッドシートの用意

まずはスプレッドシートを作成し、データを用意します。

Googleドライブから新規スプレッドシートを作成するか、使いたいスプレッドシートを開いてください。

スプレッドシートを開いたら1行目はヘッダーとし、2行目にデータを記載します。

画像に alt 属性が指定されていません。ファイル名: image-12-1024x671.png

このときシート名はコードで使用しますので、わかりやすいように変更しておきます。
今回は「シート1」のままにしています。

そしてスプレッドシートのIDも控えておきます。

スプレッドシートのIDはURLのd/の後ろです。
例）『https://docs.google.com/spreadsheets/d/★スプレッドシートのID★/edit#gid=0』

スプレッドシートの作業はこれで完了です。

Google Apps ScriptでWebAPI作成

次にGASでコードを記載し、WebAPIとして公開していきます。
今回はスプレッドシートのデータを取得して返却する処理を作ります！

まずはGASを書くため、スクリプトエディタを起動してください。
こちらもGoogleドライブから「その他」＞「GoogleAppsScript」を選択します。

クリックするとスクリプトエディタが起動します。

開いたらタイトルを変更しておきましょう。
そのままだとなんのプロジェクトかわからなくなってしまいます。

それではコードを書いていきます！

デフォルトで記載されているmyFunction関数は使わないので、消してしまって構いません。

今回使用するコードはこちらです。

//ここは各自変更してください。
const SPREAD_SHEET_ID = 'スプレッドシートのID';
const SHEET_NAME = 'シート名';

//GETリクエスト時に呼び出される関数
function doGet(e) {
  //スプレッドシートをIDで取得
  const app = SpreadsheetApp.openById(SPREAD_SHEET_ID);
  //シートをシート名で取得
  const sheet = app.getSheetByName(SHEET_NAME);
  //シートの入力内容を全て配列で取得
  const values = sheet.getDataRange().getValues();
  const data = [];

  //シートの入力内容をオブジェクトに詰め替え
  for(let i=0; i<values.length; i++){
    //ヘッダー部(1行目)はスキップ
    if(i === 0)continue;
    const param = {};
    for(let j=0; j<values[i].length; j++){
      param[values[0][j]] = values[i][j];
    }
    data.push(param);
  }
  //返却情報を生成
  const result = ContentService.createTextOutput();

    //Mime TypeをJSONに設定
    result.setMimeType(ContentService.MimeType.JSON);

    //JSONテキストをセットする
    result.setContent(JSON.stringify(data));

    return result;
}

ここで、2行目,3行目のスプレッドシートのIDとシート名はスプレッドシート作成で確認したものに各自書き換えてください。

スクリプトエディタに記載するとこのようになります。

WebAPIの公開

先ほどのGASが外部から使用できるように公開します。

右上の「デプロイ」から「新しいデプロイ」をクリックしてください。

選択内容は、

種類の選択は「ウェブアプリ」
説明は自由に記載
次のユーザとして実行は「自分」
アクセスできるユーザは「全員」

をそれぞれ選択し、デプロイをします。

デプロイするとウェブアプリのURLが吐き出されますので、メモしておきます。
※デプロイするたびにURLが変わる場合があるようです。デプロイするたびにURLは確認しましょう。

それでは公開されたか確認してみます。
ブラウザのURLに入力してください。

ブラウザ上にスプレッドシートに記載した内容のテキストが表示されればWebAPIの公開は完了です。

HTMLでフォーム作成

WebAPIの呼び出し方法がわかる方は自由に記載して頂いてよいのですが、
わからない方向けに今回はHTMLでフォームを作成し、そこにスプレッドシートの内容を表示したいと思います。

なんでもよいですがテキストエディタでHTMLファイルを作成します。

今回はフォームの見た目を簡単に整えるため、BootstrapをCDNで使用します。

ではHTMLのコードはこちらになります。記載していきましょう！

<!DOCTYPE html>
<html lang="ja">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <!--Bootstrap-->
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"
        integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
    <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js"
        integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous">
    </script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js"
        integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous">
    </script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"
        integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous">
    </script>

    <title>GAS API</title>
</head>

<body>
    <div class="container">
    <form action="#" method="POST">
        <div class="form-group ">
            <label for="name">名前</label>
            <input type="text" id="name" name="name" class="form-control">
        </div>
        <div class="form-group">
            <label for="email">メールアドレス</label>
            <input type="text" id="email" name="email" class="form-control">
        </div>
        <div class="form-group">
            <input type="radio" id="sex0" name="sex" value="0" checked><label for="sex0">選択しない</label>
            <input type="radio" id="sex1" name="sex" value="1"><label for="sex1">男</label>
            <input type="radio" id="sex2" name="sex" value="2"><label for="sex2">女</label>
        </div>
        <div class="form-group">
            <label for="age">年齢</label>
            <input type="text" id="age" name="age" class="form-control">
        </div>
        <div class="form-group">
            <input type="submit" value="送信する" class="btn btn-primary">
        </div>
    </form>
</div>
</body>

</html>

保存したら、ブラウザで開いてください。
このようなシンプルなフォームが表示されれば完了です。

JavaScriptでAPI実行

先ほどのHTMLにAPIで取得したデータを表示する処理を追記していきます。

JavaScriptのファイルを分けて作ってもいいですが、今回はHTML内にJavaScriptを書いていきます。

以下のJSのコードをHTMLファイルに追加してください。

<script>
    //GASのAPIのURL（各自変更してください。）
    const endpoint =
        "★WebAPIのURL★";
        
        //APIを使って非同期データを取得する
        fetch(endpoint)
        .then(response => response.json())
        /*成功した処理*/
        .then(data => {
            //JSONから配列に変換
            const object = data;
            //inputタグそれぞれに取得したデータを設定
            $('input').each(function (index, element) {
                if (object[0][$(element).attr('name')]) {
                    $(element).val([object[0][$(element).attr('name')]]);
                }
            });
        });
</script>

ここでも変数endpointの値はWebAPIデプロイ時取得したURLを入力してください。

今回はJavaScriptのfetch()メソッドを使用し、非同期で取得しています。
非同期といったところでは他にajaxもありますが、今回は記載量が少なくなるためfetchを選びました。
詳しい違いはFetch APIのサイトなどをご確認ください。

また、データをHTMLに表示する部分はjQueryのeach()を使用しました。
すべてのinputタグをループし、inputタグのname属性がデータとして取得したオブジェクトのキーと一致するものがあれば値を変更しています。

余談ですが、$(element).val(“値”)とせず$(element).val([“値”])としているのは、ラジオボタンが.val(“値”)では動かなかったからです。
逆に.val([“値”])のように[]で値を囲むとラジオボタンの値を変更できるなんて知らなかったです・・・

ではJavaScriptの実装が終わったところで再度HTMLをブラウザで起動してみましょう！
データが無事取得できたらWebAPIの実行も成功です！

画像に alt 属性が指定されていません。ファイル名: image-14-1024x542.png

動かなかった方はエラーがないか、URLの変更わすれなどがないかもう一度確認してみてください。

確認のためにもう一度JavaScriptを含めたHTMLの全量を載せておきます。

<!DOCTYPE html>
<html lang="ja">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <!--Bootstrap-->
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"
        integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
    <script src="https://code.jquery.com/jquery-3.3.1.slim.min.js"
        integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous">
    </script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js"
        integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous">
    </script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"
        integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous">
    </script>

    <title>GAS API</title>
</head>

<body>
    <div class="container">
    <form action="#" method="POST">
        <div class="form-group ">
            <label for="name">名前</label>
            <input type="text" id="name" name="name" class="form-control">
        </div>
        <div class="form-group">
            <label for="email">メールアドレス</label>
            <input type="text" id="email" name="email" class="form-control">
        </div>
        <div class="form-group">
            <input type="radio" id="sex0" name="sex" value="0" checked><label for="sex0">選択しない</label>
            <input type="radio" id="sex1" name="sex" value="1"><label for="sex1">男</label>
            <input type="radio" id="sex2" name="sex" value="2"><label for="sex2">女</label>
        </div>
        <div class="form-group">
            <label for="age">年齢</label>
            <input type="text" id="age" name="age" class="form-control">
        </div>
        <div class="form-group">
            <input type="submit" value="送信する" class="btn btn-primary">
        </div>
    </form>
</div>
</body>
<script>
    //GASのAPIのURL（各自変更してください。）
    const endpoint = "★WebAPIのURL★";
        
        //APIを使って非同期データを取得する
        fetch(endpoint)
        .then(response => response.json())
        /*成功した処理*/
        .then(data => {
            //JSONから配列に変換
            const object = data;
            //inputタグそれぞれに取得したデータを設定
            $('input').each(function (index, element) {
                if (object[0][$(element).attr('name')]) {
                    $(element).val([object[0][$(element).attr('name')]]);
                }
            });
        });
</script>

</html>

今回は以上になります。
GASをクライアントサイドから操作できると、できることが一気に増えた感じがしますね。

最後まで見て頂きありがとうございました！

GASはこちらを参考に学習しています。

スポンサーリンク
GoogleAppsScriptプログラミング
APiGASGoogleAppsScriptgoogleスプレッドシートHTMLJavaScript
スポンサーリンク
この記事を書いた人

フリーランスエンジニアです。
未経験からSIer企業に入社して開発案件でプログラミングを学び27歳でフリーランスになりました。
主にHTML,CSS,JavaScript,Javaを書いています。
本を読むことが好きなのでIT以外にもいろいろ読んでいます。
好奇心旺盛でとりあえずやってみる精神。
楽しいことが生きがいで、仕事も私生活も楽しくなるように日々奮闘中。
お酒を飲みすぎないことが目標

まさきをフォローする
シェアする
まさき
まさきのエンジニア図書館
スポンサーリンク
タイトルとURLをコピーしました