Программно даём доступ для IMPORTRANGE к другой таблице

Правильный канал о Таблицах (Google Sheets): @google_sheets

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

Сейчас мы покажем, как решается древний вопрос по предоставлению доступа для IMPORTRANGE() программно. На этот вопрос несколько лет отвечают "невозможно". Оказалось, очень даже возможно.

Чтобы использовать IMPORTRANGE() необходимо предоставить доступ к таблице, откуда будет осуществлён импорт.

Варианта три:

дать доступ на чтение (как минимум) для всех (Anyone) по ссылке
вручную нажать синюю кнопку предоставления доступа
дёрнуть специальную ссылку скриптом

⛔ Первый вариант часто не подходит, так как данные вываливаются в публичный доступ.

⛔ Второй вариант может не подойти, если это действие надо производить постоянно во многих таблицах.

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

Зачем это вообще?

В каких-то случаях и первые два варианта сгодятся. Но, например, если вы скриптом создаёте много таблиц, копируя шаблон, в котором используется IMPORTRANGE(), то вручную ходить и кликать на синюю кнопку, будет утомительно, а в открытый доступ таблицу-источник определить нельзя, так как данные секретные. Вот в таком случае надо идти автоматизировать третьим способом.

Разберём на примере

Вот наша таблица-источник. В ней какие-то данные, который надо забрать с помощью IMPORTRANGE().

В таблице-получателе формулой импортируем первую колонку первого листа (используется id таблицы-источника):

=IMPORTRANGE("1XF_Br4VvZmS0r9-MVkfI4O_yKzzTQ3VHClNVRpimxcA"; "A:A")

Без предоставления доступа получаем ошибку #REF!, так как публичный доступ к источнику не предоставлен.

При наведении курсора на ошибку, можно выдать доступ к таблице источнику (но мы кнопку не трогаем, по-другому сделаем):

Открываем редактор скриптов:

Пишем небольшую функцию:

Код функции:

function addImportrangePermission() {
  // id таблицы куда импортируем, где надо дать доступ
  const ssId = SpreadsheetApp.getActiveSpreadsheet().getId();

  // id таблицы источника, здесь мы его захардкодили для простоты
  const donorId = '1XF_Br4VvZmS0r9-MVkfI4O_yKzzTQ3VHClNVRpimxcA';

  // специальная ссылка для предоставления доступа
  const url = `https://docs.google.com/spreadsheets/d/${ssId}/externaldata/addimportrangepermissions?donorDocId=${donorId}`;

  const token = ScriptApp.getOAuthToken();

  const params = {
    method: 'post',
    headers: {
      Authorization: 'Bearer ' + token,
    },
    muteHttpExceptions: true
  };
  
  UrlFetchApp.fetch(url, params);
}

Сохраняем скрипт. Вешаем вызов функции на кнопку (кнопка только для наглядности, можно и из IDE запустить):

Указываем, какая функция должна выполняться при нажатии

При первом нажатии на кнопку надо будет выдать права для запуска:

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

Тут у gif'ки качество получше: https://i.imgur.com/rh1b8ks.mp4

В чём фишка-то?

А всё просто: надо дёрнуть специальную ссылку с соответствующими правами, и доступ будет предоставлен.

Ссылка:

https://docs.google.com/spreadsheets/d/${ssId}/externaldata/addimportrangepermissions?donorDocId=${donorId}

Вместо ${ssId} подставляется id таблицы, где используется IMPORTRANGE()
Вместо ${donorId} подставляется id таблицы-источника, откуда импортируются данные

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

Есть и другая ссылка, с помощью которой можно проверить, есть ли у пользователя права разрешить импорт (синюю кнопку он вообще увидит?):

https://docs.google.com/spreadsheets/d/${ssId}/externaldata/canaddimportrangepermissions?donorDocId=${donorId}

Если таблиц много, соответственно, по многим ссылкам надо будет пройтись, присмотритесь к UrlFetchApp.fetchAll().

Откуда хитрая ссылка?

Недокументированная функциональность. Мог бы и сам найти, посидев 5 минут в консоли браузера, посмотрев, что происходит при нажатии на синюю кнопку, но что-то не догадался. Заметил решение (от апреля 2021) в вопросе на Stackoverflow (вопросу 6 с половиной лет, январь 2015) во время гугления:

https://stackoverflow.com/a/67280686/279806

Потом уже изучил, проверил в консоли и написал пример на Google App Script (запостил на SO, ага).

Да, кстати, это только пример, не волшебная таблетка. Не получится бездумно скопировать себе код, чтоб наступило счастье. Надо разобраться и адаптировать.

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

Ссылки

Документация на функцию IMPORTRANGE()
Вопрос на Stackoverflow с упоминанием секретной ссылки
Дергаем ссылки из Google App Script — Class UrlFetchApp
Получаем OAuth 2.0 токен — ScriptApp.getOAuthToken()