Как мы добавляли лист "про нас" в сто Таблиц

Привет, сегодня рассмотрим конкретную рабочую задачу – мы нарисовали лист "⚡️ ПРО НАС", с нашими ссылками и решили добавить этот лист во все Таблицы с примерами, которые были опубликованы на канале за почти пять лет.

(мы решили, что это улучшит конверсию в оглавление / чат / и ботов заказывайте!)

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

Создаём список Таблиц

Сейчас в оглавлении нашего канала 368 постов. Таблица с примером приложена не к каждому, но все равно Таблиц много, где-то сто. Найдём все нужные Таблицы.

1. Сперва создаём запрос для DriveApp: нам нужны файлы, в которых я владелец, с mimeType Таблицы и эти файлы должны быть открыты для всех по ссылке:

function q() {

var userEmail = Session.getActiveUser().getEmail()

return `'${userEmail}' in owners and mimeType = 'application/vnd.google-apps.spreadsheet' and (visibility = 'anyoneWithLink' or visibility = 'anyoneCanFind')`;

}

2. Используем этот запрос в функции, которая создаст реестр Таблиц и вставит его на активный лист

function searchDOCs() {

var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()

var array = []

var t = 0

var max = 5000

var query = q();

var files = DriveApp.searchFiles(q())

while (files.hasNext() && t < max) {

t++

var file = files.next();

try {

array = array.concat([[file.getName(), file.getUrl(), file.getSharingAccess(), file.getDateCreated(), file.getOwner().getEmail()]]);

} catch (err) { }

}

var r = ss.getRange(4, 1, array.length, array[0].length)

r.clearContent()

r.setValues(array)

}

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

4. Дополнительно фильтруем по имени. Обычно в названии Таблиц с нашими примерами есть @google_sheets, поэтому дополнительно фильтруем формулой массив, чтобы оставить в нём файлы с google в названии:

Добавляем в каждую Таблицу лист "⚡️ ПРО НАС".

Еще помните, что мы тут делаем? :) Список Таблиц мы подготовили, теперь вставляем в Таблицу со скриптом лист, который будет копировать и пишем функцию копирования.

Функция ниже идёт диапазону с ссылками (у меня он i:i) открывает каждую Таблицу, проверяет, если ли в ней лист с названием '⚡️ ПРО НАС', если нет, то копирует этот лист из Таблицы со скрипом и переименовывает (так как при копировании к названию листа добавится (копия).

const shName = '⚡️ ПРО НАС';

function myFunction() {

const ss = SpreadsheetApp.getActive();

const sh = ss.getActiveSheet()

const template = ss.getSheetByName(shName);

const range = sh.getRange("i1:i100")

const colours = range.getBackgrounds();

range.getValues()

.forEach((url, i) => {

url = url[0];

if (url && colours[i][0] != 'red') {

const shTo = SpreadsheetApp.openByUrl(url);