【GAS】生年月日のデータをシリアル値やテキスト形式から日時データに整形する

スプレッドシートに外部から日付データを入力するとき、38575みたいなシリアル値がたまに入力されたり、テキスト形式で入力されるとカスタム日時のフォーマットが適用されないので、下記コードで日時データに変換してみました。

L列に日付が入ってるものとします。

// 生年月日をテキスト形式からDateに変換
function updateStudentBirthday(){ let ss = SpreadsheetApp.getActiveSpreadsheet(); const datash = ss.getSheetByName('master'); const lastRow = datash.getLastRow(); const data = datash.getRange('L:L').getValues(); // console.log(data); let line_number = 1; data.forEach((line)=>{ const d = line[0]; if(d!==""){ const type = typeof d; // console.log(d,type); // console.log(d.indexOf('/')) let date = ''; if(type=='string' && line_number!==1){ if(d.indexOf('/')!==-1){ const time = Date.parse(d); date = new Date(time); // console.log(d,date,type); }else{ date = dateFromSn(d); } }else if(type=='number'){ date = dateFromSn(d); } // console.log(date) if(date!='') datash.getRange("L"+line_number).setValue(date); } line_number++; });
}
function dateFromSn(serialNumber){ // シリアル値→UNIX時間(ミリ秒) var COEFFICIENT = 24 * 60 * 60 * 1000; //日数とミリ秒を変換する係数 var DATES_OFFSET = 70 * 365 + 17 + 1 + 1; //「1900/1/0」～「1970/1/1」 (日数) var MILLIS_DIFFERENCE = 9 * 60 * 60 * 1000; //UTCとJSTの時差 (ミリ秒) const ut = (serialNumber - DATES_OFFSET) * COEFFICIENT - MILLIS_DIFFERENCE; return new Date(ut);
}

下記の郵便番号と電話番号の整形も参考にしてみてください。