Достаём курсы криптовалют, Таблица, описание API

Друзья, есть открытое API, которое возвращает курсы криптовалюты (5000 монет) по ссылке cryptorates.ai/files/standard.csv (при клике на ссылку у вас скачается csv-файл).

Загрузить данные по всем монетам в Таблицу можно с помощью функции:

=IMPORTDATA(A1;;"en_US")

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

А так с помощью QUERY можно на лету отфильтровать массив и оставить в нём только нужные нам монеты:

=QUERY(IMPORTDATA(A1;;"en_US");"where Col1 matches 'BTC|ETH|TRUMP'";1)

Записаться на наш интенсив / заказать у нас работу

PS Как мы помним, запрос QUERY - текстовая строка, поэтому строку с нужными для вывода монетами легко можно собрать из листа с помощью функции

="where Col1 matches '"&TEXTJOIN("|";1;I4:I30)&"'"

Функция ТИП.ОШИБКИ / ERROR.TYPE

Даем ей ячейку, получаем порядковый номер ошибки (число). Смотрим в справку:
1 для ошибки #NULL!
2 для ошибки #DIV/0!
3 для ошибки #VALUE!
4 для ошибки #REF!
5 для ошибки #NAME?
6 для ошибки #NUM!
7 для ошибки #N/A
8 для всех других ошибок.

То есть можно отлавливать определенные ошибки по схеме:

=ЕСЛИ(ТИП.ОШИБКИ(ячейка)=2; "Кажется, тут случилось деление на ноль"; вычисление)

Еще напомним про другие функции:
ЕОШ / ISERR — возвращает ИСТИНА / TRUE для всех ошибок, кроме Н/Д (N/A)
ЕНД / ISNA — ИСТИНА только для Н/Д
IFNA — сразу заменяет N/A на второй аргумент, а без ошибки возвращает первый аргумент.

Смотрите также:
Как выделить ячейки с ошибками REF внутри формул
Подробная статья про ошибки в формулах


📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы @namokonov

В функции LET можно задавать свои функции! Это, конечно, экзотика (потому что, как правило, если нам нужно применять одно и то же вычисление много раз, мы используем LAMBDA + MAP или другую вспомогательную функцию), но тем не менее.

Если после имени переменной последует не константа / выражение, а функция LAMBDA, то это будет имя функции, которую потом можно в LET вызывать. В следующем примере у нас простая функция, умножающая число, данное ей на входе, на 2:

=LET(f; LAMBDA (a;a*2); f(10) + f(5) )

Здесь f – название функции, а – название переменной, аргумента этой функции. Затем в последнем аргументе LET мы уже ее вызываем с конкретными значениями 10 и 5.

Про базовые сценарии применения LET читайте в статье:
https://shagabutdinov.ru/tpost/47brblc2e1-novaya-funktsiya-excel-i-google-tablits


📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы @namokonov

Пара примеров, как сделать ваш GAS-скрипт быстрее

Друзья, привет! Сегодня расскажу, как без глубокого рефакторинга можно ускорить ваш код.

1) Убирайте методы из глобальной области видимости

Смотрите на скриншот: userStates будет инициализироваться при запуске любого скрипта, даже того, где он не используется.

Это отнимет у скрипта некоторое время ещё до старта.

Оставляйте в глобальной области видимости только текстовые строки, числа, объекты или массивы, но не вызывайте методы.

2) Минимизируйте количество обращений к таблице

Если записываете или получаете данные — старайтесь делать это за одно действие: формируйте массив в скрипте и вставляйте его с помощью setValues().

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

📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы @namokonov

Сколько пятниц, 13 в периоде?

Можно вычислить это такой формулой:

=ArrayFormula(СЧЁТЕСЛИ(ТЕКСТ(SEQUENCE(конец периода-начало периода+1;1;B1);"D DDD");"13 пт"))

=ArrayFormula(COUNTIF(TEXT(SEQUENCE(B2-B1+1,1,B1),"D DDD"),"13 пт"))

С другими региональными настройками будет иначе, например, для американских — "13 fri", а не "13 пт".
Ваши варианты формул приветствуются!

P.S. А если нужны просто все вторники или другие дни недели в периоде?
Кол-во вторников в месяце:

=ЧИСТРАБДНИ.МЕЖД(КОНМЕСЯЦА(дата;-1)+1;КОНМЕСЯЦА(дата;0);"1011111")

Задачка на формулы и скрипты

Друзья, привет!

Задача 1. Напишите формулу в Google Таблице, чтобы посчитать общее количество номеров в формате: две буквы + четыре цифры.

Задача 2. Напишите GAS-скрипт, который выведет все возможные номера в этом формате.

📌 Пишите свои решения в комментариях.

PS Буквы – от A до Z

