Определите, как пользователь вставляет строку или столбец в таблицу Google и реагирует в сценарии.

В Google Apps Script одним из основных инструментов является триггер onEdit в электронной таблице, который позволяет нам определять, когда пользователь редактирует ячейку, и реагировать на нее.

Как насчет того, когда пользователь вставляет строку или столбец? Есть ли способ обнаружить это?

Будет ли это вызвать onEdit? Если это так, я предполагаю, что поддержание в ScriptDb счетчика количества строк или столбцов, а затем проверка каждый раз будет делать, но это будет очень затратным по времени, поскольку getMaxRows() уже довольно медленный, и обращение к ScriptDb выглядит как Что ж.

Как вы думаете?

Источник

5 ответов

Решение

Существует ряд действий редактирования, которые не запускаются onEdit(), это не полный список, есть еще много исключений:

Если вы хотите знать, сколько строк в электронной таблице, для выполнения потребуется около 120 мс:

var numCols = SpreadsheetApp.getActiveSheet().getRange("1:1").getLastColumn();
var numRows = SpreadsheetApp.getActiveSheet().getRange("A:A").getLastRow();

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

Таким образом, если вы сможете обнаружить добавляемую строку или столбец, регистрация изменения будет стоить вам менее двух десятых секунды. это onEdit() демонстрирует методику измерения экстента электронной таблицы и сообщает об изменениях размеров листа. (Чтобы проверить, добавить или удалить строки или столбцы, затем выполните редактирование, которое вызывает onEdit().) Он также содержит таймеры - не стесняйтесь экспериментировать с другими способами измерения и / или сохранения значений, чтобы увидеть, что работает лучше для вас.

function onEdit() {
  // Use start & stop to time operations
  var start = new Date().getTime();

  // We want the size of the sheet, so will select ranges across and down the
  // whole sheet. Cannot use getDataRange(), as it selects only occupied cells.
  var numCols = SpreadsheetApp.getActiveSheet().getRange("1:1").getLastColumn()
  var numRows = SpreadsheetApp.getActiveSheet().getRange("A:A").getLastRow();

  var stop = new Date().getTime();
  var timeToMeasure = (stop-start);

  // Did things change?
  var oldSize = SpreadsheetApp.getActiveSheet().getRange("A1:B1").getValues();
  if (oldSize[0][0] != numCols || oldSize[0][1] != numRows) {
    // Yes, they did - Let's store the new dimensions
    start = new Date().getTime();

    SpreadsheetApp.getActiveSheet().getRange("A1:B1").setValues([[numCols,numRows]]);

    var stop = new Date().getTime();
    var timeToStore = (stop-start);  

    Browser.msgBox("Sheet is "+numCols+" by "+numRows+"."
                  +" ("+timeToMeasure+"ms to measure, "+timeToStore+"ms to store.)");
  }
}
Источник

Google добавил событие "При изменении", которое обнаруживает вставку / удаление строки / столбца наряду с другими типами изменений, типы которых вы можете увидеть здесь под разрешенными значениями для changeType, Ниже приведены инструкции, подробно описывающие, как добавить триггер в ваш проект, чтобы вы могли вызывать свою функцию при возникновении события "При изменении".

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

  1. В редакторе сценариев выберите "Правка"> "Триггеры текущего проекта".
  2. Нажмите на ссылку с надписью:триггеры не настроены.Нажмите здесь, чтобы добавить его сейчас.
  3. Под Run выберите название функции, которую вы хотите вызвать.
  4. В разделе " События" выберите "Управление по времени" или "Приложение Google", к которому привязан скрипт (например, Из электронной таблицы).
  5. Выберите и настройте тип триггера, который вы хотите создать (например, таймер часов, который запускается каждый час, или триггер при открытии).
  6. При желании нажмите " Уведомления", чтобы настроить, как и когда с вами свяжутся по электронной почте, если сработала ваша функция.
  7. Нажмите Сохранить.

На шаге 4 вы должны выбрать " Из таблицы", а на шаге 5 - " При изменении". Это должно иметь эффект, который вы ищете. Существуют также варианты программного добавления триггеров и запроса авторизации, если вы пытаетесь использовать это в дополнении, которое будет распространяться среди пользователей. Оба подробно описаны в документации по устанавливаемым триггерам.

Источник

Есть еще один способ, которым я недавно воспользовался. Каждый раз, когда onEdit() запускается, он возвращает объект события (e), который дает вам некоторую ценную информацию о том, что происходит.

Например, он дает вам диапазон, который вы можете получить из e.range. Оттуда вы можете проходить разными способами и, например, узнать, какая строка редактируется. Но есть и другие полезные данные в объекте e. Он дает вам "oldvalue" (e.oldValue) отредактированной ячейки и новое значение (e.value).

Один из возможных способов смешать всю эту информацию - получить диапазон, соответствующий строке, которую вы редактируете, а затем проверить, все ли ячейки пусты (кроме той, которую вы только что отредактировали) и нет ли oldValue.

Это не обязательно соответствует последнему ряду вашей таблицы, но пустой строке. Если вы согласны с тем, как вы заполняете свои данные, это может сработать для вас:

