Script for Pivot Tables

[Ram Kiami]

Good day everyone. Does anyone happen to know the script to automate the following manual procedure: Double-clicking on a number cell in a pivot table creates a new sheet in the workbook, showing the records included in that number.

I tried the following, but it just creates sheets using the name of the cell and has some errors. Any assistance is much appreciated. 

function myFunction() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getSheetByName('Pivot');

  var pivotTable = sheet.getRange('A2:B3');

  var pivotValues = pivotTable.getValues();

  var pivotData = pivotValues[0];

  var pivotDataLength = pivotData.length;

  var pivotDataValues = pivotValues.slice(1);

  var pivotDataValuesLength = pivotDataValues.length;

  for (var i = 0; i < pivotDataLength; i++) {

    var newSheet = ss.insertSheet(pivotData[i]);

    var newSheetValues = [];

    for (var j = 0; j < pivotDataValuesLength; j++) {

      if (pivotDataValues[j][0] == pivotData[i]) {

        newSheetValues.push(pivotDataValues[j]);

      }

    }

    newSheet.getRange(1, 1, newSheetValues.length, newSheetValues[0].length).setValues(newSheetValues);

  }

}

Many thanks

[cwl...@gmail.com]

This article from Martin may be useful:  https://hawksey.info/blog/2020/10/working-with-pivot-tables-in-google-sheets-using-google-apps-script/

[Ram Kiami]

Thank you for sharing the link. I just checked, and it has great information about creating pivot tables but not what I am trying to achieve. 

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/4593c063-0f21-4e4d-afc4-e1ea3ebce2e5n%40googlegroups.com.