2019年4月16日 / 最終更新日時 : 2022年7月12日 officeの杜 Google

Google Apps Scriptでスプレッドシートに画像を貼り付ける【GAS】

年度初めは忙しく、今年度こそこれまで作りためたGASアプリを全公開しようと目論んでいるのですが、GWまでは十分な時間が取れそうにない。そんな時ようの小ネタもいくつか蓄えています。

そんな中、Googleスプレッドシートの機能増強の中で地味ながら便利なものとして「セルに画像埋め込み」の機能が搭載されました（2019年3月19日より）。これはこれまであったようなimage関数のようなURLで参照して、セル内に表示するのとは違い、バイナリデータとして直接セル内に表示させるものです。両者には明確な違いがあるので、これを今回はしらべてみました。

1 今回使用するスプレッドシート
2 image関数の使い方
- 2.1 概要
- 2.2 使い方
3 セル内に画像を埋め込み
- 3.1 概要
- 3.2 使い方
4 Google Apps Scriptから利用する
- 4.1 スプレッドシート上に画像として貼り付け
  - 4.1.1 概要
  - 4.1.2 ソースコード
- 4.2 セル内に画像を貼り付け
  - 4.2.1 概要
  - 4.2.2 ソースコード
    - 4.2.2.1 外部URLを使う手段
    - 4.2.2.2 Base64変換したものを使う手段
5 関連リンク

今回使用するスプレッドシート

画像を埋め込んだスプレッドシート

特殊な関数例に含めているので、他の事例も入っています。imageシートがimage関数で、画像埋め込みがバイナリデータとして埋め込んだ事例です。

image関数の使い方

概要

この関数は非常にユニークな関数で、セルの中に画像を表示させる事が出来るという関数です。Google Spreadsheetは画像を挿入出来るのですが、その配置は他のオブジェクトと同じで、セルとは関係なく貼り付けられてしまいます。対してこの関数の場合、URLを関数に渡すと、セル内に画像を配置できるので、画像はセルの位置に属します。使いようによっては、非常に有用な関数です。

図：画像一覧や商品一覧なんかも作れますね。

使い方

使い方自体は非常に簡単です。以下のように、image()の中に画像への直リンクURLを貼ればOK。直リンクでなければ表示されずエラーになりますので、注意。スプレッドシートを元に書類や申請書をジェネレートしてる場合、画像の挿入よりも、上部に於けるレイアウトの自由度やきっちり収める上ではこの関数は非常に役立ちます（ロゴ入れたりとかね）。

//使用例
=image(A5)

1 2	//使用例 =image(A5)

としても良いでしょう。また、現在すでにdeprecated扱いになってはいますが使えるGoogle Chart APIにてURLに文字列を投げて、QRコードを生成しても良いでしょう。WebAPIにて、RESTで投げると画像で返してくれるものであれば、なんでも利用出来ます。最近はあまりそういうサービスを見かけなくなりましたが。

//Chart APIを利用した画像の生成
=image("http://chart.apis.google.com/chart?cht=qr&chs=200x200&chl=" & A11)

1 2	//Chart APIを利用した画像の生成 =image("http://chart.apis.google.com/chart?cht=qr&chs=200x200&chl=" & A11)

更には、Google DriveやPhotosの画像の直リンクを取得して、Google Apps Scriptなどを併用して貼り付けるのも面白いと思います。但し、Google DriveのケースにおいてGoogle Apps for Workだと書類やファイルの外部からの参照や共有を通常は出来ないように管理者が設定してると思われるので、この方法を試しても画像が表示されません。image関数で使用出来る画像のURLは、ウェブに一般公開され誰でも見られるリンクでなければなりません。当然、社内のイントラ内の画像も使えません。よって、パスワードが必要なサイトの中の画像等は使えません。

//Google Drive内の画像へ直リンクを貼って表示
=image("http://drive.google.com/uc?export=view&id=" & ここにファイルのIDを入れる)
 
//Google Photosの画像直リンク
=image("ここに画像への直リンクURLを入れる")

//Google Drive内の画像へ直リンクを貼って表示

=image("http://drive.google.com/uc?export=view&id=" & ここにファイルのIDを入れる)

//Google Photosの画像直リンク

=image("ここに画像への直リンクURLを入れる")

セル内に画像を埋め込み

概要

こちらはセルの中に画像データそのものを挿入するため、image関数とは違い外部参照をしません。よって、画像であればここに直接取り込む事が可能です。公式リファレンスにそれっぽいことが書いてありませんが、大きすぎる画像の場合にはエラーが起きるようです。

