スクリプトを使用し、他のファイルからデータを転記した際、日付のシリアル値が変わってしまう。

Question

前提

スクリプトを用いて、下記の動作を実現しました。
・GoogleDrive上のほかフォルダにアップロードされたExcelファイルのシートを、ファイル名で探す
・Excel形式からスプシ形式に変換し、コピー
・現在のスプシの特定のシートに貼り付け

発生している問題・エラーメッセージ

しかし、スプシに貼り付けられたデータのうち、
元データのA列は日付（"yyyy/m/d"形式）なのですが、なぜか

例）
元のエクセルA1のデータ
└2021/11/1（※シリアル値：44501）
貼付け後のA1のデータ
└2021/11/01 16:00:00（※シリアル値：44501.6666666667）

となってしまい、その後の操作で非常に都合が悪いです。
（アメリカ時間が基準になっている？？）

日付が変わらず、"yyyy/m/d"形式になるよう、コントロールできれば最高です。

該当のソースコード

コードをすべて記載しました。各箇所の概要はコメントアウトで記載しています。
個人的には、おそらくExcelからスプシ形式に変換する部分で何か起こってるのではと思います・・・。

function PastePlan() {
  //現在のスプシのidと、貼り付け対象のシートを取得
  const Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const thisSsId = Spreadsheet.getId();
  Logger.log(thisSsId);
  const pasteSheet = Spreadsheet.getSheetByName('予測');

　//コピー元データのExcelファイルを名前から取得
 const folder = DriveApp.getFolderById('1PSSwUksFZTHGdWQU351EUBgOHDlMTJBM');//ロ―データフォルダのID
 const files  = folder.getFilesByName('plan_' + thisSsId + '.csv');
 const file   = files.next();
 Logger.log(file);

  //おそらく、Excelからスプシへの変換で必要な操作（ネット上のコード）
 options = {
   title   : file.getName(),
   mimeType: MimeType.GOOGLE_SHEETS,
   parents : [{ id: folder.getId() }]
 }

　//スプシ形式に変換したコピー元データのシートから、すべての値を取得
 const newSs    = SpreadsheetApp.openById(Drive.Files.insert(options, file.getBlob()).id); //ExcelをSSに変換し取得
 const newSheet = newSs.getActiveSheet();
 const values   = newSheet.getDataRange().getValues();

　//下記ですでに、「Mon Nov 01 16:00:00 GMT+09:00 2021」の表示
 Logger.log(values[1][0]);


 //現在のスプシの貼り付け先シートに、コピーした範囲を貼り付け
 pasteSheet.getRange(1, 2, values.length, values[0].length).setValues(values);

 //スプシ形式に変換したファイルをゴミ箱に移動
 //DriveAppクラスからファイルIDでファイル一意に取得する
 const fileData = DriveApp.getFileById(newSs.getId());
//IDから取得したファイルをゴミ箱のフラグをtrueにする
 const getData = fileData.setTrashed(true);
}

試したこと

前任者は、似た状況で、下記のコードで対応しているぽいですが、僕のスキルレベルでは分かりませんでした><

plansheet_copyFrom.getRange(2,1,lastRow1-1,1).setNumberFormat(dateFormats);
var copyValue = plansheet_copyFrom.getRange(2,1,lastRow1-1,lastCol1).getValues();
Sheet2.getRange(2,2,lastRow1-1,lastCol1).setValues(copyValue);
var copyValuedate = [];
for (var i = 0; i <= copyValue.length-1 ; i++) {
 var Value = Utilities.formatDate(copyValue[i][0], "Asia/Tokyo", dateFormats);
 var c_date  = Value.toString(10);;
 copyValuedate[i] = c_date;
        }

ネットでいろいろ検索してみましたが、同様のパターンが見つからず途方に暮れているので、
皆様の力をお借りできれば幸いです。

GASをいじり始めて1週間たたない、基礎概念もおぼろな初心者ですが、何卒、よろしくお願いいたします。

Answer 1

