Скрипт для Таблицы. Переносим значения из ячеек в примечания к ячейкам.

Друзья, сегодня простой чат по следам из нашего чата.

Что делает скрипт?
1. Берёт все значения "лист1";
2. Вставляет значения в примечания к ячейкам (если в примечаниях что-то есть, то скрипт добавит данные);
3. Далее очищает лист от всех видимых значений, оставляет только примечания;

Код отдельно

function Move() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Лист1');
  var range = sheet.getDataRange();
  var values = range.getValues();
  var notes = range.getNotes();

  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
      if (values[i][j]) {
        if (notes[i][j]) {
          notes[i][j] += '\n' + values[i][j];
        } else {
          notes[i][j] = values[i][j];
        };
      };
    };
  };

  range.clearContent();
  range.setNotes(notes);
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Menu')
    .addItem('Move', 'Move')
    .addToUi();
}

Таблица со скриптом

Наконец-таки полезное! (возможно скажете вы, наши читатели)

Мы вам принесли Таблицу со скриптом, с помощью которой можно извлекать комментарии из документов Google.

Чтобы добыть комментарии:
1) копируем Таблицу себе
2) вставляем в Комментарии!A1 айди нашего документа
3) запускаем единственный скрипт из меню

Код отдельно, чтобы код работал в проекте нужно включить Drive Api

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('@google_sheets')
    .addItem('⤵️', 'extract')
    .addToUi();
}


function extract() {
  var ss = SpreadsheetApp.getActive();
  var shLog = ss.getSheetByName('Комментарии');
  var pageToken = 'None';
  var arr = [];
  do {
    c = Drive.Comments.list(shLog.getRange("A1").getValue(),
      { 'pageToken': pageToken, fields: "*" });

    c = JSON.parse(c);

    c['comments'].forEach(f => {
      arr = arr.concat([[
        'комментарий',
        f.htmlContent,
        f.deleted,
        new Date(f.modifiedTime),
        f.author.displayName]]);

      f.replies.forEach(ff => {
        arr = arr.concat([[
          'реплай',
          ff.htmlContent,
          ff.deleted,
          new Date(ff.modifiedTime),
          ff.author.displayName]]);
      });
    });

    pageToken = c.nextPageToken;
  } while (pageToken);

  if (arr.length) {

    arr = [['~', 'htmlContent', 'deleted?', ' modifiedTime', 'author']].concat(arr);
    shLog.getRange("A2:E").clearContent();
    shLog.getRange(2, 1, arr.length, arr[0].length).setValues(arr);

  };
}

Извлекаем комментарии из документа / Удаляем комментарии / xlsx-файл - это архив / достаём из XML комментарии и адреса

И вообще зачем нам читать комментарии, если мы их можем удалить

Друзья, мы модифицировали нашу Таблицу про комментарии, теперь в меню два скрипта:

⤵️ - Скрипт извлекает все комментарии из документа, ID которого вы укажете в ячейке A1.

del - Скрипт удаляет отмеченные флажком (колонка G на скриншоте) комментарии из документа.

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

Код удаления отдельно

function deleteComments() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('Комментарии');
  var d = sh.getDataRange().getValues();
  var id = d[0][0];


  d.forEach((row, i) => {
    if (row[6] == true) {
      try {
        Drive.Comments.remove(id, row[5]);
      } catch (err) { };
      sh.getRange(i + 1, 7).clearContent();
    };
  });
};

Извлекаем комментарии из документа / Удаляем комментарии / xlsx-файл - это архив / достаём из XML комментарии и адреса

---
⭐️ Заказ работы у нас

Строим воронку с помощью спарклайнов

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

В Таблицах же мы можем построить воронку с помощью спарклайнов!

Алгоритм такой:
1 Вычисляем отношение очередного этапа к первому. Для первого этапа это априори будет единица.

2 Считаем, сколько остается до единицы, чтобы сформировать массив из трех значений для будущего спарклайна типа bar (а в нем, напомним, может быть не только одно или два значения). Допустим, второй этап — это 50% от первого (половина клиентов ушли на этом этапе). Значит, нам нужен будет массив значений 25% — 50% — 25% (при этом цвет для 25% будет белым, таким образом, у нас будет спарклайн в половину ширины столбца, то есть первого этапа, и посередине ячейки).

3 Засунем все это добро в MAP и LAMBDA, чтобы работало для разного числа этапов и одной формулой.

Формула получится такой (вариантов тут может быть много):