外部参照をしていないので、例えば社内で使用する稟議書などの判子画像を用意しておき、これを挿入しておくなんて事も可能になりますね。

使い方

使い方はいたって簡単。

挿入したいセルを選択しておく
スプレッドシートのメニューより「挿入」⇒「画像」⇒「セル内の画像」を選ぶ
アップロード画面が出るので、画像を放り込む。
これで画像が取り込まれます。
Deleteキーで普通に消すことが可能

図：セルの上の画像がこれまでのシート上に画像を貼り付けるもの

図：関数ではないので直接データが表示される

Google Apps Scriptから利用する

スプレッドシート上に画像として貼り付け

概要

今回ここで紹介するテクニックは、セル内ではなくセルの外に画像を貼り付けるために、Google Apps Scriptから使う手法です。まだ、セル内に画像を埋め込むメソッドは用意されていないので、おそらく近いうちにこれをメソッドとして利用できるのではないかと思います。期待しています。実装されたらここに追記予定です。

さてこの手法を用いると、image関数では不可能であった「認証の必要なサイト内の画像をBlobで取得して挿入することが出来る」という点です。また、閉じたG Suite内のDriveからの画像をも取得して貼り付けることができるので、

例えばワークフローを構築した際に、承認したら、担当者の画像ファイルを埋め込むといった事が可能になり、捺印データをGoogle Driveに格納、その直リンクURLをワークフローのシートにリストアップ。そして、承認後にはそのURLからBlob取得して対象のセルに張り込むことが実現できます。地味に欲しかった機能です。まだまだペーパーレスになりきれない日本には必要な機能かもしれません。

もちろん、外部のサイト（例えばDropboxやInstagram）などの画像サイトからOAuth認証でデータを取得して、シートに張り込めるので、サイドバーから画像選んでセル内挿入なんて面白いと思います。

ソースコード

今回は自分のドライブにアップロードし、直リンクを生成しました。DriveのURLから貼り付けたいのであれば、直リンクそのものをsetFormulaにてimage関数を組み立てて、数式を貼り付ける手法も良いですね。また、Drive外の外部サイトならばUrlfetchAppにて外部データを取得して、Blobに変換後に貼り付ければOKです。

function insertman() {
  //シートを取得
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("画像埋め込み");
  
  //ドライブから画像を取得
  var inkan = "1qki19bF4T1huDF6-osKKHHTF0tBgwqw1"
  var blob = DriveApp.getFileById(inkan).getBlob();

  //画像を挿入
  sheet.insertImage(blob, 1, 1);
}

function insertman() {

//シートを取得

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheetByName("画像埋め込み");

//ドライブから画像を取得

var inkan = "1qki19bF4T1huDF6-osKKHHTF0tBgwqw1"

var blob = DriveApp.getFileById(inkan).getBlob();

//画像を挿入

sheet.insertImage(blob, 1, 1);

}

セル内に画像を貼り付け

概要

2022年1月19日、ようやくGoogle Apps Scriptからセル内に画像を直接埋め込みのメソッドが装備されました。CellImageBuilderと呼ばれるクラスを利用して埋め込む事が可能です。実際に使ってみましたが、以下のような特徴があります。

埋め込める画像は外部のURLである必要があります。
Google Drive上の画像の直リンクは利用することが出来ません。
また、Blobで取得してそれを埋め込む事も出来ません。
ただし、Base64変換をしたものであれば埋め込むことは可能

よって、利用するにはGASからはアクセスできる外部のサーバ等に画像が配置されている必要性があるのでここがちょっと難点です。

図：こんな具合に自動化で貼り付け可能

ソースコード

外部URLを使う手段

自分のレンタルサーバに画像を配置して、それを埋め込むコードを作ってみました。実行すると画像が対象のセル内にバッチリ表示出来ました。これを応用することで、ウェブアプリ等のワークフロー等で承認したら、ここに印鑑の画像を挿入するといったような事に利用することができそうです。

function cellimageman(){
  //シートを取得
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("画像埋め込み2");

  //外部から画像を取得
  var inkan = "http://officeforest.org/wp/wp-content/uploads/2022/02/stamp.png"

  //セルイメージを貼り付ける
  var image = SpreadsheetApp.newCellImage()
                            .setSourceUrl(inkan)
                            .setAltTextTitle("印鑑")
                            .setAltTextDescription("承認された証")
                            .build();

  sheet.getRange("B2").setValue(image);

}

