Начинаем работу с google sheets на python. от регистрации до чтения данных

Разница между авторизацией через приложение и через сервисный аккаунт

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

1. При авторизации через сервисный аккаунт не требуется подтверждать доступ к данным через браузер.
2. Сервисный аккаунт имеет доступ только к тем Google таблицам к которым вы сами ему предоставили доступ на почту. При авторизации через приложение вы подтверждаете доступ ко всей доступной вашему Google аккаунту информации.

По этим двум причинам наиболее предпочтительным способом автризации является авторизация через сервисный аккаунт

Но важно понимать, что если кто-то посторонний получит доступ к JSON файлу с ключём от сервисного аккаунта, он автоматически завладеет всеми правами и доступами которые вы предоставили этому сервисному аккаунту

Основные функции пакета googlesheets4

Все функции пакета разделены на 3 группы, каждая группа функций имеет свой префикс, который говорит об области действия этой функции:

• — объединяет функции реализующие операции над книгой GoogleSheets
• — операции над рабочими листами
• — операции над отдельными диапазонами ячеек
• — операции над отдельными ячейками

Давайте рассмотрим основные функции пакета googlesheets4.

• — Авторизация;
• — Создаёт новую Google Таблицу;
• — Открывает Google Таблицу в браузере;
• — Инициализирует подключение к Google Таблице, в качестве единственного аргумента принимает URL или ключ нужной Google Таблицы;
• — Считывает данные из указанного листа Google Таблицы;
• — Записывает данные в Google Таблицу, при необходимости создаёт новый лист. Если вы пытаетесь записать данные на существующий лист то все данные будут перезаписаны;
• — Дописывает данные на уже существующий лист;
• — Создаёт новые листы в существующей Google Таблице;
• — Удаляет существующие листы из Google Таблицы;
• — Выводит вектор содержащий имена листов Google Таблицы.

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

Авторизация через собственное приложение

Как создать своё приложение?

Для создания приложения следуйте приведённым ниже инструкциям:

1. Переходим в «Основное меню» > «API и сервисы» > «Учетные данные».
2. «Создать учетные данные» > «Идентификатор клиента OAuth».
3. Вводим любое произвольное название для вашего приложения.
4. Далее будет сгенерирован id и secret вашего приложения.
5. Теперь вам необходимо либо скачать JSON файл с созданными учётными данными, либо скопировать id и секрет созданного вами приложения.

Скачиваем JSON

Копируем ID и секрет приложения

Теперь возвращаемся в RStudio, подключаем пакет и проходим авторизацию через своё приложение.

Проходим авторизацию через JSON файл:

Проходим авторизацию указав в коде ID и Секрет приложения:

После запуска функции , в приведённых выше примерах кода, вы будете перенаправлены в браузер. Там вы подтверждаете разрешение на доступ к данным и возвращаетесь в RStudio для продолжения работы.

VLOOKUP – ищем значения в другом диапазоне данных

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

Синтаксис:

=VLOOKUP(запрос;диапазон;номер_столбца;)

Пример 1. Есть два массива ключевых фраз, полученных из разных источников. Нужно найти ключи в первом массиве, которые не встречаются во втором массиве. Для этого используем формулу:

=VLOOKUP(A2:A;B2:B;1;false)

Что мы сделали:

• задали диапазон A2:A, из которого берем ключи для сравнения;
• задали диапазон B2:B, с которым сравниваем ключи из столбца А;
• задали номер столбца (1), из которого подтягиваем ключи при совпадениях;
• false – указали, что сортировка нам не нужна.

Функция VLOOKUP часто используется при поиске данных на разных листах или в разных документах.

Пример 2. Мы выгрузили данные из Яндекс.Вебмастера и Google Search Console об индексации страниц сайта. Наша задача – сопоставить данные и определить, какие страницы индексируются в одном поисковике, но не индексируются в другом.

Заносим результаты выгрузок в файл Google Sheets. На одном листе – URL из Google, на втором – из Яндекса.

