Search code examples
google-apps-scriptgoogle-sheetsgoogle-visualization

How to delete a chart set in a specific range in a spreadsheet, using Google Apps Script?

I got a code building charts in the active cell's row, in column 9. Now, before a script deletes the source sheet of that chart, I'd like to get the chart set within that specific range (row,Col) and delete it.

var charts1 = newSheet.getCharts();
      chart = charts1[charts1.length - 1];
      chart = newSheet.newChart()
        .asLineChart()
        .addRange(newSheet.getRange(ticker + '!M71:P175'))
        .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS)
        .setTransposeRowsAndColumns(false)
        .setNumHeaders(1)
        .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH)
        .setOption('bubble.stroke', '#000000')
        .setOption('useFirstColumnAsDomain', false)
        // ...other .setOption()'s...
        .setOption('width', 305)
        .setPosition(activeCell, 9, 1, 1) //This is where the chart is set to be built
        .build();
      listOfStocks.insertChart(chart);

I've tried getting that range, but getCharts() to then delete it, but nothing of this sort comes up as an option.

How can this be done?

Solution

  • I believe your goal as follows.

    • You want to remove a chart using the coordinate of cell on the chart using Google Apps Script.

    In order to remove the chart, you can use the method of removeChart in Class Sheet. And, the coordinate of the chart can be retrieved by the method of getContainerInfo. When the sample script is prepared using these methods, it becomes as follows.

    Sample script:

    Before you use this script, please set the variables of searchRow, searchColumn and sheetName.

    function myFunction() {
  const searchRow = 1;
  const searchColumn = 9;
  const sheetName = "Sheet1";

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  sheet.getCharts().forEach(c => {
    const container = c.getContainerInfo();
    if (container.getAnchorRow() == searchRow && container.getAnchorColumn() == searchColumn) {
      sheet.removeChart(c);
    }
  });
}
    • In this sample script, the chart which is put to the cell "I1" is removed. In this case, this coordinate is the same with the upper left of the chart.

    Note:

    • If you want to remove all charts in the column "I", you can also use the following script.

        const searchColumn = 9;
  const sheetName = "Sheet1";

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  sheet.getCharts().forEach(c => {
    const container = c.getContainerInfo();
    if (container.getAnchorColumn() == searchColumn) {
      sheet.removeChart(c);
    }
  });

    References: