Геокодер / Достаем по адресу координаты, используя скрипты

Друзья, недавно у нас заказали скрипт и заказчик (Иван, спасибо ❤️), разрешил его выложить на канал для всех.

Скрипт небольшой, но полезный, он собирает адрес из первого и второго столбца и если в столбце G стоит галочка "обновить", то идет в Maps.newGeocoder(), достает по этому адресу координаты, адрес еше раз (чтобы можно было сравнить адрес, которые запрашивали и который вернулся), JSON с остальным данными и выключаем галочку.

Пример работы на гифке, гифка со звуком.

В день на одном аккаунте есть где-то 300-400 бесплатных запросов, поэтому если задача запросить много адресов - либо используйте много аккаунтов гугл, либо запрашивайте несколько дней.

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

PS Посмотрел на JSON еще раз - еще из него можно достать индекс адреса, который вы запрашиваете

Извлекаем актуальное число подписчиков телеграм-каналов из ссылки вида t.me/канал

Для этого извлечем текст "### subscribers", который есть по такой ссылке — с помощью IMPORTXML.
Ну а потом удалим (заменим на ничто) все не-цифры (\D) с помощью REGEXREPLACE.

Чтобы получить из 20 500 subscribers —> 20500.

Все вместе:
=REGEXREPLACE(IMPORTXML(A2;"//div[3]");"\D";"")

Если у вас не ссылки вида t.me/канал, а только @имя_канала, то можно собрать ссылку прямо в формуле, удалив @:
=REGEXREPLACE(IMPORTXML("https://tttttt.me/"&SUBSTITUTE(A3;"@";"");"//div[3]");"\D";"")

Меняем формат даты с ММ/ДД/ГГГГ на ДД.ММ.ГГГГ формулой

Эту задачу решает формула от постоянного участника нашего сообщества Льва:

=ArrayFormula(IF(A:A = "";;TEXT(REGEXREPLACE(TO_TEXT(A:A);"(\d{2})\/(\d{2})\/";"$2/$1/")*1;"dd.mm.yyyy HH:mm:SS")*1))

Это формула массива — одной формулой решаем задачу для всего столбца:
1. проверяем, не пусто ли в столбце — в случае пустых ячеек формула возвращает пустоту.
2. с помощью REGEXREPLACE меняем цифры дня и месяца местами. Здесь используется запись вида $1 — это ссылка на подвыражение (подвыражения берутся в круглые скобки). Таким образом, мы меняем подвыражения — две идущих подряд цифры с косой чертой после — местами.
3. Меняем формат результата с помощью функции TEXT / ТЕКСТ.
4. Делаем значение числом за счет умножения на единице (подробнее про это тут).

Регулярные выражения — компактная памятка от vitalich

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

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

Тогда можно использовать REGEXMATCH — эта функция будет возвращать TRUE / ИСТИНА, если текст, соответствующий регулярному выражению во втором аргументе, будет найден в текстовой строке из первого аргумента.

В случае проверки данных формула будет выглядеть так:
=REGEXMATCH(первая ячейка диапазона с проверкой ;"рег выражение")

В случае с кодами цветов:

=REGEXMATCH(A2;"^#[0-9A-Fa-f]{6}$")

То есть решетка + 6 символов из набора "все цифры, строчные и прописные буквы от A до F". Чтобы разрешить ввод только кодов (то есть в ячейке не должно быть больше ничего), добавляем ^ и $ (начало и конец строки).

Заказать работу

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

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

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

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

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

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

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

Друзья, сегодня у нас для вас простой скрипт для обучения 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);
};

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

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

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

🍏 выгружатор, достаем скриптом количество подписчиков чатов и каналов в телеграм 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вам нужно сортировать данные в своем порядке — не по алфавиту.
Например, какой-то регион в вашем бизнесе важнее других, хотя название города не первое по алфавиту.
Или речь про размеры одежды 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 и отсортировать данные

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

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

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

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

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

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

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

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

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

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

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

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

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)

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

Получаем из длинной строки Таблицу (часть 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 В следующем посте покажем вам, как парсить страницу с товаром так, чтобы получать и категории и описание товара

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

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

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

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

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

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

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

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

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

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

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