Google-таблицы: получение данных с других веб-сайтов - узнаем курс рубля, температуру, последние новости, заголовки H1 и др. (+ несколько полезных примеров, которые "сложно" провернуть в Excel)

Доброго времени всем!

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

Excel, конечно, вещь хорошая, но для некоторых задач его не совсем удобно использовать (а то и вовсе невозможно). И в этом случае весьма неплохим выбором может стать Google-таблица (тем паче, что она бесплатна и некоторые вещи здесь можно решать без знания скриптов!).👌

Собственно, в этой небольшой заметке хочу привести несколько примеров работы с ней, которые могут быть весьма полезными для широкого круга читателей. Прим.: для тех, кто уже лет 5-10 "ковыряется" ежедневно с таблицами, боюсь, что ничего нового я здесь не открою...

Ремарка: чтобы создать свою первую "Гугл-табличку" — перейдите на сервис 📌Google Drive, зарегистрируйтесь (или войдите под своим логином), кликните правой кнопкой мыши по свободному месту диска, и выберите функцию "Создать таблицу". См. скрин ниже.

 

*

Примеры работ с Google-таблицей

Импорт в табличку нужных данных с сайтов

Итак...

Начать заметку я хочу с одной довольно замечательной формулы: IMPORTXML(ссылка; запрос-xpath). Она позволяет получать практически любые данных с открытых веб-страничек в сети Интернет. Например, вы можете подгрузить в свою таблицу:

• курс рубля к доллару (и других валют);
• последние новости;
• списки фильмов/музыки;
• данные из других таблиц и т.д. и т.п.

*

📌 Как этим пользоваться (на примере):

1. вставляете в одну из ячеек URL-адрес веб-странички, на которой есть нужная вам информация (например, я добавил в A1 адрес: https://rambler.ru/);
2. во вторую ячейку (в моем случае A2) нужно добавить запрос Xpath (его можно скопировать в браузере, чуть ниже покажу как);
3. теперь в одной из ячеек (например A3) используем формулу =IMPORTXML(A1;A2) и получаем в ней то, что было на указанной странице и пути Xpath! В своем примере я просто получил курс рубля к доллару с Рамблера (и, разумеется, дальше этот курс будет обновляться автоматически, и вы эту информацию можете использовать для расчетов).
4. Удобно?!

 

📌 Примечание: как узнать путь Xpath (в моем примере //*[@id="main"]/div[2]/div/div/div/div/div[3]/div/div[2]/div/a[1]/span[2])

Для этого откройте веб-страницу в браузере Chrome и кликните правой кнопкой мыши по нужному элементу (цифре / заголовку и т.д.), и выберите в меню "Просмотреть код".

Затем появится консоль разработчика - в ней кликните еще раз ПКМ по строке, на которую курсор установится автоматически, и выберите в меню "Copy Xpath". Все, теперь этот элемент вы можете вставить в нужную вам ячейку таблицы (по Ctrl+V).

 

***

📌 Еще один пример: получение списка фильмов, музыки и прочих данных из однотипных блоков

Для примера я взял URL "Кинопоиска" со списком из самых новых фильмов. Но тут больше всего интересен будет Xpath запрос:

//div[@class='styles_main__2fFcM']

Чтобы повторить что-то подобное: вам нужно на веб-странице найти повторяющийся div-блок (например, в котором заключены заголовки фильмов) и посмотреть его класс (делается это через консоль разработчика в Chrome || аналогично, как мы сделали чуть выше). Далее заменить мой класс в примере на свой. В результате: получите в таблице целый список названий...

См. пару скринов ниже. 👇

 

*

Для работы с финансами (курсами валют, акциями и пр.)

Информация носит справочный характер. Не принимайте торговых решений на основе работы этой формулы (во всяком случае - перепроверяйте ее работу!). 

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

*

Для получения курсов более оперативно есть в "Гугл-таблицах" другая формула:

=GOOGLEFINANCE("CURRENCY:USDRUB")

См. ниже несколько примеров. Подставив свой тикер (или валюту) - вы сможете относительно-оперативно узнавать ее курс (а значит и пересчитывать свои значения в таблицах 😉👇).

 

 

*

Перевод текста с иностранного языка на русский

Допустим вы импортировали в табличку какие-нибудь данные с иностранных сайтов. И было бы неплохо теперь все эти строки перевести. В этом плане Google-таблица отлично решает проблему!

Пример:

1. пишем формулу вида: =GOOGLETRANSLATE(A9;"en";"ru"). Вместо "en" и "ru" нужно указать язык оригинала и перевода, а A9 — это ячейка с иностранным текстом;
2. далее просто "растягиваете" (копируете) формулу на все ячейки, напротив которых требуется перевод. См. пример ниже. 👇

 

 

*

Замена (или удаление) ненужных символов (автоматически)

Тоже довольно популярная операция. Мне импонирует ее выполнять с помощью регулярных выражений (это быстро, легко, и универсально).

Простой пример:

=REGEXREPLACE(A30 ;"[^0-9]+";"")

где A30 — ячейка с исходным текстом;

"[^0-9]+" — всё, что не относится к цифрам - обработать! (если бы было "[0-9]+" — обработать только цифры, остальное не трогать). Значок "^" - решает!

"" — если в кавычках ничего нет - менять любые символы, кроме цифр, будем на пустоту!

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

Кстати, для удаления лишних пробелов удобно также использовать формулу: =СЖПРОБЕЛЫ("text")

 

 

*

Совместная работа с документами

С документами на Google Drive могут одновременно работать неск. человек! Это очень удобно, если (скажем) в вашу табличку заносят данные 3-4 пользователя, а вы сразу же видите результат расчета (анализа) их данных.

Причем, для доступа к табличке другим пользователям - достаточно сделать правый клик мышки по файлу, и выбрать опцию "Открыть доступ" (или "Получить ссылку"). Затем можно либо дать доступ для конкретного пользователя, либо всем, у кого есть ссылка...

Кстати, с телефона документ тоже будет доступен! (и это большой плюс Google-таблице)

📌 В помощь (по теме)!

Совместная работа над документами (текстом, таблицами и пр.), или как создать общую папку в интернете — см. неск. способов

 

 

*

Вставка картинок с других сайтов

Если вам нужно вставить картинку в конкретную ячейку (и чтобы она из нее никуда не "делась") — воспользуйтесь формулой =IMAGE(). В скобках потребуется лишь указать ссылку на эту картинку.

Обратите внимание, что я привел два варианта: в одном размеры будут подобраны автоматически, во втором- указываются вручную! 👇

=IMAGE("https://ocomp.info/wp-content/themes/wikiwp/images/logo10-2.png")

=IMAGE("https://ocomp.info/wp-content/uploads/2021/09/Proverka-diska.png";4;100;150)

Второй вариант позволяет вручную задать размеры картинки: 100 - высота, 150 - ширина (см. мой пример выше).

 

*

Создание QR-кода

Тема-таки прям модная...

Делается это при помощи простой формулы:

=IMAGE("https://chart.googleapis.com/chart?cht=qr&chs=100x100&chl="&A43)

Обратите внимание, на конце формулы стоит A43 — вместе нее нужно указать свою ячейку со ссылкой (текстом). Кстати, размер 100x100 - тоже можно поменять на нужный, например, 250x250.

📌 В помощь!

Как создать и читать QR-коды (штрих-коды) на Андроид (и как сделать оплату по QR-коду с телефона) — см. пошаговую инструкцию

 

*

Дополнения и комментарии по теме - приветствуются!

Успехов!

👋