見出し画像

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

mir

GoogleフォームとGoogleスプレッドシートの連携シリーズ 3回目です。

今回はGoogleフォームのアップロード質問の理解と アップロードした画像をスプレッドシートに表示させる為の数式について書きたいと思います。

前回は チェックボックス質問の回答を集計する為の数式について書きました。

今回も 基本となる ARRAYFOMRULAを使った配列数式の理解が必須となります。


Googleフォームのアップロードとは

Googleフォームの質問タイプには「ファイルのアップロード」があります。これを使うことで、フォームの回答者は回答の際に画像やPDF など自由にファイルをアップロード出来ます。

ただしGoogleフォームのアップロード質問は、いわゆるアップローダーではありません。非常にクセが強く挙動が独特です。

回答する側もフォームを作成する側もしっかり理解をして使わないと、思いもよらぬ結果となり後悔することになります。

まずは自分がGoogleフォームのオーナーで、アップロード質問を作成する際の手順と注意点を見ていきましょう。


Googleフォームでアップロード質問を作成する

フォーム作成時に質問タイプで「ファイルのアップロード」を選択すると、上のような表示が出ます。

ここで重要になるのが

回答者は Google へのログインを求められます

この部分。

これは アップロード質問を含むGoogleフォームは「Googleアカウントでログインした状態でないと回答できない」ことを意味しています。

iPhoneユーザーだとGoogleアカウントを持ってないって人もいるんで、これが結構厄介だったりします。

「次へ」を押すと


このような アップロード質問の設定画面になります。ここで

アップロードできるファイルの形式を制限したり

一度にアップロードできるファイルの最大数を指定したり

1,5,10で それ以外は選べない

個々のファイルサイズの上限を指定できます。

これも選択肢以外の設定はできない

アップロードするファイルサイズの合計は、標準で1GBまでとなっていますが、こちらの上限を変更したい場合は「変更」の文字がリンクになっているので、ここから

かなり大きいサイズまで指定できる

フォームの設定のタブに遷移して 「アップロード済みファイルの合計サイズの上限」を変更することができます。

アップロードされたファイルが格納される保存場所ですが、アップロード質問の「フォルダを表示」をクリックして確認できます。

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

フォルダの構成と名前は

フォームが入っているフォルダ
　L　フォームのファイル名 (File responses)
　　　Ｌ　アップロード質問名 (File responses)　◀ ここにファイルが入る

となります。

1つのフォーム内に複数のアップロード質問を作る可能性もあるので、そのフォーム全体のアップロード用フォルダが生成され、その子フォルダ（サブフォルダ）として質問毎のアップロード用フォルダが生成されます。

これでアップロード質問を含むフォームが作成できました。

もし、この手順で アップロード質問が作成出来ない（選択出来ない）場合は、GoogleWorkspaceを利用していて

  • このフォームが 共有ドライブ内に 保存されいている

  • 管理者が 制限をかけている（データ損失防止をオンにしている）

のどちらかである可能性が高いです。

公式ヘルプを参照

次に回答者側の画面を見てみましょう。


Googleフォーム アップロード質問に回答する

作成したアップロード質問を含むフォームの回答画面は、冒頭にこのような文言が表示されます。

ファイルをアップロードしてこのフォームを送信すると、Google アカウントに関連付けられている名前、メールアドレス、および写真が記録されます

なんか誤認を与えそうな表記ですが、プライバシーにかかわる重要なポイントです。

Googleフォームのアップロード質問は、Googleアカウントでログインが必須であり、ファイルをアップロードして送信した際は フォームのオーナー（および共同編集者）に、

回答者（自分）の

名前（ニックネーム）
メールアドレス
写真（アイコン画像）

が伝わる仕様となっています。

「写真」って書いてあるんで、たまに「自分のGoogleフォトの写真が全部相手に見られてしまうんでは？」と過剰に恐怖を感じる人がいますが、そんなことはありません。

言葉が足りないんで誤認を与えやすいですが、ここでオーナーに伝わる「写真」は、あくまでGoogleアカウントのアイコンで仕様している写真（画像）のことです。

