Google Таблицы

Обводим 2

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

Мы немного изменили код, теперь вам нужно задать список открытых диапазонов, с которыми скрипт должен поработать (смотрите на подчеркнутую на скриншоте строку в коде).

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

Мы не стали ограничиваться рамками и для примера применяем к ячейкам фоновую заливку и покрасили все в розовый. По аналогии можно применять и другое форматирование, конечно, методы смотрите в документации.

Таблица со скриптом

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

function onOpen() {
  const ss = SpreadsheetApp.getActive();
  const ranges = ["Лист!a3:d", "Лист!h2:i", "Лист!l2:n"]

  ranges.forEach(range => {
    const data = ss.getRange(range).getValues();
    var lr = 0;
    data.forEach((row, i) => {
      if (row.some(f => f)) {
        lr = i;
      };
    });

    const real_lr = range.match(/\!\D(\d+)/)?.[1] * 1 + lr
    ss.getRange(range + real_lr)
    .setBorder(true, true, true, true, true, true, '#000000', SpreadsheetApp.BorderStyle.DOTTED)
    .setBackground('pink');
  })
};

---
⭐️ Заказ работы у нас (по ссылке - примеры)

Достаём характеристики и описание товаров из ВБ, Таблица со скриптом

Друзья, привет! Делимся с вами Таблицей, пользуйтесь, пока работает.

Просто копируйте Таблицу к себе, вставляйте номенклатуры в первый столбец и запускайте скрипт из меню с "🐱". Таблица загружает всё, что на скриншоте.

В скрипте две части:

1) сначала формируем исходя из номера товара (того самого номера, который вы видите в ссылке на товар в ВБ) ссылку на JSON (ссылка, например)

2) загружаем этот JSON по каждому товару и достаём из него составные части (название категории, сезон, опции), результат вставляем на лист Таблицы

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

Любимый ВБ, формируем ссылки на изображения товара

---
⭐️ Мы создаём разные полезные решения для ОЗОН и ВБ, пишите в заказ работы

Перевод строки: как разделить по нему текст или очистить текст от него в Google Таблицах и Excel

Сегодня говорим про СИМВОЛ(10), он же Alt+Enter, он же перевод строки:
- Как разбить текст из одной ячейки, в которой много строк, на отдельные столбцы
- Как удалить переходы на новую строку из всех ячеек диапазона

https://teletype.in/@renat_shagabutdinov/arny9pYwYld

Обводим данные при открытии Таблицы

Друзья, сегодня у нас очень простой скрипт, идея скрипта возникла у нас в чате (Вероника, спасибо ❤️)

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

function onOpen() {
  const sh = SpreadsheetApp.getActive().getSheetByName('Лист');
  const dr = sh.getDataRange();
  dr.setBorder(true, true, true, true, true, true, '#000000', SpreadsheetApp.BorderStyle.DOTTED);
};

2) Дополнение, по просьбам трудящихся, делаем границы на всех листах Таблицы при открытии:

function onOpen() {
  const sheets = SpreadsheetApp.getActive().getSheets();

  sheets.forEach(sh => {
    const dr = sh.getDataRange();
    dr.setBorder(true, true, true, true, true, true,
      '#000000',
      SpreadsheetApp.BorderStyle.DOTTED);
  })
};

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

Вычисляем первый и последний рабочие дни месяца

Первый день вычислим так: найдем последнюю дату предыдущего месяца (КОНМЕСЯЦА / EOMONTH с аргументом -1, то есть на 1 месяц раньше от заданной даты) и прибавим один рабочий день с помощью РАБДЕНЬ / WORKDAY:
=WORKDAY(EOMONTH(дата;-1);1)

Ну а последний рабочий день месяца — это первый день следующего месяца (= конец текущего плюс один день), из которого мы вычитаем один рабочий день:
=WORKDAY(EOMONTH(дата;0)+1;-1)

Удаляем лишние пробелы

Для этого можно использовать функцию TRIM / СЖПРОБЕЛЫ — она удаляет пробелы в начале строки (все до первого слова), в конце и оставляет только по одному пробелу между словами.

