/**
* @param {{
* range: globalThis.SpreadsheetApp.Range
* }} e
*/
function addRow(e) {
const sheet = e.range.getSheet();
if (sheet.getName() !== 'Лист1') return;
if (e.range.getRow() === 2) {
if (sheet.getRange('2:2').getValues()[0].join('').length)
sheet.insertRowBefore(2);
}
}
addRow
к триггеру изменения Таблицы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
.file
как можно раньше, не забудьте сделатьfile = None
file
после Upload()
там должно быть что-то полезное.def load_google(filename, filecontent):
try:
drive = GoogleDrive(google)
file = drive.CreateFile({'title': f'{filename}'})
file.SetContentString(filecontent)
file.Upload()
link=file['alternateLink']
file=None
return link
except Exception as ex:
return ex
=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);
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};)
)