Друзья, в продолжение темы спарклайнов — а мы недавно писали про то, как построить воронку с помощью них — напомним вам про прежние публикации по этой теме и видеоурок для новичков!

Прогресс-бар (выполнение плана) спарклайном

Динамический спарклайн: выбираем период, точку отсчета и цвет спарклайна и максимальной точки (цвет - на русском языке)

Спарклайн с условием

Видеоурок по спарклайнам: синтаксис и примеры

Превращаем строку с числами в спарклайн одной формулой

Спарклайны. Меняем цвет у отрицательных, максимальных и минимальных, первых и последних значений.

Вышеобещанное – достаём из XML с комментариями текст комментария и адрес ячейки, в которой он расположен, с помощью Google Apps Script

Что делать

Скопировали xml, вставили в бэктиках (`) в редактор скриптов, назвали переменную xmlString

Скопировали туда же код скрипта, который найдете ниже

Запустили и получаем перечисление в цикле каждого комментария и его адреса (смотрите картинку)
 
Один XML-файл с комментариями внутри XLSX архива соответствует одному листу Таблицы

const xmlString = ``

function parseXML() {
  var o = {};
  var document = XmlService.parse(xmlString);
  var root = document.getRootElement();
  var ns = XmlService.getNamespace('http://schemas.openxmlformats.org/spreadsheetml/2006/main');
  var comments = root.getChild('commentList', ns).getChildren('comment', ns);

  for (var i = 0; i < comments.length; i++) {
    var comment = comments[i];
    var ref = comment.getAttribute('ref').getValue();

    var textElement = comment.getChild('text', ns);
    var tElement = textElement.getChild('t', ns);
    var textContent = tElement.getText();

    console.log(ref, textContent);
  };
};

А в следующий раз мы отправим комментарии на луну :)

Извлекаем комментарии из документа / Удаляем комментарии / xlsx-файл - это архив / достаём из XML комментарии и адреса

---
⭐️ Заказ работы у нас

Статья в первую очередь для тех, кто работает в Р7-Офис. Но остальным будет полезно пробежаться и вспомнить про принципы работы формул массива в Google Таблицах / Excel и узнать, как все устроено в российском табличном редакторе :)

А там своеобразное сочетание — есть некоторые функции, появившиеся в Excel только в 2021 версии вместе с динамическими массивами и в Таблицах тоже недавно. При этом формулы массива в Р7 работают как “старые” формулы массива Excel (возможно, это когда-нибудь изменится — очень надеемся). Так что новыми функциями пользоваться не так удобно, как в Таблицах или новом Excel, но зато они в принципе есть🙂

В статье разбираем:
— Как вводятся и работают старые и новые формулы массива в Excel
— Какие функции из новых доступны в Р7
— И разбираем, как работать с новыми функциями в Р7, где принципы работы формул массива старые, несмотря на наличие новых функций :)

https://shagabutdinov.ru/r7array/

Копируем таблицу от имени владельца, скрипт

Друзья, задачка из недавней практики - есть Таблица, в ней есть владелец, как сделать копию этой Таблицы, как будто бы ее сделал владелец, но чтобы это мог сделать любой сотрудник.

Зачем это нужно? Чтобы сохранить все защиты диапазонов, которые есть в Таблице, они должны быть также от имени владельца. А также, чтобы Таблица лежала на Google Диске владельца.

--

Мы придумали скрипт для решения задачи, скрипт и поля для ввода в Таблице, делайте копию.

Как все работает:

1) Скопировали нашу Таблицу

2) Далее владелец Таблицы, которую мы будем копировать должен зайти в редактор скриптов и поставить скрипт on() на триггер по редактированию Таблицы

(триггеры > добавление нового триггера > выбираем функцию on() и выбираем "при редактировании"

2) Далее "сотрудники" заполняют лист (ссылка на Таблицу, кого добавим как редакторов в копию и новое имя Таблицы)

3) Далее кто угодно активирует чекбокс и происходит магия - триггер запускает скрипт от имени того, кто поставил триггер и в ячейках ниже вы увидите ссылку на скопированную Таблицу и дату и время копирования

И отдельно код:

function on(e) {
  const sheet = e.source.getActiveSheet();
  const range = e.range;

  if (sheet.getName() == 'настройки' && range.getA1Notation() == 'B4' && e.value == 'TRUE') {
    copy();
  };
};

function copy() {
  const ss = SpreadsheetApp.getActive();
  const settings = ss.getRange("настройки!b1:b3").getValues().flat();
  var [originalUrl, addEmails, name] = settings;
  if (!originalUrl) return;
  var newUrl = '';
  try {
    const or = SpreadsheetApp.openByUrl(originalUrl);
    newUrl = or.copy(name || 'копия, ' + or.getName());
    if (addEmails) newUrl.addEditors(addEmails.split(',').map(f => f.trim()));
    newUrl = newUrl.getUrl();
  } catch (e) {
    newUrl = e.message;
  };

  ss.getRange("настройки!b5:c5")
    .setValues([[newUrl, new Date()]]);
};

Видео для новичков-новичков: "умные" таблицы

Друзья, для тех, кому удобнее формат видео, ролик про таблицы (tables) в Google Spreadsheets:
https://www.youtube.com/watch?v=Pn_b_mpr__k

Ранее мы писали про основные опции таблиц и ссылки на данные в них тут

И еще немного про таблицы. Теперь, с их появлением, можно использовать их в качестве источника для сводной таблицы.

В чем преимущества?
— Новые строки будут автоматом попадать в сводную
— Пустые строки не будут попадать в нее и их не придется фильтровать (как если бы мы брали в качестве источника столбцы, например, A:F)
— Таблица может начинаться не с первой строки листа и над/под ней может быть что-то еще. А если мы берем столбцы в качестве источника сводной, то в них больше ничего, кроме данных для нее, быть не может.

Форматируем данные как таблицу, выделяем любую ячейку, вставляем сводную —> видим, что в качестве источника фигурирует не диапазон, а имя таблицы (де-факто именованный диапазон, меняющий свои размеры при добавлении новых данных).

Вечная схватка двух ёкодзун: Google Таблицы и Excel

Ну что, друзья, с появлением таблиц в Google Spreadsheets пришло время обновить таблицу-сравнение двух табличных редакторов, а заодно напомнить про нее и рассказать тем, кто пропустил.

Она живет по этой постоянной ссылке:
https://docs.google.com/spreadsheets/d/10jhUBPLhmtx-km5sdITumlmkgIn8DKqsdxyULChVaq8

Для тех, кто работает и там и там или переходит и хочет сориентироваться — подавляющее большинство отличий в одной таблице:
— интерфейс, автоматизация, другие нюансы
— функции и формулы
— диаграммы и спарклайны
— сводные таблицы
— что ломается и сохраняется при переносе файлов из одного редактора в другой

Как ускорить работу в Google Таблицах: 7 лайфхаков

Статья в РБК для новичков табличных дел! Делюсь 7 лайфхаками для работы в Google Spreadsheets: форматирование, горячие клавиши для вызова команд из меню, работа с ссылками, сортировка, вычисление фрагмента, пользовательские числовые форматы.

https://companies.rbc.ru/news/qnC1EmgtQx/kak-uskorit-rabotu-v-google-tablitsah-7-lajfhakov/

Почему объединение ячеек — это не очень хорошо?

Потому что за объединенной ячейкой всегда скрывается только одна заполненная (левая верхняя, первая, попросту говоря) и все остальные пустые!

А значит, формулами мы это дело не обработаем: в примере ломается ВПР / VLOOKUP, потому что название курса берется только из первой из объединенных ячеек.

Что если вы повторили все названия, но визуально хотите сохранить эффект объединения?

Как вариант — придумать формулу условного форматирования, которая будет "прятать" (применять белый цвет шрифта) все "лишние", нецентральные подписи.

Сортируем данные по фамилии (или другому слову в составе текстовой строки)

Вот такой лайфхак от маэстро табличных дел Бена Коллинса.

Если в ячейках данные формата Имя Фамилия, а нам надо сортировать по фамилии, а не по всей текстовой строке (включая имя), то можно в функцию SORT засунуть текстовые функции, которые будут извлекать фамилию:

=SORT(диапазон ;RIGHT(диапазон ;LEN(диапазон )-SEARCH(" ";диапазон ));1)

Здесь мы определяем, сколько символов в фамилии (находим с помощью SEARCH / ПОИСК или FIND / НАЙТИ разделитель — пробел — и вычитаем его позицию из общего числа символов в строке (LEN / ДЛСТР).
Затем извлекаем соответствующее число символов с конца строки с помощью функции RIGHT / ПРАВСИМВ.

А как это делается в Excel с новыми функциями (в частности, TEXTAFTER / ТЕКСТПОСЛЕ), можно посмотреть здесь:
https://t.me/lemur_excel/273

Таблица с примером

Загружаем расходы в Таблицу по рекламе ВБ, API

Друзья, привет! Мы подготовили для вас Таблицу со скриптом, с помощью нее вы сможете загружать расходы по рекламным кампаниям из API ваших любимых диких ягод.

Копируйте Таблицу, вставляйте даты на первый лист (максимальный диапазон для загрузки - 31 день), вставляйте API-токен и запускайте скрипт из меню.

Из интересного, если захотите разобраться в коде - скрипт не будет задваивать строки, если они уже были загружены на лист и вернуться из ответа API, скрипт проверяет уникальность по ключу дата и время операции + номер кампании.

Также мы расшифровываем статус кампании и тип кампании.

Описание метода API: https://openapi.wildberries.ru/promotion/api/ru/#tag/Finansy/paths/~1adv~1v1~1upd/get

Таблица с кодом

Код отдельно:

function onOpen() {
  SpreadsheetApp.getUi().createMenu("🔥")
    .addItem('загрузить расходы за диапазон дат', 'google_sheets')
    .addToUi();
};

function google_sheets() {
  try {
    url_api = 'https://advert-api.wb.ru/';
    tz = Session.getScriptTimeZone();
    name = 'Расходы';

    ss = SpreadsheetApp.getActive();
    sh = ss.getSheetByName(name);
    d = sh.getDataRange().getValues();

    [start, end, token] = ss.getRange("!!b1:b3").getValues().flat()
      .map((f, i) => i < 2 ? d2s(f) : f);

    o = [];
    url = `${url_api}adv/v1/upd?from=${start}&to=${end}`

    r = request(url, 'get', token);

    r = r
      .map(k => {
        var [advertType, updTime, paymentType, advertStatus, updSum, advertId, campName] =
          [advert_type[k.advertType] || k.advertType, new Date(k.updTime), k.paymentType, obj_status[k.advertStatus], k.updSum, k.advertId, k.campName];

        updTime = d2s(updTime, 'yyyy-MM-dd HH:mm:ss', tz);
        key = updTime + '|' + advertId;
        if (!o.includes(key)) o.push(key);

        return [advertType, updTime, paymentType, advertStatus, updSum, advertId, campName];
      });

    if (r.length) {

      d = d.slice(1).filter(g => {
        date = d2s(g[1], 'yyyy-MM-dd HH:mm:ss', tz);
        key = date + '|' + g[5];
        return !o.includes(key);
      });

      console.log('стало: ' + d.length);
      r = [...d, ...r];

      console.log('суммарно вставляем: ' + r.length);
      sh.getRange("A2:M").clearContent();
      sh.getRange(2, 1, r.length, r[0].length).setValues(r);
    };
  } catch (err) {
    Browser.msgBox(err.message);
  }
};

obj_status = {
  4: 'готова к запуску',
  7: 'завершена',
  8: 'отказался',
  9: 'активна',
  11: 'приостановлена'
};

advert_type = {
  9: 'поиск',
  8: 'автокомпания'
};

function d2s(date, format = 'yyyy-MM-dd', tz = Session.getScriptTimeZone()) {
  return Utilities.formatDate(date, tz, format);
};

function request(url, method, token, data) {
  params = {
    method: method,
    muteHttpExceptions: true,
    contentType: 'application/json;',
    payload: JSON.stringify(data),
    'headers': {
      Authorization: token
    }
  };

  r = UrlFetchApp.fetch(url, params);
  r = JSON.parse(r)
  return r;
};

PS Токен ВБ нужен "продвижение", получить можно в личном кабинете продавца ВБ (как)

Что есть еще по WB + Таблицы

1) Таблица, которая загружает из API остатки и отчет по реализации t.me/google_sheets/1143

2) WB, достаём топ-100 товаров по запросу t.me/google_sheets/1116

3) Таблица, которая выгружает данные по артикулам WB, t.me/google_sheets/1104

---
⭐️ Заказ работы у нас

Телеграм бот для техподдержки, связанный с Google Таблицами

Друзья, привет! Пока сделаем анонс того, что скоро выложим на канал. Идея простая и уже проверенная нами в ряде компаний.

Концепция
– Телеграм бот, которому ваши сотрудники (ну или клиенты) задают вопросы, то есть просто отправляют сообщения

– Все сообщения приходят в Google Таблицу

– Оператор отвечает на сообщение прямо из Таблицы и оно отправляется автору вопроса в Телеграм

– Помимо этого можно сделать рассылку среди тех, кто боту писал и его не заблокировал

Мы сейчас заканчиваем упаковывать этот скрипт, поэтому все целиком мы вам презентуем в ближайшие дни.

Горячая линия своими руками (телеграм бот + отвечаем на вопросы из Таблицы)

В видео - демонстрация работы.

Чтобы развернуть у себя - делаем копию Таблицы, открываем редактор скриптов, читаем лист "!!!ИНСТРУКЦИЯ". Всё очень просто, справится даже новичок.

Чтобы отформатировать сообщение, которое отправляем - используйте HTML style

Нужен бот посложнее – ⭐️ Заказ работы у нас

PS
Это – прекрасный пример того, как неожиданно мощны могут быть Таблицы. В этой Таблице мы развернули "сервер" с веб-приложением, который работает круглосуточно и бесплатно, ждёт сообщений от Телеграм бота и логирует их в Таблицу, далее также легко вы можете из этой Таблицы отвечать.

В последнее время в чатах и личных сообщениях с той или иной долей паники спрашивают, чем заместить доски Miro…

Которыми, как выясняется, пользуется едва ли не столько же компаний и команд, сколько и таблицами 🤠 Это такой же нужный инструмент для командной работы, в том числе, если не в первую очередь, для удаленных команд

Чем же заменить ушедший сервис? Опцию бесплатного переноса данных из Miro обещает VK Доска. Да и собственно весь сервис пока бесплатный. Число досок бесконечное, число участников тоже, так что если работаете удаленно (а раз вы пользуетесь Таблицами, то это весьма вероятно) — подойдет.

Пока это бета-версия, и кое-каких привычных вещей для совместной работы нет (комментариев, разграничения доступа) — но их обещают добавить скоро.

Будем надеяться, что это случится быстро и на этом новые инструменты не закончатся...

На Google Диске новые горячие клавиши

Старые, более короткие, увы, теперь холодны и не работают.

Например, для создания папки раньше нужно было нажать Shift + F и все.
А теперь Alt + C, а потом F.

Соответственно, для создания новых файлов теперь нужно нажать Alt + C (Control + C), а потом:
Документ — T
Таблица — S
Презентация — P
Форма — O

Переименовать файл или папку раньше можно было просто нажатием клавиши N. А теперь — F2 (или Ctrl + Alt + N).

Добавить в список помеченных (Starred) — раньше S, а теперь Ctrl + Alt + S 🤯 (Command + Option + S).

P.S. А почему поменялось? Добавилась возможность навигации по первым буквам файлов. То есть вводим букву и попадаем к файлу, имя которого начинается с нее.

Решаем проблему поиска ВПР-ом в трех диапазонах

Допустим, у нас есть три Таблицы и нам нужно найти наше значение сначала в одной, потом, если не нашли, то в следующей, не нашли в следующей - ищем в последней.

Два варианта, вариант олдскульный и классический:

Функция ищет в первом диапазоне, если возвращается NA - в следующим и так далее.

=IFNA(VLOOKUP(A6;A1:B3;2;0);IFNA( VLOOKUP(A6;D1:E3;2;0);VLOOKUP(A6;G1:H3;2;0)))

И вариант молодежный и прогрессивный:

С помощью VSTACK соединяем диапазоны в один и дальше используем один ВПР.

=VLOOKUP(A6; VSTACK(A1:B3;D1:E3;G1:H3) ; 2; 0)

Статья про VSTACK и другие модные формулы от нашего Михаила

Вот такую задачу решали на практикуме по формулам: выгрузка из реальной жизни, только упрощенная и данные вымышленные.

А проблема с ней в том, что название филиала есть только в заголовках в начале группы строк по нему.
И для нормальной обработки этого безобразия формулами нам нужен столбец, в котором в каждой строке будет название филиала.

Число строк по каждому городу разное, так что за это не зацепишься.

Какие варианты в такой ситуации возможны?

1 Смотреть на первое слово / символы в ячейке. Например, проверять, что первые 6 символов (ЛЕВСИМВ / LEFT) — это "филиал"
=ЕСЛИ(ЛЕВСИМВ(A2;6)="филиал"; A2; значение сверху)

2 А если не везде начало со слова "филиал", как в реальной жизни? Там было то "Филиал", то "Центр", то сразу город.

Тогда можно смотреть на следующую строку — если там подзаголовок другого уровня, значит, в текущей строке заголовок по городу. Это решение на скриншоте.

3 Можно смотреть на какой-то из соседних столбцов (B, C и так далее), но не конкретно в этом случае, т.к. здесь эти столбцы пустые и в строках с городами, и в строках с категориями услуг (Основные, дополнительные). Но в каких-то случаях это подойдет, можно будет проверять столбец на пустоту функцией ЕПУСТО / ISBLANK.

Показывает чекбоксы только на строках с данными / снижаем визуальный шум :)

Друзья, привет, представьте - вы выводите диапазон данных и хотите, чтобы слева или справа у вас выстроились чекбоксы по каждой строке выведенного диапазона, но не больше

Как?
1. создаём целый столбец чекбоксов (выделили столбец > вставка > флажок)

2. применяем белый цвет шрифта на этот столбец

3. и создаем правило условного форматирования формулой для этого столбца - если в соседнем столбце есть данные - крась шрифт ячейки со скрытым чекбоксом в черный (смотрите на на гифку)

ПС а еще чекбоксы называют крыжами, слышали про это?

Наш чат, про Таблицы, скрипты и крыжи: @google_spreadsheets_chat