Ответы пользователя по тегу Google Apps Script
  • Как использовать модуль написанный на PHP в google script?

    oshliaer
    @oshliaer
    Google Products Expert
    Никак. Google Apps Script - это язык программирования. Даже не все модули nodejs можно сразу поднять в среде скриптов.

    Максимально без заморочек:
    • Опубликуйте веб-сервер php с нужным модулем и обвязкой по параметрам
    • Делайте запросы из скриптов
    Ответ написан
  • Как воспользоваться данными из файла по ссылке с помощью скрипта?

    oshliaer
    @oshliaer Куратор тега Google Sheets
    Google Products Expert
    /**
     * Get currencies by a date
     */
    function run() {
      const httpResponse = UrlFetchApp.fetch(
        'https://www.cnb.cz/cs/financni-trhy/devizovy-trh/kurzy-devizoveho-trhu/kurzy-devizoveho-trhu/denni_kurz.txt?date=20.05.2020'
      );
      const data = Utilities.newBlob(httpResponse.getContent(), 'plain/text')
        .getDataAsString()
        .split('\n')
        .map((line) => line.split('|')) 
        .slice(1);
      console.log(data);
      /*
      // Paste to a sheet
      sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
      */
    }


    https://github.com/contributorpw/google-apps-scrip...
    Ответ написан
  • Как скопировать данные из ячейки в Google Sheets в другую ячейку c помощью скриптов, если в исходной ячейке находитсяформула?

    oshliaer
    @oshliaer Куратор тега Google Sheets
    Google Products Expert
    Порядок выполнения процедур нарушен. Сначала надо заменить все результаты на значения, а потом удалять листы.

    Вот универсальный сниппет. Экспортирует лист/листы в разные форматы

    • spec - массив имен листов, которые нужно экспортировать
    • spreadsheet - копия Таблицы для экспорта


    /**
     *
     */
    function runSheet() {
      const spec = ['COUNTIF']; // List of sheets for export
    
      const spreadsheet = SpreadsheetApp.openById(
        '1TpHUfTvA7xBi4TLnWaplGasDumauA3YyMgXjXeQ2cyo'
      ).copy('tmp');
    
      spec.forEach(sheetName => {
        const dr = spreadsheet.getSheetByName(sheetName).getDataRange();
        dr.setValues(dr.getValues());
      });
    
      spreadsheet.getSheets().forEach(sheet => {
        if (spec.indexOf(sheet.getName()) < 0) spreadsheet.deleteSheet(sheet);
      });
    
      const spreadsheetId = spreadsheet.getId();
    
      const file = exportSpreadsheetToFile_(spreadsheetId, 'xlsx');
    
      DriveApp.getFileById(spreadsheetId).setTrashed(true);
    
      return file;
    }


    exportSpreadsheetToFile_ можно найти тут google-apps-script-snippets/standalone/export_spre...
    Ответ написан
  • Как пересчитать формулы по запросу?

    oshliaer
    @oshliaer Куратор тега Google Sheets
    Google Products Expert
    Нет. Все формулы участвуют в одном цикле событий.

    Вы можете только заставить полностью пересчитаться всю Таблицу, вызвав SpreadsheetApp.flush().

    В пользовательских формулах от flush толку не будет. В этих формулах так же не работают set-операторы.

    Использование CacheService мало полезно

    5ecf2d47878ae972525511.png

    Перевел весь скрипт в удобоваримый вид с сделал небольшой пример работы такой функции

    /**
     * @OnlyCurrentDoc
     */
    
    /**
     *
     * The TESTCACHESERVICE function
     *
     * @param {param} param
     * @return {number}
     * @customfunction
     */
    function TESTCACHESERVICE(param) {
      const cache = CacheService.getScriptCache();
      const cached = param === 'break' ? 0 : +cache.get('cached') + 1 || 0;
      cache.put('cached', cached);
      return cached;
    }


    Пример в Таблице https://docs.google.com/spreadsheets/d/11Z1pT2y28B...
    Ответ написан
  • Как автоматически создать копию листа Таблицы?

    oshliaer
    @oshliaer Куратор тега Google Sheets
    Google Products Expert
    Вам потребуется Google Apps Script для запуска программы из меню Таблицы.

    Создаем меню

    function onOpen() {
      SpreadsheetApp.getUi()
        .createMenu('Reset sheet')
        .addItem('Make a copy before reset', 'userActionMakeCopyBeforeReset')
        .addToUi();
    }


    Действие пользователя

    function userActionMakeCopyBeforeReset() {
      const book = SpreadsheetApp.getActive();
      const sheet = book.getActiveSheet();
      makeCopyBeforeReset_(sheet, book);
    }


    Копирование и сброс листа-шаблона

    function makeCopyBeforeReset_(sheet, book) {
      const copy = sheet.copyTo(book);
      const rangesAddressesList = ['B5', 'B7', 'B9', 'B11'];
      resetByRangesList_(sheet, rangesAddressesList);
      return copy;
    }


    Основной метод сброса данных

    function resetByRangesList_(sheet, rangesAddressesList) {
      sheet.getRangeList(rangesAddressesList).clearContent();
    }


    Собрав это все, вы должны получить программу как на видео https://twitter.com/i/status/1260851838942957574
    Пример в Таблице https://docs.google.com/spreadsheets/d/1g8cCxofljF...

    ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
    Alexander Ivanov
    contributor.pw Google Apps & API. Скрипты, Таблицы...
    Ответ написан
  • Как в Utilites.formatDate() задать русскую локаль?

    oshliaer
    @oshliaer Куратор тега Google Sheets
    Google Products Expert
    Не помню, чтобы Utilities поддерживали локали.

    Для V8 можно использовать DateTimeFormat

    function myFormatDate(date) {
      const y = new Intl.DateTimeFormat('ru',{
        year: 'numeric'
      }).format(date)
      const m = new Intl.DateTimeFormat('ru',{
        month: 'long'
      }).format(date)
      return `${m}-${y}`
    }
    
    function test(){
      const date = new Date();
      console.log(Utilities.formatDate(date, "GMT+3", "MMM-yyyy"));
      console.log(myFormatDate(date));
    }


    По идее, не нужно беспокоиться о производительности в таких местах, в основном они хорошо оптимизируются системой.

    Обратите внимание на свой пример, "MMM-d" не вернет год - вернет дату.
    Ответ написан
  • Как форматировать результаты скрипта для вывода?

    oshliaer
    @oshliaer Куратор тега Google Sheets
    Google Products Expert
    Рекомендую использовать метод getDisplayValues()

    var drr = sheet.getRange(sheet.getLastRow(), 1, 1, 3).getDisplayValues()[0];


    т.о. вам не придется беспокоится о типах данных в коде, и будет достаточно привести тип данных в ячейке. Если в ячейке будет видно 0.00%, то вернет именно 0.00%, если 1-02-20, то 1-02-20.

    Это наименьшее и наиболее надежное решение в данном случае.
    Ответ написан
  • Как обратиться к параметру в квадратных скобках POST запроса?

    oshliaer
    @oshliaer
    Google Products Expert
    Обратите внимание, что передается x-www-form-urlencoded. При получении аргумента формы вы должны "преобразовать" его к требуемому формату. В вашем случае

    const payments = JSON.parse(e.parameter.payments);

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

    /**
     *
     * @param {GoogleAppsScript.Events.DoPost} e
     */
    function doPost(e) {
      const payments = JSON.parse(e.parameter.payments);
      return ContentService.createTextOutput(JSON.stringify(payments.products[0]));
    }


    5eb3a269ca4ea797180580.png

    Если строка запроса более интересная, то лучше всего использовать сборку для gas из https://github.com/sindresorhus/query-string.

    Более простое представление можно взять тут form2json.js

    Соответственно, чтобы получить в doPost объект нужно сделать
    form2Json(e.postData.contents)

    Пример моего приложения
    /* global form2Json */
    /* exported doPost */
    /**
     *
     * @param {GoogleAppsScript.Events.DoPost} e
     */
    function doPost(e) {
      // const payments = JSON.parse(e.parameter.payments);
      return ContentService.createTextOutput(
        JSON.stringify(form2Json(e.postData.contents), null, '  ')
      );
    }


    5eb51b8925e51758421765.png

    Обратите внимание, что возвращается не массив, а объект с индексами. Но это не мешает работать

    const data = form2Json(contents);
    console.log(JSON.stringify(data.payment.products[0]));

    ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
    Alexander Ivanov
    contributor.pw Google Apps & API. Скрипты, Таблицы...
    Ответ написан
  • Как в функции закрыть и открыть доступ на редактирования в листе ОДНОЙ ячейки?

    oshliaer
    @oshliaer Куратор тега Google Sheets
    Google Products Expert
    Этого нельзя сделать. Ну, или можно, но совсем ерунда получится.

    Для перехвата "даблклика" вы должны блокировать повторное исполнение кода до момента исполнения текущего. Я обычно для этого использую LockService.

    Скрипты - это REST. REST - это пошаговое изменение состояния. Никаких "кликов" "setTimeout" и пр. в Таблицах нет. А еще там нет доступа к интерфейсу, лишь небольшие вкрапления фреймов на GAS в разрешенные области приложения (алерт, диалог, сайдбар, меню, тост).
    Ответ написан
  • Вопрос о правах доступа, или как защищаются google табличные скрипты?

    oshliaer
    @oshliaer Куратор тега Google Sheets
    Google Products Expert
    Скрипты никак не связаны с ограничением на ввод в ячейку, потому что скрипты это REST.
    • Если вы удалите скрипт, то защита ячеек никак не изменится.
    • Если вы попробуете изменить скрипт так, чтобы значение защищенных (условно от вас) ячеек изменилось, то сработает защита.
    • Если вы измените скрипт пользовательской функции так, чтобы она возвращала другое значение, то в защищенной ячейке она будет возвращать другое значение, но само значение ячейки по прежнему не изменить.


    По моему мнению (а ровно и по мнению Гугл) никаких логических противоречий.

    Ни в каких ячейках никаких скриптов не находится. Google Apps Script - это синтаксический сахар для Google REST API.

    К сожалению, вопрос поставлен так, что тут нельзя дать и хороший и правильный ответ. Если коротко, то вам нужно изменить свое отношение к этой парадигме. Это просто работает не так, как вы себе представляете. Для познающего это нормально.

    Если конкретно о защите кода, вне свойств Таблиц, то существуют механизмы сокрытия и распространения защищенного кода. Например, дополнения позволяют "встраивать код в Таблицу", при этом пользователь не может его изменить, он даже не видит этот код.

    Особенно отмечу бессмысленность сравнения VBA и GAS.
    Ответ написан
  • Скрипты в Google Sheets, как начать с нуля?

    oshliaer
    @oshliaer Куратор тега Google Sheets
    Google Products Expert

    ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
    Alexander Ivanov
    contributor.pw Google Apps & API. Скрипты, Таблицы...
    Ответ написан
  • Почему программа не срабатывает так, как ожидается при изменении всего одной типовой команды?

    oshliaer
    @oshliaer Куратор тега Google Sheets
    Google Products Expert
    Перед тем, как копировать формулы в значения вам нужно завершить все расчеты в Таблице.

    Вызовите flush()

    SpreadsheetApp.flush();

    Например,

    /**
     *
     * @param {GoogleAppsScript.Events.SheetsOnEdit} e
     */
    function onEdit(e) {
      var activeSheet = e.source.getActiveSheet();
      SpreadsheetApp.getActive().toast(['Лист'].indexOf(activeSheet.getName()));
      if (['Лист'].indexOf(activeSheet.getName()) == -1) return;
      var r = SpreadsheetApp.getActiveRange();
      var cols = r.getColumn();
      if (cols == 6) {
        // если изменяем 6 колонку, то тригер работает и:
        var cell1 = activeSheet.getRange(2, 2); // во второй колонке берет данные из второй строки (там лежит формула)
        var destination1 = activeSheet.getRange(activeSheet.getLastRow(), 2); // находит последнюю строку с данными и выбирает вторую колонку
    
        cell1.copyTo(destination1); // копирует из второй строчки в последнюю
        var cell2 = activeSheet.getRange(2, 3); // тоже самое но только для 3 колонки
        var destination2 = activeSheet.getRange(activeSheet.getLastRow(), 3); // тоже самое но только для 3 колонки
        cell2.copyTo(destination2); // тоже самое но только для 3 колонки
        var cell3 = activeSheet.getRange(2, 5); // тоже самое но только для 5 колонки
        var destination3 = activeSheet.getRange(activeSheet.getLastRow(), 5); // тоже самое но только для 5 колонки
        cell3.copyTo(destination3); // тоже самое но только для 5 колонки
        var cell4 = activeSheet.getRange(2, 8); // тоже самое но только для 8 колонки
        var destination4 = activeSheet.getRange(activeSheet.getLastRow(), 8); // тоже самое но только для 8 колонки
        cell4.copyTo(destination4); // тоже самое но только для 8 колонки
    
        SpreadsheetApp.flush();
    
        destination1.copyTo(
          destination1,
          SpreadsheetApp.CopyPasteType.PASTE_VALUES,
          false
        ); // во второй колонке меняет формулу на значение
        destination2.copyTo(
          destination2,
          SpreadsheetApp.CopyPasteType.PASTE_VALUES,
          false
        ); // в третьей колонке меняет формулу на значение
        destination3.copyTo(
          destination3,
          SpreadsheetApp.CopyPasteType.PASTE_VALUES,
          false
        ); // в пятой колонке меняет формулу на значение
        destination4.copyTo(
          destination4,
          SpreadsheetApp.CopyPasteType.PASTE_VALUES,
          false
        ); // в восьмой колонке меняет формулу на значение
        SpreadsheetApp.getActive().toast('Готово');
      }
    }
    Ответ написан
  • Иерархия файлов в проекте?

    oshliaer
    @oshliaer
    Google Products Expert
    Это зависит от версии среды исполнения V8, STABLE, DEPRECATED_ES5.

    Для V8 порядок файлов однозначно не играет роли и даже не все файлы загружаются для исполнения. Поэтому лучше избегать этой условности.

    В STABLE, DEPRECATED_ES5 порядок обычно зависит от алфавитного расположения файлов, но это негарантировано.

    Что происходит у вас.

    В одном проекте есть два файла код.gs и код2.gs. В обоих есть функция onOpen. При загрузке текста программы в исполнительную среду одна функция перезапишет другую и исполняться на самом деле будет только одна функция, т.к. другой нет и в помине (она перезаписана). Какая функция будет перезаписана, это зависит от среды (см. выше).

    Что делать.

    Одним из решений может быть создание одной функции onOpen и вызова из нее двух других

    function onOpen(e){
      onOpen1(e);
      onOpen2(e);
    }
    
    function onOpen1(e){
      ...
    }
    
    function onOpen2(e){
      ...
    }


    Или же вы можете создать еще один проект в том же контейнере [Таблице] (см. меню текущего проекта).

    ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
    Alexander Ivanov
    contributor.pw Google Apps & API. Скрипты, Таблицы...
    Ответ написан
  • Как открыть Таблицу по ссылке после ее создания?

    oshliaer
    @oshliaer Куратор тега Google Sheets
    Google Products Expert
    Все довольно просто.

    Передайте значение ss в функцию

    /**
     * Open the url in a new tab
     * @param {string} url
     */
    function openUrlInBrowser_(url) {
      const tmp = HtmlService.createTemplateFromFile('app');
      tmp.url = url;
      const htmlOutput = tmp.evaluate();
      SpreadsheetApp.getUi().showModelessDialog(htmlOutput, 'Opening url ...');
    }


    Эта функция требует наличие файла app.html в проекте

    /**
     * Open the url in a new tab
     * @param {string} url
     */
    function openUrlInBrowser_(url) {
      const tmp = HtmlService.createTemplateFromFile('app');
      tmp.url = url;
      const htmlOutput = tmp.evaluate();
      SpreadsheetApp.getUi().showModelessDialog(htmlOutput, 'Opening url ...');
    }


    5e9f194323664331291861.gif

    Полный код https://github.com/contributorpw/google-apps-scrip...
    Ответ написан
  • Как сравнить даты в разных ячейках с помощью скрипта?

    oshliaer
    @oshliaer Куратор тега Google Sheets
    Google Products Expert
    Оно и не должно "пахать". Это не лошадь.

    Я попробовал без примера понять, что делает этот код, и пришел к выводу, что это все надо менять хотя бы на

    const values = s.getRange(1, col1, s.getLastRow(), col1).getValues();
    const count = values.filter(
      (row, i, arr) => arr[i + 1] && compareAsDate_(row[0], arr[i + 1][0])
    );
    console.log(count);


    Функция сравнения дат

    /**
     *
     * @param {any} a
     * @param {any} b
     */
    function compareAsDate_(a, b) {
      return (
        a && b && a.getTime && b.getTime && a.toDateString() === b.toDateString()
      );
    }


    ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
    Alexander Ivanov
    contributor.pw Google Apps & API. Скрипты, Таблицы...
    Ответ написан
  • Почему не удаётся войти в редактор скриптов Google Sheets из Chrome?

    oshliaer
    @oshliaer Куратор тега Google Sheets
    Google Products Expert
    Для диагностики необходимо:
    • Изменить подключение
    • Изменить приложение
    • Изменить аккаунт/доступ
    • Изменить режим браузера, например, запустить проблемную страницу в Инкогнито


    Если проблемы именно с Хромом, начните по порядку, пока проблема не прояснится:
    1. Учтите, что Гугл сервисы официально поддерживают только Google Chrome, FF, Safari, Edge (Браузер от Я и прочее - это не то)
    2. Запустите в Инкогнито
    3. Отключите расширения
    4. Очистите кеш и куки
    5. Удалите папку Cache при закрытом Google Chrome. Для моей ОС эта папка находится по пути ~/.cache/google-chrome/Default/Cache


    С уважением.
    Ответ написан
  • Как одновременно редактировать несколько листов?

    oshliaer
    @oshliaer Куратор тега Google Sheets
    Google Products Expert
    К сожалению, даже программными способами не все форматирование копируется.

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

    Попробуйте записать макрос.

    ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
    Alexander Ivanov
    contributor.pw Google Apps & API. Скрипты, Таблицы...
    Ответ написан
  • Существуют ли инструменты, позволяющие создавать пункты списка в выпадающем списке в Google таблицах?

    oshliaer
    @oshliaer Куратор тега Google Sheets
    Google Products Expert
    Как вариант, можно использовать самонаполняющийся список.

    Это такой список, который строится из данных, которые вводит пользователь. А выпадающие списки строятся уже на его основе.

    5e7b57caa1cd7233254630.png

    Пример Таблицы

    ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
    Alexander Ivanov @oshliaer
    contributor.pw Google Apps & API. Скрипты, Таблицы...
    Ответ написан
  • Google Apps Script Jdbc.getConnection как подключиться к MySQL?

    oshliaer
    @oshliaer
    Google Products Expert
    Классическая версия

    var conn = Jdbc.getConnection(
      'jdbc:mysql://sql4.freemysqlhosting.net:3306/sql427628?useUnicode=true&characterEncoding=UTF-8',
      'sql427628', '****');
    var stmt = conn.createStatement();
    stmt.setMaxRows(1000);
    var start = new Date();
    var rs = stmt.executeQuery('select * from person');
    
    var row = 0;
    while (rs.next())
      for (var col = 0; col < rs.getMetaData().getColumnCount(); col++)
        console.log(rs.getString(col + 1));
    rs.close();
    stmt.close();
    conn.close();


    Насколько я помню, у https://www.db4free.net есть какая-то особенность в плане имени базы данных - она находится не в корне.

    С уважением,
    contributor.pw
    Ответ написан
  • Как передать аргументы в функцию, вызванную из меню?

    oshliaer
    @oshliaer Куратор тега Google Sheets
    Google Products Expert
    Допустим, у вас есть описание меню для некоторой функции

    var MENU = [
      {
        caption: 'Пункт меню 1',
        functionName: 'itemMenu',
      },
      {
        caption: 'Пункт меню 2',
        functionName: 'itemMenu',
      },
      {
        caption: 'Пункт меню 3',
        functionName: 'itemMenu',
      },
    ];


    Очевидно, что вы помимо основного меню сможете построить и меню из этого массива

    function onOpen() {
      var ui = SpreadsheetApp.getUi();
    
      var menu = ui.createMenu('Test');
    
      MENU.forEach(function(item, i) {
        menu.addItem(item.caption, item.functionName + i);
      });
    
      menu.addToUi();
    }


    Положим, что itemMenu работает как-то так

    function itemMenu(e) {
      var caption = e.item.caption;
      var order = e.order;
      Browser.msgBox(
        Utilities.formatString('Был нажат %sй пункт меню: %s', order + 1, caption)
      );
    }


    Тогда можно передать глобальному контексту обернутые колбэки с заданным аргументом

    (function(self) {
      MENU.forEach(function(item, i) {
        self[item.functionName + i] = function() {
          return self[item.functionName]({ item: item, order: i });
        };
      });
    })(this);


    Полный листинг тут

    5e5bcdbc5dd9e315037179.png

    С уважением,
    contributor.pw
    Ответ написан