Блог

Я знаю, что это довольно просто для опытного разработчика Google App Script. Я новичок в Google App Script и пробую это с 2 дней, поэтому я пришел сюда.

Позвольте мне объяснить сценарий, у нас есть надстройка Google Sheet. Когда пользователь выполняет какой-либо запрос с помощью нашего дополнения, мы сохраняем идентификатор запроса [столбец A], название листа [столбец B] и обновленный диапазон ячеек [столбец C] на скрытом листе, как показано на рисунке.

Теперь мы хотим обновить эти диапазоны, если пользователь добавит / удалит столбец / строку с листа.

например, у нас есть диапазон J4: k14 на листе. например, пользователь добавляет столбец слева от C, затем диапазон J4: K14 сдвигается на K4: L14.

 function onChange(e) {
 if (['EDIT', 'INSERT_ROW', 'INSERT_COLUMN', 'REMOVE_ROW', 'REMOVE_COLUMN'].includes(e.changeType)) {
    
    var range = SpreadsheetApp.getActiveRange();
    var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var querySheet = activeSpreadsheet.getSheetByName("OurHiddenSheet");
    // First I want to read C column to get values. that will be assigned in **range**.
    var addresses = Sheets.Spreadsheets.Values.get(querySheet.getSheetId(), range);
    // Since addresses contain all ranges, I can create RangeList from this.
    var rangeList = activeSpreadsheet.getRangeList(addresses);

    // Is there any function available in Google App Script, if the Range was impacted by the operation or not?
    

  }
}
  

Как прочитать весь столбец B и C и отфильтровать на основе названия текущего листа и диапазона?

Я хочу знать, доступны ли какие-либо функции, позволяющие узнать, повлияла ли операция на диапазон?

Лучший способ — хранить данные как developerMetadata. Например, для данных первой строки ( NewSheet!J4:K14 ) вы можете сохранить метаданные в новой таблице строки!4:4 и новой таблице столбца!J: J, где key может быть uuid запроса, а value — количество строк (10) и количество столбцов (2) соответственно.

Смотрите:

• Руководство по метаданным

Решение

Нет встроенной функции, которая позволяет узнать, был ли изменен диапазон. Но вы можете легко создать алгоритм обнаружения столкновений, поскольку это простое 2D-пространство:

 /**
 * Detects the collision between to ranges specified in A1 Notation
 *
 * @param {string} a - The first range in A1 Notation.
 * @param {string} b - The second range in A1 Notation.
 *
 * @returns {boolean} - true if the two ranges overlaps false otherwise
 */
function collision(a,b) {
  var _a = SpreadsheetApp.getActiveSpreadsheet().getRange(a);
  var _b = SpreadsheetApp.getActiveSpreadsheet().getRange(b);
  
  var _ax = _a.getColumn();
  var _ay = _a.getRow();
  var _awidth = _a.getLastColumn() - _ax;
  var _aheight = _a.getLastRow() - _ay;
  
  var _bx = _b.getColumn();
  var _by = _b.getRow();
  var _bwidth = _b.getLastColumn() - _bx;
  var _bheight = _b.getLastRow() - _by;
  
  return _ax < _bx   _bwidth amp;amp; _ax   _awidth > _bx amp;amp; _ay < _by   _bheight amp;amp; _ay   _aheight > _by;
}
  

С помощью этой функции вы можете создать, switch case который обновляет ваши ссылки на диапазон в соответствии с Event type координатами перекрытия и активного диапазона.

Например, в случае «INSERT_COLUMN» вы можете обновить следующим образом:

    var values;
   switch(e_type) {
     case "INSERT_COLUMN":
       values = querySheet.getRange("A2:C2")
                .getValues()
                .map((row) => {
                     let r = querySheet.getRange(row[2]);
                     // If the active range is outside our rangelist we don't have to update
                     if (row[1] == sheetName amp;amp; range.getColumn() < r.getLastColumn()) {
                          let width = range.getLastColumn() - range.getColumn()   1;
                          // When overlapping you should check where the active range start wither to offset the whole range or to extend its width.
                          if (collision(row[2], range.getA1Notation())) {
                                 if (range.getColumn() <= r.getColumn()) {
                                        row[2] = getShiftOffsetColumn(r, width);
                                 } else {
                                        row[2] = getShiftExtendedColumn(r, width);
                                 }
           
                           } else {
                                 // No collision, just offset.
                                 row[2] = getShiftOffsetColumn(r, width);
                           }
                     }
                     return row;
       });
       
       break;
     case "REMOVE_COLUMN":

       [...]
     
     default:
       // Exception handling
   }
   querySheet.getRange("A2:C2").setValues(values); //updates the values in hidden sheet

[...]

// --- Support functions ---

// This function handles the offset
function getShiftOffsetColumn(r, width) {
   return SpreadsheetApp.getActiveSheet().getRange(r.getRow(), r.getColumn()   width, r.getLastRow() - r.getRow()   1, r.getLastColumn() - r.getColumn()   width).getA1Notation();
}

// This function handles the extension
function getShiftExtendedColumn(r, width) {
  return SpreadsheetApp.getActiveSheet().getRange(r.getRow(), r.getColumn(), r.getLastRow() - r.getRow()   1, r.getLastColumn() - r.getColumn()   1   width).getA1Notation();
}