/**
*
* @param {string} companyName
* @param {string} contactName
* @param {('Да'|'Нет')} informStatus
* @param {string} phone
* @param {string} email
* @returns
*/
function addCompany_(companyName, contactName, informStatus, phone, email) {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Создавать список и чекбокс');
// Создаем объекты для чекбокса и компании
var company = companyName;
const companyId = `${String(companyName).toUpperCase().replace(/\s+/, '_')}_${new Date()}`;
var newRow = [companyId, false, company, contactName, informStatus, phone, email];
const lr = sheet.getLastRow() + 1;
sheet.appendRow(newRow);
const validations = sheet.getRange('2:2').getDataValidations();
sheet.getRange(`${lr}:${lr}`).setDataValidations(validations);
return companyId;
}
/* exported runAddCompany */
function runAddCompany() {
addCompany_(`Comp_${new Date().getTime()}`, 'Contact', 'Да', '77777777777', 'qerwerq@afaffa.ru');
}
class Recalculator {
constructor(calcs) {
this.calcs = calcs;
}
calc(params, fix) {
if (params.filter((p) => p === '' || isNaN(p)).length > 1)
return params.map((p) => (isNaN(p) || p === '' ? '' : Number(p)));
const index = params.map((p) => (isNaN(p) || p === '' ? '' : Number(p))).findIndex((p) => p === '');
if (index === -1) return params.map((v, i) => (i === fix ? v : ''));
const res = this.calcs[index](params);
const out = [...params];
out[index] = Number.isInteger(res) ? res : Number(res).toFixed(2);
return out;
}
}const recalc = new Recalculator([([_, b, c]) => c - b, ([a, _, c]) => c - a, ([a, b, _]) => a + b]);
const range = sheet.getRange('B3:F3');
const [a, _, b, __, c] = range.getValues()[0];
const params = [a, b, c];
const [a1, b1, c1] = recalc.calc(params, fix);
range.setValues([[a1, _, b1, __, c1]]);
function triggerOnEditOrFormulaOrValue(e) {
if (e.range.getSheet().getName() !== 'Или формула или значение (скрипт)') return;
if (e.range.getA1Notation() !== 'J8') return;
if (Object.prototype.hasOwnProperty.call(e, 'value')) e.range.getSheet().getRange('H8').setValue('EXEMPT');
else e.range.getSheet().getRange('H8').setValue('');
}
