見出し画像

Googleフォーム▶Googleスプレッドシートで、アップロードした画像をスプレッドシートに自動表示する 2

mir

２週違うネタを挟みましたが、今回は 

こちらの Googleフォーム でアップロードした画像をスプレッドシートに自動表示する シリーズの続きです。

先週はGoogleスプレッドシートのスライサーという機能について書きました。（今回に繋がるネタです）



Googleフォームでアップロードした画像ファイルを スプレッドシート上で閲覧権限メンバーが自由に選択して拡大表示して見れるようにしたい

今回のゴールはこれです。

閲覧権限の共有メンバーが同時に開いていても、各自が見たい画像を選択して大きい表示で閲覧できる。こんな仕組みを GASや AppSheetを使わずに

Googleフォーム + Googleスプレッドシート のシート関数 と機能

だけで実現します。


Googleフォーム でアップロードした画像を リンクしたスプレッドシートで自動表示する おさらい

その前に、前回のシリーズの説明がちょっとわかりにくいなって反省がありまして、おさらいってことで

Googleフォームでアップロードしてもらった画像を自動で スプレッドシートに表示させる手順

を再度まとめてみました。

1. Googleフォームでアップロード質問を作成するとアップロードされたファイルが補完されるフォルダが生成される

Googleフォームの編集画面で、質問タイプで「ファイルのアップロード」を選択してアップロード質問を作ると

回答者がアップロードしたファイルが保存されるフォルダが、Googleフォームが入っているフォルダ内（マイドライブでフォームを作成した場合はマイドライブ）に自動で生成されます。

自分が作ったフォームに回答でアップロードされたファイルはどこにあるの～って人は、Googleドライブを確認しましょう。


2. アップロードされたファイルが入るフォルダを共有設定しておく

アップロードされたファイルは、共有設定をしなければ オーナー以外は見ることが出来ませんし、スプレッドシートでIMAGE関数を使っても表示することも出来ません。

IMAGE関数でスプレッドシート上に画像を表示させるには、アップロードファイルが入る フォルダ（File responses フォルダ）の共有権限を設定する必要があります。

個人情報などコンフィデンシャルなデータを扱う場合は避けた方が良いですが、フォルダ単位で 「リンクを知っている全員」を閲覧者 に共有設定することで、IMAGE関数を使った画像表示が出来ます。

フォルダ単位で共有設定すれば、今後そのフォルダに入る新たな回答でアップロードされるファイルも同じ共有設定が適用されます。


3. 一度に複数ファイルをアップロードする場合は ARRAYFORMULA + SPLIT関数を使う

Googleフォームの回答をGoogleスプレッドシートにリンクした場合、アップロード質問の回答は、そのファイルのURL

https://drive.google.com/open?id=***ファイルID***

がセルに入ります。

ファイルが1つなら自動でハイパーリンクになりますが、アップロードしたファイルが2件以上あると、このように1つのセルにURLが

, 区切り（カンマ+半角スペース区切り）

で複数入る為、それぞれがハイパーリンクにはなりません。

1つのセル内に複数のハイパーリンクを設定することは、GASなら可能ですが、数式（シート関数の組み合わせ）では無理です。

その為、SPLIT関数で分割して 1つのセルに1つのURLが入るようにします。

=SPLIT(C2,", ")

SPLIT関数で分割して、1セルに1URL状態にすると 自動でハイパーリンクとなり、マウスポインタをあてると 画像がプレビューできるようになります。

SPLIT関数を事前に入れておけば良いわけですが、Googleフォームの回答は スプレッドシートにまっさらな行が追加される独特な仕様である為、オートフィルやコピペで数式を下のセルに入れておいても意味がありません。

この式を事前にセットしておく場合は、ARRAYFORMULA関数を組み合わせる必要があります。

※2行目に既に1件目の回答が入ってることが前提

=ARRAYFORMULA(IFERROR(SPLIT(C2:C,", ")))

まだ回答が入っていない（C列が空白）の行はSPLIT関数がエラーを返してしまうんで、IFERROR関数も組み合わせておきましょう。


4. URLをSUBSTITUTE関数で加工して、IMAGE関数で画像表示

このURLを画像として表示させる為にIMAGE関数を組み合わせるんですが、このURLのままだとIMAGE関数が利用できません。

■アップロードで書き込まれるURL
https://drive.google.com/open?id=***ファイルID***
　▼
■IMAGE関数で画像として表示できるURL
https://drive.google.com/uc?id=***ファイルID***

このようにURLの一部を変えて 画像直リンクURLとします。

ここは SUBSTITUTE関数を使って変換しましょう。