それでも ニックネームで本名を使っていたり、他の活動で使ってるGoogleアカウントで検索されると身バレしちゃうものだったり、会社アカウントを使ってたりする場合は、アップロード質問に回答する場合注意が必要です。

残念ながらこれを回避する方法はなく、Googleアカウントでログインしていない状態でフォームを開いても

このようにログイン画面に飛ばされてしまいます。

普段使用しているGoogleアカウントがフォームのオーナーに伝わるのが絶対に嫌だ！という場合は、別で匿名用Googleアカウントを取得して、そちらでログインした状態で回答しましょう。

また、オーナーが アップロード質問で ファイル形式や最大数、サイズの制限をしていた場合

たとえば 画像ファイルのみ、1ファイル、1MB上限

回答者画面にも制限内容が記載され

この制限に反するファイルや 個数、サイズだとアップロードが出来ません。


アップロード質問 回答時の動き（ローカルファイル）

Googleフォームでアップロードした時の挙動を見ていきましょう。

質問内の「ファイルを追加」というボタンを押すと

このようなダイアログ（画面）が立ち上がります。

ここでローカル（端末内）に保存されているファイルを選択してアップロード、もしくは自分のGoogleドライブ内のファイルを選択することが出来ます。

スマホで開いた場合もほぼ同じような表示

まずはローカルファイルをアップロードした場合を見ていきましょう。

こんな感じで複数ファイルを選択して一気にアップロードが出来ます。

ここで重要なのが アップロードのダイアログにあった

送信されたファイルを編集したり削除したりすることはできません。

こちらの記載。

これは、仮にGoogleフォームの設定でオーナーが 「回答の編集を許可する」をオンにしていた場合でも、

オーナーがここをONにしていた場合

アップロードしたファイルに関しては、差し替えや削除が出来ないってことです。

その為、回答後に

このように「回答を編集」から編集画面に入っても

アップロード質問に関しては、アップロード済みのファイルは削除も中身の確認もできず、ファイルの追加だけ出来る状態となります。

また、オーナーが 「回答者にコピーを送信」としていた場合

回答者のGmail（Googleアカウントのメール）に回答の控えが届きますが

ここからも アップロードしたファイルの中身を確認することは出来ません。


アップロード質問の回答は Googleドライブに履歴として残る

じゃあフォーム回答後に、アップロードしたファイルの内容を確認したい時はどうしたらよいか？

実はGoogleドライブに回答の痕跡が残っており、そこから確認することが出来ます。

Googleドライブの「マイドライブ」を開いた状態で、右上の丸にi の詳細表示ボタンを押して、サイドバーを「履歴」に切り替えます。

履歴を確認すると アップロード質問でアップしたファイルの動きがわかります。

  1. アップロードしたファイルはマイドライブに入り

  2. そのファイルのコピーを生成して、「ファイル名＋自分のアカウント名」に名前を変更

  3. そのファイルをフォームのオーナーのフォルダに移動させる（マイドライブからは削除）

こんな動きをしているわけです。

つまり、Googleフォームの回答でアップロードしたファイルは、自分のGoogleドライブ（マイドライブ）に控えが残っています！

マイドライブを開けば、アップロードしたファイルを確認することが出来るわけですね。

この時の オーナー側の Googleドライブの動きも見てみましょう。

オーナー側の Googleドライブ File resposes フォルダの履歴

こちらは 回答者がアップロードしたファイルを、アップロードしたファイルが格納されるフォルダ（File resposes フォルダ）に追加したという履歴だけが残っています。

この履歴からオーナーやフォームの共同編集者は、回答者のアカウント名、メールアドレス、アイコン画像を確認することが出来るわけです。

フォームオーナーの File resposes フォルダ に格納されたファイルは、設定しない限りは、基本的には回答者も閲覧・編集することは出来ません。

アップロードしたファイルは、完全に回答者の手を離れ「オーナーの所有物」となる仕組みです。

