【GAS】スプレッドシート上にスクリプトからボタンを生成しスクリプトを割り当てる方法。

この記事は GAS道場 Advent Calendar 2019 の16日目の記事です。 Google Apps Script(GAS)をこれから使おうという方向けのアドベントカレンダーになります。

今回は、スプレッドシートにボタンを配置することをスクリプト上で行い、自動化したいなと思います。ボタンの配置とスクリプトの割り当てについては以下の記事で紹介しましたが、こちらの記事で紹介した方法はすべて手動になります。

www.pnkts.net

そこをスクリプトで生成＆割り当てをすることで自動化につなげたいなと思いまして、今回の記事で紹介したいと思います。

ボタンの準備

まずは、スプレッドシートに貼り付けるためにボタンを作成します。 f:id:ponkotsu0605:20191215182105p:plain

画像ファイルを用意したいので、例えばGoogle図形描画を利用しても良いと思います。

Google図形描画での画像ファイル作成方法

まずは、ボタンを頑張って作ります。

f:id:ponkotsu0605:20191215182316p:plain

ここで注意しないといけないのが、キャンバス全体が画像になってしまうので、上記スクショのようにキャンバスに対してボタンが小さいと、ボタンの周りのボタンじゃないところも当たり判定ができてしまので、なるべくキャンバスを小さくしておきましょう。 f:id:ponkotsu0605:20191215182732p:plain

例えば、このように、キャンバスを小さくします。

f:id:ponkotsu0605:20191215182753p:plain

画像ファイルに出力するには、ファイル → ダウンロード → PNG画像(.png) を選択すると、png画像としてダウンロードできます。

f:id:ponkotsu0605:20191215183100p:plain この画像を今回使用するGoogleドライブにあげておきましょう。これで準備が完了です。

アップした画像のID取得

次に、スクリプトからアップした画像を扱うために、IDを知る必要があります。 Googleドライブ上で取得する方法を知らなかったため、以下のようなスクリプトを用意しました。

function fileCheck() {

  var files = DriveApp.getFolderById('12K7GoCZ_5fgYj9U9ik7maB6TSvl44evd').getFiles();

  while (files.hasNext()) {
    var file = files.next();
    Logger.log(file.getName() + ' ' + file.getId());
  }
}

getFolderById()で指定しているIDは、アップしたGoogleドライブのディレクトリのURLに書いてあります。 https://drive.google.com/drive/u/0/folders/12K7GoCZ_5fgYj9U9ik7maB6TSvl44evd これのfoldersの右側のところの 12K7GoCZ_5fgYj9U9ik7maB6TSvl44evd になります。

f:id:ponkotsu0605:20191215183100p:plain

上記のディレクトリに対して上記のスクリプトを実行してみます。

[19-12-15 01:36:30:893 PST] button 12pbpu50VlKjwcsmb3eGM-_EHdoPKzvRx1V8NzTDFaUo
[19-12-15 01:36:30:895 PST] すき家 12-GbPpq5nlh1M8wsVC0DGsOpBJ9YW3uPoMZqSkt1zzs
[19-12-15 01:36:30:896 PST] button.png 127h6WJcpqJmzgqbIMPYOAzFaQqGX2AQf

このようにログが出力されますが、先程アップしたbutton.pngのIDの 127h6WJcpqJmzgqbIMPYOAzFaQqGX2AQf になります。

ボタンを配置するスクリプト

やっとですが、ボタンを配置するスクリプトです。

function myFunction() {  
  var data = DriveApp.getFileById('127h6WJcpqJmzgqbIMPYOAzFaQqGX2AQf')

  var blob = data.getBlob()
  Logger.log(blob.getBytes().length)
  var sheet = SpreadsheetApp.openById('12-GbPpq5nlh1M8wsVC0DGsOpBJ9YW3uPoMZqSkt1zzs').getSheetByName('シート1')
  sheet.insertImage(blob, 1, 1).assignScript('test4')
}

SpreadsheetApp.openById()には、同じように、スプレッドシートのIDも入力しましょう。

そして、実行してみましょう。今回も、以下の記事で紹介した行と列を転置するスプレッドシートとスクリプトを使用します。

www.pnkts.net

function test4() {
  // 現在のアクティブなスプレッドシートを読み込む
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  
  // 現在のアクティブなシートを読み込む
  var sheet = SpreadsheetApp.getActiveSheet();
  
  var data = sheet.getDataRange().getValues();
  data = transpose(data)
  Logger.log(data);
  sheet.clear();
  var rows = data.length;
  var cols = data[0].length;
  sheet.getRange(1,1,rows,cols).setValues(data); 
}

function transpose(a) {
  return Object.keys(a[0]).map(function (c) {
    return a.map(function (r) {
      return r[c];
    });
  });
}

今回のボタン生成のスクリプトでは、このtest4()というメソッドをボタンに割り当てています。

スクリプトの実行

ボタンを押してみましょう。スプレッドシート上にボタンが配置されたと思います。 (スプレッドシートを開いている場合は、ページの更新を押す必要があると思います。) f:id:ponkotsu0605:20191215184649p:plain 生成するボタンの位置は、 sheet.insertImage(blob, 1, 1)の第2引数と第3引数でしている行と列の指定で調整することができます。