按名称获取工作表

问题描述

我正在尝试使用以下公式:

I'm trying to get the following formula to work:

function setDataValid(range, sourceRange) {
  var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange, true).build();
  range.setDataValidation(rule);
}

function onEdit() {
  var aCell = SpreadsheetApp.getActiveSheet().getActiveCell();
  var aColumn = aCell.getColumn();

  if (aColumn == 2 && SpreadsheetApp.getActiveSheet().getName() == 'Local' ) {
    var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
    var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
    setDataValid(range, sourceRange)
  }
}

在调试onEdit()时，它显示setDataValid(range，sourceRange)中的sourceRange为空.由于范围在我的工作表本地"中，因此我试图将getActiveSpreadsheet()更改为按名称获取电子表格.有谁可以帮助您?

When debugging onEdit() it shows that sourceRange in setDataValid(range, sourceRange) is null. As the range is in my sheet 'Local' I'm trying to change the getActiveSpreadsheet() to a get spreadsheet by name. Anyone who can help ?

推荐答案

在电子表格上按名称使用获取工作表:

Use get sheet by name on the spreadsheet:

var sheet = SpreadsheetApp.getActive().getSheetByName('Local')

这篇关于按名称获取工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！