貼り付け後の値が、2021/11/01 16:00:00となるのは、
csvから変換した後のGoogleスプレッドシートのタイムゾーンが
日本標準時の地域以外に設定されてしまうことが原因と推測されます。

(変換後のスプレッドシートのメニューから「ファイル」->「設定」を選択して開いた例)

したがって、下記のように、変換後のGoogleスプレッドシートのタイムゾーンを「Asia/Tokyo」に設定すれば期待する動作になるのではないでしょうか。

function PastePlan() {
  //現在のスプシのidと、貼り付け対象のシートを取得
  const Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

～略～

　//スプシ形式に変換したコピー元データのシートから、すべての値を取得
  const newSs    = SpreadsheetApp.openById(Drive.Files.insert(options, file.getBlob()).id); //ExcelをSSに変換し取得

+ // 変換後のスプレッドシートのタイムゾーンをAsia/Tokyoに設定しなおす。
+ newSs.setSpreadsheetTimeZone("Asia/Tokyo");
  const newSheet = newSs.getActiveSheet();
  const values   = newSheet.getDataRange().getValues();
(以下略)

"yyyy/m/d"形式になるよう、コントロール

貼り付け先の書式を「yyyy/m/d」形式にして貼り付けます。

（略）
 //現在のスプシの貼り付け先シートに、コピーした範囲を貼り付け
-  pasteSheet.getRange(1, 2, values.length, values[0].length).setValues(values);
+  pasteSheet.getRange(1, 2, values.length, values[0].length).setNumberFormat('yyyy/m/d').setValues(values);

---　

・全体コード

function PastePlan() {
  //現在のスプシのidと、貼り付け対象のシートを取得
  const Spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const thisSsId = Spreadsheet.getId();
  Logger.log(thisSsId);
  const pasteSheet = Spreadsheet.getSheetByName('予測');

　//コピー元データのExcelファイルを名前から取得
 const folder = DriveApp.getFolderById('1PSSwUksFZTHGdWQU351EUBgOHDlMTJBM');//ロ―データフォルダのID
 const files  = folder.getFilesByName('plan_' + thisSsId + '.csv');
 const file   = files.next();
 Logger.log(file);

  //おそらく、Excelからスプシへの変換で必要な操作（ネット上のコード）
 options = {
   title   : file.getName(),
   mimeType: MimeType.GOOGLE_SHEETS,
   parents : [{ id: folder.getId() }]
 }

　//スプシ形式に変換したコピー元データのシートから、すべての値を取得
 const newSs    = SpreadsheetApp.openById(Drive.Files.insert(options, file.getBlob()).id); //ExcelをSSに変換し取得
 // 変換後のスプレッドシートのタイムゾーンをAsia/Tokyoに設定しなおす。
 newSs.setSpreadsheetTimeZone("Asia/Tokyo");
 const newSheet = newSs.getActiveSheet();
 const values   = newSheet.getDataRange().getValues();

 Logger.log(values[1][0]);

 //現在のスプシの貼り付け先シートに、コピーした範囲を貼り付け
 pasteSheet.getRange(1, 2, values.length, values[0].length).setNumberFormat('yyyy/m/d').setValues(values);

 //スプシ形式に変換したファイルをゴミ箱に移動
 //DriveAppクラスからファイルIDでファイル一意に取得する
 const fileData = DriveApp.getFileById(newSs.getId());
//IDから取得したファイルをゴミ箱のフラグをtrueにする
 const getData = fileData.setTrashed(true);
}

Comments

[mattsuncaca]

ありがとうございます！
まさにこちらで、希望通りの挙動になりました！

Answer 2

過去に私も同様の問題があったのでこちら張っておきます。
Googleスプレッドシートに数値や文字列を含めた配列を貼ったときに、頭が0で始まる数値が消える問題

ヒント程度ですが、
sheet.getRange().setNumberFormats().setValues();
という感じにフォーマットを張る様にするともしかしたらうまくいくかもしれません。

今後ずっと固定したセルに張り続ける場合は、事前に書式を設定するだけでもいけるかもしれません。

すでにテスト済みの場合は私もわからない感じですので悪しからずよろしくお願いします。