=ARRAYFORMULA(IFERROR(
SPLIT(SUBSTITUTE(C2:C,"open?","uc?"),", ")))

これに IMAGE関数を組み合わせれば

=ARRAYFORMULA(IFERROR(
IMAGE(SPLIT(SUBSTITUTE(C2:C,"open?","uc?"),", "))))

Googleフォームの回答でアップロードされた画像を、スプレッドシート上で自動表示が実現出来ました。


5. 画像表示数式を アレンジ

上の式は、ダミーでもよいのでスプレッドシートの2行目に1件目の回答が入った状態で セットする必要があります。

もし、未回答の状態で 式を入れておく必要がある場合は、上の式をアレンジして見出し行（1行目）に

=ARRAYFORMULA(LET(
data,IFERROR(IMAGE(SUBSTITUTE(SPLIT(C:C,", "),"open?","uc?"))),
IF(SEQUENCE(ROWS(data))=1,C1&SEQUENCE(1,COLUMNS(data)),data)
))

こんな式を 入れます。（いきなりコレは難しいですよね）


さらに画像表示だけじゃなくて、マウスをあてた時のプレビューが欲しい！ ユーザーが欲しい画像をダウンロードできるようにしたい！ というニーズに対応する場合は、

HYPERLINK関数を加えて

=ARRAYFORMULA(LET(
data,IFERROR(SPLIT(C:C,", ")),
img,IMAGE(SUBSTITUTE(data,"open?","uc?")),
IF(SEQUENCE(ROWS(data))=1,
C1&SEQUENCE(1,COLUMNS(data)),
IF(data="",,HYPERLINK(data,img)
))))

こんな式にすることで、ユーザーによるプレビューやダウンロードが可能となります。

クリックするとGoogleドライブに保存されている画像が開く

ここまでが前回の内容です。

前回がやや説明が言葉足らずだったので、丁寧目に振り返りましたｗ


改善したい点

このようにアップロードされた画像を自動でスプレッドシート上に表示する仕組みを前回紹介しましたが、この仕組みには幾つか不満点があります。

1つは 画像を表示させるには、ある程度の行の高さ が必要ですし、出来れば上下左右を中央揃えにしたいんですが、新しい回答が真っ新な行挿入である為、行の高さや書式を事前に設定できません。

もう1つは アップロードした画像のファイル名に、アップロードした人のGoogleアカウント名（ニックネーム）が入って表示されてしまう点。

ここから 画像を開くて アップロードした人のGoogleアカウントのアイコンやメアドも見れちゃいます。

これらを解消したい！ってのが今回のテーマです。


Googleフォームをスプレッドシートにリンクして、メンバーに閲覧共有 + 見たい画像を拡大表示できて、プライバシーにも配慮したい

というわけで

  • GASは使いたくない（ちょっとハードルが高い）けど

  • Googleフォームでメンバーに画像をアップロードしてもらって

  • 回答をリンクしたスプレッドシートを閲覧共有でメンバーが見れるようにして

  • 新しい回答があっても設定した表示形式を適用できて

  • さらに 各ユーザーが画像を指定して拡大表示できるようにしたい

  • でもメンバーのアカウント名はオーナー以外には見えないようにしたい

こんなリクエストに応える 仕組みにチャレンジしてみましょう。


1. 列を指定して別シートで参照する

まず、GASなしで 新しい回答があっても設定した表示形式を適用 となると、
事前に表示形式を設定した別シートを用意して「フォームの回答 1」のシートを数式で参照するという方法になります。

閲覧のみでOKなら、この方法がベストですね。

メンバーのアカウント名はオーナー以外には見えないようにしたという要件があるので、ハイパーリンクは諦めましょう。

というわけで、「フォームの回答 1」のC列（アップロード質問の回答列）の隣 D1 に入れる式は HYPERLINK関数を使わない

=ARRAYFORMULA(LET(
  data,IFERROR(IMAGE(SUBSTITUTE(SPLIT(C:C,", "),"open?","uc?"))),
  IF(SEQUENCE(ROWS(data))=1,C1&SEQUENCE(1,COLUMNS(data)),data)
))

こちらの式になります。

それでは 閲覧者に見せる為の回答一覧シートを準備しましょう。

まずフォームの回答 1のシートと別に シートを1枚新たに追加します。

もし簡単にいきたいなら

A1セルに　={'フォームの回答 1'!A:H}　
を入れて 丸ごとフォームの回答シートを参照。

URLが入る C列を 非表示

とします。

これでURLは表示されず、新しい回答があっても書式を事前に設定したセルに出力させることが出来ます。

