Работа с данными в Google Таблицах. Кейсы, решения и угар.

Сколько пятниц, 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 минут;

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

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

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

ПС И да, "скрипты" звучит абстрактно, поэтому в том числе будем помогать создавать телеграм-бота, разворачивая его как веб-приложение и все на gas-скриптах.

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

– 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 дней

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

Google Таблицы pinned a photo

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

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

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

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

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

💡 Зачем это нужно?

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

⚡ Пример кода
В примере всего две функции:
• colour() — заполняет каждый лист в таблице (массив 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 colour() {
  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=colour → мгновенная заливка таблицы цветами.

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

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

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