GoogleSheetsで野鳥フォトブック作成に挑戦！①画像リスト取得編

（2021.7.19更新）
今回は、Google Sheetsでブックレット作成に挑戦してみました。クラウドのドライブにアップロードした画像を自動面付するGASプログラムです。悪戦苦闘しながらどうにか完成。日中、PDFで出力しようとするとデータが正確に戻ってこないという現象が多発し、しばらく頭を悩ませまていたのですが、そこはVBAと連携すればなんとかクリアできることが判明。

人それぞれ生活の中で大切にしているものは千差万別ですが、自由に表現できる世界は心をじわーっと癒やしてくれます。世界に一つしかない手作りのオリジナルブックレット。ストレスフルな生活の中でどなたかの笑顔を作り出せるヒントとなれば嬉しいです。

それでは、以下に駆け出しエンジニアによる未熟なソースではありますが、GSブックレット作成手順をご紹介します。このプログラムは、事前に640px × 640pxの画像（ファイル名には必ず「_P△」とページ数を入れる）をGoogleドライブにアップロードしてから使用します。

①まず、GmailにログインしてGoogleのスプレッドシートを新規作成。
https://drive.google.com/drive/u/0/my-drive

②スクリプトエディタを起動します。

シートにオリジナルメニューを追加

③ファイル名を「onOpen」としてシートメニューにbookletSを追加します。

↓↓↓ソースはこちら↓↓↓
※2021.7.16 下記スクリプトに右綴じメニューを追加しました💻

function onOpen(){
 //メニューにbookletSを追加
 var myMenu=[
   {name: "画像リスト取得", functionName: "getFileList" },
  null,
   {name: "セルサイズ変更(640px)", functionName: "ImageSize_640px"},
  null,
   {name: "左綴じ(100px)", functionName: "ImageSize_l100px"},
   {name: "右綴じ(100px)", functionName: "ImageSize_r100px"},    
 ];
SpreadsheetApp.getActiveSpreadsheet().addMenu("bookletS",myMenu); 
}

④ブレークポイントを追加し、「デバッグ」を実行。エラーが出ないことを確認したら、続けて「実行」をクリックします。

スクリプトを利用するためには、初回のみ「承認」作業が必要になります。

シートに「bookletS」のメニューが追加されました。

Googleドライブフォルダ内のファイル一覧とミニサムネイル表示のスクリプト

⑤次に、Googleドライブにアップロード済みの画像リストを取得するためのスクリプトを登録します。ファイル名には予め「_P（半角数字）」と入れておくことが必須条件です。

「onOpen」と同様、スクリプトファイル名を「getFileListt」とし、下記ソースを登録。

function getFileList() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName('シート1');
 var lastRow = sheet.getLastRow();
 var clRange = ss.getRange("A:F");
    clRange.clear();   
 var iRange = sheet.getRange("B3");
 var cutRange = sheet.getRange("A:F");
 var topRange = sheet.getRange("A1:F1");
 var eRange = sheet.getRange("E:E");

 //セル幅を設定
 sheet.setColumnWidth(1,30);
 sheet.setColumnWidth(2,200);
 sheet.setColumnWidth(3,60);
 sheet.setColumnWidth(4,680);
 sheet.setColumnWidth(5,40);  
 sheet.setColumnWidth(6,330);

 //100行目まで100ピクセルの高さに設定
 sheet.setRowHeights(2, 100, 30);  

 //文字折返し、センタリング設定
 cutRange.setWrapStrategy(SpreadsheetApp.WrapStrategy.CLIP);
 eRange.setHorizontalAlignment('center');  

 // Googleドライブの指定フォルダにあるファイルを一括取得
 var files = DriveApp.getFolderById('★★画像をアップロードしたGoogleドライブフォルダのIDを入力してください★★').getFiles();
 var gdFiles = [];
 while (files.hasNext()) {
   var file = files.next();
   var name = file.getName();
   var size = file.getSize();
   var url = file.getUrl(); 
   gdFiles.push([name,size,url]);
 }
 console.log(gdFiles);
 
 // B2セルを起点にして一括書き込み
 sheet.getRange(2,2,gdFiles.length,gdFiles[0].length).setValues(gdFiles);

 //文字体裁
 cutRange.setFontSize(12);
 topRange.setHorizontalAlignment('center');
 topRange.setVerticalAlignment('middle');

 // 指定のセルに文字列、関数を書き込み
 sheet.getRange(1,1).setValue('No'); 
 sheet.getRange(1,2).setValue('ファイル名');
 sheet.getRange(1,3).setValue('サイズ');
 sheet.getRange(1,4).setValue('URL');
 sheet.getRange(1,5).setValue('画像');
 sheet.getRange(1,6).setValue('画像ID');
 sheet.getRange(2,1).setFormula('=IFERROR(VALUE(REGEXEXTRACT(B2,"_P([0-9]+)")),"")');
 sheet.getRange(2,5).setFormula('=IFERROR(IMAGE("https://drive.google.com/uc?export=download&id="&(F2),1),"") ');
 sheet.getRange(2,6).setFormula('=IFERROR(REGEXEXTRACT(D2,"d/(.*)/view"),"")');

　//データがある行のみ数式を書き込む
 for (var i = 3; i <= lastRow; i++) {
   if(iRange.isBlank()){
   }else{
 //コピー対象のセル範囲を選択する
 var copyRange = sheet.getRange(2, 5, lastRow, 6);
 //貼り付け先のセル範囲を選択する
 var pasteRange = sheet.getRange(3, 5, lastRow, 6);
 //コピー対象のセル範囲のデータを貼り付け先のセルにコピーする
 copyRange.copyTo(pasteRange);
 var copyRange = sheet.getRange(2, 1 ,lastRow, 1);
 var pasteRange = sheet.getRange(3, 1, lastRow, 1);
 copyRange.copyTo(pasteRange);
 }
}
}

↑↑↑「'★★画像をアップロードしたGoogleドライブフォルダのIDを入力してください★★'」の部分にフォルダIDを入力してください。

ブレイクポイントを設定し、「デバッグ」→「実行」します。

すると…スクリプトで指定したフォルダに入っているファイルリストとミニサムネイルが出現！リストが表示されない場合には、メニューの「画像リスト取得」を再度クリックしてください。

注）尚、こちらのスクリプトを参考に製作をされる場合には、オリジナル素材を用いる、アーティストの著作権を侵害しない使い方を必ずお守りください。以上をご理解いただいたかたのみご利用いただけます。

↓↓続きの面付スクリプトシート解説は、こちら↓↓(*•̀ᴗ•́*)و ̑̑