Google Apps Scriptはスプレッドシートだけでなく、ドライブ、ドキュメントなどほかのG Suiteでも使える。スプレッドシートにまとめた情報をもとに、ドキュメントで報告書を自動生成することも可能だ。
これまで手作業で作成していた報告書をGASで作成するにあたって、2つ手の込んだことを求められた。ひとつには、セルを結合して少々複雑な形をした表をつくることだった。ふたつには、単なるテキストではなく、上付きや下付き、斜体といった書式設定を伴う形で表に文書を書き込むことだった。
ひとつ目の「セルを結合した表」は少々厄介であった。GASのリファレンスを読んでみても、セルの結合に相当するMethodがないのだ。試しにドキュメントでセルの結合をした表を作ってGASで読み込んでみると、セルを結合してできたセルは、TableCell.getColSpan()の値が1より大きい値になっていた。しかし、setColSpan()というMethodは存在しない。merge()も期待したような動作をしない。ドキュメントではなくHTMLファイルとして出力することはできそうだが、いまはドキュメントを書き換えたいので不適当だ。
結局のところ、あるドキュメントファイルにあらかじめセルを結合した表を用意しておいて、新しいドキュメントにコピーするという手法をとることにした。TableRowのレベルで挿入していけば、下の図のような2行を単位とした繰り返し構造の表がつくれる。
ふたつ目の書式を維持した文書を挿入するのは簡単である。上で挿入した表のセルに対して、転写したい文書のParagraphをappendしてやればよい。
次に示す例では、元になるドキュメントファイルの1行目 (getCHild(0) またはpp[0] )から5行目(getCHild(4)またはpp[4])に書式をいじった文書が入っている。これを段落ごとに読みだしている。また、7行目( getCHild(6))にはテンプレートにしたい表がある。
appendParagraphする文書をどこのドキュメントファイルに置いておくか、スプレッドシートにでもまとめておけば、表の繰り返し単位ごとに適切なドキュメントファイルを読みだして、表の各セルを埋めることができる。
appedTableやappendParagraphをする際には、copy()を付けてやる必要がある。また、 appendParagraphをする際には、 .merge()をつけないと、余分な改行がセル内に残る。
function tryTable(){
var doc_from = DocumentApp.openById('元になるドキュメントファイルのID'),
doc_to = DocumentApp.openById('書き込み先になるドキュメントファイルのID'),
pp = doc_from.getBody().getParagraphs();
var body = doc_to.getBody();
//表をつくる
var table0 = body.appendTable(doc_from.getBody().getChild(6).copy());
table0.getRow(0).getCell(0).clear().appendParagraph(pp[0].copy()).merge();
table0.getRow(0).getCell(1).clear().appendParagraph(pp[1].copy()).merge();
table0.getRow(0).getCell(2).clear().appendParagraph(pp[2].copy()).merge();
table0.getRow(0).getCell(3).clear().appendParagraph(pp[3].copy()).merge();
table0.getRow(1).getCell(0).clear().appendParagraph(pp[4].copy()).merge();
//表のテンプレートを下に伸ばす。例として10回繰り返し。
for (var i = 0; i < 10; i++) {
table0.appendTableRow(table0.getRow(0).copy());
table0.appendTableRow(table0.getRow(1).copy());
}
}
参考
【Microsoft Office 英語】日本語/英語の対照表(Excel, Word)
さらに複雑な表を扱う方法を考える。異なるRowのセルを結合したセルではどのようになるだろうか?次の図に2行5列の表のセルを結合した表と、各セルにアクセスする際に使うRowやCellの番地を示した。複雑な形状に結合した表であっても、結合したセルの左上に位置するセルの値が表示されると見なせるようだ。
自分で作成した表の各セルにアクセスする番地を確認したければ、getChild(index)で各セルの値を読み出すのが簡単だろう。BODY>TABLE>TABLE ROW>TABLE CELL>PARAGRAPH>TEXTという順序で各オブジェクトがぶら下がっている。TABLE CELLまで行けばgetText()でセルの中のテキストを表示できる。また、各要素にぶら下がっている要素の数はgetNumChildren()で取得できるので、あとはforループで読みだせばよい。
ところで、セルを結合すると表示されないセルが出てくるが、ファイルから消えているわけではないようだ。BODYに対してgetParagraphs() をすると、上の図で表示されていないTABLE CELL (R1C0やR1C2~4)に対応するはずのParagraphも取得される。
BODYに直接ぶら下がっている要素だけを取り出したいときは、BODYに対して getChild() とgetNumChildren()を使うのがよさそうだ。
はじめまして。GAS勉強中の初学者です。
こちらのコードを参考に作成しているプログラムがあるのですが、うまくいかないところがあり、ご教示いただきたくご連絡しました。
ご質問を受けて下されば幸いでございます。
どうぞよろしくお願いいたします。
【やりたいこと】
スプレッドシートに入っている各行の値をドキュメント内のテーブルに挿入したい。
【試したこと】
こちらのコードを参考にテーブルをドキュメントで作成して読み込み、そこにスプレッドシートの値をfor文で挿入しようと試みておりますが、スプレッドシートの1行分の値しか読み込まず、同じものが11行できてしまいます。
for文がうまくいってないと思うのですが、どこをどう修正していいか分からずじまいです。
読み込んだテーブルの型:2行4列(結合セルあり)を1セット
スプレッドシート:行を[i]とし、列は[0]~[14]
↓こんな感じで作っています。
function tryTable(){
var doc_from = DocumentApp.openById(‘A ID’),//テーブルの型が保存されているdocファイルのID
doc_to = DocumentApp.openById(‘B ID’),//テーブルの型を読み込むdocファイルID
pp = doc_from.getBody().getParagraphs();
var sheet = SpreadsheetApp.openById(“C ID'”).getSheetByName(“シート”);//スプレッドシート、シート名を読み込む
var rows = sheet.getLastRow()-1;
var data = sheet.getRange(2,1,rows,14).getValues();
Logger.log(data);
var body = doc_to.getBody();
//表をつくる
var table0 = body.appendTable(doc_from.getBody().getChild(1).copy());//doc_fromからテーブルの型をコピーする
table0.setFontSize(14).setFontFamily( “MS PMincho” );
for(var i=1; i< data.length; i++){
var status = data[i][0];//A列の項目をキーに、行データを抽出する
if(status == false){
break;
}
if(status =='A列1' || status=='A列2' || status=='A列3'){
table0.getRow(0).getCell(0).clear().appendParagraph(data[i][13].copy()).merge();
table0.getRow(0).getCell(1).clear().appendParagraph(data[i][7].copy()).merge();
table0.getRow(1).getCell(0).clear().appendParagraph('【'+data[i][9].copy()+' '+data[i][10].copy()+' '+'資料:'+data[i][12].copy()+'】').merge().setAlignment(DocumentApp.HorizontalAlignment.RIGHT);
}
//表のテンプレートを下に伸ばす。A列1,2,3のデータの分だけ伸ばす。
for (var i = 0; i < data.length; i++) {
table0.appendTableRow(table0.getRow(0).copy());
table0.appendTableRow(table0.getRow(1).copy());
}
}
}
【不明点】
ここのコードで、table0.getRow(0).getCell(0).clear().appendParagraph(data[i][13].copy()).merge();
TypeError: data[i][13].copy is not a functionが出てしまいます。
.copyを外すとエラーは出ないのですが、実行結果は同じ行の値が11行できてしまいます。
.copyの意味は、スプレッドシートの各行のdata[i][13]をコピーする、ということではないのでしょうか。
このfor文はindex.htmlで作成した時にはちゃんとループしていたので、それを利用しています。
説明が分かりにくく、質問が不鮮明で申し訳ありません。
ヒントやアドバイスをいただければ幸いでございます。
何卒宜しくお願い致します。
ご質問を拝見して、自分がかつて悪戦苦闘した日々を思い出します。
初学者と書かれていますが、おそらくデータの型を意識したり、リファレンスを読むということはあまりされていませんよね。そういう想定で以下回答を書きます。
【不明点】に書かれたところから順を追ってみていきます。
data[i][13]の中身はスプレッドシートから読み取った値(数値またはテキスト)です。これを挿入したいだけであれば、appendParagraph(text) を使えばいいのでappendParagraph(data[i][13])が正しいです。これはappendParagraph(text)の(text)部分にdata[i][13]の値を渡すという意味です。
この記事で.copy()を使っている理由は、読み込む元がスプレッドシートの表ではなくドキュメントの表(の1セルにぶらさがっているParagraphオブジェクト)だからです。配列に入った値data[][]を読み出すのにcopy()は不要です。
二重のfor文で、カウンタを両方とも同じ文字iにしていることが原因でしょう。いまのコードだと、外側のループ1回目で内側のループによりiが大きな値になって外側のループも終了します。
for文どちらかを別の文字にする必要があります。よく使うのはi,j,kあたりですが、1文字である必要はないのでtableRowだとかindexだとか意味を取りやすいフレーズを使っても大丈夫です。
もう一つ変える必要がある点があります。
現在のコードではappendParagraph()する先はtable0.getRow(0).getCell(0), table0.getRow(0).getCell(1), table0.getRow(1).getCell(0)と固定になっています。これでは、ドキュメントの表table0をいくら下に伸ばしても、スプレッドシートから読み取った値data[][]が書き込まれるのは1行1列(0,0)と1行2列(0,2)と2行1列(1,0)のセルだけです。
表を伸ばすのに合わせて、書き込み先も1,2行目から3,4行目、5,6行目へと変えていく必要があります。
たとえば、for文の前で何個目のデータを書き込むかを示す index=0 と初期化しておいて
appendParagraph()する先はtable0.getRow(index).getCell(0), table0.getRow(index).getCell(1), table0.getRow(index+1).getCell(0)のような表記にすることと、表を伸ばすtable0.appendTableRow(table0.getRow(1).copy());の後にindex = index + 1とすれば、書き込み先の番地が変わっていきます。
こちらで動作確認などしていませんが、コードを見てぱっと思いついたことをお送りします。
こんばんは。
詳細なご説明をありがとうございます。
仰る通り、型もよく分かっておらず、リファレンスを読んではいますが理解できず、基礎がないまま
今月中にこれを仕上げなくてはならないという状況です。
不勉強なまま質問してしまい、申し訳ございません。
いただいたご指摘をもとに修正をしました。
index = 0;//スプレッドシートの何行目のデータを書き込むテーブルセルの位置(getRow)かを示す変数indexを初期化する
for(var i=1; i< data.length; i++){
// Logger.log(i)
var status = data[i][0];
if(status == false){//データがある最終行までいったらループを抜ける
break;
}
if(status == 'A' || status=='B' || status=='C'){
// Logger.log(status)
table0.getRow(index).getCell(0).clear().appendParagraph(data[i][13]).merge();
table0.getRow(index).getCell(1).clear().appendParagraph(data[i][7]).merge();
table0.getRow(index+1).getCell(0).clear().appendParagraph('【'+data[i][9]+' '+data[i][10]+' '+'資料:'+data[i][12]+'】').merge().setAlignment(DocumentApp.HorizontalAlignment.RIGHT);
// Logger.log(data[i][7])
}
}
//テーブル(doc_from)の型を下に追加する(案件分の行を足していく)
for (var index = 0; index < data.length; index++) {
table0.appendTableRow(table0.getRow(0).copy());
table0.appendTableRow(table0.getRow(1).copy());
index = index + 1;
}
こう理解したのですが合っていますでしょうか。
これを実行したところ、同じ行のデータが6セット表示されました。
(スプレッドシートの5行目のデータ)
【修正を試みた結果】
index = index + 1;を、} の下に移すと46セット表示されたり、
for文の括り位置を変更すると8ページにわたって表示されてしまい、
行数は増えてもデータは同じ行ばかり表示されてしまいます。
for (var index = 0; index < data.length; index++) {
table0.appendTableRow(table0.getRow(0).copy());
table0.appendTableRow(table0.getRow(1).copy());
index = index + 1;
}
このコードをコメントアウトすると、当然1セットしか表示されません。
求めている表示は、スプレッドシートでいうB2~N5に入っている4行分の値を4セット(1セット=1行のデータを2段に分けて表示)表示することです。
自分なりに調べてはいますが、答えをみつけることができませんでした。
コメントいただければ幸いです。
どうぞよろしくお願いいたします。
先の返信でよく考えずに書いてしまいました。
内側のfor文は要らないのと、中身を別の場所に移すべきです。
やりたいことの流れを整理しますと
スプレッドシートから読んだ値が書き込むべきstatusなら、その行のデータをドキュメントで2段の表で書き込む。
という条件分岐があります。
また、テンプレートとなる表をコピーして貼り付けたあとに、その行の中に値を書き込むとなると、最後の値を書き込んだあとに追加で空白の行を書き込んでしまいます。これに例外処理(末端ではテンプレをコピーしないなど)が必要となります。
そこで、if文の条件に合うときだけ行を足して、直前に足した行に値を書き込むという流れにしてみましょう。
「//表をつくる」から下の部分を以下のように書き換えてみてください。動作しなかったらまた教えてくd債。
//表をつくる
var table0 = doc_from.getBody().getChild(1).copy();//doc_fromからテーブルの型をコピーするが、貼付けはしない。
var table1 = body.appendTable();//コピー先に空のテーブルを貼り付ける。ここにデータを書き込んでいく。
for(var i=1; i< data.length; i++){ var status = data[i][0];//A列の項目をキーに、行データを抽出する //if(status == false){}は要らない if(status =='A列1' || status=='A列2' || status=='A列3'){ //ここでtable1に行を追加する //追加した行(appendTableRowの返り値)を変数で受ける var oddrow = table1.appendTableRow(table0.getRow(0).copy()); var evenrow = table1.appendTableRow(table0.getRow(1).copy()); //追加した行に値を書き込む oddrow.getCell(0).clear().appendParagraph(data[i][13].copy()).merge(); oddrow.getCell(1).clear().appendParagraph(data[i][7].copy()).merge(); evenrow.getCell(0).clear().appendParagraph('【'+data[i][9].copy()+' '+data[i][10].copy()+' '+'資料:'+data[i][12].copy()+'】').merge().setAlignment(DocumentApp.HorizontalAlignment.RIGHT); } } //最後にフォントや見た目の設定をする。 table1.setFontSize(14).setFontFamily( “MS PMincho” );
こんにちは。
お世話になっております。
ご対応いただきありがとうございます。
感謝しかありません!!
//表をつくる以下を変更した結果、
Service Documents failed while accessing document with id
というエラーが出てしまいました。
このエラーは昨日も出たのですが、その時は複数作っていたgsファイルをコメントアウトしたら解消されました。
今日はそれでも解消されなかったので、ドライブを再起動したり、新たにドキュメントファイルを作成して、そこにコードをコピーしてみたり、
doc_to = DocumentApp.openById();のところを、DocumentApp.create(‘Document Name’)やDocumentApp.openByUrl() やgetActiveDocument()に変えてみたりしましたが、解消しませんでした。
ネットで同様の事象が起きてるか調べると、googleのバグではないかというコメントがありました。
https://qiita.com/nerikesi/questions/8412bdbd0eeb8cf7610a
あと、空のテーブルを作る、
var table1 = body.appendTable();
ですが、テーブルのセル幅はドキュメントファイルに表示された時点で修正する
という理解でよろしいでしょうか。
doc_fromで指定したテーブルは、セル幅の調整を行い、保存しています。
以上です。
どうぞよろしくお願いいたします。
こんばんは。お世話になっております。
連投失礼します。
分からないなりにいろいろ試してみました。
var evenrow = table1.appendTableRow(table0.getRow(1).copy());
evenrow.getCell(0).clear().appendParagraph(‘【’+data[i][9]+’ ’+data[i][10]+’ ’+’資料:’+data[i][12]+’】’).merge().setAlignment(DocumentApp.HorizontalAlignment.RIGHT);
こちらをコメントアウトしますと、Service Documents failed while accessing document with id doc_to
のエラーが出ます。
var oddrow = table1.appendTableRow(table0.getRow(0).copy());
oddrow.getCell(0).clear().appendParagraph(data[i][13]).merge();
oddrow.getCell(1).clear().appendParagraph(data[i][7]).merge();
こちらをコメントアウトしますと実行完了にはなりますが、実行結果を見ると、
statusで指定したA列1~3の条件は無視され、全データが表示されます。
そして最終行に、statusで指定したA列1~3の条件のdata[i][13])data[i][7]が表示されていました。
oddrow.getCell(1).clear().appendParagraph(data[i][7]).merge();
こちらをコメントアウトしますと,statusで指定したA列1~3の条件は無視され、全データが表示され、
その最終行に、statusで指定したA列1~3の条件のdata[i][13])(‘【’+data[i][9]+’ ’+data[i][10]+’ ’+’資料:’+data[i][12]+’】’)が表示されていました。
oddrow.getCell(0).clear().appendParagraph(data[i][13]).merge();
こちらをコメントアウトしますと、Service Documents failed while accessing document with id doc_to
のエラーが出ます。
この結果からどうすれば良いのか分からなくて立ち止まっています。
図々しいですが、またコメントいただければ幸いです。
どうぞよろしくお願いいたします。
お世話になっております。
先にお伝えしましたエラー等は解決し、形を変えたテーブルも追加で表示させることができました。
この度は助けていただき、本当にありがとうございました。
GAS+スプレッドシートについては情報が多くありますが、ドキュメントは少ないので、とても貴重な記事でした。
親切にご教示いただき、感謝しております。
勉強して、きちんと理解を深めていきますが、躓いたときにまたお世話になるかもしれません。
その際はどうぞよろしくお願いいたします。
ありがとうございました‼
やりたいことができたようですね。よかったです!