首页 文章

Google Apps脚本：如何根据公式复制粘贴范围？

提问于
浏览 次
0

我在Google表格中有一个模型，每天设置一列 . 它包含实际值和预测，每天我需要推进公式以用实际值替换预测 . 我不能向前滚动整列，只有它的一部分（上面和下面的参考数字不应该改变） .

我每天都试着写一个脚本为我做这个，但我不知道如何让 getRange 引用动态范围 . 这是我的尝试：

function rollColumn() {
  var ss2 = SpreadsheetApp.openById('<ID redacted>');
  ss2.getRange("=index(Model!$7:$7,,match(today()-2,Model!$4:$4,0)):index(Model!$168:$168,,match(today()-2,Model!$4:$4,0))").copyTo(ss2.getRange("=index(Model!$7:$7,,match(today()-1,Model!$4:$4,0)):index(Model!$168:$168,,match(today()-1,Model!$4:$4,0))"))
};

INDEX 公式可以在它们引用相关范围的情况下工作（我已在电子表格中对它们进行了测试） . 但显然 getRange 不允许使用公式创建命名范围（这就是我在Excel中解决这个问题的方法） .

有人可以帮我用GAS重新创建这个功能吗？

这是我能够使其工作的最接近的现有问题：Google Apps Script performing Index & Match function between two separate Google Sheets

谢谢！

google-apps-script google-sheets

2 回答

  • 0

    您应该在代码中添加{contentsOnly：false}参数 . 这样的事情：

    TemplateSheet.getRange("S2:T2").copyTo(DestSheet.getRange("S2:T"+LRow2+""), {contentsOnly:false});
    回复于
  • 0

    从列的 Headers 获取日期，然后将公式粘贴到右侧的行：

    // note: we assume that sheet is disposed as in the following document: https://docs.google.com/spreadsheets/d/1BU2rhAZGOLYgzgSAdEz4fJkxEcPRpwl_TZ1SR5F0y08/edit?ts=5a32fcc5#gid=0
function find_3formulas() {

  var sheet = SpreadsheetApp.getActiveSheet(),
      leftTitle, // this variable will stay unused because we do not need a vertical index
      topTitle = todayMinus_xDays(2),
      topTitlesRange = sheet.getRange("G3:T3"),
      leftTitlesRange = sheet.getRange("A4:A8"); // this range will stay unused.

  var coor = findCoordinates(leftTitlesRange, leftTitle, topTitlesRange, topTitle);

  if (coor.row == null || coor.column == null) {
    sheet.getRange("M12:M14").setFormula('="NULL: please check logs"'); 
    return;
  }

  var rowAxis = 4 + coor.row;
  var colAxis = 8 + coor.column;
  var fromRange = sheet.getRange(rowAxis, colAxis, 3, 1);
  var toRange = sheet.getRange(rowAxis, colAxis + 1, 3, 1);

  Logger.log(fromRange.getA1Notation())
  Logger.log(toRange.getA1Notation());

  var threeFormulas = fromRange.getFormulas();

  toRange.setFormulas(threeFormulas)
}

// unused in current script!
function findCoordinates(leftTitlesRange, leftTitle, topTitlesRange, topTitle) {

  var formattedDate, 
      row = 0, 
      column = 0;

  if (leftTitle) {
    row = findRow(leftTitlesRange, leftTitle);
  }

  if (topTitle) {
    column = findColumn(topTitlesRange, topTitle);
  }

  var array = {row:row, column:column}

  return array;
}

// unused in current script!
function findRow(range, valueToSearch) {

  var colRows = range.getValues();
  for (i = 0; i < colRows.length; i++) {
    if (valueToSearch == colRows[i][0]) {return i;}
  } 
  // however, if found nothing: 
  Logger.log("the value " + valueToSearch + " could not be found in row titles");
  return null;
}

// assumes that column titles are dates, therefore of type object.
function findColumn(range, valueToSearch) {

  var colTitles = range.getValues();
  for (i = 0; i < colTitles[0].length; i++) { 
    if (typeof colTitles[0][i] == "object") {
      formattedDate = Utilities.formatDate(colTitles[0][i], "GMT", "yyyy-MM-dd")
    };
    if (valueToSearch === formattedDate) {return i;}
  }
  // however, if found nothing:
  Logger.log("today's date, " + valueToSearch + ", could not be found in column titles");
  return null;
}

// substracts 2 days from today, then returns the result in string format.
function todayMinus_xDays(x) {
  var d = new Date();
  d = new Date(d - x * 24 * 60 * 60 * 1000);
  d = Utilities.formatDate(d, "GMT", "yyyy-MM-dd");
  return d;
}
    回复于

相关问题