[document][GAS]業務マニュアルとアセスメントシートを作ろう-その6-

どうも。つじけ（tsujikenzo）です。このシリーズでは「業務マニュアルとアセスメントシートを（同時に）作ろう」についてお送りしています。全7回の予定で、今日は第6回目です。

前回のおさらい
セルの書式設定をする
1. 2次元配列によるセルの書式設定
関数を準備する
セルの書式設定のコードのパーツ化
1. コードの呼び出し
コードのパーツ化
まとめ
このシリーズの目次

前回のおさらい

前回は、「ドキュメントからスプレッドシートの新規作成」の続きで「フォルダ移動」「2次元配列の流し込み」「No列の更新」を行いました。

[document][GAS]業務マニュアルとアセスメントシートを作ろう-その5-

どうも。つじけ（tsujikenzo）です。このシリーズでは「業務マニュアルとアセスメントシートを（同時に）作ろう」についてお送りしています。全7回の予定で、今日は第5回目です。前回のおさらい前回はドキュメントから2次元配列用...

スプレッドシートはここまで自動で完成できるようになっています。

今回は「セルの書式設定」についてお話します。

セルの書式設定をする

今回のセルの書式設定は必要最低限、パッと見て区別がつくような背景色付けや太字程度にします。最近は紙のプリントアウトをしませんので、背景色をベタ塗りすることも多いです。

例として下記5点のセルの書式設定を行います。

見出し行を背景[紺]・文字色[白]・太字に設定する
スタイル[TITLE]レコードを太字にする。C列のみ背景[黒]・文字色[白]にする。
スタイル[HEADING2]レコードを文字色[灰色]。C列のみ背景[黒]・文字色[白]にする。
スタイル[HEADING4]レコードを文字色[薄い灰色]・C列のみ背景[黒]にする。
全て左揃えにする

2次元配列によるセルの書式設定

シートの書式設定はRangeクラスの.setFontSize(size)メソッドや.setBackgrounds(color)メソッドなど、Rangeオブジェクトに対して処理するメソッドが用意されています。そして、セル単体ではなく2次元配列で処理ができるのもスプレッドシートの面白いところです。

「2次元配列によるセルの書式設定処理」について詳細は別途記事を書きましたので、詳細はこちらを参考にしてください。

[スプレッドシート]セルの書式設定は2次元配列の版画で

どうも。つじけ（tsujikenzo）です。今日は「スプレッドシートのセルの書式設定は2次元配列の版画で」というお話をします。「スプレッドシート」や「セルの書式設定」というのはよく聞きますが、「版画」というのは初めてかもしれません。とは...

関数を準備する

引き続き、スタンドアロンスクリプトの最下部に追記していきます。関数名はsetBackgroundColor()にします。

“` function setBackgroundColor(ss) { //ssを使った処理 } “`

setBackgroundColor()を実行するのは最上部に書いている関数createAssessmentSheet()ですので、追加しておきましょう。また、引数には関数createSS_()の戻り値[ss]を指定します。

“` /** * ドキュメントから新規スプレッドシートを作成する * * @param {string} ドキュメントID * @param {string} スプレッドシートの保存先フォルダID

セルの背景色

セルの背景色については.setBackgrounds(color)メソッドで一度に処理ができます。

まず、シートのセル範囲全体を2次元配列に格納します。

“` function setBackgroundColor_(ss) { const sheet = ss.getActiveSheet(); const values = sheet.getDataRange().getValues(); console.log(values); }

