使用应用程序脚本在单个单元格内按日期对值进行排序

Question

我有类似的值

"3月2日-布拉布拉

02 Jan-blablabla"；

在单个单元格中给定，在多行中给定一列。

如何按日期排序，如："1月2日-布拉布拉

02三月-布拉布拉"；

此外，每当编辑此特定单元格时，都需要执行此操作。当单元格被编辑并添加新文本时，时间戳会添加到新文本的左侧

因此，如果今天是7月3日，我在上面的单元格中添加了一些新内容，它将变成："1月2日-布拉布拉

3月2日-布拉布拉

03七月新文本"；

我可以使用谷歌表单公式进行排序，使用拆分、展平和联接的组合可以很容易地进行排序，但这使用了另一列。我想在输入新文本的同一列上使用应用程序脚本做同样的事情。

Answer 1

在单个单元格内排序日期：

function sortSingleCell() {
const mA =["Jan","Feb","Mar","Apr","May","Jun","July","Aug","Sep","Oct","Nov","Dec"];
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName("Sheet0");
const vs = sh.getRange("A1").getDisplayValue();
let arr = vs.match(/d{2}[ -]+(Jan|Feb|Mar|Apr|May|Jun|July|Aug|Sep|Oct|Nov|Dec)[^d{2}]+/gm);
arr.sort((a,b) => {
let ta = a.split(/[ -]/);
let vA = new Date(new Date().getFullYear(),mA.indexOf(ta[1].trim()),ta[0].trim()).valueOf();
let tb = b.split(/[ -]/);
let vB = new Date(new Date().getFullYear(),mA.indexOf(tb[1].trim()),tb[0].trim()).valueOf();
return vA - vB;
});
Logger.log(arr.join(""))
sh.getRange("A1").setValue(arr.join(""))
}

单元格数据：

" 02-Mar - blablabla
02-Jan - blablabla"
Given in a single cell, in multiple rows for a single column.
How do I sort this by date like: " 02-Jan - blablabla
02-Mar - blablabla"
Also, this needs to be done whenever this particular cell is edited. When the cell gets edited and a new text is added - a timestamp gets added on the left side of this new text
So if today was 03-July and I added something new to the above cell, it would become: " 02-Jan - blablabla
02-Mar - blablabla
03-July newtext"

排序单元格数据：

02-Jan - blablabla"
Given in a single cell, in multiple rows for a single column.
How do I sort this by date like: " 02-Jan - blablabla
02-Jan - blablabla
02-Mar - blablabla
02-Mar - blablabla"
Also, this needs to be done whenever this particular cell is edited. When the cell gets edited and a new text is added - a timestamp gets added on the left side of this new text
So if today was 02-Mar - blablabla
03-July and I added something new to the above cell, it would become: " 03-July newtext"