В ячейке C2 прописываем функцию VLOOKUP. Сразу заключаем в функцию в ARRAYFORMULA для автоматического протягивания вниз:

=ARRAYFORMULA(VLOOKUP(A2:A;Yandex!A2:A;1;false))

Теперь мы сразу видим, какие страницы проиндексированы в Google, но не проиндексированы в Яндексе.

Что мы сделали:

• задали диапазон A2:A текущего листа, из которого берем значение для сравнения;
• задали диапазон Yandex!A2:A листа с выгрузкой из Яндекса, с которым будем сравнивать значения URL из Google;
• указали номер столбца листа с выгрузкой из Яндекса, значения из которого подтягиваем при совпадении значений из сравниваемых диапазонов;
• false – указали, что сортировка нам не нужна.

Если же вам нужно проверить одновременно индексацию конкретных страниц в Яндексе и Google, воспользуйтесь инструментом от PromoPult. Загрузите список URL и запустите проверку. Если страница проиндексирована в поисковике, в столбце будет цифра 1, если нет – 0.

Каким пользоваться этим инструментом и в каких ситуациях он полезен, читайте в этом гайде.

Перемещение Google Таблиц между папаками Google Диска

Ещё одна операция которая может вам пригодиться, но в данном случае помимо вам понадобится пакет .

Установка googledrive

Переместить созданную ранее Google Таблицу можно с помощью функции . Но предварительно необходимо пройти автооризацию с помощью функции .

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

Далее открываем на Google Диске нужную папку и копируем её URL или ключ. Если нужная папка ещё не создана её можно создать с помощью функции .

Для инициализации подключения к папке используем функцию .

Пример кода для перемещения Google Таблицы из одной папки Google Диска в другую

Заключение

Описанных в статье возможностей пакета достаточно для решения подавляющего большинства задач, в которых необходимо использовать Google Sheets API.

На данный момент находится в стадии активной разработки. Автор пакета планирует реализовать его функционал в полном объёме к марту 2020 года, в связи с чем в статье возможны корректировки и дополнения по мере изменения или расширения возможностей пакета.

LEN – считаем количество символов в ячейке

Эта функция особенно полезна при составлении объявлений контекстной рекламы – когда важно не заступать за отведенное количество символов для заголовков, описаний, отображаемых URL, быстрых ссылок и уточнений

В SEO функция LEN применяется, например, при составлении мета-тегов title и description. Символы функция считает с пробелами.

Синтаксис:

=LEN(ячейка с текстом)

Пример. Нам нужно составить тайтлы для всех страниц сайта. Мы знаем, что в результатах поиска отображается около 55 символов. Наша задача – составить тайтлы так, чтобы самая важная информация была в первых 55 символах. Прописываем формулу LEN для заполняемых ячеек. Теперь мы точно знаем, когда приближаемся к отображаемым 55 символам.

Форматы и форматирования в Google Таблицах

Буду краток, форматы данных доступны самые разные:

• текст или числа;
• дата и время;
• проценты;
• финансы и валюты.

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

За подобные изменения отвечает специальный раздел, который расположен в «шапке» вашей Google Таблицы.

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

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

Как начать работу с Google-таблицами?

Начать работать с гугл-таблицей очень просто, но и здесь есть несколько вариантов. Первый, он самый простой и распространенный, – через облачное хранилище Google Drive. Думаю, нет смысла вдаваться в подробности и освещать всю историю этого сервиса, ведь вы и так всё знаете об этом диске и его функционале. Вернемся к таблицам. Как сделать новую гугл таблицу в облаке? Для этого вам нужно выполнить всего два действия:

1. нажать большой и яркий «+» в левом верхнем углу вашего экрана;

1. курсором выбрать «Таблицы» из перечня возможных действий.

Но что делать тем, кто уже начал работать в Microsoft Excel (или Numbers, если речь идет о компьютере родом из Купертино). Все данные уже собраны, формулы проставлены, информация отфильтрована, а ваш руководитель только-только соизволил сообщить, что ему также необходим доступ к этой таблице. Ситуация печальная, но весьма реалистичная и ничуть не смертельная. Выход есть – нужно все ваши наработки из Excel загрузить в Google Sheets и отправить незадачливому боссу ссылку на новенькую онлайн-таблицу.

