Google Apps Script — Публикация листов
Michael SmirnovПравильный канал о Таблицах (Google Sheets): @google_sheets
Чат канала: @google_spreadsheets_chat
Здравствуйте, товарищи!
В 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-энкодить запрос, хотя и в таблицах есть соответствующая функция) и ещё вот тут.
Правильный канал о Таблицах (Google Sheets): @google_sheets
Чат канала: @google_spreadsheets_chat