どうも。つじけ（tsujikenzo）です。このシリーズでは「業務マニュアルとアセスメントシートを（同時に）作ろう」についてお送りしています。全7回の予定で、今日は第6回目です。
前回のおさらい
前回は、「ドキュメントからスプレッドシートの新規作成」の続きで「フォルダ移動」「2次元配列の流し込み」「No列の更新」を行いました。
[document][GAS]業務マニュアルとアセスメントシートを作ろう-その5-
どうも。つじけ（tsujikenzo）です。このシリーズでは「業務マニュアルとアセスメントシートを（同時に）作ろう」についてお送りしています。全7回の予定で、今日は第5回目です。前回のおさらい前回はドキュメントから2次元配列用の配列を作成し
tgg.jugani-japan.com
スプレッドシートはここまで自動で完成できるようになっています。
今回は「セルの書式設定」についてお話します。
セルの書式設定をする
今回のセルの書式設定は必要最低限、パッと見て区別がつくような背景色付けや太字程度にします。最近は紙のプリントアウトをしませんので、背景色をベタ塗りすることも多いです。
例として下記5点のセルの書式設定を行います。
- 見出し行を背景[紺]・文字色[白]・太字に設定する
- スタイル[TITLE]レコードを太字にする。C列のみ背景[黒]・文字色[白]にする。
- スタイル[HEADING2]レコードを文字色[灰色]。C列のみ背景[黒]・文字色[白]にする。
- スタイル[HEADING4]レコードを文字色[薄い灰色]・C列のみ背景[黒]にする。
- 全て左揃えにする
2次元配列によるセルの書式設定
シートの書式設定はRangeクラスの.setFontSize(size)メソッドや.setBackgrounds(color)メソッドなど、Rangeオブジェクトに対して処理するメソッドが用意されています。そして、セル単体ではなく2次元配列で処理ができるのもスプレッドシートの面白いところです。
「2次元配列によるセルの書式設定処理」について詳細は別途記事を書きましたので、詳細はこちらを参考にしてください。
[スプレッドシート]セルの書式設定は2次元配列の版画で
どうも。つじけ（tsujikenzo）です。今日は「スプレッドシートのセルの書式設定は2次元配列の版画で」というお話をします。「スプレッドシート」や「セルの書式設定」というのはよく聞きますが、「版画」というのは初めてかもしれません。とは言っ
tgg.jugani-japan.com
関数を準備する
引き続き、スタンドアロンスクリプトの最下部に追記していきます。関数名はsetBackgroundColor()にします。
function setBackgroundColor(ss) {
//ssを使った処理
}
setBackgroundColor()を実行するのは最上部に書いている関数createAssesmentSheet()ですので、追加しておきましょう。また、引数には関数createSS_()の戻り値[ss]を指定します。
/**
* ドキュメントから新規スプレッドシートを作成する
*
* @param {string} ドキュメントID
* @param {string} スプレッドシートの保存先フォルダID
*/
function createAssesmentSheet(docId, folderId){
//引数が無い時は下記2行をコメントインしてください
//docId = 'マニュアルのドキュメントID';
//folderId = '保存先のドライブID';
const array = documentConverter_(docId);
const ss = createSS_(array[0], array[1], folderId)
setBackgroundColor_(ss);
}
セルの背景色
セルの背景色については.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<b> </b>= 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<b> </b>[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_()は最上部の関数createAssesmentSheet()の中で呼び出します。
関数setConditionalFormatRule_()の戻り値を変数に格納して、スプレッドシートに貼り付けます。
function createAssesmentSheet(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 createAssesmentSheet(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);
}
/**
* ドキュメント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) {
countHeading4 = 0;
flag4 = false;
}
break;
case 'HEADING3':
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;
}
保存して関数createAssesmentSheet()を実行すると、セルの書式設定が反映されたスプレッドシートが新規作成されます。
コーディング作業は以上です。お疲れ様でした。でもトータル230行だから対したこと無かったですね💦
まとめ
さて、今回は「セルの書式設定」についてまとめました。別記事でもご紹介しましたが、2次元配列でセルの書式設定を行うのは印刷技術の「版と刷」のような関係のようで、非常に興味深い内容でした。
次回は最終回として恒例の「ライブラリマニュアル」をお届けします。
ライブラリとして公開する方法はこちらの記事を参考にしてください（新IDE対応）。
[GAS]2次元配列をオブジェクト化ライブラリ -Day2‐
2021年12月に、この記事（2次元配列を連想配列にするテクニック）に関する最新情報をブログにしました。どうも。つじけ（tsujikenzo）です。このシリーズでは「便利な関数を使いまわす為にライブラリを活用しよう」についてお送りしておりま
tgg.jugani-japan.com
ライブラリの名前は「DocumentParagraphConverter」としました。
Comments