function cleanBook() {
const sheetsForClean = [
{
rangeA1: 'Sheet2!E4:AI140'
},
{
rangeA1: 'Sheet3!E4:AI140'
},
{
rangeA1: 'Sheet4!E4:AI140'
}
];
const book = SpreadsheetApp.getActive();
sheetsForClean.forEach(item => book.getRange(item.rangeA1).clearContent());
}
sheetsForClean
и вызвать функцию. Можно вызвать и через макросы. =QUERY({
FILTER(A1:A24;MOD(ROW(A1:A24);4)=2)\FILTER(A1:A24;MOD(ROW(A1:A24);4)=0)
};
"select sum(Col2) where Col1 contains 'День'";
0)
function getLastRowIndexOfArray_(arr) {
const index = arr.reverse().findIndex(row => row.join('') !== '');
return arr.length - (index === -1 ? arr.length : index);
}
function run() {
const book = SpreadsheetApp.getActiveSpreadsheet();
const sheet = book.getSheetByName('Sheet4');
const range = sheet.getRange('G:G');
insertValueToEndOfRange_(range, [[22]]);
}
function insertValueToEndOfRange_(range, values) {
const lastValueRow = getLastRowIndexOfArray_(range.getValues()) + 1;
range.getSheet().getRange(lastValueRow, range.getColumn(), values.length, values[0].length)
.setValues(values);
}
function formSendAction() {
const book = SpreadsheetApp.openById('1EbRQ1yV0upNFW11fuyfl3Bts7OTJ9rSAN2kiBekhzRg');
const data = book.getSheetByName('Лист2').getDataRange()
.getValues().slice(1)
.map(row => row[1])
.sort(stringCompare_)
.reverse();
const target = book.getSheetByName('Результат');
const targetData = target.getDataRange()
.getValues()
.map(row => row[0]);
const targetDataR = [...targetData].reverse();
const firstIndexR = targetDataR.findIndex(fioTarget => fioTarget !== '');
const firstIndex = targetData.findIndex(fioTarget => fioTarget !== '');
const lastIndexR = targetDataR.length - firstIndex;
data.forEach(fio => {
if (!targetData.includes(fio)) {
let index = targetDataR.findIndex(fioTarget => fioTarget !== '' && stringCompare_(fioTarget, fio) === -1);
if (index === -1) {
console.log(stringCompare_(targetData[firstIndex], fio), firstIndex, targetData[firstIndex], fio);
if (stringCompare_(targetData[firstIndex], fio) === 1) {
index = lastIndexR + 1;
} else {
index = firstIndexR - 2;
}
};
index = targetDataR.length - index + 2;
target.insertRowsBefore(index, 2);
target.getRange(index, 1).setValue(fio);
target.setRowHeights(index + 1, 1, 10);
}
});
}
function stringCompare_(a, b) {
return String(a).toLocaleLowerCase() < String(b).toLocaleLowerCase() ? -1 :
String(a).toLocaleLowerCase() > String(b).toLocaleLowerCase() ? 1 : 0;
};
function onEdit(e) {
try {
const sheet = e.range.getSheet();
const col = e.range.getColumn();
if (
sheet.getName() === 'Sheet3' &&
(col === 15 || col === 16) &&
(e.value === 'TRUE' || e.value === 'ИСТИНА')
) {
const vals = [[col === 15, col === 16]];
sheet.getRange(e.range.getRow(), 15, 1, 2).setValues(vals);
}
} catch (err) {
SpreadsheetApp.getActive().toast(err);
}
}
={
FILTER('Лист1'!A:C;IFERROR(MATCH('Лист1'!C:C;{"стул"};0);));
FILTER('Лист2'!A:C;IFERROR(MATCH('Лист2'!C:C;{"кровать"};0);))
}
SpreadsheetApp.openById()
book.getSheetByName()
sheet.getRange()
=INDEX(IFERROR(VLOOKUP(
ROW(A2:A25);
SPLIT(FLATTEN(IF(
REGEXMATCH(A2:A25;TRANSPOSE(B2:B25));
ROW(A2:A25) & "❤" & REGEXREPLACE(A2:A25;TRANSPOSE(B2:B25);TRANSPOSE(C2:C25));
));"❤");
2;
);A2:A25))
function onEdit(e) {
const range = e.range;
const sheet = range.getSheet();
const sheetName = sheet.getName();
const row = range.getRow();
const column = range.getColumn();
if (sheetName === 'Лист4' && column === 21 && row > 1) {
const range2 = range.offset(0, 1);
const value2 = range2.getValue();
range2.setValue(+value2 + +e.value);
range.setValue(null);
}
}