Друзья, привет!

Сегодня берём "интересный" скрипт и заставим его работать.

Вот то, что нам прислал пользователь.

Пользователь пишет, что скрипт работает только для отправки одного сообщения.

Но если присмотреться внимательно, то данный скрипт вообще никуда сообщение отправить не может, так как внутри нет ни одного e-mail адреса:

var ID = "1btifyGowr_cWzkSzo0tZF_5bjRAs2ahgxTmsxju2VkE"; //speadsheet id
var EMAIL = "Лист1!A1:A"; //email 
var RANGE = "Лист1!B1:B"; //data range to send
var check = "Лист1!C1:C"; //parametr
var text = "Информация по Вашему заказу"; //subject


function sendData() {
  var spreadsheet = SpreadsheetApp.openById(ID);
  var data = spreadsheet.getRangeByName(RANGE).getValues();


  var message = {};
  if (SpreadsheetApp.openById(ID).getRangeByName(check).getValues() < 100) { //condition
    message.subject = "Заказ готов к отгрузке" + text;
    message.to = EMAIL;
    message.htmlBody = dataToHtmlTable_(data) +
      "<br><br>С уважением";
    MailApp.sendEmail(message);
  }
}

Array.prototype.datesToString = function () {
  return this.map(function (row) {
    return row.map(function (cell) {
      return cell && cell.getTime ? Utilities.formatDate(cell, Session.getScriptTimeZone(), "yyyy-MM-dd") : cell;
    });
  });
}


function dataToHtmlTable_(data) {
  return JSON.stringify(data, null, "  ")
    .replace(/^\[/g, "<table>")
    .replace(/\]$/g, "</table>")
    .replace(/^\s\s\[$/mg, "<tr>")
    .replace(/^\s\s\],{0,1}$/mg, "</tr>")
    .replace(/^\s{4}"{0,1}(.*?)"{0,1},{0,1}$/mg, "<td>$1</td>");
}}"

Заставляем работать чудо-код сверху:

function send() {
  const spreadsheet = SpreadsheetApp.openById(ID);
  const sh = spreadsheet.getSheetByName('лист1');
  const data = sh.getDataRange().getValues();

  for (let x = 0; x < data.length; x++) {
    const to = data[x][0];
    const Htmlbody = data[x][1] + "<br><br>С уважением";
    const subject = "Заказ готов к отгрузке." + "Информация по Вашему заказу.";
    GmailApp.sendEmail(to, subject, Htmlbody);
    sh.getRange(x + 1, 4).setValue(new Date());
  }
};

📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы @namokonov

оnEdit, который дает e-mail того, кто аккаунт редактирует

Друзья, пара открыть страшную тайнов веков - простый триггер onEdit умеет достать email того, кто редактирует ячейку только если действие (редактирование) происходит в платном google workspace аккаунтe.

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

Детали:
🔹 В бесплатных аккаунтах Google (обычные @gmail.com) простой триггер onEdit(e) не возвращает email.
Аргумент e содержит, например:

range — ячейку, которую изменили,

value / oldValue — новое и старое значения,

user — пустое поле.

🔹 В платных Google Workspace (корпоративных доменах) при срабатывании onEdit(e) можно достать e.user.getEmail(), и тогда действительно видно, кто редактировал.

Стартуем интенсив: формулы + скрипты

Скоро сентябрь — а это значит, что пора учиться!

Мы запускаем интенсив, старт — в пятницу, 29 августа.

Формат обучения:

каждую неделю — два занятия;

4 занятия по продвинутым (и просто полезным) формулам;

4 занятия по практичным скриптам;

продолжительность каждого занятия — 80–90 минут;

домашние задания для закрепления.

Это отличная возможность прокачать уставшие за лето нейроны 🙂

Отзывы https://t.me/google_sheets1/7

Вопросы — @namokonov (Евгений). С формулами мне будет помогать приглашённый специалист.

Что входит по скриптам: https://t.me/google_sheets/1861

Если просто хотите узнать и больше вопросов: 75 000 за 10 занятий, два + месяца, если есть любые вопросы: @namokonov

Скрипты на интенсиве: навыки, которые экономят часы работы и приносят деньги 💰

– Telegram-боты под ваши задачи
Научитесь создавать собственных ботов, которые пишут в чаты и каналы, собирают сообщения и даже следят за порядком.

– Google Документы на автопилоте
Зачем вручную готовить договоры и отчёты, если это можно делать за секунды?
👉 Вы увидите, как данные из Google Таблиц автоматически превращаются в готовые документы. Минимум кликов — максимум экономии времени.

– Интеграции через API и маркетплейсы
Разберём простыми словами, что такое API и как с его помощью соединять сервисы.
👉 На практике — примеры работы с Wildberries и OZON: вы поймёте, как автоматизировать продажи и быстрее масштабировать бизнес.

