const SETTINGS = {
cells: [{
sheet: 'Sheet2',
row: 5,
col: 3
}, {
sheet: 'Sheet2',
row: 5,
col: 4
}, {
sheet: 'Sheet2',
row: 5,
col: 5
}, {
sheet: 'Sheet2',
row: 5,
col: 9
}]
};
/**
* @param {{
* range: globalThis.SpreadsheetApp.Range
* }} e
*/
function myFunction(e) {
const sheet = e.range.getSheet();
if (sheet.getName() !== 'Sheet2') return;
const row = e.range.getRow();
const col = e.range.getColumn();
const trueed = SETTINGS.cells.findIndex(cell => cell.row === row && cell.col === col);
if (trueed !== -1 && (e.value === 'TRUE' || e.value === 'ИСТИНА')) {
SETTINGS.cells.forEach((cell, i) => sheet.getRange(cell.row, cell.col).setValue(trueed === i));
SpreadsheetApp.getActive().toast('OK');
}
}myFunction.
=QUERY(UNIQUE(A2:D125);"select Col1, Col2, Col3, count(Col1) group by Col1, Col2, Col3")
function myFunction() {
const arr = [1, 2, 2, 3, 34, 54, 3, 4, 45, 34, 53, 45, 4, 1, 23, 12, 3, 235, 2, 5, 1, 2, 6, 76, 54, 6, 84, 5, 23, 2, 34, 6, 735];
const done = [];
let attempts = 5;
while (attempts--) {
const a = [...arr];
const res = [];
const exclude = [];
while (a.length) {
const index = Math.floor(Math.random() * a.length);
const v = a[index];
if (exclude.indexOf(v) === -1) {
a.splice(index, 1);
res.push(v);
exclude.push(v);
if (exclude.length > 4)
exclude.shift();
} else if (!a.filter(v => exclude.indexOf(v) === -1).length) {
console.log('break', a, exclude);
break;
}
}
if (!a.length) {
done.push(...res);
break;
}
}
console.log(done);
}
"
function update() {
const book = SpreadsheetApp.getActive();
const sheet = book.getSheetByName('Пример');
sheet.getRange('A2:A')
.setValues(sheet.getRange('A2:C').getValues()
.map(([kladovshik, _, externalSource]) =>
[externalSource === 'Продан' ? externalSource : kladovshik]));
}
IF=IF(LEFT(A2;2)="CJ";8;IF(LEFT(A2;2)="RJ";15;IF(LEFT(A2;2)="EJ";37;"?")))
=ARRAYFORMULA(VLOOKUP(LEFT(A2:A16;2);H2:I4;2;))
const datainfo = JSON.parse(response);
datainfo.suggestions.forEach(suggestion => console.log(suggestion.value));const datainfo = JSON.parse(response);
const values = datainfo.suggestions.map(suggestion => [suggestion.value]);
book.getSheetByName('Sheet1').clearContent()
.getRange(1, 1, values.length, values[0].length).setValues(values);const content = response.getContentText();
const data = JSON.parse(content);const values = data.rows.map(item => [item.name, item.code]);
sheet.getRange(1,1,values.length, values[0].length).setValues(values);QUERY
=ARRAYFORMULA(
VLOOKUP(DAY(TODAY());A3:K33;{2\3\4\5\6\7\8\9\10\11};)-
VLOOKUP(DAY(TODAY())-1;A3:K33;{2\3\4\5\6\7\8\9\10\11};)
)
function onFormSubmit(e){
UrlFetchApp.fetch(endpoint, {
payload: JSON.stringify(e)
});
}Основное правило
Проценты и числа - это разные типы данных. Их можно связать через умножение, как действие определения долей, потому что процент является формой десятичной дроби.
if(col1 <> 0 && col2 === 1) {
}for(){
if(){
continue;
}
}for(){
if(){
break;
}
}else if для группировки проверокif(col1 <> 0) {
} else if(col2 === 1) {
}
/**
* @param { globalThis.SpreadsheetApp.Range } range
*/
function uniqByRow_(range, compact = false) {
const mapper = compact ?
row => {
const uniq = [...new Set(row)];
return row.map((_, i) => uniq[i] ?? '');
} :
row => row.map((cell, i) => row.indexOf(cell) === i ? cell : '');
range.setValues(
range.getValues().map(mapper));
}
function run() {
const book = SpreadsheetApp.getActive();
const range = book.getSheetByName('Данные').getDataRange();
uniqByRow_(range, true);
}
uniqByRow_(range, false), то получим
function run() {
const book = SpreadsheetApp.getActive();
const range1 = book.getRange('Лист1!B4');
const range2 = book.getRange('Лист1!B6');
const joinRichTextValue = new JoinRichTextValue();
joinRichTextValue.separator = { text: '\n' };
joinRichTextValue.push(range1.getRichTextValue());
joinRichTextValue.push(range2.getRichTextValue());
const range = book.getRange('Лист1!B16');
range.setRichTextValue(joinRichTextValue.build());
}
JoinRichTextValue описан в проекте Таблицы https://docs.google.com/spreadsheets/d/11tjtnmr_F-...
=ARRAYFORMULA(VLOOKUP(
R5:R;
QUERY(
SPLIT(FLATTEN(A5:A & "|" & B5:D);"|");
"select Col1, sum(Col2) group by Col1");
2;
))
={A4:A7\{"Сумма";INDEX(
{TRANSPOSE(QUERY(
{TRANSPOSE(B5:E7)};
"select " & "sum(Col" & JOIN("), sum(Col"; SEQUENCE(1;ROWS(A5:A7);1;1)) & ")";
0))}
;;2
)}}
={INDEX(
{TRANSPOSE(QUERY(
{TRANSPOSE(B5:E7)};
"select " & "sum(Col" & JOIN("), sum(Col"; SEQUENCE(1;ROWS(A5:A7);1;1)) & ")";
0))}
;;2
)}