このように アップロード質問は、回答者、フォームのオーナー、両方のGoogleドライブにファイルが生成されるので、両方のドライブの容量を消費します。

回答者（自分）、もしくはオーナーの Googleアカウント容量が、どちらかが 上限（無料版の場合は15GB）を超えている場合は、アプロード質問は正しく機能しません。


アップロード質問 回答時の動き（Googleドライブから選択）

それでは、端末内のファイルをアップロードするのではなく、アップロード質問で 既にGoogleドライブ内にあるファイルを選択した場合はどうなるのか？

こちらの動きを見てみましょう。

上のように 例えばGoogleスプレッドシートをGoogleフォームでアップロード（選択）した場合

回答者側のドライブの履歴


  1. アップロードで選択したファイルのコピーを そのファイルが保存しているフォルダ内で生成、「ファイル名＋自分のアカウント名」に名前を変更

  2. コピーしたファイルをフォームのオーナーのフォルダに移動させる（自分のドライブのフォルダからは削除）

このようになります。

一方、オーナー側のドライブには

オーナーのドライブの履歴

回答者がコピーして ファイル名を「ファイル名 ＋ アカウント名」に変更したファイルを生成して、フォルダに追加したという履歴が残ります。

このGoogleフォームでアップロードした（送った）Googleスプレッドシートは、回答者のドライブにあるものと、オーナーが受け取ったものは 別ファイルとなります。

ここが勘違いしやすいんですが、Googleスプレッドシートを共有したわけではなく、相手にコピーを渡したと考えてください。

Googleフォームで自分のGoogleドライブ内にあるファイルや、Googleスプレッドシート、Googleドキュメントをアップロードして相手に送る場合、自分の Googleスプレッドシートを「共有」する必要はありません。

また、フォーム回答後に 自分のGoogleスプレッドシートを編集しても、相手が受け取ったスプレッドシートが同期することはありません。

注意しましょう。


【検証】Googleフォームのアップロードを使って、コピー不可のファイルはコピーできないか？

Googleフォームのアップロード質問は、自動でファイルのコピーが生成される。

この挙動を使って、他の人から共有された「印刷やコピー、ダウンロード不可」の設定がされているファイルをコピーできないか？

こんな悪いことを考える人がいるかもしれませんｗ

で、試してみたわけですが・・・　やっぱ無理でしたｗ

エラーになってフォーム送信が完了せず、ファイルのアップロード（コピー）は出来ませんでした。

さすがに対策されてますね。


Googleフォームのアップロード まとめ

ここまでをまとめると

■Googleフォームのアップロード まとめ
【フォーム作成者側の注意点】
・アップロード質問は、ファイルの種類、数、サイズを正しく設定する
・Googleフォームが共有ドライブにある場合はアップロード質問は作成できない

【フォーム回答者側の注意点】
・回答者はGoogleアカウントでログインしておく必要がある
・回答者のニックネーム、メールアドレス、アイコン画像はフォームのオーナーに伝わる
・アップロードしたファイル名には 自分のニックネームが付く
・アップロードしたファイルは、回答者からは削除や編集が出来ない
・アップロードしたファイルはGoogleドライブから確認できる
・Googleドライブ内のファイル（スプレッドシートやドキュメント含む）をアップロードした場合、送られるのはコピーしたファイルである
　-自分のドライブ内のファイルと同期はしない
　-自分のファイルの共有設定を変える必要はない
・コピーが制限されてる閲覧権限の共有されたファイルはアップロードできない

匿名ではGoogleフォーム経由でファイルアップロードは出来ないってことですね。

これは 違法なファイルのやりとりに使えてしまうことを抑止する為の仕様かなと考えます。仕方ないですね。

どうしてもGoogleアカウントの無い人や匿名の人でも、自分のGoogleドライブにファイルをアップロードできる仕組みが欲しい！という場合は、GASを使ってアップローダーを作ることは出来ます。

ただ、この場合当然違法なファイルを誰かがアップロードした場合も、そのファイルのオーナーは自分となります。

匿名アップローダーを公開 みんなに使ってもらう
 ▼