И вообще, взаимная файловая поддержка Microsoft Excel и Google Sheets делает работу с обоими сервисами очень удобной и комфортной. Респект создателям!

Подписывайтесь на наши новости

Одно письмо в неделю с самыми актуальными статьями + обзор digital-профессий!

*Нажимая «Подписаться» вы даете согласие на обработку персональных данных.

Начало работы

Для того, чтобы начать работу в документах Гугл Докс заведите почту в Google. Этот аккаунт даст вам возможность для дальнейшей работы. Заполните информацию о себе и создайте название почты. Чтобы взаимодействовать с заказчиками придумайте нейтральный логин.

Создаём аккаунт в Google

После завершения регистрации можно полноценно пользоваться сервисом. Открываем сервис google.com и нажимаем на такой значок:

Открываем сервис google.com

После этого, как нажали на этот значок появляется меню, где жмём на «Диск». Это будет началом работы с гугл диском.

Выбираем в меню «Диск»

Далее, откроется меню, котором нужно выбрать пункт «Создать».

В открывшемся меню выбираем пункт «Создать»

Создаём «Новый документ» в открывшемся окне.

Создаём «Новый документ»

Если вы всё сделали правильно, можно начинать работу. Открывшийся документ выглядит так:

Если у вас уже есть документы в Гугл Докс их можно открыть при нажатии на синий значок:

Как открыть Microsoft файл в Гугл Докс

Сервис даёт возможность открывать файлы формата Microsoft Word без ограничений. Нажмите сначала на «Файл», потом на «Открыть».

Открываем файлы формата Microsoft Word

Откроется новое окно. В нём нужно выбрать пункт «Загрузка».

В новом окне выбираем пункт «Загрузка»

После выбора и загрузки файла можно начинать работу.

Ой-ой, твой браузер не поддерживает видео.

Сохраняем документы в Google Docs

После создания файла на гугл диске он сохраняется в автоматическом режиме. Для того, чтобы его переименовать нужно нажать на «Новый документ» вверху экрана. Когда появится выделение голубым цветом можете вводить своё название.

Переименовываем файл в Google Docs

При работе с файлом в Google Docs, он сохраняется сразу, походу вашей работы вместе с изменениями. К примеру, при внезапном выключении компьютера данные не пропадут. Все сохранённые документы находятся здесь:

Это основная папка вашего гугл диска. Папки можно создавать самостоятельно и переносить туда документы. Для этого нужно кликнуть на значок папки и выбрать нужный пункт в меню. Оно позволяет создавать новые папки и перетаскивать в них имеющиеся файлы.

Создаём новые папки и перетаскиваем в них имеющиеся файлы

На диске можно работать просто перетаскивая документы. Всё по аналогии с компьютером. Нажмите на нужный файл и перетащите его в папку.

Работа на диске – выбираем нужный файл и перетаскиваем его в папку

При необходимости скачать файл на ПК жмите на «Файл», «Скачать» и сохраняйте в необходимом формате.

Скачиваем файл на ПК

Быстрые клавиши в основном меню:

Быстрые клавиши в основном меню

Подписывайтесь на наши новости

Одно письмо в неделю с самыми актуальными статьями + обзор digital-профессий!

*Нажимая «Подписаться» вы даете согласие на обработку персональных данных.

Как опубликовать файл из общего диска

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

1. Откройте сайт drive.google.com на компьютере.
2. Слева выберите Общие диски и дважды нажмите на нужный диск.
3. В верхней части страницы нажмите на стрелку вниз рядом с названием общего диска Настройки общего диска.
4. Нажмите Изменить в разделе «Доступ для пользователей вне общего диска».
5. Нажмите «Доступ к объектам на этом общем диске можно открывать тем, кто не входит в число его пользователей».
6. Нажмите Применить.
7. .

