OfficeスクリプトでExcelシートの必要項目を書式補正して転記する

よくある、「外部からもらったデータを取込みCSV用に転記」をOfficeスクリプトで書いてみる。もらった時点でExcelファイルになっちゃってて頭のゼロが消えちゃってる（あるある）とか、日付の書式を変えたいとか、書式補正しつつ指定の列に並び替える、みたいなの。

仕様（というほどのものでもないけど）

転記元のシート名が「Data」で、ws1という変数に入れて使います。転記先のシート名は「export」で、ws2という変数に入れます。転記先のシートは毎回新規挿入するので、同名のシートがあったら削除します。

受注番号・品番（仮）は、8桁でゼロ埋め、日付は「yyyymmdd」形式を「yyyy-mm-dd」へ変換します。

コード

function main(workbook: ExcelScript.Workbook) {
  //転記元シート取得
  let ws1 = workbook.getWorksheet("Data");

  //転記先シートの準備
  let sheetName = "export" //エクスポート用シート名を定義
  workbook.getWorksheet(sheetName)?.delete(); //すでにあったら削除
  let ws2 = workbook.addWorksheet(sheetName); //エクスポート用シート作成

  //転記先カラム名記入
  ws2.getRange("A1").setValue("得意先ID");
  ws2.getRange("B1").setValue("受注番号");
  ws2.getRange("C1").setValue("受注日");
  ws2.getRange("D1").setValue("品番");
  ws2.getRange("E1").setValue("数量");
  ws2.getRange("F1").setValue("単価");

  //補正&転記
  let lastRow = ws1.getUsedRange().getLastRow().getRowIndex(); //データ範囲の最終行取得
  for (let row = 1; row <= lastRow; row++) { //1（ゼロ始まりなので2行目）から最終行まで繰り返し
    //得意先ID(B列→A列)
    ws2.getCell(row, 0).setValue(ws1.getCell(row, 1).getValue()); //転記（文字列→文字列）

    //受注番号(D列→B列)
    let v1 = String(ws1.getCell(row, 3).getValue()); //D列を文字列型へ変換して取得
    ws2.getCell(row, 1).setNumberFormatLocal("@"); //セルの書式を文字列へ
    ws2.getCell(row, 1).setValue(v1.padStart(8, '0')); //8桁へ補正して転記

    //受注日(C列→C列)
    let tmp = String(ws1.getCell(row, 2).getValue()); //C列を文字列型へ変換して取得
    let yyyy = Number(tmp.slice(0, 4)); //年を取得
    let mm = Number(tmp.slice(4, 6)); //月を取得
    let dd = Number(tmp.slice(-2)); //日を取得
    let tmpDate = new Date(yyyy, mm - 1, dd); //日付を生成（月の範囲は0～11なので注意！） 
    let v2 = tmpDate
      .toLocaleDateString("ja-JP", { //日付型をYYYY-MM-DD形式の文字列へ変換
        year: "numeric",
        month: "2-digit",
        day: "2-digit"
      })
      .split("/")
      .join("-");
    ws2.getCell(row, 2).setNumberFormatLocal("@"); //セルの書式を文字列へ
    ws2.getCell(row, 2).setValue(v2); //転記

    //品番(H列→D列)
    let v3 = String(ws1.getCell(row, 7).getValue()); //H列を文字列型へ変換して取得
    ws2.getCell(row, 3).setNumberFormatLocal("@"); //セルの書式を文字列へ
    ws2.getCell(row, 3).setValue(v3.padStart(8, '0')); //8桁へ補正して転記

    //数量(J列→E列)
    ws2.getCell(row, 4).setValue(ws1.getCell(row, 9).getValue()); //転記（数値→数値）

    //単価(K列→F列)
    ws2.getCell(row, 5).setValue(ws1.getCell(row, 10).getValue()); //転記（数値→数値）
  }

  ws2.activate(); //エクスポート用シートを選択
}

文字列から文字列、数値から数値のセルはそのまま転記しても問題ないけど、数値として認識される値をゼロ埋めするためにはあらかじめ文字列型に変換して取得、というひと手間が要りますね。

日付の変換は、いったん年・月・日をバラバラに取得しておいて（30～33行）、その値で日付を生成して（34行）、さらにそれを「yyyy-mm-dd」形式へ変換する（35～42行）、という手順を踏んでいます。

せっかくがんばって補正してもそのまま入れるとセル側で型変換されちゃうので、セルの書式を文字列にしておくのも忘れずにやらないといけないですね。

所感

セルの数値指定がゼロはじまりっていうのが慣れないとズレちゃうな！！　こればっかりは気を付けるしかしょうがないか。

転記したシートをCSVで保存するのは、現時点（2024年7月）ではOfficeスクリプトからは不可能みたいなので、VBA以外でやるなら、手動で「名前を付けて保存」からCSV保存するのもいいし、Power Automate Desktop使うってところですかね。これも実験したのでまた別で記事書きたいですね。（追記：書きました。）

Pythonが使える環境なら転記からCSV出力まで一発でできるんだろうけど。自分だけで使うならどうとでもなるけど、人に使ってもらうにはやっぱ環境構築がネックかな。（追記：こっちも書きました。）