[GoogleAppsScript] 日付と時間を縦に1時間ごとに並べる

問題

時間割とかタイムログ的なものを書きたいので、

スプレッドシートに日付と時間を1時間ごとに、A列、B列に書き込みたいです。

答え

現状のスプレッドシートの値を見て、続きを書いていく形にする場合。

つまり、下図のようになっているときに、その続きから追記していく場合。

// 続きを入れる（A列の最終行を探し、日付を取得、次の行から日付と時間を入れていく）
function setAB() {
  // A列の末尾の値を取得
  const sheet=SpreadsheetApp.getActiveSheet()
  const lastRow = sheet.getDataRange().getLastRow()
  const d = sheet.getRange(lastRow,1).getValue()
  // 例えば100日分のループ
  for(let i = 0; i < 100; ++i){
    // A列の末尾の翌日から開始
    d.setDate(d.getDate()+1)
    let r = lastRow + 1 + i * 24
    // 24時間分の24行を出力
    for(let h = 0; h < 24; ++h){
      sheet.getRange(r+h, 1).setValue(d)
      sheet.getRange(r+h, 2).setValue(h+':00')
      // 土日の文字色を変える
      if (d.getDay() == 6) {
        sheet.getRange(r+h, 1).setFontColor('blue')
        sheet.getRange(r+h, 2).setFontColor('blue')
      } else if (d.getDay() == 0) {
        sheet.getRange(r+h, 1).setFontColor('red')
        sheet.getRange(r+h, 2).setFontColor('red')
      } else {
        sheet.getRange(r+h, 1).setFontColor('black')
        sheet.getRange(r+h, 2).setFontColor('black')
      }
    }
  }
}

改善の余地メモ

setValueを繰り返しているので遅い。

書き込みたい内容を配列に入れて、setValues、setFontColors でまとめて書き込むと早くなる。

たまにしか実行しない想定で、わかりやすさ優先でそのままにしている。頻繁に実行する場合は修正するとよい。

最後にまとめて書き込むバージョン

function setAB2() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const lastRow = sheet.getDataRange().getLastRow();
  const lastDateValue = sheet.getRange(lastRow, 1).getValue();
  let currentDate = new Date(lastDateValue);
  console.log(sheet.getSheetName(), lastRow, lastDateValue)
  console.log(currentDate)

  // 書き込むデータと色の情報を保持する配列
  const dataToWrite = [];
  const colorsToSet = [];

  // 例えば100日分のループ
  for (let i = 0; i < 10; ++i) {
    // 翌日に日付を進める
    currentDate.setDate(currentDate.getDate() + 1);
    const dayOfWeek = currentDate.getDay(); // 0:日曜日, 1:月曜日, ..., 6:土曜日

    let fontColor = 'black';
    if (dayOfWeek === 6) { // 土曜日
      fontColor = 'blue';
    } else if (dayOfWeek === 0) { // 日曜日
      fontColor = 'red';
    }

    // 24時間分の24行のデータを追加
    for (let h = 0; h < 24; ++h) {
      dataToWrite.push([new Date(currentDate), `${h}:00`]);
      colorsToSet.push([fontColor, fontColor]); // A列とB列両方に同じ色を設定
    }
  }

  // データの一括書き込み、色の一括設定
  const numCols = 2; // A列とB列で2列
  sheet.getRange(lastRow + 1, 1, dataToWrite.length, numCols).setValues(dataToWrite);
  sheet.getRange(lastRow + 1, 1, colorsToSet.length, numCols).setFontColors(colorsToSet);
}