generateValue:function generateValue() {
var spreadsheet = SpreadsheetApp.getActive();
var from = 1;
var to = spreadsheet.getRange('Sheet1!C5').getValue();
var rand = randBetween_(from, to);
spreadsheet.getRange('Sheet1!D5').setValue(rand);
}
function randBetween_(from, to){
return Math.floor(Math.random()*(to - from + 1) + from);
}from для randBetween_ равно 1, а второе to берется из Таблицы. Триггер настроен таким образом, что данные в ячейке D5 обновляются периодически.
/**
*
* @param {GoogleAppsScript.Events.SheetsOnEdit} e
*/
function onEdit(e) {
const allowRanges = [
{ sheetName: 'Заглавная буква', cells: ['B2', 'B3', 'B4'] },
{ sheetName: 'Стартовая страница', cells: ['B2', 'C2', 'D2'] },
]; // Список
const sheetNames = allowRanges.map(r => r.sheetName);
if (e && e.range) {
const sheet = e.range.getSheet();
if (
sheetNames.indexOf(sheet.getName()) > -1 &&
allowRanges.find(r => r.sheetName === sheet.getName()).cells.indexOf(e.range.getA1Notation()) > -1
) {
const value = '' + e.range.getValue();
e.range.setValue(value.slice(0, 1).toLocaleUpperCase() + value.slice(1).toLocaleLowerCase());
}
}
}
Не работает для группы ячеек
let a = [{v: 1}, {v: 2}, {v: 3}];
for (let obj of a) {
obj.v += 1;
}
a // [{"v":2},{"v":3},{"v":4}]
fs.writeFile(file, data[, options], callback)#
History:
- file
<string> | <Buffer> | <URL> | <integer>filename or file descriptor- data
<string> | <Buffer> | <TypedArray> | <DataView> | <Object>- options
<Object> | <string>
- encoding
<string> | <null>Default: 'utf8'- mode
<integer>Default: 0o666- flag
<string>See support of file system flags. Default: 'w'.- signal
<AbortSignal>allows aborting an in-progress writeFile- callback
<Function>
- err
<Error> | <AggregateError>
When file is a filename, asynchronously writes data to the file, replacing the file if it already exists. data can be a string or a buffer.
When file is a file descriptor, the behavior is similar to calling fs.write() directly (which is recommended). See the notes below on using a file descriptor.
The encoding option is ignored if data is a buffer.
If data is a plain object, it must have an own (not inherited) toString function property.
...
fs.write). После этого нужные пункты оглавления подсветятся желтым и вы легко найдете нужный. 
генерирует файлы htaccess во всех папках
find /home/.../papka_saita -type f -name ".htaccess" -delete
function COUNTUNIQWORDS(cell) {
var result = []
var words = String(cell).split(" ");
var wordsCount = words.reduce(function (acc, w) {
if(acc[w]) {
acc[w] += 1
} else {
acc[w] = 1;
}
return acc;
}, {});
for (var w in wordsCount) result.push('Cлово "'+ w +'" встречается '+ wordsCount[w] + ' раз');
return result
}
=REGEXEXTRACT("" & B8;"(\d\d)(\d\d)(\d\d)(\d\d)")
=IF(--C8>22;REGEXEXTRACT(C8;"(\d)(\d)"); C8)
=INDEX(SORTN(TRANSPOSE({
IF({B19:L19\B28:L28}="";"";--{B19:L19\B28:L28});
TRIM(QUERY(
IF("" & TRANSPOSE({B19:L19\B28:L28})="" & {B19:L19\B28:L28};IF({B19:L19\B28:L28}="";"";--{B19:L19\B28:L28}););
;
9^9))
});9^9;2;1;1);;2)
var list = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var counter = 6;
var logics = {
"F10": "=IFERROR(VLOOKUP(B10;'Эта неделя'!$B$3:$E$120;3;FALSE);0)",
"G10": "=IFERROR(VLOOKUP(B10;'Эта неделя'!$B$3:$E$120;4;FALSE);0)",
"H10": "=IF(C10>=10;F10-70;(F10+G10)-70)+J10",
"I10": "=H10+J10"
}
for (var key in logics) {
formulaPaste (list, counter, key, logics[key]);
counter++;
}
function formulaPaste (list, i, cell, formula){
list.getRange(cell).setFormula(formula);
var row = list.getLastRow();
var range = list.getRange(10, i, row-9);
list.getRange(cell).copyTo(range);
}
=FILTER('Лист1'!F:F;ISNA(MATCH('Лист1'!F:F;'Лист1'!K:K;0)))