А если нужно удалять переносы строк, воспользуйтесь CLEAN / ПЕЧСИМВ — эта функция удаляет непечатаемые символы (первый 31 символ ASCII, в том числе и перенос строки, с которым мы можем столкнуться в Таблицах).

Обе функции есть и в Excel.

А в Таблицах еще есть инструмент для удаления пробелов без формул, если вам нужно сделать это разово (без пересчета).

Данные — Очистка данных — Удалить пробелы
Data — Data cleanup — Trim whitespace

Или клавиши: (Alt+D) + U + I

Получаем из длинной строки Таблицу (часть 2)

Ребята, усложняем наш пример, в реальности в наших данных под каждой датой может быть несколько платежей (смотрите, обвёл эти случаи на скриншоте).

Что делать в этом случае, как получить из этого правильную Таблицу? Использовать либо продвинутую формулу (в комментарии отправлю пример такой формулы от прекрасной Елизаветы), либо написать простую пользовательскую функцию на GAS, пример такой функции с комментариями по каждой части ниже.

function myFunction(arg) {
//делим на строки по " ₽" и переносу строки
  var first = arg.split(" ₽\n");

//каждую часть еще раз делим по переносу строки и проверяем, сколько получилось элементов, если четыре – всё хорошо, если три - даты нет, вместо даты подставляет пустую ячейку
  var second = first.map(row => {
    row = row.split("\n");
    if (row[3]) {
      return row;
    } else {
      return [''].concat(row);
    };
  });

//пробегаемся в цикле по массиву, по каждой строке и если дата нет, то берем ее из строки выше
  var third = second.map((row, i) => {
    if (!row[0]) {
      row[0] = second[i - 1][0];
    }
    return row;
  })

//возвращаем результат
  return third;
};

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

PS В следующем посте покажем вам, как парсить страницу с товаром так, чтобы получать и категории и описание товара

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

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

Друзья, к нам приходит выписка вот в таком формате:

26 Марта
Пополнение
#173/Лицевой счет
20000.00 ₽
26 Марта
Пополнение
#1730/Лицевой счет
50000.00 ₽
25 Марта
Пополнение
#1726/Лицевой счет
25000.00 ₽

Как бы нам из этого получить нормальную Таблицу, со строками и столбцами?

Поехали получать:

1) Сначала разделим наш длинный текст по переносу строки (символ char(10)), с этим поможет формула
=SPLIT(A1;char(10))

2) Получаем строки, в каждой строке у нас четыре элемента (дата, суть платежа, наименование и сумма) , чтобы каждую строку разложить на эти элементы, используем функцию WRAPROWS (подробнее про функции работы с массивами), а в аргументах будет полученный массив строк и на сколько частей каждую строку нужно разделить:
=WRAPROWS(SPLIT(A1;char(10));4)

Ура, все вышло

Рассчитываемое поле не работает в сводной таблице?

Проверьте формат заголовков.
Если вы применили формат "Расчеты" (Accounting) не только к числам, но до кучи в заголовках, то в ячейках появится отступ. Выглядит как пробел, но вы не найдете его в значении — ни в строке формул, ни текстовыми функциями ДЛСТР / LEN или ЛЕВСИМВ / RIGHT, он себя никак не проявит.

Только вот в рассчитываемом поле ссылка на эти столбцы будет с ошибкой, пока вы не добавите пробел в название столбцов.
То есть в рассчитываемом поле придется ссылаться на столбец как на ' Выручка' (напоминаем, что в целом все названия полей берутся в апострофы в рассчитываемых полях).

Или, что будет правильнее, не поменяете формат в ячейках с заголовками на "Обычный текст" (Plain text) или "Автоматический" (Automatic).

Как сделать бекап Google Диска

1. Самый простой и правильный способ забекапить данные
https://takeout.google.com/

2. А также – нажатие на кнопку Download / Скачать в контекстном меню по щелчку на папке Google Диска создаёт ZIP-архив с файлами (при этом файлы редакторов Google будут преобразованы в форматы Microsoft Office, например, Google Таблицы в XLSX) и скачивает его на ваш компьютер.

3. Вот здесь мы писали о скрипте, с помощью которого можно сохранить лист Таблицы в выбранном формате (PDF / CSV / XLSX).