//val = inserted value (e.value);
//old = old Value (e.oldValue);
//col = number of column being edited
//arr = array with the indexes of the columns that should be completed so as to make a new row [0,1,2...n]
function isInsert(old, val, col, arr){
   if((typeof val != "object")&&!old&&(arr.some(isNotEmpty, col)))
     return true;
   else
     return false;
}

function isNotEmpty(el){
   if(this == el)
     return true;
}
Источник

У меня были проблемы с этим, пока я не дал разрешения для сценария. В противном случае функциональность PropertiesService не будет работать. Как только я это сделал, я смог определить, какая строка была вставлена, с помощью следующего кода:

var props = PropertiesService.getUserProperties();

function onEdit(e) {
  props.setProperty("firstRow", e.range.getRow());
  props.setProperty("lastRow", e.range.getLastRow());
}


function onChange(e){
if(e.changeType=="INSERT_ROW")
    SpreadsheetApp.getUi().alert("Inserted Rows: " +
                                 props.getProperty("firstRow") + 
                                 " - " +
                                 props.getProperty("lastRow"));
}
Источник

Я играл с onEdit и onChange. Ответ onEdit позволяет получить доступ к отредактированным строкам. К сожалению, ответ onChange не позволяет вам сделать это. Таким образом, для надежного решения вам нужно обратиться к обоим триггерам. Если вашему листу не требуются пустые строки / столбцы, приведенный ниже скрипт удаляет все вновь добавленные строки / столбцы, удаляет все пустые строки / столбцы (в случае, если пользователь массово добавил строки / столбцы), а затем предупреждает пользователя о том, что они не могут добавлять строки или колонки:

//////////////////////
// Global Variables //
//////////////////////

var SHEET = SpreadsheetApp.getActiveSheet();
var PROPERTIES = PropertiesService.getScriptProperties();

////////////////////
// Event Triggers //
////////////////////

/**
 * Track original sheet row/column count and register onChange trigger.
 */
function onOpen()
{
    // Set original dimensions
    PROPERTIES.setProperty('rows', SHEET.getMaxRows());
    PROPERTIES.setProperty('columns', SHEET.getMaxColumns());

    // Create onChange trigger
    ScriptApp
        .newTrigger('deleteNewRowsAndColumns')
        .forSpreadsheet(SpreadsheetApp.getActive())
        .onChange()
        .create();
}

/**
 * If new rows or columns were added to the sheet
 * warn the user that they cannot perform these
 * actions and delete empty (new) rows and columns.
 *
 * @param e
 */
function deleteNewRowsAndColumns(e)
{
    switch(e.changeType) {
        case 'INSERT_COLUMN':
            removeEmptyColumns();
            warn();
            break;
        case 'INSERT_ROW':
            removeEmptyRows();
            warn();
            break;
        default:
            return
    }
}

///////////////
// Utilities //
///////////////

/**
 * Remove empty columns.
 *
 * This function assumes you have a header row in which
 * all columns should have a value. Change headerRow value
 * if your headers are not in row 1.
 */
function removeEmptyColumns() {
    var maxColumns = SHEET.getMaxColumns();
    var lastColumn = SHEET.getLastColumn();
    if (maxColumns - lastColumn != 0) {
        // New column(s) were added to the end of the sheet.
        SHEET.deleteColumns(lastColumn + 1, maxColumns - lastColumn);
    } else {
        // New column was added in the middle of the sheet.
        // Start from last column and work backwards, delete
        // first column found with empty header cell.
        var headerRow = 1;
        var headers =  SHEET.getRange(headerRow, 1, 1, lastColumn).getValues()[0];
        for (var col = lastColumn; col >= 1; col--) {
            if (headers[col -1] == '') {
                SHEET.deleteColumn(col);
                // Since can only insert one column to the left
                // or right at a time, can safely exit here;
                break;
            }
        }
    }
}

/**
 * Remove empty rows.
 *
 * This function assumes that all rows should
 * have data in the first cell.
 */
function removeEmptyRows() {
    var maxRows = SHEET.getMaxRows();
    var lastRow = SHEET.getLastRow();
    if (maxRows-lastRow != 0) {
        // New row(s) were added to the end of the sheet.
        SHEET.deleteRows(lastRow + 1, maxRows - lastRow);
    } else {
        // New row was added in the middle of the sheet.
        // Start from last column and work backwards, delete
        // first empty column found.
        var values = SHEET.getRange('A:A').getValues();
        var startIndex = values.length - 1;
        for (var i = startIndex; i >= 0; i--) {
            if (values[i] && values[i][0] == '') {
                SHEET.deleteRow(i + 1);
                // User can bulk add rows to the bottom of the file
                // but can only add 1 above or below at a time in the
                // middle of the file, so it's safe to exit here.
                break;
            }
        }
    }
}

/**
 * Return user warning message about adding new rows and columns
 */
function warn()
{
    SpreadsheetApp.getUi().alert('You cannot add new rows or columns.');
}
Источник
Другие вопросы по тегам