={"Заголовок";ARRAYFORMULA()}
ARRAYFORMULA
постараться рассчитать свои задачи function run() {
const book = SpreadsheetApp.getActiveSpreadsheet();
const sheet = book.getSheetByName('Пример 1');
sheet.sort(5);
const values = sheet.getDataRange().getValues();
const buffer = {
value: values[values.length - 1][4]
};
values.forEach((_, i, arr) => {
const j = arr.length - 1 - i;
const value = arr[j][4];
if (value !== buffer.value) {
sheet.insertRowAfter(j + 1);
buffer.value = value;
}
});
}
function run() {
const from = SpreadsheetApp.openById(
'1DrMcZylPeacuZm7zTxRZmd3hcx1l0pKS2RjeqSphUeE'
);
const fromSheet = from.getSheetByName('Данные');
const fromData = fromSheet.getDataRange().getValues().slice(1);
const to = SpreadsheetApp.openById(
'1LFgJE1cCRNM4PoVAWmZRPCm5BbdsAN5YkhgxC7okJc8'
);
const toSheet = to.getSheetByName('Сводные');
const toDataIndex = toSheet
.getDataRange()
.getValues()
.slice(1)
.filter((row) => row[5] !== '')
.map((row) => row[5]);
const data = fromData.filter((row) => toDataIndex.indexOf(row[5]) === -1);
if (data.length)
toSheet
.getRange(toSheet.getLastRow() + 1, 1, data.length, data[0].length)
.setValues(data);
}
function triggerSort(e) {
const book = SpreadsheetApp.getActiveSpreadsheet();
const sheet = book.getSheetByName('Sheet1');
sheet.sort(2, false).sort(1);
}
=ARRAYFORMULA(VLOOKUP(A:A;QUERY(A:A;"select A, count(A) group by A";0);2;0))
=ARRAYFORMULA(COUNTIF(A2:A;A2:A))
=QUERY(
IMPORTXML(
"https://fuelo.eu/?convertto=eur",
"//table[@class=('table table-striped table-hover tablesorter')]//tr"
),
"Select Col1, Col2, Col6, Col10
label Col1'Countries', Col2'Gasoline', Col6'Diesel', Col10'AutoGas'
format Col2'€ 0.00', Col6'€ 0.00', Col10'€ 0.00'"
)
/**
* Очищает B4, если L4 содержит значение "пусто".
* Работает на листе Sheet3
*/
function run() {
const book = SpreadsheetApp.getActiveSpreadsheet();
const sheet = book.getSheetByName('Sheet3');
const valueL4 = sheet.getRange('L4').getValue();
if (valueL4 === 'пусто')
sheet.getRange('B4').clearContent();
}
={"Сотрудник";FILTER(A2:A,B2:B=E1)}
=ARRAYFORMULA(FILTER(
A2:B;
MATCH(
A2:A;
QUERY(QUERY(
{A2:B;G2:G\-1*H2:H};
"select Col1, sum(Col2) where Col1<>'' group by Col1";
0
);"select Col1 where Col2<>0";0);
0
)
))
function myFunction() {
const book = SpreadsheetApp.getActiveSpreadsheet();
const sheet = book.getSheetByName('Лист');
const range = sheet.getRange('A1:L4');
const values = range.getValues();
range.setValues(calculate_(values));
}
/**
* @param {any[][]} values
* @returns {any[][]}
*/
function calculate_(values) {
return values.map((row, i) => {
const collection = row.slice(0, 3);
return row.map(((cell, j) => {
if (i < 1 || j < 3) return cell;
const header = values[0][j];
return collection.includes(header) ? 1 : '';
}));
});
}