SpreadsheetApp.openById()
book.getSheetByName()
sheet.getRange()
IMPORTRANGE
и не экспортируются. Тоже самое и в отношении Документов и Слайдов. const data = JSON.parse(response.getContentText());
function myFunction() {
const response = UrlFetchApp.fetch("https://suppliers-api.wildberries.ru/api/v2/stocks...", options);
const data = JSON.parse(response.getContentText());
console.log(data);
/**
{
"stocks":[
{
"subject":"Брюки",
"brand":"R.O.S.E.",
"name":"Брюки",
"size":"42",
"barcode":"2036686215443",
"article":"292022",
"warehouseName":"Брест",
"barcodes":[
"2036686215443"
],
"stock":0,
"warehouseId":198679,
"id":145904543,
"chrtId":145904543,
"nmId":90612284
}
],
"total":1
}
*/
console.log(data.stocks[0]);
/**
{
"subject":"Брюки",
"brand":"R.O.S.E.",
"name":"Брюки",
"size":"42",
"barcode":"2036686215443",
"article":"292022",
"warehouseName":"Брест",
"barcodes":[
"2036686215443"
],
"stock":0,
"warehouseId":198679,
"id":145904543,
"chrtId":145904543,
"nmId":90612284
}
*/
}
=INDEX(IFERROR(VLOOKUP(
ROW(A2:A25);
SPLIT(FLATTEN(IF(
REGEXMATCH(A2:A25;TRANSPOSE(B2:B25));
ROW(A2:A25) & "❤" & REGEXREPLACE(A2:A25;TRANSPOSE(B2:B25);TRANSPOSE(C2:C25));
));"❤");
2;
);A2:A25))
function onEdit(e) {
const range = e.range;
const sheet = range.getSheet();
const sheetName = sheet.getName();
const row = range.getRow();
const column = range.getColumn();
if (sheetName === 'Лист4' && column === 21 && row > 1) {
const range2 = range.offset(0, 1);
const value2 = range2.getValue();
range2.setValue(+value2 + +e.value);
range.setValue(null);
}
}
= {
FILTER('Вспомогательный лист'!A:A;'Вспомогательный лист'!A:A<>"");
SUM('Вспомогательный лист'!Z:Z;)
}
IMPORTRANGE
. $> google-chrome --app=https://docs.google.com/spreadsheets --profile-directory=default
function run2() {
const book = SpreadsheetApp.getActiveSpreadsheet();
const sheet = book.getSheetByName('Пример 2');
sheet.sort(5);
const values = sheet.getDataRange().getValues();
const buffer = {
value: values[values.length - 1][1]
};
values.forEach((_, i, arr) => {
const j = arr.length - 1 - i;
const value = arr[j][1];
if (value !== buffer.value) {
sheet.insertRowAfter(j + 1);
buffer.value = value;
}
});
}
function copyRange() {
const book = SpreadsheetApp.getActiveSpreadsheet();
const sheet = book.getSheetByName('Пример');
const fromRange = sheet.getRange('A2:C4');
const fromValues = fromRange.getValues();
const fromColors = fromRange.getBackgrounds();
const values = fromValues.filter(([_, __, value]) => value !== 'Закрыт');
const colors = fromColors.filter((_, i) => fromValues[i][2] !== 'Закрыт');
const toRange = sheet.getRange(1, 5, values.length, values[0].length);
sheet.getRange(1, 5, sheet.getLastRow(), 3).clear();
toRange.setValues(values).setBackgrounds(colors);
}
=ARRAYFORMULA({
"Пробег выезда";
IFERROR(VLOOKUP(
B4:B18;
{'1'!B4:B18\'1'!F4:F18};
2;
0
))
})
=INDEX(TRANSPOSE(SORT(
{IFNA(--REGEXEXTRACT(TRANSPOSE(D5:H5);"^\d+");TRANSPOSE(D5:H5))\TRANSPOSE(D5:H5)};
1;1));2)