Google Sheets Apps Script中moveNames函数批注迁移失效问题

我编写了名为moveNames()的Google Sheets Apps Script函数，核心功能是将指定行内的名称向右迁移；若当前行已到允许的最后一列，则将名称移至下一行的起始列（如A1内容移至B1，G1内容移至A2）。目前内容迁移功能正常，但添加批注迁移逻辑后出现问题：尝试用原有单元格批注替换目标单元格现有批注时操作失效，仅直接设置新字符串批注可生效。

完整函数代码 #

function moveNames() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Schedule');
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Admin').getRange(6, 2).setValue("yes");
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Admin').getRange(5, 2).setValue("Running");

  var startingColumn = 2; // Column B
  var endingColumn = 7; // Column G
  var rows = [36, 38, 40, 42, 44, 46, 48, 50]; // rows with names
  // sheet.getRange(rows[0], startingColumn).setNote("HERE IS A SAMPLE NOTE");

  var data = sheet.getRange(3, startingColumn, rows[rows.length-1], endingColumn).getValues(); // 2D array containing all the data

  var lastRowWithTable = -1;
  for (var i = rows.length - 1; i >= 0; i--) {
    var nameRow = rows[i];
    if (data[nameRow-3].join().trim() !== '') { // finds the last non-empty row with table
      lastRowWithTable = nameRow;
      break;
    }
  }

  var lastRowTable = sheet.getRange(rows[i]-1, startingColumn);
  var lastRowColumn = 0;
  var lastRowRow = 0;

  for(var i = 0; i < rows.length; i++){
    for(var j = startingColumn; j < endingColumn; j++) {
      var rowAbove = sheet.getRange(rows[i] -1, j);
      if(rowAbove.getValue() != ''){
        lastRowTable = rowAbove;
        lastRowColumn = j-1;
        lastRowRow = rows[i];
      }
      // console.log(rowAbove.getValue())
    }
    
  }

  console.log(lastRowTable.getValue());
  console.log(lastRowRow)
  console.log(lastRowColumn)

  var curr = null;
  var note = null;

  for(var i = 0; i < rows.length; i++){
    for(var j = startingColumn; j < endingColumn; j++){
      //check first that the first item has a value later
      if(curr == null){
        curr = sheet.getRange(rows[i], j).getValue(); //current cell starting will be B4 shift this value to the right as long as there is a table name to the right
        note = sheet.getRange(rows[i], j).getNote();
        console.log("INITIAL NOTE: ", note);
      }

      if(rows[i] == lastRowRow && j == lastRowColumn+1){
        //add the curr to the first then end the program
        // var currValue = curr.getValue();
        // console.log("Value to change to: ", curr)
        // console.log("VALUE GETTING CHANGED: ", sheet.getRange(rows[0], startingColumn).getValue());
        sheet.getRange(rows[0], startingColumn).setValue(curr);
        sheet.getRange(rows[0], startingColumn).setNote(note);
        SpreadsheetApp.flush()
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Admin').getRange(5, 2).setValue("Completed");
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Admin').getRange(7, 2).setValue(new Date());
        return;
      }



      console.log(curr);
      console.log(note);
      console.log("HERE IS J: ", j)
      console.log("HERE IS THE ROW: ", rows[i])
      
      var rowAboveRight = sheet.getRange(rows[i] -1, j+1);
      var pointerForRowAbove = j+2;
      var columnThatNextNameIsOn = j+1;
      while(rowAboveRight.getValue() == '' || (rows[i] == 26 && pointerForRowAbove >= 7)){
        console.log("Column: ", pointerForRowAbove)
        console.log("ROW: ", rows[i])
        rowAboveRight = sheet.getRange(rows[i] -1, pointerForRowAbove)
        
        columnThatNextNameIsOn = pointerForRowAbove;
        pointerForRowAbove++;
        if(pointerForRowAbove > endingColumn){ //meaning that we have exhausted all columns in this particular row
          i++; //move to the next row
          pointerForRowAbove = startingColumn;

          continue;
        }
        
        
      }
      var tempRight = sheet.getRange(rows[i], columnThatNextNameIsOn).getValue();
      var tempRightNote = sheet.getRange(rows[i], columnThatNextNameIsOn).getNote();
      // var right = sheet.getRange(rows[i], columnThatNextNameIsOn)
      if(j+1 != columnThatNextNameIsOn){
        j = columnThatNextNameIsOn-1;
      }
      
      
      // var currValue = curr;
      console.log("Value to change to: ", curr)
      console.log("Value to change to: ", note)
      console.log("VALUE GETTING CHANGED: ", sheet.getRange(rows[i], columnThatNextNameIsOn).getValue());
      console.log("NOTE GETTING CHANGED: ", sheet.getRange(rows[i], columnThatNextNameIsOn).getNote());
      sheet.getRange(rows[i], columnThatNextNameIsOn).setValue(curr);
      sheet.getRange(rows[i], columnThatNextNameIsOn).setNote(tempRightNote);
      curr = tempRight;
      note = tempRightNote;
    }
  }
}

执行效果 #

• 执行前：指定行的名称位于对应列，部分单元格带有批注。
• 执行后：名称按预期向右/跨行迁移，但批注未正确跟随迁移，目标单元格的原有批注未被替换。

内容的提问来源于stack exchange，提问作者devin