СКРИПТ таблицы Google Проверьте, находится ли отредактированная ячейка в определенном диапазоне

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

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

Вот что я должен обновить время.

function onEdit(e) 
{
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  ss.getRange("G10").setValue(new Date());
} ​

Я только хочу установить дату в G10, если я редактирую определенные ячейки (в диапазоне "B4:J6")

Источник

7 ответов

Решение

Событие предоставляется в качестве параметра для вашего onEdit() функция, и она содержит необходимую информацию о том, что было отредактировано. Если вам интересно, что это (e) было все, вот и все.

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

function onEdit(e) 
{
  var editRange = { // B4:J6
    top : 4,
    bottom : 6,
    left : 2,
    right : 10
  };

  // Exit if we're out of range
  var thisRow = e.range.getRow();
  if (thisRow < editRange.top || thisRow > editRange.bottom) return;

  var thisCol = e.range.getColumn();
  if (thisCol < editRange.left || thisCol > editRange.right) return;

  // We're in range; timestamp the edit
  var ss = e.range.getSheet();
  ss.getRange(thisRow,7)   // "G" is column 7
    .setValue(new Date()); // Set time of edit in "G"
} ​
Источник

Самый простой способ - НАИМЕНОВАТЬ свой диапазон запуска

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

Шаг 1: назовите свой диапазон

Назовите диапазон ячеек, которые вы хотите использовать в качестве триггера для вашего скрипта, если вы отредактируете любую из ячеек в вашем диапазоне в соответствии с инструкциями здесь: https://support.google.com/docs/answer/63175

Я назвал свой диапазон "triggerRange".

Шаг 2: отредактируйте имя вашего диапазона в следующем скрипте:

function onEdit(e) {
      var myRange = SpreadsheetApp.getActiveSheet().getRange('triggerRange'); //<<< Change Your Named Ranged Name Here inside the getRange() function.
      //SpreadsheetApp.getUi().alert("myRange in A1 Notation is: " + myRange.getA1Notation()); //If you're having problems, uncomment this to make sure your named range is properly defined

      //Let's get the row & column indexes of the active cell
      var row = e.range.getRow();
      var col = e.range.getColumn();
      //SpreadsheetApp.getUi().alert('The Active Cell Row is ' + row + ' and the Column is ' + col); //uncomment this out to do testing

      //Check that your active cell is within your named range
      if (col >= myRange.getColumn() && col <= myRange.getLastColumn() && row >= myRange.getRow() && row <= myRange.getLastRow()) { //As defined by your Named Range
        SpreadsheetApp.getUi().alert('You Edited a Cell INSIDE the Range!');//Repalace Your Custom Code Here
      } else {
        SpreadsheetApp.getUi().alert('You Edited a Cell OUTSIDE the Range!');//Comment this out or insert code if you want to do something if the edited cells AREN'T inside your named range
        return;
      }
    }

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

function onEdit(e) {
  var myRange = SpreadsheetApp.getActiveSheet().getRange('triggerRange'); //<<< Change Your Named Ranged Name Here

  //Let's get the row & column indexes of the active cell
  var row = e.range.getRow();
  var col = e.range.getColumn();

  //Check that your active cell is within your named range
  if (col >= myRange.getColumn() && col <= myRange.getLastColumn() && row >= myRange.getRow() && row <= myRange.getLastRow()) { //As defined by your Named Range
    SpreadsheetApp.getUi().alert('You Edited a Cell INSIDE the Range!');//Repalace Your Custom Code Here
  }
}
Источник

Вы можете просто проверить, произошло ли событие редактирования в пределах диапазона.

function onEdit(e)
{
  var sheet = SpreadsheetApp.getActiveSheet();
  var editRange = sheet.getActiveRange();
  var editRow = editRange.getRow();
  var editCol = editRange.getColumn();
  var range = sheet.getRange("B4:J6");
  var rangeRowStart = range.getRow();
  var rangeRowEnd = rangeRowStart + range.getHeight();
  var rangeColStart = range.getColumn();
  var rangeColEnd = rangeColStart + range.getWidth();
  if (editRow >= rangeRowStart && editRow <= rangeRowEnd 
      && editCol >= rangeColStart && editCol <= rangeColEnd)
  {
    // Do your magic here
  }
}