Как отключить автоматическое обновление

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

1. Откройте опубликованный файл в Google Документах или Таблицах.
2. Выберите Файл Опубликовать в Интернете.
3. Нажмите Опубликованные материалы и настройки.
4. Снимите флажок «Автоматически публиковать после внесения изменений».

Чтобы снова включить автоматическое обновление, установите флажок.

Примечание. В Google Презентациях отключить эту функцию нельзя.

Как отменить публикацию файла

1. Откройте файл в Google Документах, Таблицах или Презентациях.
2. Выберите Файл в верхней части страницы Опубликовать в Интернете.
3. Нажмите Опубликованные материалы и настройки.
4. Выберите Отменить публикацию.

Как выглядит опубликованный файл

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

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

Как настроить права на публикацию файла

Кроме владельца, публиковать файл могут только пользователи с правом на редактирование.

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

1. Откройте файл в Google Документах, Таблицах или Презентациях.
2. В правом верхнем углу экрана нажмите Настройки доступа.
3. Нажмите на значок «Настройки» .
4. Снимите флажок Пользователи с правом на редактирование смогут изменять разрешения и параметры доступа.
5. Нажмите Готово.

Embed files

You can make a document, spreadsheet, presentation, or form available to view on an existing website by embedding it in your site or blog.

Embed a document, spreadsheet, or presentation

1. Open a file in Google Docs, Sheets, or Slides.
2. At the top, click File Publish to the web.
3. In the window that appears, click Embed.
4. Choose a publishing option:

• Spreadsheet: Choose to publish the entire spreadsheet or individual sheets.
• Presentation: Choose a presentation size and how quickly to change the slides.

1. Click Publish.
2. Copy the HTML in the text box and paste it into your site or blog.

Edit embedded spreadsheets

If you’re embedding a spreadsheet, you can show or hide parts of the spreadsheet after you publish to the web.

1. Open a file in Google Sheets.
2. At the top, click File Publish to the web.
3. In the window that appears, click Embed.
4. Click Publish.
5. Copy the code in the text box and paste it into your site or blog.
6. To show or hide parts of the spreadsheet, edit the HTML on your site or blog.

• : The sheet ID.
• : The rows and columns that are published to the web. For example, A1:B14.
• : True or false. If true, the sheet tab is displayed at the bottom.
• : True or false. If true, row numbers and column letters are displayed.
• : True or false. If true, the title and footer are displayed.

Embed a form

1. In Google Forms, open a form.
2. At the top right, click Send.
3. At the top of the window, click Embed.
4. To copy the HTML that appears, click Copy.
5. Paste the HTML into your site or blog.

Файл создан, а что дальше?

Сперва окинем взглядом панель инструментов Google Таблицы. Ничего сверхъестественного мы там при всем желании не найдем. Но именно это и позволяет новым пользователям в кратчайшие сроки адаптироваться к онлайн-таблицам от Google. Интерфейс выглядит даже проще, чем в Microsoft Office. Хотя у некоторых пользователей, привыкание может отнять немного времени.

Сравнивать иконки и сопоставлять наименования мы с вами не будем, не в детском садике все-таки. Но основные инструменты Google Таблицы перечислить необходимо. Слева-направо, как они расположены в панели, так я их и оставил:

• отменить/повторить последнее действие;
• отправка в печать;
• копировать форматирование;
• масштаб;
• денежный и процентный форматы;
• изменение количества знаков после запятой;
• другие форматы;
• шрифт и его размер;
• стандартные текстовые инструменты;
• заливка ячеек, их границы, объединение;
• различные вариации выравнивания текста;
• вставка ссылки, комментария, диаграммы и пр.;
• фильтрация и функции.

Ничего сложного, все понятно интуитивно. Поехали дальше!

Common terms

This section explains some of the terms you’ll encounter when reading about the
Sheets API.

Spreadsheet ID

Every API method requires a parameter which is used to identify
which spreadsheet is to be accessed or altered. This ID is the value
between the «/d/» and the «/edit» in the URL of your spreadsheet. For example,
consider the following URL that references a Google Sheets spreadsheet:

https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=0

The spreadsheet ID is a string containing letters, numbers, and some special
characters. The following regular expression can be used to extract the
spreadsheet ID from a Google Sheets URL:

/spreadsheets/d/(+)

If you’re familiar with the Drive API, the corresponds to the
ID of the
resource.

Sheet ID

Individual sheets in a spreadsheet have titles (which must be unique) and
IDs. The is used frequently in the Sheets API to specify which sheet
is being read or updated. In the Sheets UI, you can find the of the
open sheet in the spreadsheet URL, as the value of the parameter.
The following shows the structure of the URL and where can be
found:

https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=sheetId

The sheet ID is numeric, and the following regular expression can be used to
extract it from a Google Sheets URL:

gid=(+)

You can also fetch the IDs of sheets using the API — see the

sample.

A1 notation

Some API methods require a range in A1 notation. This is a string like
, that refers to a group of cells in the spreadsheet, and is
typically used in formulas. For example, valid ranges are:

• refers to the first two cells in the top two rows of Sheet1.
• refers to all the cells in the first column of Sheet1.
• refers to the all the cells in the first two rows of Sheet1.
• refers to all the cells of the first column of Sheet 1, from row 5 onward.
• refers to the first two cells in the top two rows of the first visible sheet.
• refers to all the cells in Sheet1.

Named ranges are also supported. When a named range conflicts with a sheet’s
name, the named range is preferred.

If the sheet name has spaces or starts with a bracket, surround the sheet name
with single quotes (), e.g . For simplicity, it is safe to
always surround the sheet name with single quotes.

Date/Time serial numbers

Google Sheets, like most other spreadsheet applications, treats
date/time values as decimal values. This lets you perform arithmetic on them in
formulas, so you can increment days or weeks, add or subtract two date/times,
and perform other similar operations.

Google Sheets uses a form of epoch
date
that is commonly used in spreadsheets. The whole number portion of the value
(left of the decimal) counts the days since December 30th 1899. The fractional
portion (right of the decimal) counts the time as a fraction of one day. For
example, January 1st 1900 at noon would be 2.5, 2 because it’s two days after
December 30th, 1899, and .5 because noon is half a day. February 1st 1900 at
3pm would be 33.625.

Note that Google Sheets correctly treats the year 1900 as a common year, not a
leap year.

Загрузка данных из существующих Google Таблиц

Считывание данных из Google Таблицы является наиболее часто используемой операцией.

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

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

Как вы поняли из приведённого выше примера ключ это часть URL адреса таблицы, которая находится между и следующие слешем .

Для чтения данных в предназначена функция .

Пример кода для чтения данных из Google Таблицы

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

Publish file

Important: 

• When you publish a chart to the web, people can see the data used to create it. Be careful when publishing a chart with private or sensitive info.
• Any changes you make to the original document will be updated in the published version. The automatic update might take a few minutes.
• To remove a file from the web, you must stop publishing it. .
• To stop sharing a file with collaborators, learn how to change sharing permissions.

1. In Google Docs, Sheets, or Slides, open a file.
2. At the top, click File Publish to the web.
3. Choose a publishing option:
   • Spreadsheet: Publish the entire spreadsheet or individual sheets. You can also choose a publishing format.
   • Presentation: Choose how quickly to advance the slides.
4. Click Publish.
5. Copy the URL and send it to anyone you’d like to see the file. Or, .

Share & collaborate on a file with more than 100 people

Up to 100 people with view, edit, or comment permissions can work on a Google Docs, Sheets, or Slides file at the same time. When more than 100 people are accessing a file, only the owner and some users with editing permissions can edit the file.

To share and collaborate on a file with more than 100 people: 

Publish the file

• If you need many people to view a file at once, publish it and create a link to share to viewers. You can give edit access to people who need to edit or comment on the file. Learn how to publish a file. 
• Publishing a file makes it visible to everyone on the web. Be careful when publishing private or sensitive info. If you have an account through work or school, your administrator can limit who can view a published file. If you’re an administrator, .
• To remove a file from the web, you must stop publishing it. .
• To stop sharing a file with collaborators, learn how to change sharing permissions.

Create a Google Site 

• Create a Google Site to share information with many people.  You can embed documents, spreadsheets, and presentations on the site, which can be viewed by a large amount of users. Learn how to embed documents on a site.
• If you anticipate high traffic to your site, first publish your document in Google Docs, Sheets or Slides, then embed the published URL into Google Sites. Learn how to publish a file.

Collect feedback with Google Forms

If you need to gather a lot of information, create a Google Form. Responses will be recorded in a Google Sheet. Give edit access only to people who need to work with the responses. To let more than 100 people view the responses, publish the spreadsheet to the web and create a link to share with viewers. Learn how to publish a file. 

Fix problems with documents shared with many people

If your document is shared with many people and it’s crashing or not updating quickly, try these troubleshooting tips: 

• Instead of allowing people to comment on a document or spreadsheet, create a Google Form to collect feedback. Learn how to create a Google Form.
• If you’re making a copy of a document, don’t include resolved comments and suggestions. . 
• Delete older information or move data into a new document. 
• Ask viewers to close the document when they aren’t using it. 
• Include only the most important information in a published document. Shorter documents load faster.
• Reduce the amount of people with edit access to a document. 
• If collecting information from multiple documents, create a new, view-only document to share with a large number of people. 

Creating a Chart from a Separate Spreadsheet

Typically, people create Google Charts by populating a data table
and drawing the chart using that data. If you want to retrieve the
data from a Google Spreadsheet instead, you’ll query the spreadsheet
to retrieve the data to be charted:

function drawChart() {
  var query = new google.visualization.Query(URL);
  query.send(handleQueryResponse);
}

function handleQueryResponse(response) {
  var data = response.getDataTable();
  var chart = new google.visualization.ColumnChart(document.getElementById('columnchart'));
  chart.draw(data, null);
}

This works because Google Spreadsheets support the Google
Charts query
language for sorting and filtering data; any system supporting
the query language can be used as a data source.

Note that charts cannot use the privileges of the person viewing
them without explicit authorization. The spreadsheet must either be visible to
everyone or the page must explicitly acquire an end-user credential as
documented under the section of this
page.

To use a Google Spreadsheet as a data source, you’ll need its URL:

1. Open an existing spreadsheet. This spreadsheet should
   have the format expected by your visualization, and it should have viewing
   privileges set properly. (View privileges of «Public on the web» or «Anyone
   with the link» will be easiest, and the instructions in this section assume
   a spreadsheet that has been set up this way. You can restrict by keeping
   the spreadsheet «Private» and granting individual Google accounts access,
   but you will need to follow below).
2. Copy the URL from your browser.
   See for
   details about selecting specific ranges.
3. Provide the URL
   to .
   The query supports the following optional parameters:
   • : Specifies how
     many rows are header rows, where N is an integer zero or greater.
     These will be excluded from the data and assigned as column labels in the
     data table. If you don’t specify this parameter, the spreadsheet will guess
     how many rows are header rows.
     Note that if all your columns are string data, the spreadsheet might have
     difficulty determining which rows are header rows without this parameter.
   • : Specifies which sheet in
     a multi-sheet document to link to, if you are not linking to the first sheet.
     N is the sheet’s ID number. You can learn the ID number by navigating to the
     published version of that sheet and looking
     for the parameter in the URL. You
     can also use the parameter instead of this
     parameter. Gotcha: Google Spreadsheets might
     rearrange the gid parameter in the URL when viewed in a browser; if copying
     from a browser, be sure that all parameters are before
     the URL’s mark. Example: .
   • : Specifies which
     sheet in a multi-sheet document you are linking to, if you are
     not linking to the first sheet. sheet_name is the
     display name of the
     sheet. Example: .

Here’s a complete example:

Below are two ways to draw this chart, one using the parameter and the other
using the parameter. Entering either URL in the browser will produce the
same result/data for the chart.

Query Source Ranges

The query source URL specifies what part of a spreadsheet to use in the
query: a specific cell, range of cells, rows, or columns, or a whole spreadsheet. Specify the
range using the «range=<range_expr>» syntax, for example:

https://docs.google.com/spreadsheets/d/1XWJLkAwch5GXAt_7zOFDcg8Wm8Xv29_8PWuuW15qmAE/gviz/tq?range=A1:C4
   

Here are a few examples that demonstrate the syntax:

• — A range from cell A1 through B10
• — Rows 5-7
• — Columns D-F
• — The first 70 cells in column A
• — Column
  A from row 70 to the end
• — B5 to
  the end of row 5
• — D3 to the end of column D
• — From
  the beginning of column C to C10

IMPORTXML – парсим данные с веб-страниц

«Развесистая» функция для парсинга данных с веб-страниц с помощью XPath.

Синтаксис:

=IMPORTXML(«url»;»xpath-запрос»)

Вот лишь несколько вариантов использования этой функции:

• извлечение метаданных из списка URL (title, description), а также заголовков h1-h6;
• сбор e-mail со страниц;
• парсинг адресов страниц в соцсетях.

Пример. Нам нужно собрать содержимое тегов title для списка URL. Запрос XPath, который мы используем для получения этого заголовка, выглядит так: «//title».

Формула будет такой:

