谷歌电子表格脚本

Google-Spreadsheet Scripts

本文关键字：脚本 电子表格 谷歌      更新时间：2023-09-26

我在谷歌电子表格文档的特定表格中有一列单元格。此列使用内置的JOIN命令引用另一个工作表中的多个值：

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

每个这样的单元格的典型输出是逗号分隔的整数列表，例如：

2、5、10、12、13

一些单元格使用这样的范围：

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

我想在公式中这样锁定这些单元格：正则$B$3，普通$B： $9…

现在，我希望每个引用都锁定列和行，但让我选择行、列或两者的解决方案是更好的解决方案。

1） 我还没有找到一种不使用自定义脚本的方法——我错过了什么吗？

2） 我的自定义脚本解决方案尚未完成：

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

我需要制作一个正则表达式模式，识别公式的B3、B9…部分，并将它们更改为$B$3、$B$9……但在B1:B8情况下也不会中断

目前，所有引用都以SheetName为前缀！（例如常规！B9:B20），未来可能不会有，因此首选最通用的解决方案。

我不确定这是否是您想要的，但我会替换您目前拥有的一点点：

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

通过

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

这确保了该公式是JOIN公式。

另外，我对JS不太熟悉，但我把它放在JSFiddle中，看看它进展如何。

警告：如果工作表名称包含字母数字字符（字母和数字混合），则此操作将失败。

使用@Jerry的有用答案，我能够满足我的需求：

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));
};
相关文章：