“`

このような2次元配列[values]が取れると思いますが、

これを、色情報（HexCode）の2次元配列に変更します。

.map() メソッドをネストしながら、switch文で[Style列]を判定しました。

“` let copy = ”; const colors = values.map(record => record.map(element => { switch (record[0]) { case ‘Style’: copy = ‘#073763’; break; case ‘TITLE’: copy = ‘#000000’; break; case ‘HEADING2’: copy = ‘#000000’; break; case ‘HEADING3’: copy = ‘#ffffff’; break; case ‘HEADING4’: copy = ‘#f3f3f3’; break; } return copy; }) ); console.log(colors); } “`

2次元配列が生成されています。

この2次元配列[colors]をRangeクラスの.setBackgrounds(color)メソッドの引数に渡すと、シート全体の背景色を設定することができます。

“` sheet.getRange(1, 1, colors.length, colors[0].length).setBackgrounds(colors); “`

実行前

実行後

背景色が設定されましたね。後は応用です。

文字色

同様に文字色を設定します。.setFontColors(colors)メソッドで2次元配列でシート全体を設定します。引数に渡すfontWeightsには背景色と同様にHexCodeで色指定します。

“` const fontColors = values.map(record => record.map(element => { switch (record[0]) { case ‘Style’: copy = ‘#ffffff’; break; case ‘TITLE’: copy = ‘#ffffff’; break; case ‘HEADING2’: copy = ‘#ffffff’; break; case ‘HEADING3’: copy = ‘#000000’; break; case ‘HEADING4’: copy = ‘#000000’; break; } return copy; }) ); sheet.getRange(1, 1, fontColors.length, fontColors[0].length).setFontColors(fontColors); } “`

文字色が反映されました。

太字

同様に太字を設定します。.setFontWeights(fontWeights)メソッドの引数に、‘bold’、‘nomal’、nullのいずれかの要素を持つ2次元配列を渡します。

“` const fontWeights = values.map(record => record.map(element => { switch (record[0]) { case ‘Style’: copy = ‘bold’; break; case ‘TITLE’: copy = ‘normal’; break; case ‘HEADING2’: copy = ‘normal’; break; case ‘HEADING3’: copy = ‘normal’; break; case ‘HEADING4’: copy = ‘normal’; break; } return copy; }) ); sheet.getRange(1, 1, fontWeights .length, fontWeights[0].length).setFontWeights(fontWeights); } “`

太字が反映されました。

左揃え

同様に左揃えを設定します。.setHorizontalAlignments(alignments)メソッドの引数に、’left’, ‘right’, ‘center’のいずれかの要素を持つ2次元配列を渡します。

“` const alignments= values.map(record => record.map(element => { switch (record[0]) { case ‘Style’: copy = ‘left’; break; case ‘TITLE’: copy = ‘left’; break; case ‘HEADING2’: copy = ‘left’; break; case ‘HEADING3’: copy = ‘left’; break; case ‘HEADING4’: copy = ‘left’; break; } return copy; }) ); sheet.getRange(1, 1, alignments.length, alignments[0].length).setHorizontalAlignments(alignments); } “`

左揃えが反映されました。

セルの書式設定のコードのパーツ化

このコードは使いまわしがききそうですし、どこでも使えるようにパーツ化しましょう。関数名もsetBackgroundColor()からsetConditionalFormatRule_()に変更しました。

“` /** * セルの書式設定の要素を持つ2次元配列を返す * * @param {object} スプレッドシートオブジェクト * @param {string} Styleの書式設定 * @param {string} TITLEの書式設定 * @param {string} HEADING2の書式設定 * @param {string} HEADING3の書式設定 * @param {string} HEADING4の書式設定 * @return {array} 2次元配列 */ function setConditionalFormatRule_(ss, arg1, arg2, arg3,arg4, arg5){ const values = ss.getActiveSheet().getDataRange().getValues(); let copy = ”; const rules = values.map(record => record.map(element => { switch (record[0]) { case ‘Style’: copy = arg1; break; case ‘TITLE’: copy = arg2; break; case ‘HEADING2’: copy = arg3; break; case ‘HEADING3’: copy = arg4; break; case ‘HEADING4’: copy = arg5; break; } return copy; }) ); return rules; } “`

コードの呼び出し

関数setConditionalFormatRule_()は最上部の関数createAssessmentSheet()の中で呼び出します。

関数setConditionalFormatRule_()の戻り値を変数に格納して、スプレッドシートに貼り付けます。

“` function createAssessmentSheet(docId, folderId){ ～中略～ let [arg1, arg2, arg3, arg4, arg5] = [‘#073763′,’#000000′,’#000000′,’#ffffff’,’#f3f3f3′]; const backGroundColors = setConditionalFormatRule_(ss, arg1, arg2, arg3, arg4, arg5); ss.getActiveSheet().getRange(1, 1, backGroundColors .length, backGroundColors [0].length).setBackgrounds(backGroundColors ) } “`

4つの書式設定を加えるとこのようなコードになります。分割代入を活用して見た目をスッキリさせました。

“` let [arg1, arg2, arg3, arg4, arg5] = [‘#073763′,’#000000′,’#000000′,’#ffffff’,’#f3f3f3′]; const backGroundColors= setConditionalFormatRule_(ss, arg1, arg2, arg3, arg4, arg5); [arg1, arg2, arg3, arg4, arg5] = [‘#ffffff’,’#ffffff’,’#ffffff’,’#000000′,’#000000′]; const fontColors = setConditionalFormatRule_(ss, arg1, arg2, arg3, arg4, arg5); [arg1, arg2, arg3, arg4, arg5] = [‘bold’,’normal’,’normal’,’normal’,’normal’]; const fontWeights = setConditionalFormatRule_(ss, arg1, arg2, arg3, arg4, arg5); [arg1, arg2, arg3, arg4, arg5] = [‘left’,’left’,’left’,’left’,’left’]; const alignments = setConditionalFormatRule_(ss, arg1, arg2, arg3, arg4, arg5); ss.getActiveSheet().getRange(1, 1, backGroundColors.length, backGroundColors[0].length) .setBackgrounds(backGroundColors) .setFontColors(fontColors) .setFontWeights(fontWeights) .setHorizontalAlignments(alignments); “`

コードのパーツ化

完成したコード全体はこちらです。

“` /** * ドキュメントから新規スプレッドシートを作成する * * @param {string} ドキュメントID * @param {string} スプレッドシートの保存先フォルダID

*/ function createAssessmentSheet(docId, folderId){ //引数が無い時は下記2行をコメントインしてください //docId = ‘マニュアルのドキュメントID’; //folderId = ‘保存先のドライブID’; const array = documentConverter_(docId); cosnt ss = createSS_(array[0], array[1], folderId) let [arg1, arg2, arg3, arg4, arg5] = [‘#073763′,’#000000′,’#000000′,’#ffffff’,’#f3f3f3′]; const backGroundColors= setConditionalFormatRule_(ss, arg1, arg2, arg3, arg4, arg5); [arg1, arg2, arg3, arg4, arg5] = [‘#ffffff’,’#ffffff’,’#ffffff’,’#000000′,’#000000′]; const fontColors = setConditionalFormatRule_(ss, arg1, arg2, arg3, arg4, arg5); [arg1, arg2, arg3, arg4, arg5] = [‘bold’,’normal’,’normal’,’normal’,’normal’]; const fontWeights = setConditionalFormatRule_(ss, arg1, arg2, arg3, arg4, arg5); [arg1, arg2, arg3, arg4, arg5] = [‘left’,’left’,’left’,’left’,’left’]; const alignments = setConditionalFormatRule_(ss, arg1, arg2, arg3, arg4, arg5); ss.getActiveSheet().getRange(1, 1, backGroundColors.length, backGroundColors[0].length) .setBackgrounds(backGroundColors) .setFontColors(fontColors) .setFontWeights(fontWeights) .setHorizontalAlignments(alignments); return ss; } /** * ドキュメントIDを渡すと2次元配列を返す * * @param {string} ドキュメントトID * @return {array} [title,2次元配列, */ function documentConverter_(id){ const doc = DocumentApp.openById(id); const title = doc.getName(); const array = [[‘Style’, ‘No’, ‘Heading’],[‘TITLE’,”,title]]; const paragraphs = doc.getBody().getParagraphs(); for (const parag of paragraphs) { const style = parag.getHeading().toJSON(); switch (style) { case ‘HEADING2’: array.push([style, ”, parag.getText()]); break; case ‘HEADING3’: array.push([style, ”, parag.getText()]); break; case ‘HEADING4’: array.push([style, ”, parag.getText()]); break; default: } } return [title, array]; } /** * ドキュメントを変換した2次元配列から新規スプレッドシートを作成する * * @param {string} ドキュメントのタイトル * @param {array} ドキュメントの2次元配列 * @param {string} 保存先のフォルダID

* @return {object} 新規作成したスプレッドシートオブジェクト */ function createSS_(title, array, folderId){ const ss = SpreadsheetApp.create(title); const SSID = ss.getId(); const file = DriveApp.getFileById(SSID); const folder = DriveApp.getFolderById(folderId); file.moveTo(folder); ss.getActiveSheet().getRange(1,1,array.length, array[0].length).setValues(array); //[No]列を補完する array = setParagraphNumbers_(SSID); ss.getActiveSheet().getRange(1,1,array.length, array[0].length).setValues(array);

return ss; } /** * スプレッドシートオブジェクトを渡すとNoを補完した2次元配列を返す * * @param {object} スプレッドシート * @return {array} Noを補完した2次元配列 */ function setParagraphNumbers_(ss) { let titles = ss.getActiveSheet().getDataRange().getValues();

//empty array const addNums = []; //count var let countHeading2 = 0; let countHeading3 = 0; let countHeading4 = 0; //def flag let flag1 = false; let flag2 = false; let flag3 = false; let flag4 = false; for (const title of titles) { //set a flag on switch (title[0]) { case ‘TITLE’: flag1 = true; break; case ‘HEADING2’: if (flag2 === false && flag3 === false && flag4 === false) flag2 = true; if (flag2 && flag3 && flag4 === false) { countHeading3 = 0; flag3 = false; } if (flag2 && flag3 && flag4) { countHeading3 = 0; countHeading4 = 0; flag3 = false; flag4 = false; } break; case ‘HEADING3’: if (flag2 && flag3 && flag4) { countHeading4 = 0; flag4 = false; } flag3 = true; break; case ‘HEADING4’: flag4 = true; break; } if (flag1) addNums.push([‘1’]); flag1 = false; //append nums if (flag2 && flag3 === false && flag4 === false) { countHeading2++; addNums.push([`1.${countHeading2}`]); } if (flag2 && flag3 && flag4 === false) { countHeading3++; addNums.push([`1.${countHeading2}.${countHeading3}`]); } if (flag2 && flag3 && flag4 === true) { countHeading4++; addNums.push([`1.${countHeading2}.${countHeading3}.${countHeading4}`]); } } titles.shift(); titles = titles.map((element, index) => [element[0], addNums[index][0], element[2]]); titles.unshift([‘Style’, ‘No’, ‘Heading’]); return titles; } /** * セルの書式設定の要素を持つ2次元配列を返す * * @param {object} スプレッドシートオブジェクト * @param {string} Styleの書式設定 * @param {string} TITLEの書式設定 * @param {string} HEADING2の書式設定 * @param {string} HEADING3の書式設定 * @param {string} HEADING4の書式設定 * @return {array} 2次元配列 */ function setConditionalFormatRule_(ss, arg1, arg2, arg3,arg4, arg5){ const values = ss.getActiveSheet().getDataRange().getValues(); let copy = ”; const rules = values.map(record => record.map(element => { switch (record[0]) { case ‘Style’: copy = arg1; break; case ‘TITLE’: copy = arg2; break; case ‘HEADING2’: copy = arg3; break; case ‘HEADING3’: copy = arg4; break; case ‘HEADING4’: copy = arg5; break; } return copy; }) ); return rules; } “`

保存して関数createAssessmentSheet()を実行すると、セルの書式設定が反映されたスプレッドシートが新規作成されます。