回答編集履歴
3
利用メソッドを一部変更
test
CHANGED
|
@@ -28,7 +28,7 @@
|
|
|
28
28
|
|
|
29
29
|
// 念のため、B列に「001-nnnnn」以外のデータが紛れているケースを考慮
|
|
30
30
|
|
|
31
|
-
const word = '=query(B:B,"select max(B) '
|
|
31
|
+
const word = '=query(B:B, "select max(B) '
|
|
32
32
|
|
|
33
33
|
+ "where B matches '^001-\d{5}$' " //紛れがない場合はこの行を削除
|
|
34
34
|
|
|
@@ -48,7 +48,7 @@
|
|
|
48
48
|
|
|
49
49
|
.toString().padStart(5, '0');
|
|
50
50
|
|
|
51
|
-
|
|
51
|
+
clearContent();
|
|
52
52
|
|
|
53
53
|
sheet.getRange(last_row + 1, B).setValue(ret);
|
|
54
54
|
|
|
@@ -82,9 +82,9 @@
|
|
|
82
82
|
|
|
83
83
|
.getValues()
|
|
84
84
|
|
|
85
|
-
.filter(s => s[0].toString().search(/^001-\d{5}$/)
|
|
85
|
+
.filter(s => ~s[0].toString().search(/^001-\d{5}$/))
|
|
86
86
|
|
|
87
|
-
.sort((x,y) =>
|
|
87
|
+
.sort((x, y) => y[0].localeCompare(x[0]))[0];
|
|
88
88
|
|
|
89
89
|
const ret = '001-'
|
|
90
90
|
|
2
test
CHANGED
|
@@ -82,6 +82,8 @@
|
|
|
82
82
|
|
|
83
83
|
.getValues()
|
|
84
84
|
|
|
85
|
+
.filter(s => s[0].toString().search(/^001-\d{5}$/) > -1)
|
|
86
|
+
|
|
85
87
|
.sort((x,y) => x > y ? -1:(x < y ? 1 : 0))[0];
|
|
86
88
|
|
|
87
89
|
const ret = '001-'
|
1
例を追加
test
CHANGED
|
@@ -4,7 +4,7 @@
|
|
|
4
4
|
|
|
5
5
|
|
|
6
6
|
|
|
7
|
-
|
|
7
|
+
例を載せておきます。解析はご自身でお願いしますね。
|
|
8
8
|
|
|
9
9
|
```GAS
|
|
10
10
|
|
|
@@ -57,3 +57,39 @@
|
|
|
57
57
|
}
|
|
58
58
|
|
|
59
59
|
```
|
|
60
|
+
|
|
61
|
+
```GAS
|
|
62
|
+
|
|
63
|
+
function sortVersion() {
|
|
64
|
+
|
|
65
|
+
let sheet = SpreadsheetApp.getActiveSheet();
|
|
66
|
+
|
|
67
|
+
const B = 2;
|
|
68
|
+
|
|
69
|
+
const last_row = sheet.getRange(sheet.getMaxRows(), B)
|
|
70
|
+
|
|
71
|
+
.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
|
|
72
|
+
|
|
73
|
+
if (last_row < 2) {
|
|
74
|
+
|
|
75
|
+
sheet.getRange(2, B).setValue('001-00001');
|
|
76
|
+
|
|
77
|
+
return ;
|
|
78
|
+
|
|
79
|
+
}
|
|
80
|
+
|
|
81
|
+
const v = sheet.getRange(2, B, last_row - 1, 1)
|
|
82
|
+
|
|
83
|
+
.getValues()
|
|
84
|
+
|
|
85
|
+
.sort((x,y) => x > y ? -1:(x < y ? 1 : 0))[0];
|
|
86
|
+
|
|
87
|
+
const ret = '001-'
|
|
88
|
+
|
|
89
|
+
+ (+[...v].toString().replace(/^001-/, '') + 1).toString().padStart(5, '0');
|
|
90
|
+
|
|
91
|
+
sheet.getRange(last_row + 1, B).setValue(ret);
|
|
92
|
+
|
|
93
|
+
}
|
|
94
|
+
|
|
95
|
+
```
|