Я признаю, что это очень многословно, но я все еще не нашел простой метод для диапазона ala range.contains(range)

Источник

Просто быстрое решение возможной проблемы с предыдущим ответом Расмуса:

На линии:

var rangeRowEnd = rangeRowStart + range.getHeight();
var rangeColEnd = rangeColStart + range.getWidth();

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

var rangeRowEnd = rangeRowStart + range.getHeight()-1;
var rangeColEnd = rangeColStart + range.getWidth()-1;

Код Расмуса Фуглснага и исправление должны выглядеть так:

function onEdit(e)
{
  var sheet = SpreadsheetApp.getActiveSheet();
  var editRange = sheet.getActiveRange();
  var editRow = editRange.getRow();
  var editCol = editRange.getColumn();
  var range = sheet.getRange("B4:J6");
  var rangeRowStart = range.getRow();
  var rangeRowEnd = rangeRowStart + range.getHeight()-1;
  var rangeColStart = range.getColumn();
  var rangeColEnd = rangeColStart + range.getWidth()-1;
  if (editRow >= rangeRowStart && editRow <= rangeRowEnd 
      && editCol >= rangeColStart && editCol <= rangeColEnd)
  {
    // Do your magic here
  }
}

Спасибо Расмусу Фуглснагу за предоставленный самый простой способ сделать это. Я не могу поверить, что есть лучший способ сделать это с помощью кода.

Источник

Если вы хотите очень мало строк кода, вы можете использовать:

function onEdit(){
  var cell = SpreadsheetApp.getActive().getActiveCell();
  var row = cell.getRow();
  var col = cell.getColumn();

  //if col > A && col < K ...
  if(col > 1 && col < 11 && row > 3 && row < 7){ //B4:J6
    //do something
  }

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

Источник

Чтобы сохранить удобный код, вы можете использовать диспетчер (вдохновленный ответом Doomd).

/** 
 if the changed cell is in the good sheet and range:
 @returns {Range} : the changed cell range
 else
 @returns {boolean} : false
*/
function onEdit_inRange(e,sheetName,sheetRange){
    var sh=e.range.getSheet();
    if(sh.getName()===sheetName){
        var range = SpreadsheetApp.getActiveSheet().getRange(sheetRange);
        var xy=[e.range.getColumn(),e.range.getRow()];
        if (xy[0]>=range.getColumn() && xy[0]<=range.getLastColumn() && xy[1]>=range.getRow() && xy[1]<=range.getLastRow()) {
            return e.range;
        }
    }
    return false;
}

Как пользоваться:

function onEdit(e){
    var range=0;
    if((range=onEdit_inRange(e,'Stats','Q47:Z47'))){
        _handle_stats(range);
    }else if ((range=onEdit_inRange(e,'Calendar','A5:B29'))) {
        _handle_calendar(range);
    }// etc ...
}
Источник

с тестом

      function cellInRange_Test() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getRange("E:H");
  let cell = sheet.getRange("E5");

  let result = cellInRange(range, cell);

  Logger.log(result);
  if (!result) {
    throw new Error("Ячейка не находится в заданном диапазоне, хотя должна");
  }

  cell = sheet.getRange("A5");
  result = cellInRange(range, cell);

  Logger.log(result);
  if (result) {
    throw new Error("Ячейка находится в заданном диапазоне, хотя НЕ должна");
  }
}

/**
 * Проверяем, что ячейка находится в заданном диапазоне
 */
function cellInRange(range, cell) {
  const cellRow = cell.getRow();
  const cellColumn = cell.getColumn();
  const rangeRowStart = range.getRow();
  const rangeRowEnd = rangeRowStart + range.getNumRows() - 1;
  const rangeColumnStart = range.getColumn();
  const rangeColumnEnd = rangeColumnStart + range.getNumColumns() - 1;

  return (
    cellRow >= rangeRowStart &&
    cellRow <= rangeRowEnd &&
    cellColumn >= rangeColumnStart &&
    cellColumn <= rangeColumnEnd
  );
}
Источник
Другие вопросы по тегам