=QUERY(range; sql_query; 1)1
SEARCH или FIND могут решить эту задачу
=ARRAYFORMULA(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IFERROR(
IF(
FIND(TRANSPOSE(LOWER('словарь'!B2:B11));LOWER(B2:B11));
TRANSPOSE('словарь'!B2:B11)
)
));;9^9))))"dd.MM.yyyy" и "HH:mm:ss".const dateTimeStart = sheets.getRange('C1').getValue();dateTimeEnd - только вам известно, но в итоге, полученный dateTimeStart подходит для передачи в качестве значений аргументов в методы CalendarApp.
[
{
vid: 221,
age: 4,
bdate: "2020-05-27T15:00:00-04:00",
type: "Ұрғашы бұзау",
statusChangeTime: "2020-10-19T04:27:05.000Z",
},
{
vid: 225,
age: 6,
bdate: "2020-07-09T15:00:00-04:00",
type: "Ұрғашы бұзау",
statusChangeTime: "",
},
{
vid: -34,
age: 7,
bdate: "2020-02-21T14:00:00-05:00",
type: "Ұрғашы бұзау",
statusChangeTime: "2020-10-17T05:17:55.000Z",
},
{
vid: -30,
age: 4,
bdate: "2019-01-03T14:00:00-05:00",
type: "Еркек бұзау",
statusChangeTime: "2019-05-06T07:24:00.000Z",
},
]
.map((item) => +("" || item.bdate).split("-")[0])
.filter((y, i, a) => a.indexOf(y) === i);> [2020, 2019]
IFS характерна линейность, сводящаяся к принципу "кто первый, тот и решение". Т.е., если вы поменяете строки таким образом, то вы получите нужный результатK6 = TRUE, iferror(QUERY(Pricelist!$A:$U,"select U where A like '"&$D5&"'",0),""),
I5 = TRUE, iferror(QUERY(Pricelist!$A:$R,"select R where A like '"&$D5&"'",0),""),
I6 = TRUE, iferror(QUERY(Pricelist!$A:$S,"select S where A like '"&$D5&"'",0),""),
I7 = TRUE, iferror(QUERY(Pricelist!$A:$T,"select T where A like '"&$D5&"'",0),""),
I5 = TRUE- это 1I6 = TRUE - это 2I7 = TRUE - это 5K6 = TRUE - это 91, то включен только I5, если 3 - I5 и I6, если 12 - I5 и I6 и K6 и т.п. Теперь вы можете выбирать решение по вкусу - вы знаете все варианты.
=DATE(
REGEXEXTRACT(A1,"(\d{4})"),
SWITCH(REGEXEXTRACT(A1,"\w{3}"),"Jan",0,"Feb",1,"Mar",2,"Apr",3,"May",4,"Jun",5,"Jul",6,"Aug",7,"Sep",8,"Oct",9,"Nov",10,"Dec",11),
REGEXEXTRACT(A1,"(\d{2}),")
)
+
TIME(
IF(REGEXMATCH(A1,"PM"),12,0)+REGEXEXTRACT(A1,"(\d{2}):"),
REGEXEXTRACT(A1,":(\d{2})"),
0
)
=ARRAYFORMULA(
DATE(
REGEXEXTRACT(A1:A12,"(\d{4})"),
SWITCH(REGEXEXTRACT(A1:A12,"\w{3}"),"Jan",0,"Feb",1,"Mar",2,"Apr",3,"May",4,"Jun",5,"Jul",6,"Aug",7,"Sep",8,"Oct",9,"Nov",10,"Dec",11),
REGEXEXTRACT(A1:A12,"(\d{2}),")
)
+
TIME(
IF(REGEXMATCH(A1:A12,"PM"),12,0)+REGEXEXTRACT(A1:A12,"(\d{2}):"),
REGEXEXTRACT(A1:A12,":(\d{2})"),
0
))SWITCH - не лучшая идея. Можно заменить на MATCH=ARRAYFORMULA(
DATE(
REGEXEXTRACT(A2:A100;"(\d{4})");
MATCH(REGEXEXTRACT(A2:A100;"\w{3}");{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"};0)-1;
REGEXEXTRACT(A2:A100;"(\d{2}),")
)
+
TIME(
IF(REGEXMATCH(A2:A100;"PM");12;0)+REGEXEXTRACT(A2:A100;"(\d{2}):");
REGEXEXTRACT(A2:A100;":(\d{2})");
0
)
)/**
*
*/
function onEdit(e) {
const sheetName = 'Sheet6'; // Имя листа, который отслеживается
const colToStamp = 7; // Номер колонки для даты
if (e && e.range && e.range.getRow() > 9 && e.range.getColumn() == 6) {
const sheet = e.range.getSheet();
if (sheet.getName() == sheetName) {
const writeVal =
e.value == true || e.value == 'TRUE' || e.value == 'ИСТИНА'
? new Date()
: '';
sheet.getRange(e.range.rowStart, colToStamp).setValue(writeVal);
SpreadsheetApp.getActive().toast('Обновлено');
}
}
}
/**
*
*/
function createTrigger() {
ScriptApp.getProjectTriggers().forEach(
(trigger) =>
trigger.getHandlerFunction() === 'saveData' &&
trigger.getEventType() === ScriptApp.EventType.CLOCK &&
(ScriptApp.deleteTrigger(trigger) ||
console.info(`Tirgger ${trigger.getUniqueId()} was deleted`))
);
// every minutes for testing
// ScriptApp.newTrigger('saveData').timeBased().everyMinutes(1).create();
// at 9 o'clock every days
ScriptApp.newTrigger('saveData').timeBased().atHour(9).everyDays(1).create();
}
/**
*
*/
function saveData() {
const book = SpreadsheetApp.openById(
'1FUSSiDQoXyvKXfzYydoUUfcCGYq_TskpRiwfb28_1Z0'
);
const sheet = book.getSheetByName('Лист1');
const value = sheet.getRange('A1').getValue();
book.getSheetByName('Лист2').appendRow([new Date(), value]);
console.info(`saveData was called successful`);
}1FUSSiDQoXyvKXfzYydoUUfcCGYq_TskpRiwfb28_1Z0 на ваш.Лист1!A1 и добавляет новую строку на Лист2.createTrigger один раз.
=ARRAYFORMULA(QUERY(
SPLIT(FLATTEN(B2:B6 & "♡" & C2:C6 & "♡" & D2:G6);"♡");
"select Col1, Col2, Col3, count(Col3)
where Col3 is not null
group by Col1, Col2, Col3 label count(Col3)''"
))