🔥 После этих занятий вы сможете убрать рутину, запускать процессы «на автомате» и использовать те инструменты, которые реально дают результат.

Вопросы — @namokonov

PS в понедельник покажем веб-приложения, которое может запускать любые скрипты

Сколько месяцев в году имеют 30 дней и более?

Напишем и разберем формулу:

=ARRAYFORMULA(
  QUERY(
    EOMONTH(DATE(2025; SEQUENCE(12); 1); 0);
    "select Col1, day(Col1) where day(Col1) >= 30";
    0
  )
)

1. SEQUENCE(12)
Даёт массив чисел от 1 до 12 → номера месяцев.

2. DATE(2025; SEQUENCE(12); 1)
Создаёт массив дат:

01.01.2025
01.02.2025
01.03.2025
...
01.12.2025

3. EOMONTH(...; 0)
Функция EOMONTH(дата; смещение) даёт конец месяца.
Так как смещение = 0, берём последний день того же месяца:

31.01.2025
28.02.2025
31.03.2025
30.04.2025
...
31.12.2025

4. QUERY(...;"select Col1, day(Col1) where day(Col1) >= 30";0)

QUERY смотрит на массив дат:
Col1 = дата конца месяца
day(Col1) = количество дней в месяце

Фильтр "where day(Col1) >= 30" убирает месяца, в которых меньше 30 дней

📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас / интенсив (стартует скоро)

🚀 Запускаем своё первое doGet-приложение в Google Таблицах

Хотите, чтобы ваши скрипты выполнялись по одной ссылке?
Это реально! Мы покажем, как за пару минут развернуть простое веб-приложение прямо в Google Таблицах.

🔥 Что это за приложение?
doGet-приложение запускает функцию, имя которой вы укажете в ссылке (?func=).
Например:

• ?func=clear — очистить таблицу,
• ?func=color — закрасить её случайными цветами.

Всё работает в один клик, а код остаётся полностью скрытым.

💡 Зачем это нужно?
• Вы даёте коллегам только ссылку — без раскрытия кода.
• Приложение запускается от вашего аккаунта.
• Можно подключить сколько угодно функций и запускать их прямо из браузера.

⚡ Пример кода
В примере всего две функции:
• color() — заполняет каждый лист в таблице (массив 20×20) случайными цветами.
• clear() — очищает все листы в таблице.

function doGet(e) {
  const funcName = e.parameters['func'];
  console.log(funcName);
  this[funcName]();
}

function clear() {
  const ss = SpreadsheetApp.getActive();
  const sheets = ss.getSheets();
  for (let sh of sheets) {
    sh.clear();
  }
  SpreadsheetApp.flush();
}

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

  for (let sh of sheets) {
    let arr = [];
    for (let i = 0; i < 20; i++) {
      arr[i] = [];
      for (let j = 0; j < 20; j++) {
        arr[i][j] = basicColors[getRandomInt(0, basicColors.length - 1)];
      }
    }
    sh.getRange(1, 1, arr.length, arr[0].length).setBackgrounds(arr);
  }
  SpreadsheetApp.flush();
}

const basicColors = [
  "#FF0000", // красный
  "#00FF00", // зелёный
  "#0000FF", // синий
  "#FFFF00", // жёлтый
  "#FFA500", // оранжевый
  "#800080", // фиолетовый
  "#00FFFF", // бирюзовый
  "#FFC0CB", // розовый
  "#A52A2A", // коричневый
  "#FFFFFF", // белый
  "#000000"  // чёрный
];

function getRandomInt(min, max) {
  min = Math.ceil(min);
  max = Math.floor(max);
  return Math.floor(Math.random() * (max - min + 1)) + min;
}

🚀 Как это работает
• [ссылка веб-приложения]?func=clear → моментальная очистка.
• [ссылка веб-приложения]?func=color → мгновенная заливка таблицы цветами.

Названия функций можно менять. Добавьте свои — и запускайте любое количество скриптов через ?func=.

📺 В видео показано, как развернуть приложение и получить на него ссылку.

🔥 На интенсиве мы разберём такие приёмы подробно и соберём рабочие проекты прямо на ваших глазах.
t.me/google_sheets/1860

Обновления Google Таблиц и Презентаций

Спасибо Бену Коллинсу и его чудесной рассылке за информирование — а мы в свою очередь делимся с вами.

Функции ISEMAIL и ISURL стали работать точнее, и должны корректно определять больше электропочт и доменных имен. Напоминаем — обе возвращают TRUE / ИСТИНА, если в аргументе корректная почта или адрес.
Если хочется превратить тру-фолсы в красивые флажки — никто вам не сможет запретить.