誰かが違法な児童ポルノ画像をアップ
 ▼
Googleさんが検知、規約違反と判断される
 ▼
身に覚えがないのにアカウントをBANされる

こんなことにならないように注意しましょう。


Googleフォームのアップロード質問を Googleスプレッドシートで扱う

つづいて Googleフォームの回答をスプレッドシートにリンク（出力）した場合、アップロード質問がどうなるのか？を見ていきましょう。

複数アップロードした場合は自動でリンクにならない

Googleフォームの回答をスプレッドシートにリンクした場合、アップロード質問の回答は、アップロードしたファイルのURLが書き込まれます。

URLは

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

こんな構成です。

で、1回の回答のアップロードファイル数が1件であれば

こんな感じで ハイパーリンクとなり、マウスオーバーで プレビューが表示されるんですが

アップロードしたファイルが2件以上あると、このように1つのセルにURLが 

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

で複数はいる為、ハイパーリンクにはなりません。

つまり、このままだとちょっと扱いづらいってことです。


またまた ARRAYFORMULA + SPLITの出番

1つ1つのURLが , 区切り（カンマ+半角スペース区切り） ってことなら、前回と同じく ARRAYFORMULA関数とSPLIT関数の式が使えますね。

Googleドライブ内のファイルのURLなんで カンマや半角スペースが含まれることはないので、第3引数「個々の文字の分割」を FALSEにしなくても良さそうです。

C列がアップロード質問の回答だった場合、

D列に

=ARRAYFORMULA(LET( data,IFERROR(SPLIT(C:C,", ")), IF(SEQUENCE(ROWS(data))=1,C1&SEQUENCE(1,COLUMNS(data)),data)))

前回のQ3の回答の式を入れることで、アップロードされたファイルのURLが分割され、1つのURLが1つのセルに入り 自動でハイパーリンクになります。

オーナーである自分が扱うだけなら これでも十分ですが、回答者にも見てもらいたいと思った場合は、このスプレッドシートを単純に閲覧権限でリンク共有しただけではうまくいきません。

スプレッドシートは閲覧できても、リンク先のアップロードされたファイルの閲覧権限が無いので プレビューは表示されず、リンクを開いても「アクセス権が必要です」となります。

というわけで、アップロードされたファイルが入る フォルダ（File responses フォルダ）の権限を設定する必要があります。

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

このフォルダ単位の共有設定・管理は 過去のnoteでも触れています。

共有設定は 特定のメンバーであれば アカウントを指定して共有、もしくはグループアドレスで共有とすべきですが、特に流出しても影響がない情報であれば リンク共有としても良いでしょう。

これで閲覧メンバーも アップロードされたファイルが閲覧できるようになりました。


アップロードした画像ファイルを直接スプレッドシートで表示する

では、今回の本題であるこの一つ一つのGoogleドライブの画像ファイルのURLを 画像として直接スプレッドシート上で表示できるように式をアレンジしていきましょう。

Googleドライブ内の画像ファイルを、スプレッドシート上で表示させるには IMAGE関数が活用できます。

重要: 使用できる URL は、drive.google.com でホストされていないもののみです。

現在はこのような記載があるので、そのうちIMAGE関数でGoogleドライブ内の画像ファイルを表示できなくなる可能性はありますが、とりあえず現状ではまだ使えます。

このGoogleドライブ内の画像をIMAGE関数で表示させる方法は、過去note「画像にまつわるエトセトラ」でも解説しています。

ポイントは2つで

  1. Googleドライブの画像ファイルが一般的なアクセスが可能な状態である（リンクを知っている人は誰でも閲覧可能という共有設定）

  2. URLを https://drive.google.com/uc?id=***ファイルID***  にする

これだけです。

リンク共有とするので、個人情報に関連する画像や絶対に外部に見せたくない画像ファイルを Googleスプレッドシート上で表示させるのは避けた方が良いです。

この点が問題なければ、 アップロード用フォルダを リンク共有状態として

SPLIT関数で分割したアップロードされた画像ファイルのURL

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

