GAS スプレッドシートの文字列を時間として取得してカレンダーにエクスポートしたい

Question

前提・実現したいこと

GASでスプレッドシートの文字列を時間として取得してカレンダーにエクスポートしたいと思っています。

スプシのデータのフォーマットが時間ではないのは、データの入力規則を利用してプルダウンを作っており、そこで使うデータをQueryする際に「書式なしテキスト」に設定しているからです。

カレンダーにエクスポートするためにはgetHoursを使って時間を取得したいのですが、値の種類が違うからか、実行できません。
GASでスプシの値を取得するときにどのようにして時間に変換すれば良いか、お教えいただきたいです。

使用しているスプレッドシート

該当のソースコード

function onOpen() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
 
  //スプレッドシートのメニューにカスタムメニュー「カレンダー連携 > 実行」を作成
  var subMenus = [];
  subMenus.push({
    name: "実行",
    functionName: "createSchedule"  //実行で呼び出す関数を指定
  });
  ss.addMenu("カレンダー連携", subMenus);
}
 
/**
 * 予定を作成する
 */
function createSchedule() {
 
  // 連携するアカウント
  const gAccount = "〇〇";  // 連携するカレンダーのアドレスを入れる
  
  // 読み取り範囲（表の始まり行と終わり列）
  const topRow = 2;
  const lastCol = 6;
 
  // 0始まりで列を指定しておく
  const statusCellNum = 0;
  const dayCellNum = 1;
  const startCellNum = 3;
  const endCellNum = 4;
  const titleCellNum = 5;
  
  // シートを取得
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 
  // 予定の最終行を取得
  var lastRow = sheet.getLastRow();
  
  //予定の一覧を取得
  var contents = sheet.getRange(topRow, 1, sheet.getLastRow(), lastCol).getValues();
 
  // googleカレンダーの取得
  var calender = CalendarApp.getCalendarById(gAccount);
 
  //順に予定を作成（今回は正しい値が来ることを想定）
  for (i = 0; i <= lastRow - topRow; i++) {
 
    //「済」っぽいのか、空の場合は飛ばす
    var status = contents[i][statusCellNum];
    if (
      status == "済" ||
      status == "済み" ||
      status == "OK" ||
      contents[i][dayCellNum] == ""
    ) {
      continue;
    }
 
    // 値をセット 日時はフォーマットして保持
    var day = new Date(contents[i][dayCellNum]);
    var startTime = contents[i][startCellNum];
    var endTime = contents[i][endCellNum];
    var title = contents[i][titleCellNum];
    
    try {
      // 開始終了が無ければ終日で設定
      if (startTime == '' || endTime == '') {
        //予定を作成
        calender.createAllDayEvent(
          title,
          new Date(day),
        );
        
      // 開始終了時間があれば範囲で設定
      } else {
        // 開始日時をフォーマット
        var startDate = new Date(day);
        startDate.setHours(startTime).getHours()
        startDate.setMinutes(startTime).getMinutes();
        // 終了日時をフォーマット
        var endDate = new Date(day);
        endDate.setHours(endTime).getHours()
        endDate.setMinutes(endTime).getMinutes();
        // 予定を作成
        calender.createEvent(
          title,
          startDate,
          endDate,
        );
      }
 
      //無事に予定が作成されたら「済」にする
      sheet.getRange(topRow + i, 2).setValue("済");
 
    // エラーの場合（今回はログ出力のみ）
    } catch(e) {
      Logger.log(e);
    }
    
  }
  // ブラウザへ完了通知
  Browser.msgBox("完了");
}

試したこと

setNumberFormatで変換

Answer 1

const onOpen = ()=> {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    //スプレッドシートのメニューにカスタムメニュー「カレンダー連携 > 実行」を作成
    const subMenus = [{
        name: "実行",
        functionName: "createSchedule"  //実行で呼び出す関数を指定
    }];
    ss.addMenu("カレンダー連携", subMenus);
}
/**
 * 予定を作成する
 */
const createSchedule = () => {
    // 連携するアカウント
    const gAccount = "〇〇";  // 連携するカレンダーのアドレスを入れる

    // 1 行目は見出し行 "連携	日付	曜日	開始	終了	名前"

    // 0始まりで列を指定しておく
    const statusCellNum = 0;
    const dayCellNum = 1;
    const startCellNum = 3;
    const endCellNum = 4;
    const titleCellNum = 5;

    const dropDone = generateFilter(statusCellNum,["済","済み","OK"]);
    const dropEmptyDate = generateFilter(dayCellNum,[""]);
    // googleカレンダーの取得
    const calender = CalendarApp.getCalendarById(gAccount);
    // シートを取得
//    const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    const sheet = SpreadsheetApp.getActive().getSheetByName("マスター");
    const data = sheet.getDataRange().getValues();
    data.forEach((r,i)=> {
        if(i===0) return;
        if(dropDone(r)) return;
        if(dropEmptyDate(r)) return;
        const eventDay = r[dayCellNum];
        const eventStart = r[startCellNum];
        const eventEnd = r[endCellNum];
        const eventTitle = r[titleCellNum];
        if(eventStart === "" || eventEnd === "") {
            try {
                calender.createAllDayEvent(eventTitle, eventDay);
                sheet.getRange(i + 1, 1).setValue("済");
                return;
            } catch(e) {
                console.log(e);
            }
        }
        const startTime = eventDay.setHours(eventStart.split(":")[0],eventStart.split(":")[1]);
        const endTime = eventDay.setHours(eventEnd.split(":")[0],eventEnd.split(":")[1]);
        try {
            calender.createEvent(eventTitle, startTime, endTime);
            sheet.getRange(i + 1, 1).setValue("済");
        } catch(e) {
            console.log(e);
        }
    });
    // ブラウザへ完了通知
    Browser.msgBox("完了");
}
const generateFilter = (idx, values) => (e) => e[idx] === undefined || values.includes(e[idx]);

「済」を書き戻す都合上、この体裁の表でしか動かないので、2 行目からはじまるとか、6 列あるとかっていうのは定義しなくていいのではないかと思って削除しました。
時間のパースあたりは、 fj68 様の回答もご参考に。

Answer 2

コード内の// 開始終了時間があれば範囲で設定のブロックの話だとしたら、日時の文字列をあらかじめ作ったうえでDate型にしたほうが早いと思います。

    // 開始日時をフォーマット
    var startDate = new Date(`${day.toDateString()} ${startTime}`);
    var endDate = new Date(`${day.toDateString()} ${endTime}`);

※動作確認時のログ

  var day = new Date("2021/1/1");
  var startTime = "11:00";
  console.log(day);
  // Fri Jan 01 2021 00:00:00 GMT+0900 (Japan Standard Time)
  console.log(new Date(`${day.toDateString()} ${startTime}`));
  // Fri Jan 01 2021 11:00:00 GMT+0900 (Japan Standard Time)

Answer 3

Date.prototype.setHours()やDate.prototype.setMinutes()は時・分・秒などそれぞれの整数値を引数に指定します。
13:00のような文字列だとうまくいかないので、事前にパースするとよいでしょう。
設定するだけならDate.prototype.setHours()で時・分・秒・ミリ秒まで一括で指定できます。

Date.prototype.setHours() - JavaScript | MDN

/* '13:00'→[13, 0]にする関数 */
function parseHours(hours) {
    return hours.split(':').map(v => Number(v));
}

/* ...省略... */
var startDate = new Date(day);
startDate.setHours(...parseHours(startTime));