見出し画像

GoogleSheetsで野鳥フォトブック作成に挑戦！②自動面付編

SONO

※2021.8.27 面付けシートの解説を追記しました。

前号①の画像リスト取得編では、「シート1」で使うマクロをご紹介しました。

今回は、「シート2」で使用するスクリプト3点をご紹介します。SEQUENCEとVLOOKUPとIMPORTRANGEの関数をトリオにした自動面付の高パフォーマンスは圧巻。万が一、シートの関数を触って壊してしまっても、メニューのマクロを再度実行すれば復活します。

①＜左綴じ＞画像を100pxで表示するスクリプト

下記は、「シート1」のAセル『NO』（頁番号）と「シート2」のSEQUENCE値とをVLOOKUPで照合させ、自動面付するスクリプトです。別シートから照合するのでIMPORTRANGE関数も使っています。画像を100pxで表示する左綴じ用のスクリプトです。これを『ImageSize_l100px』というスクリプト名で登録します。ソース内にある5ヶ所の『"https://docs.google.com/spreadsheets/d/××××××××××/edit#gid=××××"』には、シート1のURLを入力します。

function ImageSize_l100px() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[1];
var kRange = ss.getRange("A:L");
  kRange.clearContent(); 
var fRange = sheet.getRange("A:N");
var tRange = sheet.getRange("M2:M3");
var copydRange = sheet.getRange("D5:D100");
var copyeRange = sheet.getRange("E5:E100");
var copyiRange = sheet.getRange("I5:I100");
var copyjRange = sheet.getRange("J5:J100");   
var col1Range = sheet.getRange("C4:F4");
var col2Range = sheet.getRange("H4:K4");
var color="#cef8dc";

//セルの幅を設定
sheet.setColumnWidth(1,80);
sheet.setColumnWidth(2,45);
sheet.setColumnWidths(3,14,100);
//セルの高さを設定
sheet.setRowHeights(1,2,30);
sheet.setRowHeights(5,100,100);

// セルのカラー設定
col1Range.setBackground(color);
col2Range.setBackground(color);

// 指定のセルに文字列、関数を書き込み
fRange.setFontSize(18);
tRange.setFontSize(14);
fRange.setHorizontalAlignment('center');
fRange.setVerticalAlignment('middle');
sheet.getRange(2,13).setValue('ページ数');
sheet.getRange(3,13).setValue('シート枚数');
sheet.getRange(3,7).setValue('左綴じ');
sheet.getRange(3,14).setValue('=$N$2/4');
sheet.getRange(4,4).setValue('表');
sheet.getRange(4,5).setValue('面');
sheet.getRange(4,9).setValue('裏');
sheet.getRange(4,10).setValue('面');
sheet.getRange(5,1).setFormula('=IFERROR(SEQUENCE(N3),"")');
sheet.getRange(5,3).setFormula('=IFERROR(SEQUENCE($N$3,1,$N$2,-2),"")');

//★★下記5件は、「https://docs.google.com/spreadsheets/d/××××××××××/edit#gid=××××」の部分にシート1のURLを入力してください★★
copydRange.setFormula('=IFERROR(VLOOKUP(C5,IMPORTRANGE("https://docs.google.com/spreadsheets/d/××××××××××/edit#gid=××××","シート1!A:E"),5,false),"")');
copyeRange.setFormula('=IFERROR(VLOOKUP(F5,IMPORTRANGE("https://docs.google.com/spreadsheets/d/××××××××××/edit#gid=××××","シート1!A:E"),5,false),"")');
copyiRange.setFormula('=IFERROR(VLOOKUP(H5,IMPORTRANGE("https://docs.google.com/spreadsheets/d/××××××××××/edit#gid=××××","シート1!A:E"),5,false),"")');
copyjRange.setFormula('=IFERROR(VLOOKUP(K5,IMPORTRANGE("https://docs.google.com/spreadsheets/d/××××××××××/edit#gid=××××","シート1!A:E"),5,false),"")');
sheet.getRange(5,13).setValue('=VLOOKUP(F5,IMPORTRANGE("https://docs.google.com/spreadsheets/d/××××××××××/edit#gid=××××","シート1!A:E"),5,false),"")');

