={Лист1!A2:C10;Лист2!A2:C10;Лист3!A2:C10}
=FILTER({'1'!A:H;'2'!A:H;'3'!A:H;'4'!A:H};{'1'!A:A;'2'!A:A;'3'!A:A;'4'!A:A}<>"")
=ARRAYFORMULA(ROW(A:A) - 1)
={"Номер ответа";ARRAYFORMULA(ROW(A2:A) - 1)}
={"Номер ответа";ARRAYFORMULA(MATCH(A2:A;SORT(A2:A;1;1);0))}
=REGEXREPLACE(B16;"\.";",")
/**
* @OnlyCurrentDoc
*/
/**
*
* The RAINREPORT function
*
* @param {rain} rain Two-column data array
* @param {boolean} skipEmpty
* @return {any[][]}
* @customfunction
*/
function RAINREPORT(rain, skipEmpty = true) {
const _rain_ = rain.map((row) => row[0]);
const _cloud_ = rain.map((row) => row[1]);
const _report_ = {};
_rain_.forEach((item, i) => {
if (_cloud_[i] === '' && skipEmpty === true) return;
if (!Object.prototype.hasOwnProperty.call(_report_, _cloud_[i]))
_report_[_cloud_[i]] = {
data: [],
name: _cloud_[i],
};
if (_report_[_cloud_[i]].data.indexOf(item) === -1)
_report_[_cloud_[i]].data.push(item);
});
return Object.keys(_report_)
.sort()
.map((key) => [key, ..._report_[key].data.sort()]);
}
=TRANSPOSE(RAINREPORT(
QUERY(
ДАННЫЕ_ВЫДАЧИ;
"select C,E where " &
TEXTJOIN(" and ";1;IF(A1="ВСЕГО";"";"A='" & A1 & "'");"D < " & REGEXEXTRACT(B1;"\d+")))
))
=IMPORTXML(
"http://www.cbr.ru/scripts/XML_daily.asp?date_req=" & TEXT(A2;"DD/MM/YYYY");
"//ValCurs/Valute[CharCode=""USD""]/Value"
)
/**
* Get currencies by a date
*/
function run() {
const httpResponse = UrlFetchApp.fetch(
'https://www.cnb.cz/cs/financni-trhy/devizovy-trh/kurzy-devizoveho-trhu/kurzy-devizoveho-trhu/denni_kurz.txt?date=20.05.2020'
);
const data = Utilities.newBlob(httpResponse.getContent(), 'plain/text')
.getDataAsString()
.split('\n')
.map((line) => line.split('|'))
.slice(1);
console.log(data);
/*
// Paste to a sheet
sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
*/
}
spec
- массив имен листов, которые нужно экспортироватьspreadsheet
- копия Таблицы для экспорта/**
*
*/
function runSheet() {
const spec = ['COUNTIF']; // List of sheets for export
const spreadsheet = SpreadsheetApp.openById(
'1TpHUfTvA7xBi4TLnWaplGasDumauA3YyMgXjXeQ2cyo'
).copy('tmp');
spec.forEach(sheetName => {
const dr = spreadsheet.getSheetByName(sheetName).getDataRange();
dr.setValues(dr.getValues());
});
spreadsheet.getSheets().forEach(sheet => {
if (spec.indexOf(sheet.getName()) < 0) spreadsheet.deleteSheet(sheet);
});
const spreadsheetId = spreadsheet.getId();
const file = exportSpreadsheetToFile_(spreadsheetId, 'xlsx');
DriveApp.getFileById(spreadsheetId).setTrashed(true);
return file;
}
exportSpreadsheetToFile_
можно найти тут google-apps-script-snippets/standalone/export_spre... SpreadsheetApp.flush()
.flush
толку не будет. В этих формулах так же не работают set-операторы./**
* @OnlyCurrentDoc
*/
/**
*
* The TESTCACHESERVICE function
*
* @param {param} param
* @return {number}
* @customfunction
*/
function TESTCACHESERVICE(param) {
const cache = CacheService.getScriptCache();
const cached = param === 'break' ? 0 : +cache.get('cached') + 1 || 0;
cache.put('cached', cached);
return cached;
}
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Reset sheet')
.addItem('Make a copy before reset', 'userActionMakeCopyBeforeReset')
.addToUi();
}
function userActionMakeCopyBeforeReset() {
const book = SpreadsheetApp.getActive();
const sheet = book.getActiveSheet();
makeCopyBeforeReset_(sheet, book);
}
function makeCopyBeforeReset_(sheet, book) {
const copy = sheet.copyTo(book);
const rangesAddressesList = ['B5', 'B7', 'B9', 'B11'];
resetByRangesList_(sheet, rangesAddressesList);
return copy;
}
function resetByRangesList_(sheet, rangesAddressesList) {
sheet.getRangeList(rangesAddressesList).clearContent();
}
GET https://www.googleapis.com/drive/v3/files/fileId
возвращает список возможных экспортов{
"exportLinks": {
"application/rtf": "https://docs.google.com/feeds/download/documents/export/Export?id=ABC&exportFormat=rtf",
"application/vnd.oasis.opendocument.text": "https://docs.google.com/feeds/download/documents/export/Export?id=ABC&exportFormat=odt",
"text/html": "https://docs.google.com/feeds/download/documents/export/Export?id=ABC&exportFormat=html",
"application/pdf": "https://docs.google.com/feeds/download/documents/export/Export?id=ABC&exportFormat=pdf",
"application/epub+zip": "https://docs.google.com/feeds/download/documents/export/Export?id=ABC&exportFormat=epub",
"application/zip": "https://docs.google.com/feeds/download/documents/export/Export?id=ABC&exportFormat=zip",
"application/vnd.openxmlformats-officedocument.wordprocessingml.document": "https://docs.google.com/feeds/download/documents/export/Export?id=ABC&exportFormat=docx",
"text/plain": "https://docs.google.com/feeds/download/documents/export/Export?id=ABC&exportFormat=txt"
}
}
https://www.googleapis.com/auth/drive.readonly
, и вызовитеGET https://docs.google.com/feeds/download/documents/export/Export?id=ABC&exportFormat=txt
Utilities
поддерживали локали.DateTimeFormat
function myFormatDate(date) {
const y = new Intl.DateTimeFormat('ru',{
year: 'numeric'
}).format(date)
const m = new Intl.DateTimeFormat('ru',{
month: 'long'
}).format(date)
return `${m}-${y}`
}
function test(){
const date = new Date();
console.log(Utilities.formatDate(date, "GMT+3", "MMM-yyyy"));
console.log(myFormatDate(date));
}
"MMM-d"
не вернет год - вернет дату. getDisplayValues()
var drr = sheet.getRange(sheet.getLastRow(), 1, 1, 3).getDisplayValues()[0];
0.00%
, то вернет именно 0.00%
, если 1-02-20
, то 1-02-20
.ARRAYFORMULA
для адресного расчета массива.=ARRAYFORMULA('Ответы на форму (4)'!B2:B * 13)