• Как сделать Limit динамическим в QUERY?

    @curyliis Автор вопроса
    Нашел решение сам.
    65f6375a9f5fd050298895.png
    Ответ написан
    1 комментарий
  • Как подсветить (выделить) строку по выбранной ячейке в Гугл таблицах (без Apps Script)?

    @katefromr Автор вопроса
    Найдено расширение для браузера Google Sheets Row Highlighter.
    Задача решена!
    Ответ написан
    1 комментарий
  • Какую формулу задать в гугл-таблице, чтобы часть данных перенеслась на другой лист по фильтру даты?

    ForestAndGarden
    @ForestAndGarden
    Совершенствовать среду обитания
    Правильный вопрос — половина решения.

    по фильтру

    Осталось по-английски и заглавными написать: FILTER.

    Использовать два условия в параметрах функции: для фамилии и для даты.
    Ответ написан
    Комментировать
  • Как сделать гистограмму в виде гонок?

    ProgrammerForever
    @ProgrammerForever Куратор тега Excel
    Учитель, автоэлектрик, программист, музыкант
    1) Использовать внешний сервис, который сгенерирует картинку по данным, и получить её с помощью IMAGE(). Сомневаюсь что есть уже готовое, но что-то похожее стоит поискать.
    2) Посмотреть в сторону chart-api, возможно если углубиться в кастомизацию, что-то подобное можно нарисовать, но это не точно.
    3) Нарисовать диаграмму ячейками, их границами и т.п. Это самый простой способ, хотя и костыльный.
    Ответ написан
    1 комментарий
  • Как закрасить область цветом при появлении данных в другой ячейке?

    ProgrammerForever
    @ProgrammerForever Куратор тега Excel
    Учитель, автоэлектрик, программист, музыкант
    1. Выделить диапазон в котором менять цвет
    2. Условное форматирование
    3. Своя формула

    =$A$1<>""
    Так при появлении чего-то в A1 цвет поменяется в первоначальном диапазоне
    Ответ написан
    4 комментария
  • Как вытащить адрес почты из тела письма и отправить на него стандартный ответ?

    Amunrah
    @Amunrah Автор вопроса
    Сказочный персонаж ))
    Короче решил вопрос. Оставлю тут, вдруг кому пригодится.

    В общем сделал табличку с двумя одинаковыми листами (Новые и Обработанные) вида:
    61154258cbce4938784054.png

    И написал следующий код:
    var name, email, phone;
    name = email = phone = 'None';
    
    function getEmails(){
      var threads = GmailApp.search('label:Заявка');
    
      if (threads.length) {
        for (var i=0; i < threads.length; i++) {
          var messages = threads[i].getMessages();
          for (var j=0; j < messages.length; j++) {
            var message = messages[j];
            message.markRead(); 
            extractDetails(message);
          }
          getSheetData();
          threads[i].removeLabel(GmailApp.getUserLabelByName("Заявка"));
          threads[i].addLabel(GmailApp.getUserLabelByName("Обработано"));
        }    
      } else {}
    }
    
    function extractDetails(message){
      var dateTime = message.getDate();
      searchInBody(message.getPlainBody());
      var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
      var activeSheet = SpreadsheetApp.setActiveSheet(spreadsheet.getSheetByName("Новые"));
      activeSheet.appendRow([dateTime, name, email, phone]);
    }
    
    function searchInBody(mes) {
      var lines, subline;
      lines = mes.split("\n");
    
      for (index = 0; index < lines.length; ++index) {
        if(lines[index].indexOf('Name:') + 1) {
          subline = lines[index].split(":");
          name = subline[1].toString().trim();
        }
        if(lines[index].indexOf('Email:') + 1) {
          subline = lines[index].split(":");
          email = subline[1].toString().trim();  
        }
        if(lines[index].indexOf('Phone:') + 1) {
          subline = lines[index].split(":");
          phone = subline[1].toString().trim().slice(1,);
        }
      }
    }
    
    function getSheetData() {
      var re = /\S+@\S+\.\S+/;
      var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = SpreadsheetApp.setActiveSheet(spreadsheet.getSheetByName("Новые"));
      var data = sheet.getRange('A2:F').getValues();
      sheet.getRange('A2:F').clearContent();
      
      data.forEach(row => {
        if (row[2] !== '' && re.test(row[2])) {
          sendMail();
        } else {}
      })
    
      var sheet1 = SpreadsheetApp.setActiveSheet(spreadsheet.getSheetByName("Обработанные"));
      var row = data.length;
      var column = data[0].length;
      sheet1.getRange(sheet1.getLastRow()+1, 1, row, column).setValues(data);
    }
    
    function sendMail() {
      var html = 'Здравствуйте '+name+'!<br><br>Ваша заявка принята, наш специалист скоро свяжется с вами.<br><br>Спасибо за обращение.';  
      var recipientsTO = email;
      var recipientsCC = email;
      var Subject = "Ваша заявка принята";
      
      MailApp.sendEmail({
        to: recipientsTO,
        cc: recipientsCC,
        subject: Subject,
        htmlBody: html
      });
    }

    Дал ему нужные разрешения и поставил расписание. Код сыроват и далековат от оптимального, но пока все работает.
    Ответ написан
    Комментировать
  • Как сделать автопереименование ячейки google sheets (гугл таблицы) при переименовании папки?

    MakhmudovDamir
    @MakhmudovDamir
    привет, да это возможно только скриптом. Если нет опыта в программировании расшарь таблицу помогу samrukov@gmail.com

    Вам необходимо войти в редактор кода Google таблицы. Для начала необходимо определиться как новые папки будут попадать в таблицу. Например кнопкой или автоматически - неважно. Например у вас куча папок находится водной главной директории. Можно бегать по ее составу и сверять с тем, что есть в таблице.

    let sp = SpreadsheetApp.getActiveSpreadsheet() - подключаемся к нашей таблице
    let sheet = sp.getSheetByName() - имеем дело в листом котором данные
    
    let cell = sheet.getRange(2,3) - обратились к ячейке по корординатам (способом много)
    let id = cell.getValue() - взяли значение например id
    
    let mainFolder = DriveApp.getFolderById() - также можно получить папку по id (id находится в url папки)
    let folders = mainFolder .getFolders - так можно получить все папки внутри
    
    while (folders.hasNext()) { так мы бегаем по всем папкам внутри и вытаскиваем имя
      let folder = folders.next();
      let name = getName();
    }


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

    Вкратце как то так
    Ответ написан
    4 комментария
  • Автоматизация работы со строками?

    ProgrammerForever
    @ProgrammerForever Куратор тега Google Apps Script
    Учитель, автоэлектрик, программист, музыкант
    Жёстко, металлично (с)
    Можно сделать и так:
    =IMPORTRANGE("https://docs.google.com/spreadsheets/d/"; "Показатель!C2:C")


    А если хочется всё-таки 1000+ формул, то как-то так:
    const formulaTemplate = '=IMPORTRANGE("https://docs.google.com/spreadsheets/d/"; "Показатель!C2")';
    let range = spreadsheet.getRange('C2:C1240');
    let formulas = range.getFormulas();
    formulas = formulas.map((row,ri)=>row.map(f=>formulaTemplate.replace('!C2', '!C'+(ri+2))));
    range.setFormulas(formulas);
    Ответ написан
    Комментировать
  • Как выбрать ячейки в google sheets в которых есть запятая?

    ProgrammerForever
    @ProgrammerForever Куратор тега Google Sheets
    Учитель, автоэлектрик, программист, музыкант
    1. выбрать ячейки, в которых есть запятая

    =filter(A2:A;REGEXMATCH(A2:A;"\,"))
    2. выбрать ячейки в которых только одно слово

    =ArrayFormula(filter(A2:A;REGEXMATCH(""&A2:A;"^[0-9a-zA-Zа-я-А-ЯёЁ]+$")))

    или
    =ArrayFormula(filter(A2:A;REGEXMATCH(""&A2:A;"^\S+$")))
    Ответ написан
    Комментировать
  • Как сделать автоматическое заполнение текста в таблицах?

    ProgrammerForever
    @ProgrammerForever Куратор тега Google Sheets
    Учитель, автоэлектрик, программист, музыкант
    Для отсеивания используйте FILTER(таблица; условие1; условие2...)
    Для прокидывания данных их другого документа IMPORTRANGE(url_таблицы; диапазон)
    Ответ написан
    1 комментарий
  • Как в Google Sheets сопоставить две колонки и найти последнее соответствие?

    @ilyakanavin
    Вроде получилось сделать то, что вы хотели, правда через небольшой костыль.

    Я добавил вспомогательный лист "Контроль авто (sort)". Там вставлена одна единственная функция: =sort('Контроль авто'!A2:G ; 1 ; false). Она сортирует записи в порядке убывания по датам. То есть первая запись самая новая.

    И уже на основе этого листа я вставил на лист "Авто" формулы: =index(' Контроль авто (sort)'!$A$1:$G$17 ; match($A2 ; ' Контроль авто (sort)'!$C$1:$C$17 ; 0) ; 5) для определения того, кто последний взял авто и =index(' Контроль авто (sort)'!$A$1:$G$17 ; match($A2 ; ' Контроль авто (sort)'!$C$1:$C$17 ; 0) ; 7) для определения статуса заказа.

    Также, на лист "Авто" добавил столбец со статусом. Там работает формула =iferror(ifs(B2 = "Сдал" ; "Свободна" ; B2 = "Взял" ; "Занята") ; "")

    Все эти изменения сделал у вас таблице, так что можете там все и проверить
    Ответ написан
    2 комментария
  • Как скриптом запретить перенос ячейки, но при этом разрешить копирование и очищение ячейки?

    @newxdigital Автор вопроса
    ilya kanavin , благодарю за вариант с =INDIRECT("A1"). Он, действительно работает, однако очень сильно падает производительность таблицы, так как функция INDIRECT запускается каждый раз при изменении любой ячейки в листе, а таких ссылок в нем более десятка тысяч.

    Однако обдумав комментарий Alexander Ivanov (благодарю за грамотное объяснение), возникла идея создать решение не на стороне интерфейса, а на стороне сервера.

    Что если сделать скрипт на стороне сервера, который, например, каждые 2 часа проходит по таблице (заранее заданным строкам), находит ячейки с формулами и просто перезаписывает в них правильные формулы? Если брать наш пример, в котором ячейку А1 сдвинули на ячейку А2 и ссылки сбились:
    6018ef476dc06604708054.png
    с точки зрения формул выглядит так:
    6018ef6ac3f00530907245.png
    В нем формулы первой строки теперь указывают на ячейку второй строки (что для нашей задачи неверно), а формулы второй строки стали битыми (что для нашей задачи также неверно).

    Поэтому можно сделать скрипт, который на стороне сервера прошелся по этим двум строкам и сделал следующее:
    6018efc2d4728322078936.png
    это будет выглядеть вот так:
    6018efd6e3a5c950482650.png
    результат получился такой: формулы в первой строке вновь ссылаются на ячейку А1, а формулы второй строки на ячейку А2.

    Если брать более приближенный пример к нашей задачи с несколькими тысячами ссылок, то у нас есть блок ячеек с исходной информацией B2:F4 и далее множество аналогичных по размеру колонок и строк блоков с ячейками, которые ссылаются на эти исходные ячейки
    6018fa1d8c341578624567.png

    скрипту необходимо будет пробежать по всем ячейкам этих строк и заменить формулы на новые формулы по схеме знак равно "=" + "Название столбца, на который ссылается ячейка" + "Номер строки, в которой находится эта текущая ячейка"

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

    Что скажете насчет такого варианта?
    Ответ написан
    4 комментария
  • Как скриптом запретить перенос ячейки, но при этом разрешить копирование и очищение ячейки?

    @ilyakanavin
    В целом, я согласен с Александром.
    Скрипт, действительно, не может сделать то, что вы хотите

    Но есть одна функция, которая косвенно может решить вашу проблему. Это функция ДВССЫЛ или INDIRECT.
    Если вы вместо =$A$1 напишете =INDIRECT("A1"), то формула всегда будет брать значения из ячейки A1, даже если она будет передвинута или, например, будет добавлен столбец или строка
    Ответ написан
    3 комментария
  • Как прописать свою формулу для условного форматирования?

    ProgrammerForever
    @ProgrammerForever Куратор тега Google Sheets
    Учитель, автоэлектрик, программист, музыкант
    =И(
         ДВСCЫЛ("RC3";0)=1;
         СТОЛБЕЦ(ДВСCЫЛ("RC";0)<=СТОЛБЕЦ($O$1)
      )

    ,где ДВСCЫЛ("RC3";0)=1 - 3 столбец (C)
    ДВСCЫЛ("RC3";0)=1 - =100%
    СТОЛБЕЦ(ДВСCЫЛ("RC";0)<=СТОЛБЕЦ($O$1) - до столбца "О" включительно.
    Ответ написан
    Комментировать
  • Как в функцию QUERY добавить новые Col?

    @kishkin
    P - это колонка за горизонтом, то есть буква, которая будет у самой правой колонки, если добавить ещё одну. У вас последняя имеющаяся - O. Поэтому следующая - P.

    =QUERY(
      ARRAYFORMULA(
        {
          QUERY(
            FLATTEN(IFERROR(SPLIT('Данные'!B:B, ", ", 0))),
            "WHERE Col1 IS NOT NULL",
            0
          ),
          VLOOKUP(
            QUERY(
              FLATTEN(IF(IFERROR(SPLIT('Данные'!B:B, ", ", 0)) = "",, ROW('Данные'!A:A))),
              "WHERE Col1 IS NOT NULL",
              0
            ),
            {
              ROW('Данные'!A:A),
              'Данные'!A:A,
              'Данные'!C:P
            },
            SEQUENCE(1, COLUMNS('Данные'!C:P) + 1, 2),
            0
          )
        }
      ),
        "SELECT Col2, Col1, Col" & JOIN(", Col", SEQUENCE(1, COLUMNS('Данные'!C:P), 3)) & "
         FORMAT Col2 'yyyy-mm-dd hh:mm:ss'",
      1
    )
    Ответ написан
    Комментировать
  • Как при заполнении таблицы через форму, столбцы с заранее прописанными формулами не очищались?

    ProgrammerForever
    @ProgrammerForever Куратор тега Google Sheets
    Учитель, автоэлектрик, программист, музыкант
    Перенесите данные из листа формы на другой лист, и там с ними работайте.
    =ARRAYFORMULA(Ответы!A:Z)

    Или вместо обычной формулы используйте "массовую", для всего столбца
    =ARRAYFORMULA("Т."&SEQUENCE(СЧЁТЗ(B:B)-1;1;1)&"-21")
    Ответ написан
    Комментировать
  • Как сделать автоматическое изменение ячейки каждый день в google sheets?

    Coolbeat
    @Coolbeat
    Количество рабочих дней в текущем месяце можно рассчитать формулой:
    =NETWORKDAYS.INTL(EOMONTH(TODAY();-1)+1;EOMONTH(TODAY();0);"0000001";F1:F)

    где в предпоследнем аргументе "0000001" можно конкретно указать какой из дней недели выходной (в моем примере - 7й день, отмеченный единицей). В качестве последнего аргумента указан диапазон, в к-рый нужно в столбец вписать все празничные даты.
    В ячейке В2 следующей формулой вычисляем количество отработанных дней до сегодняшнего дня (с учетом выходных и праздничных):
    =NETWORKDAYS.INTL(EOMONTH(TODAY();-1)+1;TODAY();"0000001";F1:F)
    Ответ написан
    1 комментарий
  • Как сделать рассылку через gsuite?

    YuriyGarasyuk
    @YuriyGarasyuk
    Google Cloud Partner
    Здравствуте.
    Без написання кода подобную рассылку можно организовать на основе Google Групп

    В Консоли администратора создайте Группу (у нее будет свой адрес на основе Вашего домена), добавьте в Группу список адресов (можно синхронизировать добавление с Таблицей), и, просто, отправьте Ваш рекламный букет на адрес Группы (все, кто входит в Группу получат ваше письмо) - https://support.google.com/groups/answer/2464926?hl=ru

    У получателей будет возможность отписаться от Вашей рассылки.

    Ещё, крайне желательно дополнительно настроить SPF и DKIM для домена - https://support.google.com/a/answer/33786?hl=ru
    https://support.google.com/a/answer/174124?hl=ru

    Спасибо.
    Ответ написан
    3 комментария
  • Как на Linux безопасно настроить синхронизацию Google Drive?

    Zoominger
    @Zoominger
    System Integrator
    Но работая с закрытыми утилитами, есть риск утечки токена...

    Тогда лучше выключить ПК и отсоединить от сети, потому что половина драйверов в Linux - закрытые блобы.

    Оффициально клиента гугл, по какой то причине делать не хочет...

    Действительно, чего это они не рвут пятую точку ради аж 0,01% пользователей?

    Вот и вопрос, чем пользоваться что бы это было максимально безопасно.

    Официальный клиент и используйте, не переживайте.
    Ответ написан
    4 комментария