0

I am trying to code a script to generate a pivot table based on input from drop down menus in Sheets, but I am new to Google Apps Script and having issues understanding the syntax. I am trying to reference cells in worksheet "FORMULAS" which identify the column# or specific filter I want to use for the pivot table, but the Apps Script does not seem to know what to do when I throw in the cell reference. It should work if I can get it to just use the values that appear in the cells referenced in the "FORMULAS" worksheet.

Here is the code:

function PIVOT_TABLE() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('E1').activate();
  var sourceData = spreadsheet.getRange('\'SAR2021\'!A1:AD3198');
  var pivotTable = spreadsheet.getRange('E1').createPivotTable(sourceData);
  pivotValue = pivotTable.addCalculatedPivotValue('WIN%', '=WIN/(WIN+LOSS)');
  pivotValue = pivotTable.addCalculatedPivotValue('WINS', '=WIN');
  pivotValue = pivotTable.addCalculatedPivotValue('RACES', '=WIN+LOSS');
  pivotGroup = pivotTable.addRowGroup('\'FORMULAS\'!I5');
  pivotGroup.showTotals(false);
  pivotGroup = pivotTable.addRowGroup('\'FORMULAS\'!I7');
  pivotGroup.showTotals(false);
  pivotGroup = pivotTable.addColumnGroup('\'FORMULAS\'!I2');
  pivotGroup.showTotals(false);
  criteria = SpreadsheetApp.newFilterCriteria()
  .setVisibleValues(['\'FORMULAS\'!I3'])
  .build();
  pivotTable.addFilter('\'FORMULAS\'!I5', criteria);
  criteria = SpreadsheetApp.newFilterCriteria()
  .setVisibleValues(['\'FORMULAS\'!I6'])
  .build();
  pivotTable.addFilter('\'FORMULAS\'!I7', criteria);
  criteria = SpreadsheetApp.newFilterCriteria()
  .setVisibleValues(['\'FORMULAS\'!I8'])
  .build();
}; 
2
  • Hi, welcome to WebApps. A couple of things: 1) you've included your code (thank you), but you haven't included a sample of your data - we need to know where it is and what it looks like. 2) You said but the Apps Script does not seem to know what to do when I throw in the cell reference. That's a bit vague as a description of your problem - would you please edit your question to describe the EXACT nature of your problem, 3) is there a reason why you are using a script to create a PIVOT table rather than via the main menu (Insert>Pivot table) or via a query with a pivot clause?
    – Tedinoz
    May 4, 2022 at 3:11
  • Are you familiar with getSheetByName() and its use when referencing ranges.
    – Tedinoz
    May 4, 2022 at 3:16

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.