=MAP(массив ;LAMBDA(x;
  SPARKLINE(
  HSTACK((1 - x / первое значение) / 2 ;
                   x / первое значение ; 
                  (1- x / первое значение) / 2 ) ; 
           {"charttype" \ "bar" ; "color1" \ "white"})))

Таблица с примером

===
📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat

Таблицы теперь должны работать быстрее (удвоена скорость вычислений в браузерах Chrome и Edge)

https://workspaceupdates.googleblog.com/2024/06/improving-calculation-speed-in-google-sheets.html

А вы заметили у себя? Напишите в комментариях.

Друзья, в продолжение темы спарклайнов — а мы недавно писали про то, как построить воронку с помощью них — напомним вам про прежние публикации по этой теме и видеоурок для новичков!

Прогресс-бар (выполнение плана) спарклайном

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

Спарклайн с условием

Видеоурок по спарклайнам: синтаксис и примеры

Превращаем строку с числами в спарклайн одной формулой

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

Вышеобещанное – достаём из XML с комментариями текст комментария и адрес ячейки, в которой он расположен, с помощью Google Apps Script

Что делать

Скопировали xml, вставили в бэктиках (`) в редактор скриптов, назвали переменную xmlString

Скопировали туда же код скрипта, который найдете ниже

Запустили и получаем перечисление в цикле каждого комментария и его адреса (смотрите картинку)
 
Один XML-файл с комментариями внутри XLSX архива соответствует одному листу Таблицы

const xmlString = ``

function parseXML() {
  var o = {};
  var document = XmlService.parse(xmlString);
  var root = document.getRootElement();
  var ns = XmlService.getNamespace('http://schemas.openxmlformats.org/spreadsheetml/2006/main');
  var comments = root.getChild('commentList', ns).getChildren('comment', ns);

  for (var i = 0; i < comments.length; i++) {
    var comment = comments[i];
    var ref = comment.getAttribute('ref').getValue();

    var textElement = comment.getChild('text', ns);
    var tElement = textElement.getChild('t', ns);
    var textContent = tElement.getText();

    console.log(ref, textContent);
  };
};

А в следующий раз мы отправим комментарии на луну :)

Извлекаем комментарии из документа / Удаляем комментарии / xlsx-файл - это архив / достаём из XML комментарии и адреса

---
⭐️ Заказ работы у нас

Статья в первую очередь для тех, кто работает в Р7-Офис. Но остальным будет полезно пробежаться и вспомнить про принципы работы формул массива в Google Таблицах / Excel и узнать, как все устроено в российском табличном редакторе :)

А там своеобразное сочетание — есть некоторые функции, появившиеся в Excel только в 2021 версии вместе с динамическими массивами и в Таблицах тоже недавно. При этом формулы массива в Р7 работают как “старые” формулы массива Excel (возможно, это когда-нибудь изменится — очень надеемся). Так что новыми функциями пользоваться не так удобно, как в Таблицах или новом Excel, но зато они в принципе есть🙂

В статье разбираем:
— Как вводятся и работают старые и новые формулы массива в Excel
— Какие функции из новых доступны в Р7
— И разбираем, как работать с новыми функциями в Р7, где принципы работы формул массива старые, несмотря на наличие новых функций :)

https://shagabutdinov.ru/r7array/

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

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

Зачем это нужно? Чтобы сохранить все защиты диапазонов, которые есть в Таблице, они должны быть также от имени владельца. А также, чтобы Таблица лежала на Google Диске владельца.

--

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

Как все работает:

1) Скопировали нашу Таблицу

2) Далее владелец Таблицы, которую мы будем копировать должен зайти в редактор скриптов и поставить скрипт on() на триггер по редактированию Таблицы

(триггеры > добавление нового триггера > выбираем функцию on() и выбираем "при редактировании"

2) Далее "сотрудники" заполняют лист (ссылка на Таблицу, кого добавим как редакторов в копию и новое имя Таблицы)

3) Далее кто угодно активирует чекбокс и происходит магия - триггер запускает скрипт от имени того, кто поставил триггер и в ячейках ниже вы увидите ссылку на скопированную Таблицу и дату и время копирования

И отдельно код:

function on(e) {
  const sheet = e.source.getActiveSheet();
  const range = e.range;

  if (sheet.getName() == 'настройки' && range.getA1Notation() == 'B4' && e.value == 'TRUE') {
    copy();
  };
};

function copy() {
  const ss = SpreadsheetApp.getActive();
  const settings = ss.getRange("настройки!b1:b3").getValues().flat();
  var [originalUrl, addEmails, name] = settings;
  if (!originalUrl) return;
  var newUrl = '';
  try {
    const or = SpreadsheetApp.openByUrl(originalUrl);
    newUrl = or.copy(name || 'копия, ' + or.getName());
    if (addEmails) newUrl.addEditors(addEmails.split(',').map(f => f.trim()));
    newUrl = newUrl.getUrl();
  } catch (e) {
    newUrl = e.message;
  };

  ss.getRange("настройки!b5:c5")
    .setValues([[newUrl, new Date()]]);
};

Видео для новичков-новичков: "умные" таблицы

Друзья, для тех, кому удобнее формат видео, ролик про таблицы (tables) в Google Spreadsheets:
https://www.youtube.com/watch?v=Pn_b_mpr__k

Ранее мы писали про основные опции таблиц и ссылки на данные в них тут

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

В чем преимущества?
— Новые строки будут автоматом попадать в сводную
— Пустые строки не будут попадать в нее и их не придется фильтровать (как если бы мы брали в качестве источника столбцы, например, A:F)
— Таблица может начинаться не с первой строки листа и над/под ней может быть что-то еще. А если мы берем столбцы в качестве источника сводной, то в них больше ничего, кроме данных для нее, быть не может.

Форматируем данные как таблицу, выделяем любую ячейку, вставляем сводную —> видим, что в качестве источника фигурирует не диапазон, а имя таблицы (де-факто именованный диапазон, меняющий свои размеры при добавлении новых данных).

Вечная схватка двух ёкодзун: Google Таблицы и Excel

Ну что, друзья, с появлением таблиц в Google Spreadsheets пришло время обновить таблицу-сравнение двух табличных редакторов, а заодно напомнить про нее и рассказать тем, кто пропустил.

Она живет по этой постоянной ссылке:
https://docs.google.com/spreadsheets/d/10jhUBPLhmtx-km5sdITumlmkgIn8DKqsdxyULChVaq8

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

Как ускорить работу в Google Таблицах: 7 лайфхаков

Статья в РБК для новичков табличных дел! Делюсь 7 лайфхаками для работы в Google Spreadsheets: форматирование, горячие клавиши для вызова команд из меню, работа с ссылками, сортировка, вычисление фрагмента, пользовательские числовые форматы.

https://companies.rbc.ru/news/qnC1EmgtQx/kak-uskorit-rabotu-v-google-tablitsah-7-lajfhakov/

Почему объединение ячеек — это не очень хорошо?

Потому что за объединенной ячейкой всегда скрывается только одна заполненная (левая верхняя, первая, попросту говоря) и все остальные пустые!

А значит, формулами мы это дело не обработаем: в примере ломается ВПР / VLOOKUP, потому что название курса берется только из первой из объединенных ячеек.

Что если вы повторили все названия, но визуально хотите сохранить эффект объединения?

Как вариант — придумать формулу условного форматирования, которая будет "прятать" (применять белый цвет шрифта) все "лишние", нецентральные подписи.

Сортируем данные по фамилии (или другому слову в составе текстовой строки)

Вот такой лайфхак от маэстро табличных дел Бена Коллинса.

Если в ячейках данные формата Имя Фамилия, а нам надо сортировать по фамилии, а не по всей текстовой строке (включая имя), то можно в функцию SORT засунуть текстовые функции, которые будут извлекать фамилию:

=SORT(диапазон ;RIGHT(диапазон ;LEN(диапазон )-SEARCH(" ";диапазон ));1)

Здесь мы определяем, сколько символов в фамилии (находим с помощью SEARCH / ПОИСК или FIND / НАЙТИ разделитель — пробел — и вычитаем его позицию из общего числа символов в строке (LEN / ДЛСТР).
Затем извлекаем соответствующее число символов с конца строки с помощью функции RIGHT / ПРАВСИМВ.

А как это делается в Excel с новыми функциями (в частности, TEXTAFTER / ТЕКСТПОСЛЕ), можно посмотреть здесь:
https://tttttt.me/lemur_excel/273

Таблица с примером

Загружаем расходы в Таблицу по рекламе ВБ, API

Друзья, привет! Мы подготовили для вас Таблицу со скриптом, с помощью нее вы сможете загружать расходы по рекламным кампаниям из API ваших любимых диких ягод.

Копируйте Таблицу, вставляйте даты на первый лист (максимальный диапазон для загрузки - 31 день), вставляйте API-токен и запускайте скрипт из меню.

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

Также мы расшифровываем статус кампании и тип кампании.

Описание метода API: https://openapi.wildberries.ru/promotion/api/ru/#tag/Finansy/paths/~1adv~1v1~1upd/get

Таблица с кодом

Код отдельно:

function onOpen() {
  SpreadsheetApp.getUi().createMenu("🔥")
    .addItem('загрузить расходы за диапазон дат', 'google_sheets')
    .addToUi();
};

function google_sheets() {
  try {
    url_api = 'https://advert-api.wb.ru/';
    tz = Session.getScriptTimeZone();
    name = 'Расходы';

    ss = SpreadsheetApp.getActive();
    sh = ss.getSheetByName(name);
    d = sh.getDataRange().getValues();

    [start, end, token] = ss.getRange("!!b1:b3").getValues().flat()
      .map((f, i) => i < 2 ? d2s(f) : f);

    o = [];
    url = `${url_api}adv/v1/upd?from=${start}&to=${end}`

    r = request(url, 'get', token);

    r = r
      .map(k => {
        var [advertType, updTime, paymentType, advertStatus, updSum, advertId, campName] =
          [advert_type[k.advertType] || k.advertType, new Date(k.updTime), k.paymentType, obj_status[k.advertStatus], k.updSum, k.advertId, k.campName];

        updTime = d2s(updTime, 'yyyy-MM-dd HH:mm:ss', tz);
        key = updTime + '|' + advertId;
        if (!o.includes(key)) o.push(key);

        return [advertType, updTime, paymentType, advertStatus, updSum, advertId, campName];
      });

    if (r.length) {

      d = d.slice(1).filter(g => {
        date = d2s(g[1], 'yyyy-MM-dd HH:mm:ss', tz);
        key = date + '|' + g[5];
        return !o.includes(key);
      });

      console.log('стало: ' + d.length);
      r = [...d, ...r];

      console.log('суммарно вставляем: ' + r.length);
      sh.getRange("A2:M").clearContent();
      sh.getRange(2, 1, r.length, r[0].length).setValues(r);
    };
  } catch (err) {
    Browser.msgBox(err.message);
  }
};

obj_status = {
  4: 'готова к запуску',
  7: 'завершена',
  8: 'отказался',
  9: 'активна',
  11: 'приостановлена'
};

advert_type = {
  9: 'поиск',
  8: 'автокомпания'
};

function d2s(date, format = 'yyyy-MM-dd', tz = Session.getScriptTimeZone()) {
  return Utilities.formatDate(date, tz, format);
};

function request(url, method, token, data) {
  params = {
    method: method,
    muteHttpExceptions: true,
    contentType: 'application/json;',
    payload: JSON.stringify(data),
    'headers': {
      Authorization: token
    }
  };

  r = UrlFetchApp.fetch(url, params);
  r = JSON.parse(r)
  return r;
};

PS Токен ВБ нужен "продвижение", получить можно в личном кабинете продавца ВБ (как)

Что есть еще по WB + Таблицы

1) Таблица, которая загружает из API остатки и отчет по реализации t.me/google_sheets/1143

2) WB, достаём топ-100 товаров по запросу t.me/google_sheets/1116

3) Таблица, которая выгружает данные по артикулам WB, t.me/google_sheets/1104

---
⭐️ Заказ работы у нас

Телеграм бот для техподдержки, связанный с Google Таблицами

Друзья, привет! Пока сделаем анонс того, что скоро выложим на канал. Идея простая и уже проверенная нами в ряде компаний.

Концепция
– Телеграм бот, которому ваши сотрудники (ну или клиенты) задают вопросы, то есть просто отправляют сообщения

– Все сообщения приходят в Google Таблицу

– Оператор отвечает на сообщение прямо из Таблицы и оно отправляется автору вопроса в Телеграм

– Помимо этого можно сделать рассылку среди тех, кто боту писал и его не заблокировал

Мы сейчас заканчиваем упаковывать этот скрипт, поэтому все целиком мы вам презентуем в ближайшие дни.

Горячая линия своими руками (телеграм бот + отвечаем на вопросы из Таблицы)

В видео - демонстрация работы.

Чтобы развернуть у себя - делаем копию Таблицы, открываем редактор скриптов, читаем лист "!!!ИНСТРУКЦИЯ". Всё очень просто, справится даже новичок.

Чтобы отформатировать сообщение, которое отправляем - используйте HTML style

Нужен бот посложнее – ⭐️ Заказ работы у нас

PS
Это – прекрасный пример того, как неожиданно мощны могут быть Таблицы. В этой Таблице мы развернули "сервер" с веб-приложением, который работает круглосуточно и бесплатно, ждёт сообщений от Телеграм бота и логирует их в Таблицу, далее также легко вы можете из этой Таблицы отвечать.