Updating historic Ranges on Add row/column Google App Script

Question

I Know this is pretty simple for experienced developer of Google App Script. I am new to Google App Script and trying on this since 2 days so I came here.

Let me explain the scenario, we have an Google Sheet add-on. When user do some query using our add on, we keep query id[A column], Sheet name[B column] and updated cell range[C column] in a hidden sheet as shown in image.

Now We want to update these ranges if user add/remove column/row from the sheet.

e.g we have range J4:k14 in the sheet. e.g User add column left of C, then the J4:K14 range shift to 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?
    

  }
}

How to read the entire B and C column and filter based on current sheet name and range?

I want to know, Is there any functions available to know if the range was impacted by the operation?

Answer 1

A better way is to store data as developerMetadata. For eg, For the first row data(NewSheet!J4:K14), You can store metadata in row NewSheet!4:4 and column NewSheet!J:J, where key could be the query uuid and value would be number of rows(10) and number of columns(2) respectively.

See:

• Metadata guide

Answer 2

Solution

There is no built in function that allows you to know if the range was impacted. But you can easily build a collision detector algorithm since it's a simple 2D Space:

/**
 * 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 && _ax + _awidth > _bx && _ay < _by + _bheight && _ay + _aheight > _by;
}

With this function you can build a switch case that updates your range references according to the Event type, overlapping and active range coordinates.

For example in the "INSERT_COLUMN" case you can update like this:

   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 && 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();
}