を IMAGE関数で扱える

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

このように変換すれば良いわけです。

変える箇所は　open? ▶ us?　だけなんで、ここはSUBSTITUTE関数を使っておきましょう。（ファイルIDには ? は使われないので、念のため ?を含んで変換させています）

試しに F7セルのURLを IMAGE関数でセル内画像として表示させる為、G7セルに

=IMAGE(SUBSTITUTE(F7,"open?","uc?"))

と入れてみましたが、ちゃんと画像が表示されてますね。

それでは、簡単なお題いってみましょう。


Q1. ARRAYFORMULA + SPLITで リンクURL化した アップロード質問の画像ファイルを IMAGE関数で 画像としてスプレッドシートに表示させる式を作りたい。

これに挑戦してみましょう。でも、既に式はほぼ出来上がっていて

=IMAGE(SUBSTITUTE(F7,"open?","uc?"))

これを

=ARRAYFORMULA(LET( data,IFERROR(SPLIT(C:C,", ")), IF(SEQUENCE(ROWS(data))=1,C1&SEQUENCE(1,COLUMNS(data)),data)))

こちらと組み合わせるだけです。

サンプルデータがなくて申し訳ないですが、式が理解できて入れば簡単です。考えてみましょう！







↓↓
ここから回答です。
↓↓



A1. ARRAYFORMULA + SPLITで リンクURL化した アップロード質問の画像ファイルを IMAGE関数で 画像としてスプレッドシートに表示させる式を作る

回答です。

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

こんな式にすれば良いですね。

試してみると、このように各セルにアップロードされた画像が表示されます。

でもこれだと画像として表示はされたけど、ハイパーリンクが失われてしまい、クリックしても大きい画像が開きませんし、マウスオーバーでプレビューも出ません。


Q2. 画像として表示させたアップロードファイルを、さらにハイパーリンクにもしたい

というわけで、もう1つお題です。

IMAGE関数で表示させた画像をさらにハイパーリンクとしても機能させるにはどうすればよいでしょうか？

式が長くなってきましたが、考え方はシンプルです。頭の中を整理して式の作成にチャレンジしてみましょう。







↓↓
ここから回答です。
↓↓



A2. 画像として表示させたアップロードファイルを、さらにハイパーリンクにする数式

回答です。

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

ここで使うのはHYPERLINK関数ですね。

画像のハイパーリンク化についても、過去のnoteで解説しています。

ただ、HYPERLINK関数を使う際に 

HYPERLINK(URL, [リンクラベル])
※今回は リンクラベルは IMAGE関数で出力される画像

このように URLと リンクラベルが必要になるので、LET関数で分けて変数化しています。

data,IFERROR(SPLIT(C:C,", ")),　// これが URL
img,IMAGE(SUBSTITUTE(data,"open?","uc?")),　//これが リンクラベル（画像）

それぞれこのように変数で置いて、最後に

HYPERLINK(data,img)

このようにしています。

ただ、この式だと 一見空白に見えるセルは実は空白になっていません。

なので、丁寧に書くとしたら

もう1回IFで空白を判定して分岐してから 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)
))))

こんな式が良いかもしれません。

新しい回答があった際も画像は自動で表示され、ハイパーリンクになっていることが確認できます。

ただし、画像の見栄えをよくする為には、もう少し行の高さがあった方がいいですし、中央寄せだと良いんですが、行の高さ や 画像を中央寄せにするといった  条件付き書式では制御できない「見た目」については、残念ながら GASを使うしか方法がありません。

また、オーナーのアカウントがバレるのはともかく、アップロードしてくれたユーザーのアカウント名がファイル名に付いててバレちゃうのも困りもの。

これもファイル名を変更するとなるとGASを使うことになります。


GAS無しで出来ないの？

というわけで 次回は、

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

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

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

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

  • さらにユーザーは指定した画像を拡大表示できて

  • 同時に2名以上が開いた場合でもそれぞれ画像拡大が使えて

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

こんなリクエストに応える 表に挑戦してみましょう。


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