sheet.getRange(5,6).setFormula('=IFERROR(SEQUENCE($N$3,1,$N$2-$N$2+1,2),"")');
sheet.getRange(5,8).setFormula('=IFERROR(SEQUENCE($N$3,1,$N$2-$N$2+2,2),"")');
sheet.getRange(5,11).setFormula('=IFERROR(SEQUENCE($N$3,1,$N$2-1,-2),"")');
}
画像6
画像2

①のスクリプトソースを下図のように入力したらブレークポイントを指定し（行番号の左をクリックすると設定できます）、「デバッグ」でエラーが発生しないことを確認し、「実行」をクリック。

画像6

②＜右綴じ＞画像を100pxで表示するスクリプト

同じく、右綴じ用スクリプトです。①と②のスクリプトは、全体のレイアウトを確認するために使用します。『ImageSize_r100px』という名前で登録し、こちらも下記ソース内5ヶ所の『"https://docs.google.com/spreadsheets/d/××××××××××/edit#gid=××××"』の部分にシート1のURLを入力します。ブレークポイント設定後、「デバッグ」→「実行」。

function ImageSize_r100px() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[1];
var kRange = ss.getRange("A:L");
  kRange.clearContent(); 
var fRange = sheet.getRange("A:N");
var tRange = sheet.getRange("M2:M3");
var copydRange = sheet.getRange("D5:D100");
var copyeRange = sheet.getRange("E5:E100");
var copyiRange = sheet.getRange("I5:I100");
var copyjRange = sheet.getRange("J5:J100");   
var col1Range = sheet.getRange("C4:F4");
var col2Range = sheet.getRange("H4:K4");
var color="#cef8dc";

//セルの幅を設定
sheet.setColumnWidth(1,80);
sheet.setColumnWidth(2,45);
sheet.setColumnWidths(3,14,100);
//セルの高さを設定
sheet.setRowHeights(1,2,30);
sheet.setRowHeights(5,100,100);

// セルのカラー設定
col1Range.setBackground(color);
col2Range.setBackground(color);

// 指定のセルに文字列、関数を書き込み
fRange.setFontSize(18);
tRange.setFontSize(14);
fRange.setHorizontalAlignment('center');
fRange.setVerticalAlignment('middle');
sheet.getRange(2,13).setValue('ページ数');
sheet.getRange(3,13).setValue('シート枚数');
sheet.getRange(3,7).setValue('右綴じ');
sheet.getRange(3,14).setValue('=$N$2/4');
sheet.getRange(4,4).setValue('表');
sheet.getRange(4,5).setValue('面');
sheet.getRange(4,9).setValue('裏');
sheet.getRange(4,10).setValue('面');
sheet.getRange(5,1).setFormula('=IFERROR(SEQUENCE(N3),"")');
sheet.getRange(5,3).setFormula('=IFERROR(SEQUENCE($N$3,1,1,2),"")');

//★★下記5件は、「https://docs.google.com/spreadsheets/d/××××××××××/edit#gid=××××」の部分にシート1のURLを入力してください★★
copydRange.setFormula('=IFERROR(VLOOKUP(C5,IMPORTRANGE("https://docs.google.com/spreadsheets/d/××××××××××/edit#gid=××××","シート1!A:E"),5,false),"")');
copyeRange.setFormula('=IFERROR(VLOOKUP(F5,IMPORTRANGE("https://docs.google.com/spreadsheets/d/××××××××××/edit#gid=××××","シート1!A:E"),5,false),"")');
copyiRange.setFormula('=IFERROR(VLOOKUP(H5,IMPORTRANGE("https://docs.google.com/spreadsheets/d/××××××××××/edit#gid=××××","シート1!A:E"),5,false),"")');
copyjRange.setFormula('=IFERROR(VLOOKUP(K5,IMPORTRANGE("https://docs.google.com/spreadsheets/d/××××××××××/edit#gid=××××","シート1!A:E"),5,false),"")');
sheet.getRange(5,13).setValue('=VLOOKUP(C5,IMPORTRANGE("https://docs.google.com/spreadsheets/d/××××××××××/edit#gid=××××","シート1!A:E"),5,false),"")');

sheet.getRange(5,6).setFormula('=IFERROR(SEQUENCE($N$3,1,$N$2,-2),"")');
sheet.getRange(5,8).setFormula('=IFERROR(SEQUENCE($N$3,1,$N$2-1,-2),"")');
sheet.getRange(5,11).setFormula('=IFERROR(SEQUENCE($N$3,1,2,2),"")');
}

