用于更新Slicer范围的Google脚本

Question

我在一次更新多个切割器的范围方面有问题，这是不可能的。我尝试使用在another thread中找到的脚本自动删除和重新创建所需范围的切片器。但我得到了以下错误。"destinationSheet“未定义。

希望你能帮我解决这个问题。

谢谢。

function test() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Tracking');



  var slicers = sheet.getSlicers();
  var rowValue = sheet.getLastRow();
  var newRange = sheet.getRange('A3:X'+rowValue); //new range

  for (i=0; i<slicers.length; i++){
    slicers[i].remove();
  }

  //Slicers to column A and D
  destinationSheet.insertSlicer(newRange, 2, 1); // choose anchorRowPos and anchorColPos
  destinationSheet.insertSlicer(newRange, 2, 4); 


  //Get new slicers
  var slicers = destinationSheet.getSlicers();

  var filterCriteria = SpreadsheetApp.newFilterCriteria().setHiddenValues(['']).build();
  slicers[0].setColumnFilterCriteria(1, filterCriteria);
  slicers[0].setTitle('Title1');

  var filterCriteria2 = SpreadsheetApp.newFilterCriteria().setHiddenValues(['1']).build();
  slicers[1].setColumnFilterCriteria(24, filterCriteria2);
  slicers[1].setTitle('Title2');
}

Answer 1

由于Tanaike建议将代码的"destinationSheet“部分更改为'sheet'，代码如下所写。

function test() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Tracking');



  var slicers = sheet.getSlicers();
  var rowValue = sheet.getLastRow();
  var newRange = sheet.getRange('A3:X'); //new range

  for (i=0; i<slicers.length; i++){
    slicers[i].remove();
  }

  //Slicers to column A and D
  sheet.insertSlicer(newRange, 2, 1); // choose anchorRowPos and anchorColPos
  sheet.insertSlicer(newRange, 2, 4); 


  //Get new slicers
  var slicers = sheet.getSlicers();

  var filterCriteria = SpreadsheetApp.newFilterCriteria().setHiddenValues(['']).build();
  slicers[0].setColumnFilterCriteria(1, filterCriteria);
  slicers[0].setTitle('Title1');

  var filterCriteria2 = SpreadsheetApp.newFilterCriteria().setHiddenValues(['1']).build();
  slicers[1].setColumnFilterCriteria(24, filterCriteria2);
  slicers[1].setTitle('Title2');
}