Ну а в Google Презентациях объекты теперь будут двигаться всего лишь на 1 пиксель при нажатии на стрелки на клавиатуре. Рай для перфекционистов! Для остальных, чтобы двигать объекты посущественнее, нужно будет теперь зажимать Shift.

Вот вам еще несколько советов для Презентаций:
— можно "вшить" ссылку в объект — в изображение, например, с помощью стандартного сочетания Ctrl + K
— создавать дубликат слайда сочетанием Ctrl + D
— увеличивать и уменьшать размер текста в выделенном объекте сочетаниями Ctrl + Shift + > и Ctrl + Shift + < (тоже работает не только лишь в Google Презентациях)
— Ctrl + Shift + V — вставка только текста, без исходного форматирования

🔥 Друзья! Уже завтра стартует наш интенсив!
Группа маленькая и уютная, поэтому каждому уделим максимум внимания.

⚡ Осталось всего 1–2 места — можно успеть вписаться! 🚀

Что будет на интенсиве:
✅ Полезные кейсы по скриптам — даже без знаний сможете просто скопировать, вставить и применить у себя.
✅ Фишки и практические приёмы по формулам.
✅ 10-12 занятий (по 2 раза в неделю) в формате лайв.
✅ Все записи сохраняются на Google Диске — доступ у вас останется навсегда, сможете и скачать к себе.
✅ Домашки и чат курса, где можно задавать вопросы и после завершения.

📩 Чтобы спросить детали или записаться — пишите: @namokonov

Друзья, наш интенсив стартовал в пятницу!

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

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

Вспомнили про новые функции: let, lambda, byrow, bycor.

Курс стартовал, поэтому, кто хочет присоединиться сейчас - скидка, 60000, вместо 75000, которые были на старте.

Вопросы, запись: @namokonov

🔥 Уже на первом занятии нашего интенсива мы разобрали пользовательские функции в Google Sheets!

Написали простейший скрипт (положил в комментарии), который возвращает HEX-цвет каждой ячейки, и научились использовать его для:
✅ подсчёта ячеек по цвету,
✅ суммирования через SUMIF,
✅ фильтрации строк по выделению,
✅ и других «умных» комбинаций формул.

И это только начало 💡

💰 Раньше участие стоило 75 000, но сейчас действует специальная цена — 60 000 (скидка 15 000).
⏳ Скидка доступна только до следующего урока, он во вторник!

⚡️ Про интенсив подробнее: t.me/google_sheets/1861
Вопросы, запись: @namokonov

Воскресное применение getActiveRange()

Друзья, в Google Скриптах метод getActiveRange() возвращает выделенный диапазон в Таблице.

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

Например, создаём простую функцию:

function воскресенье(a) {
  return a + ', ' + typeof a;
};

Вставляем её в ячейку A1 и передаём в качестве аргумента C1:C2.

Результат (на скриншоте): возвращаются данные из ячеек и их тип.

Так а как же получить аргумент, который передан в функцию?

Просто: берем активную ячейку, вытаскиваем формулу как текст и регуляркой достаём то, что внутри скобок.

Вуаля 👇

function воскресенье(a) {
  // Получаем активную таблицу
  const sh = SpreadsheetApp.getActive();

  // Берём адрес активного диапазона (в котором введена формула), например "A1"
  const rangeA1 = sh.getActiveRange().getA1Notation();

  // По адресу получаем саму формулу из ячейки, где стоит пользовательская функция
  // Например: "=воскресенье(C1:C2)"
  const formula = sh.getRange(rangeA1).getFormula();

  /* Возвращаем строку с тремя частями:
     1) сама формула, как она написана в ячейке
     2) переданный аргумент (вытаскиваем всё, что внутри скобок с помощью RegExp)
        формула.match(/\((.+?)\)/)?.[1] → ищет первую группу в скобках
        ?. — это оператор "optional chaining", чтобы не было ошибки, если нет совпадения
  3) значения, которые реально пришли в функцию (в параметр a)
*/ 
  return 'формула: ' + formula + '\n' 
       + 'переданный аргумент: ' + formula.match(/\((.+?)\)/)?.[1] + '\n'
       + 'значения: ' + a;
};

Завтра, 1 сентября, стартует наш интенсив 🚀

Будут очень практические вещи:
— как автоматически собирать данные из таблиц в Google Docs,
— как сделать бота, который отвечает в чате и подгружает данные из таблиц,
— и много других рабочих кейсов.

💰 Стоимость: 75 000
👉 со скидкой 10% — 67 500
👉 для участников чата — 52 500 (-30%)

📌 Подробнее про интенсив: t.me/google_sheets/1861
❓Вопросы и запись: @namokonov

Приходи, мы тебя ждем! ✨ Запись открыта ещё несколько дней.