/**
* Открывает 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;
};
};
};
(Колонка не 10) <b>ИЛИ</b> (значение не "выполнено")
при этом
(колонка не 13)<b> ИЛИ </b>(значение не "312")
// Файл config.gs
const config = {};
config.onEditTrigger = {
isAllowSheetName: sheetName=>(["1", "2", "3"].includes(sheetName)), // Лист - "1", "2" или "3"
isAllowColumn: column=>([11,12,13].includes(column)), // колонка - 11,12 или 13
isAllowRow: row=>(row>1), // строка - больше 1
isAllowNewValue: value=>(value==="OK"), // новое значение - "ОК"
isAllowOldValue: value=>true, // старое значение - любое
};
// Файл onEdit.gs
function onEditTrigger(event) { // переименовать onEditTrigger в onEdit, если хватит simple триггера. Если нужен полноценный триггер - установить триггер на редактирование на эу функцию
let conf = config.onEditTrigger;
let ss = SpreadsheetApp.getActiveSpreadsheet();
let sourceSheet = event.source.getActiveSheet();
let row = event.range.getRow();
let col = event.range.getColumn();
let newValue = event.value;
let oldValue = event.oldValue;
if(
conf.isAllowColumn(col) &&
conf.isAllowRow(row) &&
conf.isAllowNewValue(newValue) &&
conf.isAllowOldValue(oldValue) &&
conf.isAllowSheetName(sourceSheet.getName())
){
// что-то делаем
};
};
function Create () {
var img = DriveApp.getFileById("id");
var roman = DriveApp.getFolderById("id");
var folder = roman.createFolder("newfolder")
img.makeCopy("nameimg", folder);
img = DriveApp.getFileById("id2");
img.makeCopy("nameimg2", folder);
}
function Create (rootFolderId, newFolderName, images) {
let rootFolder = DriveApp.getFolderById(rootFolderId);
let folder = rootFolder.createFolder(newFolderName);
images.forEach(image=>{
let imageFile = DriveApp.getFileById(image.id);
img.makeCopy(image.name, folder);
});
};
// usage
let images = [
{id: "id1", name: "image1"},
{id: "id2", name: "image2"},
{id: "id3", name: "image3"},
];
Create("root folder id", "new Folder", images);
function onEdit(event) {
//Перенос в архив
//Возникает при изменении ячейки
let sheet = event.source.getActiveSheet();//Текущий лист
let address = event.range.getA1Notation().toUpperCase();//Адрес ячейки
let row = event.range.getRow(); //Номер строки
let col = event.range.getColumn(); //Номер столбца
let newValue = event.value; //Новое значение
let oldValue = event.oldValue; //Старое значение
if (["Лист1","Лист2"].indexOf(sheet.getName())==-1) return; //Указываем на каких листах должен работать скрипт
if ([1].indexOf(col)==-1) return; //Указываем в каких столбцах должен работать скрипт
//Можно при желании ещё фильтровать по строке/столбцу (row/col), или по старому/новому значению (oldValue/newValue)
sheet.getRange(row, col+1).setValue(new Date());
function sortSheets() {
const rangesInfo = [
{sheet: "2022", range: "A2:M", column:3, ascending: true},
{sheet: "Выполнено_2022", range: "A2:M", column:3, ascending: true},
];
let ss = SpreadsheetApp.getActiveSpreadsheet();
rangesInfo.forEach(rangeInfo=>{
try{
let range = ss.getSheetByName(rangeInfo.sheet).getRange(rangeInfo.range);
range.sort({column: rangeInfo.column, ascending: rangeInfo.ascending});
}catch(err){
Logger.log(`error: ${err}`);
};
});
}
function getCDEK(pointA, pointB, userId) {
var token = logInCDEK().access_token;
var url = "https://api.edu.cdek.ru/v2/calculator/tarifflist";
let payload = {
"type": 1,
"currency": 1,
"lang": "rus",
"from_location": {
"code": 270
},
"to_location": {
"code": 44
},
"packages": [
{
"height": 10,
"length": 10,
"weight": 4000,
"width": 10
}
]
};
var options = {
method: "POST",
muteHttpExceptions: true,
headers: {
"Content-type": "application/json",
"Accept": "application/json",
"Authorization": "Bearer " + token,
},
payload : payload, // или JSON.strigify(payload), проверяйте
}
var response = UrlFetchApp.fetch(url, options);
Logger.log(response)
return response;
}
function logInCDEK() {
let url = `https://api.edu.cdek.ru/v2/oauth/token`;
let options = {
method: "POST",
muteHttpExceptions: true,
headers: {
"Content-type": "application/json",
"Accept": "application/json"
},
};
let queryParams = {
grant_type : "client_credentials",
client_id : "EMscd6r9JnFiQ3bLoyjJY6eM78JrJceI",
client_secret : "PjLZkKBHEiLK3YsjtNrt3TGNG0ahs3kG",
};
url+=Object.keys(queryParams).length?`?${getQueryString(queryParams)}`:"";
let response = UrlFetchApp.fetch(url, options);
Logger.log(response.getContentText())
}
/**
* Преобразует object в query-строку для подстановки в url
*
* @author Boew Grigory (ff.nspu@gmail.com)
* @param {Object} data Объект для преобразования
* @return Возвращает query строку
*/
function getQueryString(payload) {
let payloadData = Object.entries(payload);
return encodeURI(
payloadData
.filter(o=>o[1]!==undefined)
.map(v=>`${v[0]}=${v[1]}`)
.join("&")
);
};
fetch("https://api.tinkoff.ru/v1/currency_rates?from=USD&to=RUB", {
"headers": {
"accept": "*/*",
"accept-language": "ru-RU,ru;q=0.9,en-US;q=0.8,en;q=0.7",
"content-type": "application/x-www-form-urlencoded",
"sec-ch-ua": "\" Not A;Brand\";v=\"99\", \"Chromium\";v=\"99\", \"Google Chrome\";v=\"99\"",
"sec-ch-ua-mobile": "?0",
"sec-ch-ua-platform": "\"Windows\"",
"sec-fetch-dest": "empty",
"sec-fetch-mode": "cors",
"sec-fetch-site": "same-site"
},
"referrer": "https://www.tinkoff.ru/",
"referrerPolicy": "strict-origin-when-cross-origin",
"body": null,
"method": "GET",
"mode": "cors",
"credentials": "omit"
});
{"trackingId":"WEE285K7UR","resultCode":"OK","payload":{"lastUpdate":{"milliseconds":1646839634102},"rates":[{"category":"C2CTransfers","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":111.25,"sell":138.75},{"category":"OPSRateGroup","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"DebitCardsTransfers","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"SMETransferFrom10To100G13CO","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"CUTransfersPremium","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"SMETransferBelow10G11","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"DepositPayments","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":111.25,"sell":138.75},{"category":"SMETransferAbove100","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"SMETransferBelow10","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"CreditCardsOperations","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":111.25,"sell":138.75},{"category":"CUTransferAbove100","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"PrepaidCardsOperations","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":111.25,"sell":138.75},{"category":"SMETransferAbove100G13","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"SMETransferFrom10To100G13","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"SMETransferBelow10G12","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"SMETransferFrom10To100G11","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"SMETransferBelow10G13CO","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"CUTransferFrom10To100","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"SMETransferAbove100G12","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"SMETransferAbove100G13CO","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"SMETransferAbove100G11","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"PrepaidCardsTransfers","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"DebitCardsOperations","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":111.25,"sell":138.75},{"category":"SavingAccountTransfers","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"SMETransferFrom10To100G12","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"CUTransfersPro","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"CreditCardsTransfers","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"SMETransferBelow10G13","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95},{"category":"SMETransferFrom10To100","fromCurrency":{"code":840,"name":"USD","strCode":"840"},"toCurrency":{"code":643,"name":"RUB","strCode":"643"},"buy":113.05,"sell":140.95}]}}