=SUMIF(T2:T13;"="&DATE(2020;1;25);S2:S13)
=ARRAYFORMULA(SUMIF(DATEVALUE(T2:T13);"="&DATE(2020;1;25);S2:S13))
=SUMIFS(S2:S13;T2:T13;">="&DATE(2020;1;25);T2:T13;"<"&DATE(2020;1;26))=ARRAYFORMULA({UNIQUE(A3:A), TRIM(
REGEXREPLACE(
TRANSPOSE(QUERY(IF(A3:A=TRANSPOSE(UNIQUE(A3:A)),B3:B&", ",""),,TRANSPOSE(ROWS(A3:A)))),
"(^\s+|\s*,\s*$)",
""
)
)})
/**
*
*/
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Custom menu')
.addItem('Copy to logs', 'userActionsCopyToLogs')
.addToUi();
}
/**
*
*/
function userActionsCopyToLogs() {
var headers = ['Name', 'Number'];
var from = SpreadsheetApp.getActiveSheet();
if (from.getName() !== 'Sheet1') {
SpreadsheetApp.getActive().toast('Activate a range on "Sheet1"');
return;
}
var fromValues = from.getDataRange().getValues();
var fromHeaders = fromValues[0].map(function(h) {
return headers.indexOf(h);
});
var activeRange = SpreadsheetApp.getActiveRange();
var rowStart = activeRange.getRow();
var rowEnd = activeRange.getLastRow();
var fromData = fromValues
.filter(function(row, i) {
return i >= rowStart - 1 && i <= rowEnd - 1;
})
.map(function(row) {
return row.filter(function(_, j) {
return fromHeaders[j] > -1;
});
});
var to =
SpreadsheetApp.getActive().getSheetByName('Logs') ||
SpreadsheetApp.getActive().insertSheet('Logs');
to.getRange(to.getLastRow() + 1, 1, fromData.length, fromData[0].length)
.setValues(fromData)
.activate();
}
var headers = ['Name', 'Number']; так, чтобы копировались данные из нужных вам колонок.
| Navigation | |
|---|---|
| Move focus out of editing area | Ctrl+Alt+Shift+M |
| Move to beginning of sheet | Ctrl+Home |
| Move to end of row | End |
| Move to end of sheet | Ctrl+End |
| Move to next comment | Ctrl+Alt+N Ctrl+Alt+C |
| Move to next edit | Ctrl+Alt+K or Ctrl+Alt+N Ctrl+Alt+R |
| Move to next sheet | Ctrl+Shift+Pg-Down or Alt+↓ |
| Move to previous comment | Ctrl+Alt+P Ctrl+Alt+C |
| Move to previous edit | Ctrl+Alt+J or Ctrl+Alt+P Ctrl+Alt+R |
| Move to previous sheet | Ctrl+Shift+Pg-Up or Alt+↑ |
| Move to start of row | Home |
{ }=QUERY({DB!A13:L1155};"
SELECT Col1, Col2, Col3
WHERE
( Col1 >= date'2015-10-22'
AND Col1 <= date'2015-10-25')
AND ( Col2 = 'Campaign_1'
OR Col2 = 'Campaign_2')
ORDER BY Col7 DESC")потом осуществлять последующую обработку
{ } или с помощью скриптов.
QUERY не поддерживает ее. Поэтому необходимо строить массив уже готовых значений и работать с ним. Например, добавить "шт." в текущий массив=QUERY(
{'ТЕСТ'!A7:P\ARRAYFORMULA('ТЕСТ'!F7:F & " шт.")};
"Select Col3,Col1,Col2,Col17 Where Col1 like '%' or Col3 like '%'";
)format=QUERY('ТЕСТ'!A7:P;"Select C,A,B,F Where A like '%' or C like '%' format F'#.00 шт.'")
function onEdit() {
run2();
}
function run2() {
/* Remove dash */
var sheet = SpreadsheetApp.getActiveSheet();
if (sheet.getName() === 'Журнал вода данных') return;
var archive = SpreadsheetApp.getActive().getSheetByName('Журнал вода данных');
var action = function(values, i, i2) {
var data = values.slice(i, i + i2);
archive
.getRange(archive.getLastRow() + 1, 1, data.length, data[0].length)
.setValues(data);
};
var condition = function(values, i) {
var row = values[i];
return (
i > 0 && row[0] !== '' && row[1] !== '' && row[3] !== '' && row[5] !== ''
);
};
deleteRowsByConditional_(sheet, condition, action);
}
function deleteRowsByConditional_(sheet, condition, action) {
sheet
.getDataRange()
.getValues()
.forEach(
function(_, i, arr) {
var j = arr.length - i - 1;
if (this.condition.apply(null, [arr, j])) {
this.isContinue++;
if (j > 0) return;
}
if (this.isContinue > 0) {
var prevPos = j + 1; // It's reversed
if (action) action(arr, prevPos, this.isContinue);
this.sheet.deleteRows(prevPos + 1, this.isContinue);
this.isContinue = 0;
return;
}
return;
},
{ sheet: sheet, condition: condition, isContinue: 0 }
);
}run2() к меню.
doPost(e) передается запрос от внешнего источника только тогда, когда ваш сервис опубликован и отправлен запрос POST.doPost из редактора кода.
MailApp.sendEmail({
...
body: 'https://drive...',
...
});var file = DriveApp.getFilesByName('test123.pdf');
if (file.hasNext()) {
MailApp.sendEmail(emailAddress, subject, message, {
attachments: [file.next().getAs(MimeType.PDF)],
name: 'Automatic Emailer Script'
}function sendEmail() {
var sheet = SpreadsheetApp.getActiveSheet();
var activeRange = sheet.getActiveRange();
var data = sheet
.getRange(
activeRange.getRow(),
1,
activeRange.getLastRow() - activeRange.getRow() + 1,
6
)
.getValues();
data.forEach(function(row) {
var file = DriveApp.getFilesByName(row[5]);
if (file.hasNext()) {
var namesender = row[0];
var emailAddress = row[1];
var hiddencopy = row[2];
var subject = row[3];
var message = row[5];
var information = row[4];
var emailQuotaRemaining = MailApp.getRemainingDailyQuota();
Logger.log('Remaining email quota: ' + emailQuotaRemaining);
MailApp.sendEmail({
to: emailAddress,
bcc: hiddencopy,
replyTo: 'supportmp@test.ru',
name: namesender,
subject: subject + ' ' + information,
htmlBody: message + ' ' + https,
attachments: [file.next().getAs(MimeType.PDF)],
});
} else {
Logger.log('Файл для отправки не найден');
}
});
}anchor полученный объект "anchor": "{\"type\":\"workbook-range\",\"uid\":0,\"range\":\"2121905705\"}"range в A1-нотацию, но можно и погуглить
“”«»
=FILTER(A2:A15;REGEXMATCH(A8:A27;".*БП.*"))=FILTER(A2:A15;REGEXMATCH(A8:A27;"БП"))