IT Pocket 
スプレッドシートに蓄積したデータをもとに、週報や月報を作成する際に、各データが何年何月のデータか、何週目のデータかをまとめる必要が出てきます。
その際、手入力で情報をまとめると多くの時間を消費するので、ベストな解決策としては、日付データをもとに、下記の画像のように様々なデータを作成することです。
このようなデータのつくりをすれば、スプレッドシート上でデータを週単位、月単位でまとめることができるのはもちろんですが、Looker Studioでレポートを作成する際に役立ちます。
【初心者必見】LookerStudioとは、活用方法と事例をサンプル付きで紹介
今回、GASを使用して作成するデータは下記の項目です。
日付データをもとに、年月、年、その月の何週目か、ISO基準の週（年単位の週）、曜日
週報や月報の作成において、日付データを効率的に活用することが求められますが、手入力では時間がかかりすぎることが課題です。
この記事では、Google Apps Script (GAS) を使って日付データをもとに、スプレッドシート上で週報や月報のデータ集計を劇的に簡単に行う方法を解説します。
記事を読むことで、日付データを活用した週報・月報作成の効率化が実現でき、手間を大幅に削減できるようになります。
また、自動生成された年月、週、曜日などの情報を使って、データ分析やレポート作成の精度を高めることができるようになります。
この記事を読んで、スプレッドシートを使いこなし、業務の効率化に貢献しましょう。
今回はA列の”稼働日”というデータをもとに、黄色の項目を作成します。
完成イメージは下図の通り。
このようなデータが作れれば、週単位、月単位、年単位の集計が可能となります。
早速、作成したコードを確認しましょう。
作成したコード
function updateSpreadsheet() {
//ssUrlには対象のスプレッドシートのURLが入ります。''内をご自身のスプレッドシートURLに置き換えてください。
const ssUrl = 'https://docs.google.com/spreadsheets/d/1';
const sheetName = '月報週報';
const originColName = '稼働日';
const targetColNames = ['稼働年', '稼働年月', '稼働年_int', '稼働年月_int', '稼働週', '稼働週ISO', '稼働曜日'];
const ss = SpreadsheetApp.openByUrl(ssUrl);
const sheet = ss.getSheetByName(sheetName);
const lastRow = sheet.getLastRow();
const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
const originColIndex = headers.indexOf(originColName) + 1;
const originDates = sheet.getRange(2, originColIndex, lastRow - 1).getValues().flat();
let outputData = [];
for (const dateString of originDates) {
if (dateString) {
const date = new Date(dateString);
const year = date.getFullYear();
const month = date.getMonth() + 1;
const weekOfMonth = Math.ceil((date.getDate() - date.getDay()) / 7);
const weekIso = Utilities.formatDate(date, ss.getSpreadsheetTimeZone(), "w");
const dayOfWeek = ["日曜日", "月曜日", "火曜日", "水曜日", "木曜日", "金曜日", "土曜日"][date.getDay()];
const rowData = [
`${year}年`,
`${year}年${String(month).padStart(2, '0')}月`,
year,
month,
`${weekOfMonth}週目`,
`${weekIso}週目`,
dayOfWeek
];
outputData.push(rowData);
} else {
outputData.push(Array(targetColNames.length).fill(''));
}
}
const targetColIndexes = targetColNames.map(name => headers.indexOf(name) + 1);
for (let i = 0; i < targetColNames.length; i++) {
const colData = outputData.map(row => [row[i]]);
sheet.getRange(2, targetColIndexes[i], colData.length).setValues(colData);
}
Logger.log('スプレッドシートへのデータ更新が完了しました。');
}
こちらが作成したコードです。
必要箇所を変更していただければ、そのままご使用いただくことが可能です。
要件の概要は、「特定の日付データをもとに、指定したデータを作成する」という内容です。
そして、今回コード作成に当たってPointとなる点は以下の通りです。
- 値の出力は、処理速度向上のために、一つずつセルへ入力するのではなく、まとめて対象のセルへ入力するコードにしています。
- 列名は既にスプレッドシートの1行目に用意されているため、2行目以降に値を入力します。
- 対象の列名を列番号ではなく、列名で指定しています（targetColNames）。
- 元になるデータ（今回はA列）の最終行まで処理を繰り返す。
- 空のデータに対しては、何も書き込まずにスキップするようにしています。
上記がコードの簡単なポイントで、下記が各項目の内容です。
稼働年：年のみを入力（2023年）
稼働年月：年月を入力（2023年01月）
稼働年_int：年のみを数値で入力（2023）
稼働年月_int：月のみを数値で入力(01）
稼働週：当月の何週目かを入力（1週目）
稼働週ISO：当年の何週目かをISO基準で入力（12週目）
稼働曜日：何曜日かを入力（木曜日）
それでは、各項目の解説をしていきます。
function updateSpreadsheet() {
updateSpreadsheet という名前の関数を定義します。
この関数はスプレッドシートのデータを更新する役割を持っています。
const ssUrl = '...';
const sheetName = '月報週報';
const originColName = '稼働日';
const targetColNames = [...];
スプレッドシートのURL、シート名、元になる列名、対象の列名を定数として宣言します。
これらは処理の対象となるスプレッドシートや列に関する情報です。
const ss = SpreadsheetApp.openByUrl(ssUrl);
const sheet = ss.getSheetByName(sheetName);
SpreadsheetApp.openByUrl() 関数を使ってスプレッドシートを開き、getSheetByName() 関数で指定されたシート名のシートを取得します。
const lastRow = sheet.getLastRow();
const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
const originColIndex = headers.indexOf(originColName) + 1;
const originDates = sheet.getRange(2, originColIndex, lastRow - 1).getValues().flat();
シートから最終行数を取得し、1行目のヘッダー情報を取得します。
その後、ヘッダーから元になる列名のインデックスを検索し、元になる列のデータを取得します。
getLastRow()は下記リンクを確認.
let outputData = [];
for (const dateString of originDates) {
if (dateString) {
// ...
const rowData = [...];
outputData.push(rowData);
} else {
outputData.push(Array(targetColNames.length).fill(''));
}
}
outputData という空の配列を宣言し、元になる列のデータに対してループ処理を行います。
各行のデータが空でない場合は、必要な情報を計算して rowData 配列に格納し、outputData 配列に追加します。
データが空の場合は、空の値を持つ配列を outputData に追加します。
const targetColIndexes = targetColNames.map(name => headers.indexOf(name) + 1);
for (let i = 0; i < targetColNames.length; i++) {
const colData = outputData.map(row => [row[i]]);
sheet.getRange(2, targetColIndexes[i], colData.length).setValues(colData);
}
対象の列名のインデックスを計算し、対象の列にデータを書き込むためのループ処理を行います。
outputData 配列から各列のデータを取り出し、スプレッドシートにセットします。
sheet.getRange() 関数を使ってデータを書き込むべき範囲を指定し、setValues() 関数でデータを書き込みます。
sheet.getRange() は下記リンクを参照。
Logger.log('スプレッドシートへのデータ更新が完了しました。');
}
スプレッドシートへのデータ更新が完了したことを示すメッセージをログに出力します。
これで処理が完了し、関数の実行が終了します。
このコード全体では、与えられたスプレッドシートのURLとシート名を使って、元になる列名から対象の列名に必要なデータを計算して書き込む処理を行っています。
この記事では、Google Apps Script (GAS) を使用してスプレッドシート上の日付データをもとに、効率的な週報・月報作成方法を学びました。
記事の内容を活用することで、データ集計やレポート作成の手間を大幅に削減し、業務の効率化につなげることができます。
読者の皆さんは、この記事を参考に、以下のアクションを検討してみてください。
- 自身のスプレッドシートにコードを実装し、効率化を試みる。
- 他の業務で活用できるGASの応用方法を調査し、スキルを向上させる。
- 同僚や上司にこの効率化方法を共有し、チーム全体の生産性向上に貢献する。
記事で学んだことをを活用して、スプレッドシートを使いこなし、業務効率化に繋げましょう。
