Обнаружение пользователя, вставляющего строку или столбец в электронную таблицу Google и реагирующего в скрипте

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

• Отправка форм Ошибка 4568.
• Вставка или удаление строк или столбцов Проблема 1363
• Изменения внешнего вида (шрифт, формат, выравнивание, границы) Проблема 2476
• Заполнение диапазона (например, начните с 1,2,3; выберите эти ячейки и перетащите «маркер», чтобы заполнить больше ячеек 4,5,6 ...) Проблема 399
• Ценности написаны скриптами. Проблема 338
• Найти / заменить Проблема 1754
• Проблема 1568, Проблема 1119

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

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

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

Таким образом, если вы можете обнаружить добавляемую строку или столбец, регистрация изменения будет стоить вам менее 2 десятых секунды. Этот 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. Ниже приведены инструкции из здесь, подробно описывающие, как добавить триггер в ваш проект, чтобы ваша функция могла вызываться при возникновении события «On Change».

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

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

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

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

Например, он дает вам диапазон, который вы можете получить из e.range. Оттуда вы можете перемещаться по-разному и, например, узнать, какая строка редактируется. Но в объекте e есть и более полезные данные. Он дает вам "старое значение" (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.');
}