/**
* Открывает URL и возращает код страницы
* Telegram - @ProgrammerForever
*
* @param {string} URL URL который нужно открыть
* @param {boolean} isCut Указывакт, нужно ли обрезать страницу до 50000 символов по длине, по умолчанию false
* @param {boolean} noScript Указывакт, нужно ли удалять скрипты из кода
* @return Исходный код страницы
* @customfunction
*/
function getHTML(URL,isCut,noScript) {
if ((URL === undefined)||(URL == "")) { return "#ОШИБКА Пустой URL";};
if (isCut === undefined) {var isCut=true;};
if (noScript === undefined) {var noScript=true;};
if (URL.map){ //Если задан диапазон
return URL.map(getHTML);
}else{
try {
var payload = {
'rand':(new Date()).getTime()
};
var headers={
'Connection': 'keep-alive',
'Cache-Control': 'max-age=0',
'Upgrade-Insecure-Requests': 1,
'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.142 Safari/537.36',
'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3',
'Accept-Encoding': 'gzip, deflate, br',
'Accept-Language': 'ru-RU,ru;q=0.9,en-US;q=0.8,en;q=0.7'
};
var options = {
'method' : 'get',
'headers' : headers,
'payload': payload
};
var response = UrlFetchApp.fetch(URL,options);
var charset=response.getAllHeaders["charset"];
//var responseText=response.getContentText(charset?charset:"windows-1251");
var responseText=response.getContentText(charset?charset:"UTF-8");
if (noScript){
responseText=responseText.replace(/<script[^>]*>(?:(?!<\/script>)[^])*<\/script>/gmi,"");
responseText=responseText.replace(/<!--.*?-->/gmi,"");
responseText=responseText.replace(/<link.*?\/>/gmi,"");
responseText=responseText.replace(/<meta.*?\/>/gmi,"");
responseText=responseText.replace(/[\n\r\t]/gmi,"");
};
if (isCut&&(responseText.length>50000)){return responseText.substring(0,50000);}else{return responseText;};
} catch (err) {
//return JSON.stringify(err);
return "#ОШИБКА "+err.message;
};
};
};/**
* Функция возвращает сумму ячеек в диапазоне с цветом фона равным color
* @param {Any[][]} range - Диапазон ячеек
* @param {String} color - цвет фона ячеек
* @return {Number} сумма ячеек в диапазоне с цветом фона равным color
*/
function sumByBackgroundColor(range, color) {
const values = range.getValues();
const backgrounds = range.getBackgrounds();
let sum = backgrounds.reduce((acc, row, ri) => {
return row.reduce((acc, c, ci) => {
if (c === color) {
return acc + values[ri][ci];
}
return acc;
}, acc);
}, 0);
return sum;
}Public Function SumCellsByColor(rng As Range, clr As Long) As Double
Dim cell As Range
Dim colSum As Double
colSum = 0
For Each cell In rng
If cell.Interior.ColorIndex = clr Then
colSum = colSum + cell.Value
End If
Next cell
SumCellsByColor = colSum
End Function$objPHPExcel = PHPExcel_IOFactory::load('myExcelFile.xlsx');
$objWorksheet = $objPHPExcel->getActiveSheet();
$lastRow = $objWorksheet->getHighestRow();
for ($row = 1; $row <= $lastRow; $row++) {
$currentValue = $objWorksheet->getCell('A' . $row)->getValue();
$nextValue = $objWorksheet->getCell('A' . ($row + 1))->getValue();
if ($currentValue != $nextValue) {
$objWorksheet->setCellValue('A' . $row, $currentValue . '_');
}
}
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('myExcelFile.xlsx');date = sheet['A1'].value
new_date = date + datetime.timedelta(days=5)
sheet['A1'].value = new_date=ARRAYFORMULA(SUBSTITUTE(C2:C;B2:B;""))
Sub AddSharpToBoldText()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.Font.Bold = True Then
cell.Value = "# " & cell.Value
End If
Next cell
End Sub
Sub AddMinusSignToRedText()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.Font.Color = RGB(255, 0, 0) Then
cell.Value = "- " & cell.Value
End If
Next cell
End Subconst whiteListSheetNames = [`Лист1`,`Лист2`];
const whiteListRangeNames = [`H1`, `H2`];
function onEditTrigger(e) { // установить триггер на "Редактирование". Простой onEdit имеет меньше прав
let sheet = e.source.getActiveSheet();
let address = e.range.getA1Notation().toUpperCase();
let row = e.range.getRow();
let col = e.range.getColumn();
let newValue = e.value;
let oldValue = e.oldValue;
if ((whiteListSheetNames.includes(sheet.getName())) && (whiteListRangeNames.includes(address))) { // Проверка по вкусу
// сортировка
};
};function translitEu(word, charPositions=[1,3]){
//...
for (var i = 0; i < word.length; ++i ) {
if (converter[word[i]] && charPositions.includes(i)){
answer += converter[word[i]][Math.floor(Math.random()*converter[word[i]].length)];
} else {
answer += word[i];
}
}
// ...
};arr = [];
(arr.length - 1) = -1
arr[arr.length - 1] = arr[-1] = undefined
arr[arr.length - 1].length = undefined.length = Error