=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)''"
))
=ARRAYFORMULA(QUERY((
{
QUERY(
FLATTEN(IFERROR(SPLIT('Данные'!B:B, ", ", 0))),
"WHERE Col1 IS NOT NULL",
0
),
VLOOKUP(
QUERY(
FLATTEN(IF(IFERROR(SPLIT('Данные'!B:B, ", ", 0)) = "",, ROW('Данные'!A:A))),
"WHERE Col1 IS NOT NULL",
0
),
{
ROW('Данные'!A:A),
'Данные'!A:A,
'Данные'!C:P
},
COLUMN('Данные'!B:P),
0
)
}),
"SELECT Col2, Col1, Col" & JOIN(", Col", COLUMN('Данные'!C:P)) & "
FORMAT Col2 'yyyy-mm-dd hh:mm:ss'",
1
))function sampleName(e){
// Если изменилась ячейка B5 пользователем
if (e && e.range && e.range.getColumn() == 2 && e.range.getRow() == 5) {
// тут все делается
}
}
={"Статья";ARRAYFORMULA(IFERROR(VLOOKUP(C6:C;'Справочник'!B:D;3;0)))}
/**
* @see https://gist.github.com/penguinboy/762197#gistcomment-3448642
*/
function flatten(object, path = null, separator = '.') {
return Object.keys(object).reduce((acc, key) => {
const value = object[key];
const newPath = Array.isArray(object)
? `${path ? path : ''}[${key}]`
: [path, key].filter(Boolean).join(separator);
const isObject = [
typeof value === 'object',
value !== null,
!(value instanceof Date),
!(value instanceof RegExp),
!(Array.isArray(value) && value.length === 0),
].every(Boolean);
return isObject
? { ...acc, ...flatten(value, newPath, separator) }
: { ...acc, [newPath]: value };
}, {});
}/**
*
*/
function myFunction() {
const response = {
result: {
items: [
{
product_id: 253611,
offer_id: 'УТ-00007992',
stock: {
coming: 0,
present: 100,
reserved: 23,
},
},
{
product_id: 253616,
offer_id: 'УТ-00007043',
stock: {
coming: 0,
present: 23231,
reserved: 1,
},
},
],
total: 20,
},
};
const arr = response['result']['items'];
const data = [];
arr.forEach((el) => data.push(Object.values(flatten(el))));
SpreadsheetApp.getActive()
.getSheetByName('имя')
.getRange(1, 1, data.length, data[0].length)
.setValues(data);
}
1, 06, 23, т.е. 10623.VALUE(TEXT(A1;"1MMDD"), где в A1 дата рождения сотрудника.=ARRAYFORMULA(IF(B2:B;IFERROR(
VLOOKUP(
VALUE(TEXT(B2:B;"1MMDD"));
SORT({'Знаки. Спаравочник'!J:J\'Знаки. Спаравочник'!A:L};1;1);
2;
1
)
);""))B2:B было значение, иначе выводим пустую строку, далее в составном диапазоне {'Знаки. Спаравочник'!J:J\'Знаки. Спаравочник'!A:L}J:J, т.е. "Западная астрология (вариант II)".=ARRAYFORMULA(IF(B2:B;IFERROR(
VLOOKUP(
VALUE(TEXT(B2:B;"1MMDD"));
SORT({'Знаки. Спаравочник'!J:J\'Знаки. Спаравочник'!A:L};1;1);
{2\3\4\5\6};
1
)
);""))