/**
* Возникает при изменении ячейки
* @param {e} event event-объект https://developers.google.com/apps-script/guides/triggers/events?hl=ru
* [ ProgrammerForever (c) 2020 ]
* @return Не возвращает значений
*/
function onEdit(event) {
var as = 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();//Пользователь(работает только при вручную назначенном триггере)
let archiveSheetName = "sheet";
let ss = SpreadsheetApp.getActiveSpreadsheet();
let archiveSheet = ss.getSheetByName(archiveSheetName) || ss.insertSheet(archiveSheetName);
archiveSheet.appendRow([new Date(), userEmail, as.getName(), address, oldValue, newValue]);
};
let range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName().getDataRange();
let values = range.getValues();
const formatDate = (date) => Utilities.formatDate(date, "GMT+3", "dd.MM.yyyy");
let now = formatDate(new Date());
let newValues = values.map(row=>{
let [date, ...rest] = row;
let formattedDate = formatDate(date);
return now===formattedDate?[now, ...rest]:row;
});
range.setValues(newValues);
range.setValues(newData); // Тут затираются формулы
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var range = sheet.getRange("A1:C7");
// Sorts by the values in the first column (A)
range.sort(1);
// Sorts by the values in the second column (B)
range.sort(2);
// Sorts descending by column B
range.sort({column: 2, ascending: false});
// Sorts descending by column B, then ascending by column A
// Note the use of an array
range.sort([{column: 2, ascending: false}, {column: 1, ascending: true}]);
let options = {
method: "POST",
headers: {
'Client-Id':'000',
'API-key':'111',
'accept': 'application/json',
},
payload: {
"filter": {
"date": {
"from": "2023-10-01T00:00:00.000Z",
"to": "2023-10-31T00:00:00.000Z",
},
"operation_type": [],
"posting_number": "",
"transaction_type": "all",
},
"page": 1,
"page_size": 1000,
},
};
function getListPrev(rangeName) {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();
const index = sheet.getIndex();
const sheet_prev = (ss.getSheets()[index - 2] || null);
if (!sheet_prev)
throw new Error('Нет предыдущего листа!');
return sheet_prev
.getRange(rangeName)
.getValues();
let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Лист1");
let range = sheet.getRange("b2:b");
range.setValues(range.getValues());