/**
* Возникает при изменении ячейки
* @param {e} event event-объект https://developers.google.com/apps-script/guides/triggers/events?hl=ru
* [ ProgrammerForever (c) 2020 ]
* @return Не возвращает значений
*/
function onEdit(event) {
var ss = event.source.getActiveSheet();//Текущий лист
var address = event.range.getA1Notation().toUpperCase();//Адрес ячейки
var row = event.range.getRow(); //Номер строки
var col = event.range.getColumn(); //Номер столбца
var newValue = event.value; //Новое значение
var oldValue = event.oldValue; //Старое значение
var userEmail = event.user.getEmail();//Пользователь(работает только при вручную назначенном триггере)
//Делаем что-то
};
/**
* Открывает 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;
};
};
};
/**
* Открывает 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;
};
};
};
const 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))) { // Проверка по вкусу
// сортировка
};
};
2 строка = A2 = R2C1
3 строка = B2 = R2C2
4 строка = C2 = R2C3
5 строка = A2 = R3C1
6 строка = B2 = R3C2
7 строка = C2 = R3C3
=ДВССЫЛ("R"&(ОКРУГЛВНИЗ((СТРОКА()-2)/3)+2)&"C"&(ОСТАТ(СТРОКА()-2;3)+1);0)
=LAMBDA(r;c;ДВССЫЛ("R"&r&"C"&c;0))
(
ОКРУГЛВНИЗ((СТРОКА()-2)/3)+2
;ОСТАТ(СТРОКА()-2;3)+1
)
=(IF($G1:$G<=ДатаНачала;1;0)+IF($G1:$G>=ДатаОкончания;1;0))>0
=(IF($G1:$G>=ДатаНачала;1;0)*IF($G1:$G<=ДатаОкончания;1;0))=1
/**
* Разворачивает данные в плоскую таблицу
*
* @author Boew Grigory (ff.nspu@gmail.com)
* @param {Array} data Исходные данные
* @param {Boolean} [isRemoveNull=true] Удалять ли из выдачи пустые, нулевые и ложные значения
* @customfunction
*/
function unPivot(data, isRemoveNull=true) {
let colHeaders = [...(data[0])].slice(1);
let rowHeaders = data.slice(1).map(row=>row[0]);
let outData = [];
rowHeaders.forEach((rowHeader,ri)=>{
colHeaders.forEach((colHeader,ci)=>{
let value = data[1+ri][1+ci];
if(!isRemoveNull || value){
outData.push([rowHeader, colHeader, value]);
};
});
});
if(!outData.length){
return [[]];
};
return outData;
};
Из этих трех таблиц, собирается одна, ... и вручную проставляются фактические даты и тип счета.