将google工作表数据复制到另一个使用文本公式作为静态文本的工作簿

Question

我有下面的代码，复制数据从一个谷歌表工作簿到另一个，但我确实有一个公式，是复制作为日期值，但我希望它复制为文本值。它目前正在复制为2023年1月1日11:00(日期)我想把它复制成01/01/2020 11:20 AM (text)

=arrayformula(text(A2:A, "dd/mm/yyyy hh:mm am/pm")

var sourceSpreadsheetID = "ID1";
var sourceWorksheetName = "Overview";
var targetSpreadsheetID = "ID2";
var targetWorksheetName = "Overview Static";
function importData() {

var thisSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
var thisWorksheet = thisSpreadsheet.getSheetByName(sourceWorksheetName);
var thisData = thisWorksheet.getDataRange();
//Uncomment line 11 below and comment out line 9 if you want to sync a named range. Replace "teamBugs" with your named range.
//var thisData = thisSpreadsheet.getRangeByName("data");

var toSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetID);
var toWorksheet = toSpreadsheet.getSheetByName(targetWorksheetName);
var toRange = toWorksheet.getRange(1, 1, thisData.getNumRows(), thisData.getNumColumns())
toRange.setValues(thisData.getValues()); 
}

Answer 1

var sourceSpreadsheetID = "ID1";
var sourceWorksheetName = "Overview";
var targetSpreadsheetID = "ID2";
var targetWorksheetName = "Overview Static";
function importData() {

var thisSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
var thisWorksheet = thisSpreadsheet.getSheetByName(sourceWorksheetName);
var thisData = thisWorksheet.getDataRange();
//Uncomment line 11 below and comment out line 9 if you want to sync a named range. Replace "teamBugs" with your named range.
//var thisData = thisSpreadsheet.getRangeByName("data");
var ts = thisSpreadsheet.getSpreadsheetTimeZone();
var replacedData = thisData.getValues().map(row => 
row.map((v, i) =>
// Assuming the dates are in the first column
i === 0 ? Utilities.formatDate(v, ts, 'dd/MM/yyyy HH:mm a') : v
)
);

var toSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetID);
var toWorksheet = toSpreadsheet.getSheetByName(targetWorksheetName);
var toRange = toWorksheet.getRange(1, 1, thisData.getNumRows(), thisData.getNumColumns())
toRange.setValues(replacedData); 
}

参考:

• Array.prototype.map ()
• formatDate(日期，时区，格式)