google-apps-script - 如何循环使用不同范围值的谷歌脚本函数？

Question

我需要一些帮助来编写谷歌脚本，而且我对整个事情都很陌生。我试图寻找一些解决方案或方法来执行这个简单的脚本（这个想法似乎很简单）但也许是因为我还在学习我迷失的基础知识。

我有这个电子表格，我需要为电子表格中的每一列制作一个数据透视表（从 B1；B75 开始，到 FE1；FE75 结束）。我做了一个简单的脚本，在定义的范围内做一个简单的数据透视表，但这是真正的问题，我希望范围是可变的（做各种数据透视表，完成后转到下一列，然后再做另一个直到它到达最后一列 FE1;FE75) 所以我不需要为每一列重复所有例程。这是我得到的：

  function test92pivottable() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('B1:B75').activate();
  var sourceData = spreadsheet.getRange('B1:B75');
  spreadsheet.insertSheet(spreadsheet.getActiveSheet().getIndex() + 1).activate();
  spreadsheet.getActiveSheet().setHiddenGridlines(true);
  var pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
  pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
  var pivotGroup = pivotTable.addRowGroup(2);
  pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
  var pivotValue = pivotTable.addPivotValue(2, SpreadsheetApp.PivotTableSummarizeFunction.COUNTA);
  pivotGroup = pivotTable.addRowGroup(2);
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Respostas ao formulário 1'), true);
  spreadsheet.getRange('C1:C75').activate();
  sourceData = spreadsheet.getRange('C1:C75');
  spreadsheet.insertSheet(spreadsheet.getActiveSheet().getIndex() + 1).activate();
  spreadsheet.getActiveSheet().setHiddenGridlines(true);
  pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
  pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
  pivotGroup = pivotTable.addRowGroup(3);
  pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
  pivotValue = pivotTable.addPivotValue(3, SpreadsheetApp.PivotTableSummarizeFunction.COUNTA);
  pivotGroup = pivotTable.addRowGroup(3);
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Respostas ao formulário 1'), true);
  spreadsheet.getRange('D1:D75').activate();
  sourceData = spreadsheet.getRange('D1:D75');
  spreadsheet.insertSheet(spreadsheet.getActiveSheet().getIndex() + 1).activate();
  spreadsheet.getActiveSheet().setHiddenGridlines(true);
  pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
  pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
  pivotGroup = pivotTable.addRowGroup(4);
  pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
  pivotValue = pivotTable.addPivotValue(4, SpreadsheetApp.PivotTableSummarizeFunction.COUNTA);
  pivotGroup = pivotTable.addRowGroup(4);  
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Respostas ao formulário 1'), true);
  spreadsheet.getRange('E1:E75').activate();
  sourceData = spreadsheet.getRange('E1:E75');
  spreadsheet.insertSheet(spreadsheet.getActiveSheet().getIndex() + 1).activate();
  spreadsheet.getActiveSheet().setHiddenGridlines(true);
  pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
  pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
  pivotGroup = pivotTable.addRowGroup(5);
  pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
  pivotValue = pivotTable.addPivotValue(5, SpreadsheetApp.PivotTableSummarizeFunction.COUNTA);
  pivotGroup = pivotTable.addRowGroup(5);  
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Respostas ao formulário 1'), true);
  spreadsheet.getRange('F1:F75').activate();
  sourceData = spreadsheet.getRange('F1:F75');
  spreadsheet.insertSheet(spreadsheet.getActiveSheet().getIndex() + 1).activate();
  spreadsheet.getActiveSheet().setHiddenGridlines(true);
  pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
  pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
  pivotGroup = pivotTable.addRowGroup(6);
  pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
  pivotValue = pivotTable.addPivotValue(6, SpreadsheetApp.PivotTableSummarizeFunction.COUNTA);
  pivotGroup = pivotTable.addRowGroup(6);
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Respostas ao formulário 1'), true);
  spreadsheet.getRange('G1:G75').activate();
  sourceData = spreadsheet.getRange('G1:G75');
  spreadsheet.insertSheet(spreadsheet.getActiveSheet().getIndex() + 1).activate();
  spreadsheet.getActiveSheet().setHiddenGridlines(true);
  pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
  pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
  pivotGroup = pivotTable.addRowGroup(7);
  pivotTable = spreadsheet.getRange('A1').createPivotTable(sourceData);
  pivotValue = pivotTable.addPivotValue(7, SpreadsheetApp.PivotTableSummarizeFunction.COUNTA);
  pivotGroup = pivotTable.addRowGroup(7);
  };

如您所见，我发现的解决方案是重复初始例程，但更改

电子表格.getRange(' C1:C75 ').activate();

sourceData = 电子表格.getRange(' C1:C75 ');

pivotGroup = pivotTable.addRowGroup( 3 );

pivotValue = pivotTable.addPivotValue( 3 , SpreadsheetApp.PivotTableSummarizeFunction.COUNTA);

pivotGroup = pivotTable.addRowGroup( 3 );

但这会使脚本变得庞大。

如果我不清楚，我很抱歉。我习惯在这样的论坛上查找答案，但我自己不问问题！

谢谢

标签： google-apps-scriptgoogle-sheetsgoogle-sheets-formula

Answer 1

这只是对您当前关于如何遍历不同工作表和范围的问题的一般回答。

function loopthroughfunctionwithdifferentrangevalues() {
  const ss=SpreadsheetApp.getActive();
  const rA=['A1:A75','B1:B75','C1:C75'];
  const sA=['Sheet1','Sheet2','Sheet3'];
  sA.forEach((sh,s)=>{
    let sheet=ss.getSheetByName(sh);
    rA.forEach((rg,r)=>{
      let rg=sheet.getRange(rg);
      let vals=rg.getValues();
        //Put code in here.
    })
  });
}

您可以使用类似的方法来遍历电子表格中的所有工作表，但每个工作表中的选定子集和某些范围除外。

function loopthroughfunctionwithdifferentrangesandsheetsandexcludesomesheets() {
  const ss=SpreadsheetApp.getActive();
  const rA=['A1:A75','B1:B75','C1:C75'];
  const xA=['Sheet1','Sheet2','Sheet3'];//exclude these sheets
  const shts=ss.getSheets();
  shts.forEach((sh,s)=>{
    if(xA.indexOf(sh)==-1) {
      let sheet=ss.getSheetByName(sh);
      rA.forEach((rg,r)=>{
        let rg=sheet.getRange(rg);
        let vals=rg.getValues();
        //Put code in here.
      })
    }
  });
}