/**
* User action. Runs the snippet
*/
function run2() {
const sheet = SpreadsheetApp.getActiveSheet();
const base = sheet.getRange('J3:J');
const colFormula = sheet.getRange('J3');
draggDownOneFormula_(base, colFormula);
}
/**
* @param {GoogleAppsScript.Spreadsheet.Range} base
* @param {GoogleAppsScript.Spreadsheet.Range} colFormula
*/
function draggDownOneFormula_(base, colFormula) {
const baseValues = base.getValues();
const lastBase =
baseValues.length - baseValues.reverse().findIndex(row => row[0] !== '');
const colFormulaFormula = colFormula.getFormula();
colFormula
.getSheet()
.getRange(base.getRow(), colFormula.getColumn(), lastBase + 1)
.setFormula(colFormulaFormula);
}/**
* @file Dragg down a formula
* @url https://qna.habr.com/q/709715
* */
/**
* User action. Runs the snippet
*/
function run() {
const sheet = SpreadsheetApp.getActiveSheet();
const base = sheet.getRange('C3:C');
const colFormula = sheet.getRange('J3');
draggDownFormulas_(base, colFormula);
}
/**
* @param {GoogleAppsScript.Spreadsheet.Range} base
* @param {GoogleAppsScript.Spreadsheet.Range} colFormula
*/
function draggDownFormulas_(base, colFormula) {
const baseValues = base.getValues();
const lastBase =
baseValues.length - baseValues.reverse().findIndex(row => row[0] !== '');
const colFormulaFormula = colFormula.getFormula();
colFormula
.getSheet()
.getRange(base.getRow(), colFormula.getColumn(), lastBase)
.setFormula(colFormulaFormula);
}
C заполнена на одну строку ниже, чем массив данныхJ3
count в формуле QUERY
function copyTabList() {
var from = SpreadsheetApp.getActiveSheet();
var fromValues = from.getDataRange().getValues();
var fromData = fromValues.slice(1);
var tss = SpreadsheetApp.openById('Id');
var ts = tss.getSheetByName('Sheet3');
ts.getDataRange()
.offset(1, 0)
.clearContent()
.getRange(2, 1, fromData.length, fromData[0].length)
.setValues(fromData);
}function copyTabList() {
var from = SpreadsheetApp.getActiveSheet();
var fromValues = from
.getDataRange()
.offset(1, 0)
.getValues();
var fromData = fromValues;
var tss = SpreadsheetApp.openById('Id');
var ts = tss.getSheetByName('Sheet3');
ts.getDataRange()
.offset(1, 0)
.clearContent()
.getSheet()
.getRange(2, 1, fromData.length, fromData[0].length)
.setValues(fromData);
}=FILTER('Ответы на форму (1)'!A2:N120;'Ответы на форму (1)'!N2:N120="Смартфон")=QUERY(Лист2!B4:P ,"select B,C,D,E,F,G,I,N,O,P", 0)заголовки – [ НЕОБЯЗАТЕЛЬНО ] – количество заголовочных строк в верхней части раздела данных. В случае, если параметр опущен или равен -1, его значение вычисляется автоматически в зависимости от содержимого данных.
A1 https://docs.google.com/spreadsheets/d/1nleri9Tedl.../**
*
* @param {GoogleAppsScript.Spreadsheet.Sheet} sheet
*/
function protectSheet_(sheet) {
var protection = sheet.protect().setDescription('Autoprotection');
var unprotected = sheet.getRangeList(getCommonNotasList_()).getRanges();
protection.setUnprotectedRanges(unprotected);
var me = Session.getEffectiveUser();
protection.addEditor(me);
protection.removeEditors(protection.getEditors());
protection.addEditors(editors);
if (protection.canDomainEdit()) protection.setDomainEdit(false);
};
var getCommonNotasList_ = function() {
var a1Notations = [
'B2:B4',
'B6:B7',
'A14:E30',
'I3:Q26',
'S2:V21',
'T33:W264'
];
return a1Notations;
};userActionsCopyToLogs уже у себя.
=QUERY(
A:D;
"select B, D, sum(C) where B<>'' and A>=date'"&
TEXT(H1;"yyyy-mm-dd")&
"' and A<=date'"&
TEXT(J1;"yyyy-mm-dd")&
"' group by B, D label sum(C)'Количество'";
1
)
=VLOOKUP(A3;INDIRECT(INDEX(SPLIT(B1;"-");1;1) &"!A:C");2;FALSE)
-VLOOKUP(A3;INDIRECT(INDEX(SPLIT(B1;"-");1;2) &"!A:C");2;FALSE)=ARRAYFORMULA({
VLOOKUP($A$2;INDIRECT(LEFT(B1;5)&"!A:C");{2\3};0);
IFERROR(
VLOOKUP($A$3:$A$40;INDIRECT(RIGHT(B1;5)&"!A:C");{2\3};0)-
VLOOKUP($A$3:$A$40;INDIRECT(LEFT(B1;5)&"!A:C");{2\3};0);
""
)
})