③画像を640pxで表示する（PDF出力用）

実際に製本作業をする際、100px表示で全体のレイアウトを確認後、640pxで表示してからPDFで書き出します。下記は、その際に使用するソースです。スクリプト名『ImageSize_640px』で登録します。

function ImageSize_640px() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[1];

 //セル幅・高さを640pxに設定
 sheet.setColumnWidth(4, 640);
 sheet.setColumnWidth(5, 640);
 sheet.setColumnWidth(9, 640); 
 sheet.setColumnWidth(10, 640); 
 sheet.setRowHeights(5, 100, 640);
}

①から③までを登録すると下図のようになります。

画像4

スクリプトを実際に使ってみよう！

では、早速この登録したスクリプトを使ってみます。まずは、「シート1」を開いている状態で、メニューから「画像リスト取得」を選択します。指定したGoogleフォルダ内の画像リストが表示されます。ここで下図のように表示されない場合には、スクリプト内に記述したURLやアップロードした画像ファイル名、スクリプトアクセス権限などに問題がある可能性が高いので、内容を確認して修正します。

画像5

次に「シート2」のタブに移動します。「ページ数」のセルに半角でページ数を入力したら、メニューの「左綴じ（100px）」または「右綴じ（100px）」のどちらかを選択。左綴じと右綴じの判断基準は、読書時の人の目線の動きから、文字が横書きの場合は左綴じ、縦書きなら右綴じにするのが一般的です。

これまでのスクリプトを登録すると、面付けシートは、下記のような画面になります。

画像15

「M5」セルに下記の関数を入力すると、上記画面のように「アクセス許可」が表示され、それをクリックすると、すべての画像が表示されるようになります。

=VLOOKUP(F5,IMPORTRANGE("https://docs.google.com/spreadsheets/d/★★シート1のURLを入力してください★★","シート1!A:E"),5,false)

画像6

100px表示で画像やページチェックが完了したら、「セルサイズ変更（640px）」を選択します。画像が大きくなるので、見づらい場合はブラウザ設定で縮小表示で調節します。

B4版に6枚の画像を印刷すると歌詞カードサイズのブックレットが作成できます。なので、ここでPDFに出力したい画像を「Shift」+「矢印キー」を押しながら6枚ずつ選択。

画像7

選択したらPDFドキュメントで出力します。Googleのサーバーが日中にかけて不安定なことが多いので、PDF出力するなら夜か朝に。

画像8

出力の詳細設定は下記参照。

画像9
画像10

PDF出力が正常に出来ない場合には、「ウェブページ（.html、zip形式）」でダウンロードし、それをエクセルで開き、下記のVBAをマクロに登録して画像をセルにフィットさせてPDFで出力することも可能です。

Public Sub セルに画像をフィット()
On Error GoTo NOT_SHAPE
Dim PicFit As Single
Dim CellFit As Single
With Selection
PicWtoHRatio = .Width / .Height
End With
With Selection.TopLeftCell
CellWtoHRatio = .Width / .RowHeight
End With
Select Case PicWtoHRatio / CellWtoHRatio
Case Is > 1
With Selection
.Width = .TopLeftCell.Width
.Height = .Width / PicWtoHRatio
End With
Case Else
With Selection
.Height = .TopLeftCell.RowHeight
.Width = .Height * PicWtoHRatio
End With
End Select
With Selection
.Top = .TopLeftCell.Top
.Left = .TopLeftCell.Left
End With
Exit Sub
NOT_SHAPE:
MsgBox "画像をフィットさせたいセルを選択してください"
End Sub

出力したPDFは、両面印刷して使うため「表面」「裏面」を一つのPDFファイルにする必要があります。PDFファイルの結合は、下記Adobeサイトで可能です。

PDFファイルをドラッグ＆ドロップして結合します。

画像11

PDFファイルが完成したら、いよいよ両面プリント。コンビニのマルチプリンターで印刷する場合、両面印刷に多少のズレが生じます。
気になる場合には、店員さんにトレーの用紙をぴしっとセットしてもらうように声をかけてお願いすると安心です。プリントアウトしたら、下記のようにシートごとにカッティングし、ページ順に重ねてタッカーで留れば完成です！

画像12
画像13
画像14



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