How to remove Google Sheets Chart from Google Slide if Chart has no data-CodePudding

I am inserting an embedded chart from Sheets to Slides using app script. But I would like to remove the chart from the slide if on Google Sheets the chart is empty/has no data.

I want to remove the chart from slides ONLY when it doesn't have data. But keep the chart if there is data

Can you please help me add the right line that would create this condition ?

This is my chart code:

function onOpen() {
// Get the Ui object. 
var ui = SpreadsheetApp.getUi();

// Create a custom menu. 
ui.createMenu('Present Data')
.addItem("Generate Report","generateLandingPagesReport")
.addToUi();
}

function generateLandingPagesReport() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Copy of Overall Performance 1');
var values = sheet.getRange('A2:J23').getValues(); 
var chartRegion1 = sheet.getCharts()[0];
var chartGender1 = sheet.getCharts()[1];

// Access the template presentation
var templateId = "1bXAYGCKkpZhksXz8gTCgFYbNoI1BIhAZakd68VlXHeo";
var template = SlidesApp.openById(templateId);
var templateSlides = template.getSlides();

// Create a Slides presentation, removing the default
// title slide.
var presentationTitle =
ss.getName()   " Presentation";
var slides = SlidesApp.create(presentationTitle);
var defaultSlides = slides.getSlides();
defaultSlides.forEach(function(slide) {
slide.remove()
});
var defaultSlide = defaultSlides [1]; 

// Insert slides from template
var index = 0;
templateSlides.forEach(function(slide) {
 var newSlide = slides.insertSlide(index);
 var elements = slide.getPageElements();
 elements.forEach(function(element) {
   newSlide.insertPageElement(element);
});
index  ;
});

values.forEach(function(page){
if(page[0]){

var landingPage = page[0];
var sessions = page[1];
var newSessions = page[2];
var pagesPer = page[5];
var goalRate = page[7];
var goalValue = page[9];

// Insert slides from template
var index = 0;
templateSlides.forEach(function(slide) {
 var newSlide = slides.insertSlide(index);
 var elements = slide.getPageElements();
 elements.forEach(function(element) {
   newSlide.insertPageElement(element);
});
index  ;
});

defaultSlides = slides.getSlides(); //update the slides array for 
indexes and length
defaultSlide = defaultSlides[1];
newSlide = defaultSlide;
newSlide2 = defaultSlides[2];

var shapes = (newSlide.getShapes());
 shapes.forEach(function(shape){
   shape.getText().replaceAllText('{{landing page}}',landingPage);
   shape.getText().replaceAllText('{{sessions}}',sessions);
   shape.getText().replaceAllText('{{new sessions}}',newSessions);
   shape.getText().replaceAllText('{{pages per session}}',pagesPer);
   shape.getText().replaceAllText('{{goal rate}}',goalRate);
   shape.getText().replaceAllText('{{goal value}}',goalValue);
 })
 var shapes = (newSlide2.getShapes());
 shapes.forEach(function(shape){
   shape.getText().replaceAllText('{{landing page}}',landingPage);
   shape.getText().replaceAllText('{{sessions}}',sessions);
   shape.getText().replaceAllText('{{new sessions}}',newSessions);
   shape.getText().replaceAllText('{{pages per session}}',pagesPer);
   shape.getText().replaceAllText('{{goal rate}}',goalRate);
   shape.getText().replaceAllText('{{goal value}}',goalValue);
 }); 

 presLength = defaultSlides.length; 
 newSlide.move(presLength);
 newSlide2.move(presLength); 
 defaultSlides[0].remove();
 defaultSlides[3].remove();

 } // end our conditional statement
 }); //close our loop of values

 //Get the charts
 var defaultSlides=slides.getSlides();
 var defaultSlide = defaultSlides [1]
 var position = {right: 490, bottom: 190};
 var size = {height: 140, width: 230};
 defaultSlide.insertSheetsChart(
 chartRegion1,
 position.right,
 position.bottom,
 size.width,
 size.height);

 var defaultSlides=slides.getSlides();
 var defaultSlide = defaultSlides [1]
 var position = {right: 200, bottom: 190};
 var size = {height: 140, width: 230};
 defaultSlide.insertSheetsChart(
 chartGender1,
 position.right,
 position.bottom,
 size.width,
 size.height);


 // Create and display a dialog telling the user where to
 // find the new presentation.
 var slidesUrl = slides.getUrl();
 var html = "<p>Find it in your home Drive folder:</p>"
    "<p><a href=\""   slidesUrl   "\" target=\"_blank\">"
    presentationTitle   "</a></p>";

 SpreadsheetApp.getUi().showModalDialog(
 HtmlService.createHtmlOutput(html)
  .setHeight(120)
  .setWidth(350),
  "Report Generated!"
  );
  }

Thank you for your help.

CodePudding user response：

I believe your current situation and your goal are as follows.

You have Google Slides including some charts.
When the chart is "No data", you want to remove the chart from the Google Slides.
You want to achieve this using Google Apps Script.
From your sample Spreadsheet including the sample "No data" chart, your "No data" chart has no values.

In this case, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of the Google Spreadsheet including the charts. And, please set the Google Slides ID to presentationId.

function myFunction() {
  const presentationId = "###"; // Please set the Google Slides ID.

  // 1. Retrieve the charts with "No data".
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const obj = ss.getSheets().reduce((o, sheet) => {
    sheet.getCharts().forEach(chart => {
      const check = chart.getRanges().some(e => {
        const temp = e.getDisplayValues();
        return temp[0].map((_, c) => temp.map(r => r[c])).some(col => col.join("") == "");
      });
      if (check) o[chart.getChartId()] = check;
    });
    return o;
  }, {});

  // 2. Remove the charts with "No data".
  const slides = SlidesApp.openById(presentationId).getSlides();
  slides.forEach(slide => {
    slide.getSheetsCharts().forEach(chart => {
      if (obj[chart.getChartId()]) chart.remove();
    });
  });
}

When this script is run, first, the charts of "No data" are retrieved in an object. And, using this object, the charts in Google Slides are removed.

Note:

This sample script is for your sample Spreadsheet. If the condition of "No data" is changed, this script might not be able to be used. So, please be careful about this.

Sample script:

Note:

References: