Google Apps Script — Публикация листов

Статья Михаила Смирнова, написанная для канала t.me/google_sheets

Чат канала: t.me/google_spreadsheets_chat

Другие статьи Миши, написанные для нашего канала: t.me/google_sheets/1203

Здравствуйте, товарищи!

В Google Sheets можно вручную опубликовать листы или графики, но отсутствует соответствующий штатный функционал в Google Apps Script. Сейчас мы это поправим, как уже сделали для предоставления доступа для IMPORTRANGE().

Работать будем вот с этим файлом. Все скрипты там уже есть. Делайте копию.

Доступ к таблице

К таблице можно предоставить доступ, нажав на зелёную кнопку Share (Доступ) в правом верхнем углу. Статья не про это, но пару слов скажем, чтоб понять различия.

При предоставлении доступа таким способом человеку будет доступно:

оригинальная ссылка на таблицу, в которой есть id
всё содержимое таблицы, и прятать листы не поможет: чтобы достать данные можно использовать IMPORTRANGE(), чтобы добраться до формул, можно попробовать через скрипты (id есть -> SpreadhsheetApp.openById(id).getSheet().getRange().getFormulas())

С предоставлением доступа к таблице через скрипты всё хорошо. Можно использовать что-нибудь из следующего:

В SpreadsheetApp у класса Spreadsheet есть методы addViewer() и removeViewer(), чтобы дать или забрать доступ юзера на просмотр. Есть аналогичные методы для Editor.
В DriveApp у класса File есть аналогичные методы addViewer() и removeViewer(). Но есть и много других, не только для Editor, но и для Commenter, методы для доступа по ссылке для всех, для того чтобы, дать или забрать доступ сразу у нескольких пользователей и пр. Читайте документацию.

Как показать только данные

Если дать доступ через кнопку Share, можно собрать ссылку, например, такую:

https://docs.google.com/a/google.com/spreadsheets/d/1OuBabw-fkMcCrU7urgUPf65RlUf_yMXcY5hUOCM1-rQ/gviz/tq?tqx=out:html&sheet=data1&range=A:C&tq=WHERE%20A%20IS%20NOT%20NULL

Тут будут только данные, никаких формул:

tqx=out:html - на выходе простая html-таблица. Можно поменять на tqx=out:json - будет json.
sheet=data1 - можно задать лист (по умолчанию первый - это самый левый среди вкладок). Можно использовать имя, можно id листа (gid).
range=A:C - можно задать диапазон для вывода (поумолчанию весь лист).
tq=WHERE%20A%20IS%20NOT%20NULL - и даже модно задать запрос для фильтрации, агрегации и пр. - всего, что умеет обычная QUERY(). Не забывайте URL-энкодить (все эти пробелы заменённые на %20 и пр.). Тут запрос простой: WHERE A IS NOT NULL

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

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

Но в такой ссылке id всё же светится, со всеми вытекающими. Имейте в виду.

Публикация вручную

Если не хочется показывать id, формулы и какие-то листы, можно опубликовать таблицу или её часть (набор листов и графиков).

Идём в меню File -> Share -> Publish to web:

Выбираем нужный лист для публикации (можно и несколько, можно и всю таблицу сразу):

Получаем вот такую ссылку:

https://docs.google.com/spreadsheets/d/e/2PACX-1vTts1oddYQurQr9-9UDhsqUuflCTCRAGFdGzge9bsU-TnROwTqkRaCf1L3GS_fwXwXECkLHuBGx3EuG/pubhtml?gid=2127066774&single=true

Ссылка эта не содержит id таблицы. 2PACX-... - рандомный id именно публикации. При повторной публикации не меняется.

Так выглядит опубликованный лист:

Чтобы убрать лишнее (заголовок, столбцы справа, нижний баннер) можно добавить к ссылке параметров (range=A:C и chrome=false):

https://docs.google.com/spreadsheets/d/e/2PACX-1vTts1oddYQurQr9-9UDhsqUuflCTCRAGFdGzge9bsU-TnROwTqkRaCf1L3GS_fwXwXECkLHuBGx3EuG/pubhtml?gid=2127066774&single=true&range=A:C&chrome=false

Получится так:

Про параметры ссылки опубликованной таблицы можно почитать здесь (раздел Embed files -> Edit embedded spreadsheets).

🕵️ Секретная ссылка для публикации

Штатного метода для публикации почему-то нет в скриптах, хотя казалось бы. Но у нас есть dev tools браузера и мы можем посмотреть, куда и что отправляется при нажатии на кнопку Publish:

Отправляется POST-запрос вот сюда (не такая уж и секретная, да?):

https://docs.google.com/spreadsheets/d/1OuBabw-fkMcCrU7urgUPf65RlUf_yMXcY5hUOCM1-rQ/publishingsettings?id=1OuBabw-fkMcCrU7urgUPf65RlUf_yMXcY5hUOCM1-rQ&token=какойтотокен&includes_info_params=true

С вот таким содержимым:

Используем ссылку в скрипте

Собрать правильную ссылку не сложно. Параметры имеют понятные названия. После небольших экспериментов, написал пару функций для публикации. Лежат в publishing.gs в скриптовом проекте той же таблицы.

Основная функция - requestPublishSettings(). Она собирает и отправляет запрос. Остальные её используют для конкретных задач.

requestPublishSettings() отправляет POST-запросы для применения настроек (опубликовать, снять с публикации) и GET-запрос для получения 2PACX-ссылки (это подсмотрели в dev tool при открытии настроек публикации).

Другие функции:

publishSheets() - публикует листы по именам
publishOnlyOneSheet() - публикует один лист по имени
stopPublishing() - снимает всё с публикации
getPublishedURL() - возвращает 2PACX-ссылку
getURLWithParameters() - добавляет к 2PACX-ссылке параметры
getPublishedURLForOneSheetClean() - возвращает ссылку на публикацию конкретного листа, где все лишние виджеты убраны

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

Заключение

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

А вот тут извините: я не пробовал перебирать все варианты параметров. Назначение некоторых параметров непонятно. Графики публиковать не пробовал. Публиковать таким же способом документы (Google Docs) не пробовал, должно быть похоже. Ну, и код я не причёсывал и все возможные варианты использования не закодил. Впрочем, несложно допилить для других случаев.

Напишите, если кто что-то из этого сделает, нам в чатике очень интересно.

На этом всё. Спасибо за внимание.

Ссылки

Таблица с примером и кодом
Программно даём доступ для IMPORTRANGE к другой таблице - другая статья про другую секретную ссылку
Про параметры 2PACX-ссылок опубликованной таблицы можно почитать здесь (раздел Embed files -> Edit embedded spreadsheets).
Про получение данных из таблицы в простецком виде (tqx, tq и пр.): тут, тут, здесь (тут можно URL-энкодить запрос, хотя и в таблицах есть соответствующая функция) и ещё вот тут.