javascript - 尝试从公式 Google App 脚本中粘贴值

Question

这只是我在 Google App Script 中的代码片段，它遍历第 1、2、3 列中的每一行。如果在第 3 列中进行了编辑，则会生成一个增量 ID，并将同一行和不同列连接起来也将生成 - 在本例中为 D、E 和 F 列。我正在努力寻找一种将公式更改为值的方法。我在这里想念什么？

// Location format = [sheet, ID Column, ID Column Row Start, Edit Column]
var locations = [
  ["Consolidated Media Plan",1,9,3] 
];

function onEdit(e){
  // Set a comment on the edited cell to indicate when it was changed.
  //Entry data
  var range = e.range;
  var col = range.getColumn();
  var row = range.getRow();

  // Location Data
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  function getNewID(){
    function IDrange(){
      var dataRange = sheet.getDataRange();
      var lastRow = dataRange.getLastRow();

      return sheet.getRange(IDrowStart,IDcol,lastRow-IDrowStart).getValues();
    };

    //Get largest Value in range
    function getLastID(range){
      var sorted = range.sort();
      var lastIDval = sorted[sorted.length-1][0];

      return lastIDval;

    };
    //Stores leading letters and zeroes and trailing letters
    function getLettersNzeroes(id){

      //Get any letters or zeroes.
      var re = new RegExp("^([a-zA-Z0])$");
      var letterZero = [];
      for(char = 0; char < id.length; char++){
        if(re.test(id[char])){
          letterZero.push([char,id[char]]);// [[position, letter or zero]]
        };
      };

      // Categorize letters and zeroes into start and end blocks
      var startLetterZero = "", 
          endLetter = "",
          len = letterZero.length - 1;
      for(j = 0; j < letterZero.length; j++){
        if(letterZero[j][0] === j){
          startLetterZero += letterZero[j][1];
        }else if(letterZero[j][1] !== "0" && letterZero[len][0] - (len - j) == letterZero[j][0]){
          endLetter += letterZero[j][1];
        };
      };
      var startNend = {"start":startLetterZero,"end":endLetter};

      return startNend;
    };

    //Gets last id number. Adds 1 an checks to set if its  new length is greater than the lastNumber.
    function getNewNumber(id){
      var removeZero = false;

      var lastNum = parseInt(id.replace(/\D/g,''),10);//Remove letters
      var newNum = (lastNum+1).toString();

      if(lastNum.toString().length !== newNum.length){
        var removeZero = true;
      };
      var newNumSet = {"num":newNum, "removeZero": removeZero};

      return newNumSet
    };

    var lastID = getLastID(IDrange());
    var lettersNzeroes = getLettersNzeroes(lastID);
    var newNumber = getNewNumber(lastID);

    //If the number is 9,99,999,9999 etc we need to remove a zero if it exists.
    if(newNumber.removeZero === true && lettersNzeroes.start.indexOf("0")  !== -1.0){
      lettersNzeroes.start = lettersNzeroes.start.slice(0,-1);
    };

    //Rejoin everything together
    var newID = lettersNzeroes.start +
                newNumber.num +
                lettersNzeroes.end;

    return newID;  
  };

  for(i = 0; i < locations.length; i++){
    var sheetID = locations[i][0],
        IDcol   = locations[i][1],
        IDrowStart = locations[i][2],
        EditCol = locations[i][3];

    var offset = IDcol - EditCol;
    var cell = sheet.getActiveCell();



    if(sheetID === sheet.getName()){
      if(EditCol === col){
        //ID Already Exists the editing cell isn't blank.
        if(cell.offset(0,offset).isBlank() && cell.isBlank() === false){
          var newID = getNewID();
          cell.offset(0,offset).setValue(newID);
          cell.offset(0,-1).setFormulaR1C1('=concatenate(R[0]C[-1],"_",INDEX(Glossary!K:K,MATCH(R[0]C[2],Glossary!J:J,0)))');







        };
      };
    }; 
  };        
};

编辑： 这是我的完整代码，我试图仅检索同一公式中的值没有成功（即，如果 C9 被编辑，则应填充具有特定于第 9 行的值的公式）

此外，我尝试在代码底部的连接公式中添加索引/匹配公式 - 它在谷歌表格上按预期工作，但是当我使用脚本运行它时，它会粘贴正确的公式，但它返回一个＃姓名？错误信息。但是，当我在单元格中复制并粘贴完全相同的公式时，它工作得很好，知道是什么导致了这个错误吗？

标签： javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-macros

Answer 1

这对我有用。我知道这不完全一样，但我无权访问 getNewId()

function onEdit(e) {
  var sh=e.range.getSheet();
  if(sh.getName()!='Sheet1')return;
  //e.source.toast('flag1');
  if(e.range.columnStart==3 && e.range.offset(0,1).isBlank() && e.value) {
    //e.source.toast('flag2');
    e.range.offset(0,1).setValue(e.value);
    e.range.offset(0,2).setFormulaR1C1('=concatenate(R[0]C[-1],"_",R[0]C[-2],"_",R[0]C[-3],"_",R[0]C[-4])');
  }
}