Need Help with Updating Pivot Table Source Range in Google Apps Script

[Federico Mandelli]

Hello experts,

I'm working on a Google Apps Script that interacts with Google Sheets, specifically with pivot tables. My goal is to automatically update the source data range of all pivot tables in a spreadsheet that are linked to a specific sheet (DetailedMedicalData). However, I'm facing challenges in achieving this.

Here's the current code I'm using:


var SHEET_NAME = 'DetailedMedicalData';

function deleteDuplicateRows() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
 
  // Ensure the sheet is sorted based on the "DATE" column
  var range = sheet.getRange(2, 1, sheet.getLastRow(), sheet.getLastColumn());
  range.sort([{column: 13, ascending: true}]); // Sort based on column M (DATE)
 
  var data = range.getValues();
  var uniqueData = [];
  var previousRows = new Set();
 
  for (var i = 0; i < data.length; i++) {
    var row = data[i].join(",");
    if (!previousRows.has(row)) {
      previousRows.add(row);
      uniqueData.push(data[i]);
    }
  }
 
  // Delete all existing rows
  sheet.deleteRows(2, sheet.getLastRow() - 1);
 
  // Insert only unique rows
  sheet.getRange(2, 1, uniqueData.length, uniqueData[0].length).setValues(uniqueData);
 
  // Update pivot tables
  updatePivotTables();
}

function updatePivotTables() {
  var mainSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = mainSheet.getSheets();
 
  for (var i = 0; i < sheets.length; i++) {
    var pivotTables = sheets[i].getPivotTables();
   
    for (var j = 0; j < pivotTables.length; j++) {
      var sourceRange = pivotTables[j].getSourceDataRange();
     
      // Check if the pivot table's source data is the "DetailedMedicalData" sheet
      if (sourceRange.getSheet().getName() === SHEET_NAME) {
        var newRange = mainSheet.getSheetByName(SHEET_NAME).getRange("A1:Q");
        pivotTables[j].setSourceData(newRange);
      }
    }
  }
}
When I run the updatePivotTables() function, I encounter the following error:

vbnet

TypeError: pivotTables[j].setSourceData is not a function

From my understanding, Google Apps Script doesn't provide a direct method to change the source data range of an existing pivot table. I'm looking for a solution or workaround to achieve this. Any guidance or suggestions would be greatly appreciated.

Thank you in advance for your expertise and assistance!

[Tanaike]

I think that there is no method of `setSourceData` in the Class PivotTable. https://developers.google.com/apps-script/reference/spreadsheet/pivot-table Please be careful about this. By the way, where did you find this method?

And, I think that in order to update only the source range of the existing pivot table, in the current stage, it is required to use Sheets API. This thread on Stackoverflow might be useful. https://stackoverflow.com/a/76091894

If this was not useful, I apologize.