function cellimageman(){

//シートを取得

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheetByName("画像埋め込み2");

//外部から画像を取得

var inkan = "http://officeforest.org/wp/wp-content/uploads/2022/02/stamp.png"

//セルイメージを貼り付ける

var image = SpreadsheetApp.newCellImage()

.setSourceUrl(inkan)

.setAltTextTitle("印鑑")

.setAltTextDescription("承認された証")

.build();

sheet.getRange("B2").setValue(image);

}

また、上記のコードでは、貼り付け以外にも代替テキストや説明文も入れている為、セルを右クリック⇒画像⇒代替テキストをセルに追加を見てみると、きちんと内容が含められています。よって、同時にいろいろな情報をセットしておいて、後でこれを以下のコードの用に取り出して利用することも可能です。

//セル内イメージの情報を取得
function cellimageinfo(){
  //シートを取得
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("画像埋め込み2");
  var rangeman = sheet.getRange("B2")

  //画像イメージを取得
  var value = rangeman.getValue();

  //セル内イメージの各種情報を取得
  if(val.toString() === "CellImage") {
    console.log(value.getUrl());
    console.log(value.getAltTextTitle());
    console.log(value.getAltTextDescription());
  }
}

//セル内イメージの情報を取得

function cellimageinfo(){

//シートを取得

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheetByName("画像埋め込み2");

var rangeman = sheet.getRange("B2")

//画像イメージを取得

var value = rangeman.getValue();

//セル内イメージの各種情報を取得

if(val.toString() === "CellImage") {

console.log(value.getUrl());

console.log(value.getAltTextTitle());

console.log(value.getAltTextDescription());

}

図：セル内イメージの情報もセットできる

図：取得したセル画像の情報

Base64変換したものを使う手段

Google Driveのファイル直リンクはNGでしたが、一旦取得してBlobとして受け取ったものを、Base64変換したものであれば、Builderを通じて埋め込める事がわかりましたので、これで外部サーバがなくとも埋め込みが可能です。

//Google Drive内の画像ファイルを埋め込むテク
function cellimageman2(){
  //シートを取得
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("画像埋め込み2");
  
  //ドライブから画像を取得
  var inkan = "1qki19bF4T1huDF6-osKKHHTF0tBgwqw1"　　　//画像のファイルID
  var blob = DriveApp.getFileById(inkan).getBlob();

  //base64に変換
  var c_type = blob.getContentType();
  var base64 = Utilities.base64Encode(blob.getBytes());
  var data = "data:" + c_type + ";base64, " + base64;

  //セルイメージを貼り付ける
  var image = SpreadsheetApp.newCellImage()
                            .setSourceUrl(data)
                            .setAltTextTitle("印鑑")
                            .setAltTextDescription("承認された証")
                            .build();

  sheet.getRange("B2").setValue(image);
}

//Google Drive内の画像ファイルを埋め込むテク

function cellimageman2(){

//シートを取得

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheetByName("画像埋め込み2");

//ドライブから画像を取得

var inkan = "1qki19bF4T1huDF6-osKKHHTF0tBgwqw1"　　　//画像のファイルID

var blob = DriveApp.getFileById(inkan).getBlob();

//base64に変換

var c_type = blob.getContentType();

var base64 = Utilities.base64Encode(blob.getBytes());

var data = "data:" + c_type + ";base64, " + base64;

//セルイメージを貼り付ける

var image = SpreadsheetApp.newCellImage()

.setSourceUrl(data)

.setAltTextTitle("印鑑")

.setAltTextDescription("承認された証")

.build();

sheet.getRange("B2").setValue(image);

}

Google Apps Scriptでスプレッドシートに画像を貼り付ける【GAS】

今回使用するスプレッドシート

image関数の使い方

概要

使い方

セル内に画像を埋め込み

概要

使い方

Google Apps Scriptから利用する

スプレッドシート上に画像として貼り付け

概要

ソースコード

セル内に画像を貼り付け

概要

ソースコード

外部URLを使う手段

Base64変換したものを使う手段

関連リンク

コメントを残すコメントをキャンセル

Google Cloud Next 2019で発表されたG Suite関連新機能

Google Apps Scriptでスプレッドシート上の全文検索をする【GAS】

今回使用するスプレッドシート

image関数の使い方

概要

使い方

セル内に画像を埋め込み

概要

使い方

Google Apps Scriptから利用する

スプレッドシート上に画像として貼り付け

概要

ソースコード

セル内に画像を貼り付け

概要

ソースコード

外部URLを使う手段

Base64変換したものを使う手段

関連リンク

コメントを残す コメントをキャンセル

Google Cloud Next 2019で発表されたG Suite関連新機能

Google Apps Scriptでスプレッドシート上の全文検索をする【GAS】

コメントを残すコメントをキャンセル