Google-Spreadsheet Scripts

Question

I have a column of cells in a particular sheet of Google Spreadsheet document. This column references multiple values in another sheet using the built-in JOIN command:

=JOIN(", ",Regular!B3,Regular!B9,Regular!B10,Regular!B11,Regular!B12,Regular!B13,Regular!B14)

typical output for each such cell is a list of integers that are comma-separated, f.ex:

2, 5, 10, 12, 13

Some cells use ranges like this:

=JOIN(", ",Regular!B3:B9)

I want to lock these cells in the formula as such: Regular!$B$3,Regular!$B:$9...

Right now I want each reference to lock both column and row, but a solution that lets me pick row, column or both is a better solution.

1) I haven't found a way to do this without using a custom script - have I missed something?

2) My custom script solution is unfinished:

function eachCellInRange(range, op) {
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  for (var i = 1; i <= numRows; i++) {
    for (var j = 1; j <= numCols; j++) {
      op(range.getCell(i,j), i, j);
    }
  }
};

function lockCell(cell, row, col) {
  var formula = cell.getFormula();
  if(formula) {
    var startIdx = formula.indexOf('(');
    if(startIdx > 0) { 
      //!! REGEX HERE !! //
      cell.setValue(formula);
    }
  }
}

function lockRows() {
  var range = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveRange();
  eachCellInRange(range, lockCell);
};

I need to make a regex pattern that will identify the B3, B9... parts of the formula and change them to $B$3, $B$9... but also not break in the B1:B8 case

Currently all references are prefixed with SheetName! (e.g. Regular!B9:B20), in the future some may not be, so the most general solution is preferred.

Answer 1

I'm not sure whether this is what you're looking for but I would replace the little bit you currently have:

if(formula) {
  var startIdx = formula.indexOf('(');
  if(startIdx > 0) { 
    //!! REGEX HERE !! //
    cell.setValue(formula);
  }
}

by

if(formula.substring(0,6) == "=JOIN(") {
  formula = formula.replace(/([A-Z]+(?=[0-9]))/g, function($1) {
    return "$" +$1 + "$";
  });
  alert(formula);
  // cell.setValue(formula);
}

Which ensures that the formula is a JOIN formula.

Also, I'm not that familiar with JS, but I put it in JSFiddle to see how it goes.

Warning: This will fail if your sheet names have alphanumeric characters (mix of letters and digits).

Answer 2

Using @Jerry's useful answer, I was able to suit it to my needs:

function eachCellInRange(range, op) {
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  for (var i = 1; i <= numRows; i++) {
    for (var j = 1; j <= numCols; j++) {
      op(range.getCell(i,j), i, j);
    }
  }
};

var lockOn = 1, lockOff = -1, lockNop = 0,
    lockChar = '$', lockEmpty = '';

function lock2char(newLock, curLock) {
    if(newLock == lockNop) newLock = curLock;
    return (newLock > lockNop) ? lockChar : lockEmpty; 
}

function bool2lock(boolValue) {
    return (boolValue) ? lockOn : lockOff;
}

function lockCell(lockCol, lockRow, cell, row, col) {
  var formula = cell.getFormula();
  if(formula) {
    var startIdx = formula.indexOf('(');
    if(startIdx > 0) { 

      var newFormula = formula.replace(/([A-Z|\$]+(?=[0-9]))/g, function(part) {

        var prefix = lock2char(lockCol, (part.charAt(0) == lockChar));
        var suffix = lock2char(lockRow, (part.charAt(part.length -1) == lockChar));

        part = part.replace(/\$/g, '');

        return prefix + part + suffix;
      });

      cell.setFormula(newFormula);
    }
  }
}

function lockRows() {
  var range = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveRange();
  eachCellInRange(range, lockCell.bind(this, lockOff, lockOn));
};