=IMPORTXML(A2;»//title»)

IMPORTXML не работает с ARRAYFORMULA, так что вручную копируем формулу во все ячейки.

Вот другие запросы XPath, которые вам будут полезны:

• выгрузить заголовки H1 (и по аналогии – h2-h6): //h1
• спарсить мета-теги description: //meta/@content
• спарсить мета-теги keywords: //meta/@content
• извлечь e-mail адреса: //a/@href
• извлечь ссылки на профили в соцсетях: //a[contains(href, ‘vk.com/’) or contains(href, ‘twitter.com/’) or contains(href, ‘facebook.com/’) or contains(href, ‘instagram.com/’) or contains(href, ‘youtube.com/’)]/@href

Если вам нужно узнать XPath-запрос для других элементов страницы, откройте ее в Google Chrome, перейдите в режим просмотра кода, найдите элемент, кликните по нему правой кнопкой и нажмите Copy / Copy XPath.

Авторизация через сервисный аккаунт

Для начала надо создать сервисный аккаунт, вот алгоритм действий который вам необходимо выполнить:

1. Создать проект в Google Cloud, если он ещё не создан.
2. Заполните его название и описание (при необходимости) и нажмите «Создать».
3. Роль устанавливать не надо, поэтому жмём «Продолжить».
4. В следующем окне жмём «Создать ключ» > Тип ключа выбираем JSON > Создать.
5. Запоминаем расположение и название JSON файла, при сохранении вы можете дать ему любое название.
6. Жмём «Готово».

Теперь у вас появился сервисный аккаунт, откройте его и скопируйте почту.

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

1. Открываем Google Таблицу.
2. Жмём «Настройка доступа».
3. Открываем доступ на почту созданного сервисного аккаунта.

Проходим авторизацию через сервисный аккаунт:

При авторизации через сервисный аккаунт вам не понадобится подтверждать доступ к данным через браузер, но ещё раз напомню, что вам надо отдельно расшаривать доступ сервисному аккаунту к каждой Google Таблице, с которой вам необходимо работать по API.

Limit how a file is shared

When you share a file with someone, you can choose what they can do with it.

• Viewer: People can view, but can’t change or share the file with others.
• Commenter: People can make comments and suggestions, but can’t change or share the file with others.
• Editor: People can make changes, accept or reject suggestions, and share the file with others.

Change who your link is shared with

When you share a link to a file, you control how widely the file is shared. These options depend on if your Google Account is through work, school, or Gmail.

• Public: Anyone can search on Google and get access to your file, without signing in to their Google Account.
• Anyone with the link: Anyone who has the link can use your file, without signing in to their Google Account. 

Restricted: Only people you share the file with will be able to use it.

Related articles

• Make Google Docs, Sheets, Slides & Forms public
• Stop or change how a file is shared
• Transfer file ownership
• Share folders in Google Drive
• Find files shared with you

IMPORTRANGE – импортируем данные из других таблиц

Функция позволяет вставить в текущий файл данные из других таблиц.

Синтаксис:

=IMPORTRANGE(«ссылка на документ»;»ссылка на диапазон данных»)

Пример:

=IMPORTRANGE(«https://docs.google.com/spreadsheets/d/ХХХХХХХХ/»,»имя листа!A2:A25″)

Пример. Вы продвигаете сайт клиента. Над проектом работает три специалиста: линкбилдер, SEO-специалист и копирайтер. Каждый ведет свой отчет. Клиент заинтересован отслеживать процесс в режиме онлайн. Вы формируете для него один отчет с вкладками: «Ссылки», «Позиции», «Тексты». На эти вкладки с помощью функции IMPORTRANGE подтягиваются данные по каждому направлению.

Преимущество функции в том, что вы открываете доступ только к конкретным листам. При этом внутренние части отчетов специалистов остаются недоступны для клиентов.

Publish a file from a shared drive

Important: If you’re using an account through work or school, your administrator might have turned off the ability to publish a file. If you can’t publish a file, contact your administrator.

1. On your computer, go to drive.google.com.
2. On the left, click Shared drives double-click one of your shared drives.
3. At the top, next to the name of your shared drive, click the Down arrow Shared drive settings.
4. Next to “Sharing with non-members,” click Edit.
5. Click “Non-member of this shared drive can be given access to files in this shared drive.”
6. Click Apply.
7. .

Turn off automatic updates

When you make changes to a published Docs or Sheets file, it will automatically publish the changes. To turn off automatic updates:

1. Open a file in Google Docs or Sheets that you’ve already published to the web.
2. Click File Publish to the web.
3. Click Published content & settings.
4. Uncheck the box next to «Automatically republish when changes are made.»

To turn automatic publishing back on, check the box.

Note: You can’t turn off automatic updates in Google Slides.

Stop publishing a file

1. Open a file in Google Docs, Sheets, or Slides.
2. At the top, click File Publish to the web.
3. Click Published content & settings.
4. Click Stop publishing.

How published files look when you share them

If you send someone the URL of a published file, they’ll see a version they can’t edit that looks different from yours. Here’s what others will see:

• Documents: A version with no toolbar.
• Spreadsheets: A version with no toolbar. People with «view» permissions can see charts, cell formatting, and the values of cells, but can’t view or edit formulas.
• Presentations: A view-only version or a version in presentation mode with full-screen slides.

Control who can publish a file

File owners and editors can publish files. If you’re the owner of a file and want someone else to publish the file, give them «edit» access.

If you’re the owner and don’t want anyone else to publish the file:

1. Open a file in Google Docs, Sheets, or Slides.
2. On the top right, click Share.
3. Click Settings .
4. Uncheck Editors can change permissions and share.
5. Click Done.

ARRAYFORMULA – протягиваем формулу вниз в один клик

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

Достаточно заключить исходную функцию в функцию ARRAYFORMULA, и формула применится ко всем ячейкам ниже. Причем при удалении добавлении строк формула все равно будет работать – без пробелов в расчетах.

Синтаксис:

=ARRAYFORMULA(исходная формула)

Пример. Сделаем автоматическое применение формулы, описанной выше, для всех ячеек диапазона. Для этого заключаем исходную формулу в ARRAYFORMULA:

=ARRAYFORMULA(IFERROR(IF(B2:B*0,1>=300;»1″;»0″);»нет данных»))

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

Лайфхак. Нажмите сочетание клавиш CTRL+SHIFT+ENTER после ввода основной формулы, и функция ARRAYFORMULA применится автоматически.

ARRAYFORMULA работает не со всеми функциями. Например, она не совместима с GOOGLETRANSLATE и IMPORTXML, о которых расскажем ниже.