Ответы пользователя по тегу Google Sheets
  • Как добавить новую строку, чтобы ячейка в стоблце А была сразу заполненной?

    ProgrammerForever
    @ProgrammerForever Куратор тега Google Sheets
    Учитель, автоэлектрик, программист, музыкант
    Можно как-то так
    Но почти всегда можно обычную формулу преобразовать в массовую, с помощью ARRAYFORMULA()
    Ответ написан
  • Как вывести ТОП 10 повторяющихся значений из списка (столбца)?

    ProgrammerForever
    @ProgrammerForever Куратор тега Google Sheets
    Учитель, автоэлектрик, программист, музыкант
    =QUERY({A1:A};"SELECT Col1, Count(Col1) GROUP BY Col1 ORDER BY Count(Col1) DESC LIMIT 10";0)

    Демонстрационная таблица
    Ответ написан
  • Как спарсить товары с Леруа Мерлен через гугл-таблицы?

    ProgrammerForever
    @ProgrammerForever Куратор тега Google Sheets
    Учитель, автоэлектрик, программист, музыкант
    Заходите по F12 в консоль браузера и смотрите какие запросы шлёт. Уверен, что данные получаются по API, а не содержатся в теле страницы.
    Попробуйте для начала такую конструкцию:
    /**
    * Открывает URL и возращает код страницы
    * Telegram - @ProgrammerForever
    *
    * @param {string} URL URL который нужно открыть
    * @param {boolean} isCut Указывакт, нужно ли обрезать страницу до 50000 символов по длине, по умолчанию false
    * @param {boolean} noScript Указывакт, нужно ли удалять скрипты из кода
    * @return Исходный код страницы
    * @customfunction
    */
    function getHTML(URL,isCut,noScript) {
      if ((URL === undefined)||(URL == "")) { return "#ОШИБКА Пустой URL";};
      if (isCut === undefined) {var isCut=true;};
      if (noScript === undefined) {var noScript=true;};
      if (URL.map){     //Если задан диапазон
        return URL.map(getHTML);
      }else{
        try {
          var payload = {
            'rand':(new Date()).getTime()
          };
          var headers={
            'Connection': 'keep-alive',
            'Cache-Control': 'max-age=0',
            'Upgrade-Insecure-Requests': 1,
            'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.142 Safari/537.36',
            'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3',
            'Accept-Encoding': 'gzip, deflate, br',
            'Accept-Language': 'ru-RU,ru;q=0.9,en-US;q=0.8,en;q=0.7'
          };
          var options = {
            'method' : 'get',
            'headers' : headers,
            'payload': payload
          };
          
          var response = UrlFetchApp.fetch(URL,options);
          var charset=response.getAllHeaders["charset"];
          //var responseText=response.getContentText(charset?charset:"windows-1251");
          var responseText=response.getContentText(charset?charset:"UTF-8");
          if (noScript){ 
            responseText=responseText.replace(/<script[^>]*>(?:(?!<\/script>)[^])*<\/script>/gmi,"");
            responseText=responseText.replace(/<!--.*?-->/gmi,"");
            responseText=responseText.replace(/<link.*?\/>/gmi,"");
            responseText=responseText.replace(/<meta.*?\/>/gmi,"");
            responseText=responseText.replace(/[\n\r\t]/gmi,"");
            
          };
          if (isCut&&(responseText.length>50000)){return responseText.substring(0,50000);}else{return responseText;};
        } catch (err) {
          //return JSON.stringify(err);
          return "#ОШИБКА "+err.message;
        };
      };
    };

    Если данные нужные есть в выводе - можно парсить. Получить XPATH из той же консоли F12, и подставить в IMPORTXML().
    Ответ написан
    Комментировать
  • Как скрыть диапазоны по доступу в google sheets? Возможно ли?

    ProgrammerForever
    @ProgrammerForever Куратор тега Google Sheets
    Учитель, автоэлектрик, программист, музыкант
    Сделать отдельную таблицу (всё остальное - защита от хороших людей)
    Сделать скрипт, который будет копировать значения(метод getValues()) и форматирование(тут вариантов больше).
    Навесить на onEdit или на пункт в меню.
    Ответ написан
    Комментировать
  • Можно ли как-то заблокировать строку после нажатия флажка True?

    ProgrammerForever
    @ProgrammerForever Куратор тега Google Sheets
    Учитель, автоэлектрик, программист, музыкант
    Запустите макрорекордер в режиме относительных ссылок и заблокируйте строку. Так получите код для блокировки строки. Потом добавьте функцию onEdit(event) где проверьте значение на 'TRUE':
    if (event.value==="TRUE"){
    // Код для блокировки строки
    }
    Ответ написан
    7 комментариев
  • Скрипт автоматической подстановки текущей даты?

    ProgrammerForever
    @ProgrammerForever Куратор тега Google Sheets
    Учитель, автоэлектрик, программист, музыкант
    Как-то так:
    // Файл config.gs
    const config = {};
    
    config.onEditTrigger = {
      isAllowSheetName: sheetName=>(["Лист1", "Лист2", "Лист3"].includes(sheetName)),  // Лист - "Лист1", "Лист2" или "Лист23"
      isAllowColumn: column=>true, // колонка - любая
      isAllowRow: row=>(row>1), // строка - больше 1
      isAllowNewValue: value=>true, // новое значение - любое
      isAllowOldValue: value=>true, // старое значение - любое
      action: (ss, sheet, range, oldValue, newValue)=>{
    	  sheet.getRange(`A${range.getRow()}`).setValue(new Date());
      },
    };
    
    // Файл onEdit.gs
    function onEditTrigger(event) { // переименовать onEditTrigger в onEdit, если хватит simple триггера. Если нужен полноценный триггер - установить триггер на редактирование на эу функцию
      let conf = config.onEditTrigger;
      let ss = SpreadsheetApp.getActiveSpreadsheet();
    
      let sourceSheet = event.source.getActiveSheet();
      let row = event.range.getRow();
      let col = event.range.getColumn();
      let newValue = event.value;
      let oldValue = event.oldValue;
      
      if(
         conf.isAllowColumn(col) &&
         conf.isAllowRow(row) && 
         conf.isAllowNewValue(newValue) && 
         conf.isAllowOldValue(oldValue) && 
         conf.isAllowSheetName(sourceSheet.getName())
        ){
          conf.action(ss, sourceSheet, event.range, oldValue, newValue);
      };
    };

    Подправьте конфиг под свои нужды.
    Ответ написан
  • Как посчитать сумму последовательных ячеек?

    ProgrammerForever
    @ProgrammerForever Куратор тега Google Sheets
    Учитель, автоэлектрик, программист, музыкант
    =ArrayFormula(СУММ(FILTER(F15:F;0=ОСТАТ(СТРОКА(F15:F)-15;8))))

    PS: исправляйте структуру данных. Подозреваю, что там мешанина из сырых данных и динамических (сумм за неделю, например). Нормализуйте данные, чтобы было 1 строка=1 сущность, станет намного легче с ними работать
    Ответ написан
    3 комментария
  • Почему не отображается пользовательское меню на мобильном устройстве?

    ProgrammerForever
    @ProgrammerForever Куратор тега Google Sheets
    Учитель, автоэлектрик, программист, музыкант
    Можно сделать костыль - галочки и onEdit()
    Ответ написан
    Комментировать
  • Как перемешать строки так, чтобы их значения повторялись не менее чем через 4 строки?

    ProgrammerForever
    @ProgrammerForever Куратор тега Google Sheets
    Учитель, автоэлектрик, программист, музыкант
    1) Отсортировать
    2) Взять в порядке 1-5-10...2-6-11...3-7-12...
    const a = [1,2,2,3,34,54,3,4,45,34,53,45,4,1,23,12,3,235,2,5,1,2,6,76,54,6,84,5,23,2,34,6,735];
    
      let outData = [];
      const n = 5; // Ширина блока
      for(let offset=0; offset<n; offset++){
        for(let i=offset; i<a.length; i+=n){
          outData.push(a[i]);
        };
      };
    
      Logger.log(JSON.stringify(outData));
    //Проверка на то, что все элементы исходного массива включены
      Logger.log(JSON.stringify(outData.sort()));
      Logger.log(JSON.stringify(a.sort()));
    Ответ написан
    2 комментария
  • Некорректно подставляет значения формулы при её дублировании на другие ячейки, как исправить?

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

    • Если разделить 100 на столбец B, то
      =arrayformula(iferror(100/b:b))


    • Если хотите поменять в столбце A значения на формулу
      =значение/$B$1
      то можно скопировать в Notepad++, сделать замену
      (.*?)\r\n
      на
      =$1/\$b\$1\n
      Скопировать всё обратно и вставить в таблицу.

    Ответ написан
  • Как вывести данные с сайта c таблицами?

    ProgrammerForever
    @ProgrammerForever Куратор тега Google Sheets
    Учитель, автоэлектрик, программист, музыкант
    Попробуйте для начала такую конструкцию:
    /**
    * Открывает URL и возращает код страницы
    * Telegram - @ProgrammerForever
    *
    * @param {string} URL URL который нужно открыть
    * @param {boolean} isCut Указывакт, нужно ли обрезать страницу до 50000 символов по длине, по умолчанию false
    * @param {boolean} noScript Указывакт, нужно ли удалять скрипты из кода
    * @return Исходный код страницы
    * @customfunction
    */
    function getHTML(URL,isCut,noScript) {
      if ((URL === undefined)||(URL == "")) { return "#ОШИБКА Пустой URL";};
      if (isCut === undefined) {var isCut=true;};
      if (noScript === undefined) {var noScript=true;};
      if (URL.map){     //Если задан диапазон
        return URL.map(getHTML);
      }else{
        try {
          var payload = {
            'rand':(new Date()).getTime()
          };
          var headers={
            'Connection': 'keep-alive',
            'Cache-Control': 'max-age=0',
            'Upgrade-Insecure-Requests': 1,
            'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3770.142 Safari/537.36',
            'Accept': 'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3',
            'Accept-Encoding': 'gzip, deflate, br',
            'Accept-Language': 'ru-RU,ru;q=0.9,en-US;q=0.8,en;q=0.7'
          };
          var options = {
            'method' : 'get',
            'headers' : headers,
            'payload': payload
          };
          
          var response = UrlFetchApp.fetch(URL,options);
          var charset=response.getAllHeaders["charset"];
          //var responseText=response.getContentText(charset?charset:"windows-1251");
          var responseText=response.getContentText(charset?charset:"UTF-8");
          if (noScript){ 
            responseText=responseText.replace(/<script[^>]*>(?:(?!<\/script>)[^])*<\/script>/gmi,"");
            responseText=responseText.replace(/<!--.*?-->/gmi,"");
            responseText=responseText.replace(/<link.*?\/>/gmi,"");
            responseText=responseText.replace(/<meta.*?\/>/gmi,"");
            responseText=responseText.replace(/[\n\r\t]/gmi,"");
            
          };
          if (isCut&&(responseText.length>50000)){return responseText.substring(0,50000);}else{return responseText;};
        } catch (err) {
          //return JSON.stringify(err);
          return "#ОШИБКА "+err.message;
        };
      };
    };

    Если данные получаются вразумительные и то что нужно есть в выводе - то есть вариант распарсить. А так нужные данные могут грузиться дополнительными запросами или может стоять промежуточная защита, вплоть до капчи.
    Ответ написан
  • Как объединить текст из ячеек, которые содержат строки с курсивом?

    ProgrammerForever
    @ProgrammerForever Куратор тега Google Sheets
    Учитель, автоэлектрик, программист, музыкант
    Посмотрите в сторону RichTextValue
    Ответ написан
    Комментировать
  • Как вывести строки с одного листа на другой по условию?

    ProgrammerForever
    @ProgrammerForever Куратор тега Google Sheets
    Учитель, автоэлектрик, программист, музыкант
    =FILTER('Лист1'!A2:K90;'Лист1'!B2:B90="Турция")
    Ответ написан
    1 комментарий
  • Функция скрытия строки при одновременном соблюдении двух условий. Где ошибка?

    ProgrammerForever
    @ProgrammerForever Куратор тега Google Sheets
    Учитель, автоэлектрик, программист, музыкант
    (Колонка не 10) <b>ИЛИ</b> (значение не "выполнено")
    при этом
    (колонка не 13)<b> ИЛИ </b>(значение не "312")

    Странное условие. Сгруппируйте по-другому, это должно помочь.
    Я часто пользуюсь такой универсальной конструкцией:
    // Файл config.gs
    const config = {};
    
    config.onEditTrigger = {
      isAllowSheetName: sheetName=>(["1", "2", "3"].includes(sheetName)),  // Лист - "1", "2" или "3"
      isAllowColumn: column=>([11,12,13].includes(column)), // колонка - 11,12 или 13
      isAllowRow: row=>(row>1), // строка - больше 1
      isAllowNewValue: value=>(value==="OK"), // новое значение - "ОК"
      isAllowOldValue: value=>true, // старое значение - любое
    };
    
    // Файл onEdit.gs
    function onEditTrigger(event) { // переименовать onEditTrigger в onEdit, если хватит simple триггера. Если нужен полноценный триггер - установить триггер на редактирование на эу функцию
      let conf = config.onEditTrigger;
      let ss = SpreadsheetApp.getActiveSpreadsheet();
    
      let sourceSheet = event.source.getActiveSheet();
      let row = event.range.getRow();
      let col = event.range.getColumn();
      let newValue = event.value;
      let oldValue = event.oldValue;
      
      if(
         conf.isAllowColumn(col) &&
         conf.isAllowRow(row) && 
         conf.isAllowNewValue(newValue) && 
         conf.isAllowOldValue(oldValue) && 
         conf.isAllowSheetName(sourceSheet.getName())
        ){
          // что-то делаем
      };
    };

    Меняются условия работы в конфиге - для строки/столбца/имени листа/старого значения/нового значения.
    Ответ написан
    Комментировать
  • Как подсветить ячейки в таблице?

    ProgrammerForever
    @ProgrammerForever Куратор тега Google Sheets
    Учитель, автоэлектрик, программист, музыкант
    Примените условное форматирование к P:P
    =И($R1="";(СЕГОДНЯ()-$Q1)<=15)
    Демонстрационная таблица
    Ответ написан
    5 комментариев
  • Как добавить в список по алфавиту строку?

    ProgrammerForever
    @ProgrammerForever Куратор тега Google Sheets
    Учитель, автоэлектрик, программист, музыкант
    Зачем молотить на месте? Сделайте отдельный лист, где можно сделать с данными всё что захочется.
    Если вы хотите писать данные, относящиеся к строке с данными из формы в следующей строке - это плохая идея.
    Хорошее правило для данных в таблицах: 1 строка = 1 запись
    И ещё одно правило: не смешивать статические данные (те, что дописываются руками) и динамические(те, что выводятся формулой или скриптом, сортируются, фильтруются и т.п.).
    Судя по вопросу, тут оба этих правила нарушаются. С этим можно жить, но это сложно для реализации, трудноподдерживаемо, и гарантированно сломается.
    Ответ написан
    Комментировать
  • Как в гугл таблице получить совпадения?

    ProgrammerForever
    @ProgrammerForever Куратор тега Google Sheets
    Учитель, автоэлектрик, программист, музыкант
    Можно так:
    =filter({A1:A\C1:C\D1:D};ПОИСКПОЗ(A1:A;B1:B;0)>0)
    Демонстрационная таблица
    Ответ написан
    Комментировать
  • Как данные в Гугл таблице занести в Тильду?

    ProgrammerForever
    @ProgrammerForever Куратор тега Google Sheets
    Учитель, автоэлектрик, программист, музыкант
    • Использовать Sheets API
    • Сделать doGet() который берёт данные с таблицы и отдаёт JSON. Развернуть как веб-приложение. На сайте дёргать этот адрес.
    Ответ написан
    Комментировать
  • Как в гугл таблицы вставить что то наподобии mind map?

    ProgrammerForever
    @ProgrammerForever Куратор тега Google Sheets
    Учитель, автоэлектрик, программист, музыкант
    В draw.io есть возможнсть иморта-экспорта диаграмм через xml, пользовался этим для обработки диаграмм силами таблиц. А так посмотрите в сторону Google Charts API, возможно что-то такое там уже есть.
    Ответ написан
  • Как конкатенировать текст из разных ячеек?

    ProgrammerForever
    @ProgrammerForever Куратор тега Google Sheets
    Учитель, автоэлектрик, программист, музыкант
    Так:
    =СЦЕПИТЬ("admin";"@";"192.168.1.1";" ";"-p";" ";"999")

    или так:
    ="admin"&"@"&"192.168.1.1"&" "&"-p"&" "&"999"
    вместо данных - подставьте адреса ячеек
    Ответ написан
    Комментировать