これでも十分なんですが、今回はもう少し丁寧にいきたいんで、

=LET(data,FILTER(
  CHOOSECOLS('フォームの回答 1'!A:H,1,2,4,5,6,7,8),'フォームの回答 1'!A:A<>""),{{"#";SEQUENCE(ROWS(data)-1)},data})

ちょっと複雑ですが、こんな式を A1セルに入れます。

フォームの回答データである'フォームの回答 1'!A:H を CHOOSECOLS関数 で、アップロード質問の回答で URLが入る 3列目の列を除いた データに絞り込んで

さらに FILTER関数で A列のタイムスタンプが 空でない を条件に不要な行（まだ回答されていない行）を取り除いたものを data と置きます。

3列めのURLをSPLITして画像が入る列は最大5ファイルとすると、 4～8列目 ですね。

この data を元に 回答番号として使う通し番号の1列の配列を

{"#";SEQUENCE(ROWS(data)-1)}　

で用意して、最後に data と横方向に { , } で連結しています。

通し番号のせいで少し式が複雑になってますが、これのおかげで後の式が簡単になります。

あとは、まだ回答の入ってない行も含め いい感じに行の幅や高さ、さらに中央揃え、交互の背景色など 書式を適用、1行目の見出し行を固定表示など 見た目を設定しておきます。

本当はテーブル機能を使いたいところですが、残念ながら見出し行に数式が入ってる為、テーブルは使えません。


2. フォームの回答シートを非表示にしてコピー禁止で閲覧共有する

別シートで参照ができたら フォームとリンクしている フォームの回答 1 のシートを非表示にします。

スプレッドシート右上の「共有」ボタンから 共有設定のダイアログを開き、

一般的なアクセスを 「リンクを知っている全員」を「閲覧者」にして、さらに右上の歯車ボタンから

「編集者は権限を変更して共有できます」と「閲覧者と閲覧者（コメント可）に、ダウンロード、印刷、コピーの項目を表示する」 のチェックを外します。

これで、基本的には非表示シートの中身を見られることはありません。

テストはシークレットモードを使うと良いでしょう。

※Googleログインしているユーザーの場合、Googleドライブからより多くの情報が見えることもあるので、アカウントを2つ以上持っている場合は 別アカウントから確認テストをした方が良いです。


ログインしていないユーザーでもフォームでアップロードした画像がスプレッドシート上で見れました。

コピーしようとして Ctrl + C としても、もきちんとコピー無効が表示されます。

新しい回答があった場合は

ちょっと画像が表示されるまでタイムラグはありますが、このように事前に書式を設定した行に 新しい回答が入ります。

行の高さを確保してるんで画像もほどほど大きく表示されて見やすいですね。

これで、アップロードしたメンバーの情報（Googleアカウントのニックネーム、メールアドレス、アイコン画像）は、ほぼ秘匿性を保つことが出来ました。

※後述しますが 閲覧権限メンバーが 非表示のシートの中身を確認する抜け道がある為、この方法は完全ではありません。


3.閲覧権限ユーザーでも使える フィルタビュー、スライサー

では、ユーザーが 選択した画像を大きく見えるようにする方法はどうすればよいでしょうか？

「閲覧権限」の場合、ユーザーは出来ることが非常に限られます。

ここで活用できるのが、閲覧権限メンバーでも使える「フィルタビュー」機能や

先週の noteで紹介した「スライサー」機能です。

フィルタビューと スライサーの大きな違いは、見た目の違いと、フィルタビューが 同じシートで 直接 表を操作するのに対して、フィルタビューは 別シートから 表を操作できる（操作した場合の結果だけを得ることができる）点。

今回は画像を大きく表示したいので、大きなセル範囲を用意する必要があります。

そうするとフィルタビューを使う場合は、同じシートの
表の右側に 拡大表示エリアを配置するか

表の上に 拡大表示エリアを配置するかになります。

ただ、これらは表と同じ画面に収める形になるので 拡大表示に限界がありますし、

表の右に配置した場合は フィルタビューで行が絞り込まれ非表示になることを考慮して 縦のセル結合を使う工夫が必要になる、

表の上の場合は どうしても表のセル幅を考慮すると セル結合が発生し数式が複雑になる、

という懸念点があります。

また、ユーザーにフィルタビューを使ってもらうのも なかなか難しいんですよね。

というわけで、不慣れなユーザーでも使いやすく、表とは別シートに配置できるスライサーを今回は使ってみましょう。

スライサーの使い方は 先週の noteを参考にしてください。


4. スライサーを別シートに追加する

別シートでスライサーを使うので、フォームの回答 1 シートを参照して一覧表示するシートのシート名を「一覧」