4. А еще вот здесь есть телеграм бот (точнее рассказ, как его быстро сделать в домашних условиях), который регулярно и ежедневно отправляет ваши выбранные Таблицы в телеграм чаты в формате Excel

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

Пользовательская сортировка

Вам нужно сортировать данные в своем порядке — не по алфавиту.
Например, какой-то регион в вашем бизнесе важнее других, хотя название города не первое по алфавиту.
Или речь про размеры одежды S, M, L...

Как быть? Можно создать дополнительный столбец с нумерацией.
Заполнять его будем формулой, в общем виде так:

=XMATCH (ссылка на ячейку со значением; {массив всех вариантов в нужном порядке})

В случае с одеждой так:

=XMATCH(B2;{"S";"M";"L";"XL"})

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

Не хочется дополнительный столбец? Можно сразу сделать значения вида "1 S", "2 M" или "1-S" и так далее (сначала номер для сортировки и потом само название элемента). Но такой трюк сработает только при числе элементов до 10. Потому что если их больше, то данные будут сортироваться так: 1-текст, 10-текст, 11... 19, 2, 20, 21...

📌 А в следующем посте покажем, как добавить этот столбец как виртуальный в функцию SORT и отсортировать данные

Криптовалюта, достаём курсы

Есть сайт: https://cryptoprices.cc/, из него мы можем достать курс криптовалюты (биткоина, скажем), собрав ссылку вида:

"https://cryptoprices.cc/" + "btc"

Чтобы вытянуть цифры - поместим эту ссылку в функцию importdata.

Если нужно поменять разделитель числа с точки на запятую (а загружаться всё будет с точкой) - добавляем функцию substitute / подставить.

Итоговая формула, которая у нас получилаcь:
=--SUBSTITUTE(IMPORTDATA("https://cryptoprices.cc/btc");".";",")

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

📌 Поделитесь в комментариях ресурсами, из которых вы достаёте разное в свои Таблицы

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

Регулярный бэкап Таблиц в формате XLSX в телеграм!

Таблица со скриптом (делайте копию) работает так:

1) вставляете ссылки на Таблицы, бэкап которых вы хотите сделать, в столбец A:A

2) нужно заполнить настройки: открываете редактор скриптов (расширения > apps script) и заполняете:
— chatId – чат телеграма, в который будут отправляться бэкапы Таблицы, чат может быть как личным, так и общественным, чтобы узнать chatId - используйте @myidbot в телеграм
— botToken - токен телеграм бота, чтобы получить его, зарегистрируйте бота через @botfather
— hours - часы отправки через запятую

3) Как заполните всё – запустите из меню "🐞" скрипт "создать триггер на каждые 15 минут"

Все! После этого скрипт будет каждые 15 минут запускаться и проверять, есть ли Таблицы, бекап которых нужно сделать, если есть - скрипт будет конвертировать эти Таблицы в XLSX и отправлять файлы в выбранный в настройках чат.

Таблица и скрипт

Условное форматирование. Выделяем дубликаты и не только.

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

В формулах используется функция СЧЁТЕСЛИ / COUNTIF — мы считаем, сколько раз конкретное значение (ссылка относительная без $ — мы проверяем формулу для каждого очередного значения) встречается в диапазоне (диапазон закреплен, он не меняется для всех проверяемых ячеек).
Формула в условном форматировании вводится один раз — при ее формировании учитывайте, что вы вводите формулу для левой верхней ячейки диапазона, а далее на все остальные ячейки она будет «протягиваться» (как если бы мы ввели формулу в одну ячейку и далее скопировали и вставили во все остальные ячейки в трех столбцах). И ссылки на ячейки будут меняться, если они являются относительными.

Для более чем 2 столбцов используйте COUNTIFS.

Для создания правила условного форматирования:
1) Выделите диапазон
2) Формат — условное форматирование (Format — Conditional formatting) или Alt+O + F
3) "Ваша формула" (Custom formula is) — введите соответствующую формулу.

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

Что еще у нас есть про телеграм?

🍏 выгружатор, достаем скриптом количество подписчиков чатов и каналов в телеграм t.me/google_sheets/855