それとは別に シートを1つ追加して シート名を「拡大」としておきましょう。

デザインは自由ですが、無駄な行・列は削除して 1行目はスライサー配置用、1行あけて 3行目をヘッダー行、4行目を画像表示領域としましょう。

拡大シートで メニューから　データ ＞スライサーを追加　として

範囲　'一覧'!A1:F1000
列　プレイヤーのなまえ

と設定して

デザインをいい感じにして A1に配置。候補が出るのを確認しておきましょう。

プレイヤーの名前が 一意になり昇順で表示される

このスライサーをコピーして

右隣り B1セルで貼付け。

わかりやすいように 1つ目のスライサーと色を変えて、列は 通し番号の # を選択しておきましょう。

これでスライサーは完成です。

この2つのスライサーは 連動プルダウンのように 連動しており、「プレイヤーのなまえ」を カスミ とした場合は、 ナンバーには カスミの回答データの通し番号である 3と 6だけが表示されます。

これでスライサーの設置は完成です。

先週のスライサーの noteで書きましたが、

スライサーを配置したシートは 隠れフィルタビュー状態となり、スライサーの操作、およびそれに連動して表示される結果は、閲覧しているユーザーごと（その人だけ）の画面となります。


5. スライサー + SUBTOTALで 閲覧専用スプレッドシートを Webアプリのように使う

で、このスライサーで選択した 行の画像を 表示するのに使うのがSUBTOTAL関数です。

フィルタビュー + SUBTOTAL関数
スライサー + SUBTOTAL関数

は、編集権限を与えずに 閲覧ユーザーが自分だけの 好きな表示に切り替えができて、かつ同時に開いている閲覧者がいても他の人の表示には影響がないという 魔法のテクニックです。

SUBTOTALのユニークな仕様については、過去noteを参照ください。

今回はSUBTOTALの関数コードの 4、MAXを使いましょう。

=SUBTOTAL(関数コード, 範囲1, [範囲2, ...])

関数コード
1 - AVERAGE
2 - COUNT
3 - COUNTA
4 - MAX　　◀　今回はこれを使う
5 - MIN
6 - PRODUCT
7 - STDEV
8 - STDEVP
9 - SUM
10 - VAR
11 - VARP

なぜなら 回答データに割り振った通し番号は、最新が一番大きい（MAX）となっているので、通し番号をMAX指定しておくことで 絞り込まない時は 一番下（一番最新）の回答の画像を表示することが出来るからです。


まず、ヘッダー行の B3セルに通し番号を表示させましょう。

=SUBTOTAL(4,'一覧'!A2:A))

SUBTOTAL関数の 関数コード4（MAX）を 一覧シートの A列に対して使うことで、スライサーで絞り込んだ表の可視セル（表示されているセル）の一番大きい数値を取得できます。

スライサーで何も絞り込んでいない場合は、一覧シートの 最新の回答（最終行）の番号を返します。

あとはこの番号を INDEX関数で 使えばよいですね。

A3セル（プレイヤーのなまえ）

=INDEX('一覧'!D2:F,B3)

A4セル（画像 3列分）

=INDEX('一覧'!D2:F,B3)

式を入れて 最終行（最後の回答）のプレイヤーの名前、画像が表示されれば完成です！


6. スライサーの動作確認をしてみよう

ブラウザのシークレットモードを使って 閲覧共有メンバーの動作を確認しましょう。

スライサー1のみで操作
スライサー1のあと、さらに2で絞り込み
拡大シートのスライサーのしぼ絞り込みは一覧シートには影響しない
また、他のユーザー（タブ）で開いている画面にも影響しない

いずれも動作できてますね。

というわけで、

  • GASは使いたくない（ちょっとハードルが高い）けど

  • Googleフォームでメンバーに画像をアップロードしてもらって

  • 回答をリンクしたスプレッドシートを閲覧共有でメンバーが見れるようにして

  • 新しい回答があっても設定した表示形式を適用できて

  • さらに 各ユーザーが画像を指定して拡大表示できるようにしたい

  • でもメンバーのアカウント名はオーナー以外には見えないようにしたい

これらを実現するスプレッドシートが完成しました～。


完全に秘匿する為には IMPORTRANGE関数で別シートを経由する必要がある

しかし、この方法では

・アップロードしたユーザーの ニックネーム（情報）がバレないようにしたい
・アップロードした画像のダウンロードは禁止したい
　（Googleドライブ内の ファイルにアクセスさせない）

これらを完全には満たせません。

非表示シート + 閲覧共有の弱点

なぜなら アップロードされた画像のURLが入っている フォームの回答 1のシートは非表示にしていますが、

閲覧メンバーには 非表示シートの中身を検索で覗き見るという裏技があるからです。

閲覧メンバーでも検索と置換は使えるので、URLの先頭にくる https:// という文字列で検索をかけると・・・

ご丁寧に非表示シートに発見しました！と教えてくれちゃいます。

でも URLはさすがに長いので ファイルIDまでは 表示されないから大丈夫でしょ？と安心してはいけません。

まるっとお見通しだ！

デベロッパーツールからは、表示されていなかった部分を含めたフルのURLが確認できちゃうんです。

このアドレスをコピーして開けば、ユーザーは Googleドライブ内の元画像が開けてしまい、アップロードした人の Googleアカウントのニックネームが確認できてしまいます。

さらに閲覧者がGoogleアカウントでログインした状態であれば、Googleドライブで最近使用したアイテムにこの画像が表示されるので、そこから ファイルの詳細・履歴で 画像をアップロードした人の アイコンやメールアドレスも確認できてしまいます。

もちろんここまでやる人は少ないんですが、ちょっと怖いですよね。


この辺りが許容できるメンバーで共有・運用するならいいんですが、参加メンバー同士は 連絡先を共有してないコミュニティーだと 避けた方が良いかもしれません。


IMAGE関数の画像は IMPORTRANGEで取得できる！

これを解決するためには、もう一手間かける必要があります。

回答が入る シート 「フォームの回答 1」の入ったスプレッドシートは共有せず、別に 公開用のスプレッドシートを用意して IMPORTRANGE関数で データを取得、この公開用スプレッドシートを閲覧権限で共有という方法をとります。

フォームの回答が入る スプレッドシートのIDは知られてしまいますが、共有していないスプレッドシートなので 勝手に見られる心配はありません。（念のため 非表示にした別シートに入れて参照させる方法もあります）

と言っても、IMPORTRANGE関数で 他のスプレドシートから参照できるのは テキストデータのみで、画像は無理なんでは？

そうです。IMPORTRANGEの回の noteでも そのように書いてます。

IMPORTRANGEでは画像や数式は取得できない

ですが、実は IMAGE関数で表示させている画像は、そのままIMPORTRANGE関数で別シートに出力できるんです！

これは今回色々検証していて発見しました。知らんかった～。

というわけで、大幅に式を変更する必要なく先ほどの式をベースに少しの変更だけで 画像閲覧用スプレッドシートの一覧シートを作成できます。


一覧シート

=LET(
 x,IMPORTRANGE("スプレッドシートのID","フォームの回答 1!A:H"),
  data,FILTER(CHOOSECOLS(x,1,2,4,5,6,7,8),INDEX(x,,1)<>""),
  {{"#";SEQUENCE(ROWS(data)-1)},data})

一覧シートを参照する 拡大シートの式はそのままでOKです。

これで 画像のURL（アップロードした人の情報）を完全に秘匿することができました。

  • GASは使いたくない（ちょっとハードルが高い）けど

  • Googleフォームでメンバーに画像をアップロードしてもらって

  • 回答をリンクしたスプレッドシートを閲覧共有でメンバーが見れるようにして

  • 新しい回答があっても設定した表示形式を適用できて

  • さらに 各ユーザーが画像を指定して拡大表示できるようにしたい

  • でもメンバーのアカウント名はオーナー以外には見えないようにしたい

これらを実現する Googleフォーム と Googleスプレッドシートを連携させた 共有画像表示簡易システム完成です。


Googleフォーム ネタはまだまだあるけど一旦終了

SPLIT関数の超応用例の流れで、途中他のネタを挟みつつ Googleフォームについて 4回 noteで取り上げてきました。

今回の Googleフォームのアップロード画像自動表示シートは、

  • Googleフォームの理解

  • Googleドライブの共有の仕組みの理解

  • Googleスプレッドシートのスライサー機能等の理解

  • IMPORTRANGE関数の理解

  • IMAGE関数の理解

  • SUBTOTAL関数の挙動の理解

  • LET関数、CHOOSECOLS関数 など新関数の理解

などなど、これまでの noteで取り上げたネタ 総動員でしたね。

しかしGoogleフォームについては、GASを使った運用方法・自動化など まだまだ 書いてないネタがありますｗ

いずれ、それらについても書きたいと思いますが、別ネタで書きたいものもあるんで、一旦Googleフォームシリーズは終了します。

次回は、Googleスプレッドシートの新機能 複数選択ドロップダウン を使った 面白いテクニックについて書きたいと思います。



この記事が気に入ったらサポートをしてみませんか？