🍏 выгружатор постов / просмотров / картинок из каналов Telegram t.me/google_sheets/841

🍏 отправлятор / удалятор постов и сообщений в телеграм t.me/google_sheets/937

🍏 отправка сообщения в телеграм чат при любом редактировании столбца t.me/google_sheets/950

🍏 отправлятор 2, отправляем по расписанию диапазоны из Таблицы в Телеграм t.me/google_sheets/643

🍏 регулярный бекап Таблиц в XLSX в Телеграм t.me/google_sheets/884

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

Друзья, сегодня у нас для вас простой скрипт для обучения Google Apps Script в Таблицах.

Скрипт работает так – встаём на ячейку и нажимаем на кнопку либо с плюсом, либо с минусом, значение в ячейке либо увеличивается на один, либо уменьшается на один. Если в ячейке будет текст, то скрипт выведет ошибку.

Сам скрипт, снабженный комментариями:

function main(n) {
  //обращаемся к активной Таблице
  const ss = SpreadsheetApp.getActive();

  //обращаемся к диапазону
  const range = ss.getActiveRange();

  //берём значение диапазона
  const value = range.getValue();

  //проверяем, должна быть или пустая ячейка или у значения должен быть тип число
  if (typeof value == 'number' || !value) {

    //вставляем в ячейку значение и аргумент, который мы передали в функцию
    range.setValue(value + n);
  } else {

    //если в ячейке не число - выводим ошибку
    ss.toast('Кажется, что в ячейке не число :(');
  }
};

//ну и две функции, которые мы повесили на кнопки + и -, в каждой вызываем главную функцию, в которую передаем либо +1 либо -1
function minus() {
  main(-1);
};

function plus() {
  main(1);
};

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

Заказать работу (если требуется что-то посложнее)

Извлекаем все числа из текстовой строки в отдельные ячейки

И еще пример с применением подвыражений в регулярках — от Бена Коллинса.

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

=SPLIT(REGEXREPLACE(текст;"(\d+)|.";"🔥$1");"🔥")

Друзья, приглашаю вас на практикум по Google Таблицам, который пройдет в марте.
Идея практикума очень простая: мы разберем прожиточный максимум для всех, кто начинает работать в Таблицах, продолжает или переходит из Excel.
Это три больших темы: интерфейс+форматирование (включая пользовательские форматы и условное форматирование), сводные таблицы, функции и формулы.
То есть, что нужно в большинстве случаев для оформления таблиц, обработки и анализа данных.

Маэстрам регулярок и магистрам формул, которые жонглируют QUERY и VSTACK'ами каждый день, конечно, на практикуме будет нечего делать, а остальным будет полезно закрыть пробелы и освоить ключевые инструменты.

Это три вебинара минимум по 2 часа с возможностью после основной части задавать вопросы до победного, нескучные домашки с конкурсом (лучшие ДЗ = книги и курсы от МИФа), слайды по всем темам, файлы со всеми примерами в исходном и готовом виде. А еще чат на время всего практикума, где можно спрашивать про домашки, темы курса и про таблицы вообще. Функции везде на двух языках.

Для компаний будут особые условия, если нужно обучить команду — пишите (mifcourses@m-i-f.ru). Вот, например, что мне прислали после недавнего корпоративного обучения — руководитель одной из обучавшихся команд:

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

Записываться тут, там же детали и программа:
https://www.mann-ivanov-ferber.ru/courses/gdrive_practicum/

Скидка 35% образуется путем ввода вот этих буков: PREXC. После 7-го марта магия букв исчезнет.

Ctrl+Backspace — возвращение к активной ячейке

Хорошее сочетание, но это, увы, тот случай, когда Excel все-таки выигрывает в схватке двух йокодзун.
Потому что в Google Таблицах это сочетание работает, когда ячейка просто выделена, а в Excel и при вводе формулы, и в диалоговых окнах (например, условное форматирование или расширенный фильтр).

А при вводе формулы в Google Таблицах это сочетание будет работать как в текстовых редакторах (и в Презентациях, и в браузере, и в других приложениях) — удалять последнее слово. Чтобы удалить слово, следующее после курсора, нажмите Ctrl+Delete.