CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetschartsgoogle-slides
Jayy Jangam
Jayy Jangam

Reputation: 69

How to add multiple charts into 1 google slides from google sheets

I am trying to add multiple charts into Google slides, from a designated google sheet using apps script. So 1 of my slides will have 2 charts, and if I need to insert more than 2 charts, the slide should automatically duplicate itself and then more charts can be inserted. However I can't get the right chart to be inserted, and when it duplicates the slide, it seems to be inserting the same chart over and over again.

However I am running into a couple of issues that I need help with, I was not able to find the answer on my own.

Here is the code, and the issues I am running into below that:

function importpieCharts() {
  var dataSpreadsheetUrl = "https://docs.google.com/spreadsheets/d/1tPc0KU2uYuN4rO32tW-s6lql_IW57kbDUdSR8ZSkVXY/edit#gid=0"; //make sure this includes the '/edit at the end
  var ss = SpreadsheetApp.openByUrl(dataSpreadsheetUrl);
  var deck = SlidesApp.getActivePresentation();
  var deckTitle = SlidesApp.getActivePresentation().getName();
  var sheet = ss.getSheetByName(deckTitle);
  var charts = sheet.getCharts();
  var slides = deck.getSlides();
  var pieChartTemplate = slides[1];
    
   charts.forEach(function(chart){
   if(chart.modify().getChartType() == "PIE"){
   pieChartTemplate.duplicate();
   var updateSlide = deck.getSlides()[1];
   var pageElements = updateSlide.getPageElements();
   
   pageElements.forEach(function(pageElement){
   if(pageElement.getPageElementType() == "SHAPE"){
   if(pageElement.asShape().getShapeType() == "RECTANGLE"){
      
     pageElement.asShape().replaceWithSheetsChart(chart);
     }}});
     var presLength = deck.getSlides().length;
     updateSlide.move(presLength-2);
     }});
    }
  1. Whenever I run the code, I am unable to insert 2 or more unique charts into 1 slide.
  2. When the code runs, it create more duplicates than necessary, in this particular case, the slide would only need to be duplicated once.
  3. After duplication, the code does not identify the correct chart to be inserted into google slides.

Please help out with this, or point me in the right direction please! any help is much appreciated:) Here are the relevant file links: Google slides: https://docs.google.com/presentation/d/1CyUkJ7S4eq00MRol3RzcdyAG1m6ovUFBK0H9fxfu-28/edit?usp=sharing Google Sheets: https://docs.google.com/spreadsheets/d/1tPc0KU2uYuN4rO32tW-s6lql_IW57kbDUdSR8ZSkVXY/edit?usp=sharing

disclaimer: I am a total novice, and any knowledge I have about apps script is completely self learnt in order to make my day job easier. I am of course, genuinely interested in the code and its applications

Upvotes: 1

Views: 1089

Answers (1)

Tanaike
Tanaike

Reputation: 201428

I believe your goal as follows.

  • You want to replace {{placeholder for chart}} with the Spreadsheet chart in Google Slides.
  • In your template slide, there are 2 {{placeholder for chart}}. Also, there are several charts in Google Spreadsheet.
  • You want to replace {{placeholder for chart}} with each Spreadsheet chart in order of the charts.

Moification points:

  • In your script, 2 loops are used. And at the inner loop, one chart is used for replacing. By this, each slide has the same 2 charts. I thought that this might be the reason of your issue.
  • In order to replace {{placeholder for chart}} with each Spreadsheet chart in order of the charts, only one chart is required to be replaced at the inner loop. For this, I would like to propose to add a condition for checking to copy the template slide.

When above points are reflected to your script, it becomes as follows.

Modified script:

function importpieCharts() {
  var dataSpreadsheetUrl = "https://docs.google.com/spreadsheets/d/1tPc0KU2uYuN4rO32tW-s6lql_IW57kbDUdSR8ZSkVXY/edit#gid=0"; //make sure this includes the '/edit at the end
  var ss = SpreadsheetApp.openByUrl(dataSpreadsheetUrl);
  var deckTitle = SlidesApp.getActivePresentation().getName();
  var sheet = ss.getSheetByName(deckTitle);
  var charts = sheet.getCharts();
  var slides = deck.getSlides();
  var pieChartTemplate = slides[1];
  
  // I modified below script.
  var copy = true;
  var updateSlide;
  charts.forEach(function(chart, idx, a) {
    if(chart.modify().getChartType() == "PIE"){
      if (copy) {
        updateSlide = pieChartTemplate.duplicate();
        var presLength = deck.getSlides().length;
        updateSlide.move(presLength - 2);
        copy = false;
      }
      var pageElements = updateSlide.getPageElements();
      for (var i = 0; i < pageElements.length; i++) {
        var pageElement = pageElements[i];
        if (pageElement.getPageElementType() == "SHAPE" && pageElement.asShape().getShapeType() == "RECTANGLE") {
          pageElement.asShape().replaceWithSheetsChart(chart);
          break;
        }
      }
      if (!pageElements.some(function(p) {return p.getPageElementType() == "SHAPE" && p.asShape().getShapeType() == "RECTANGLE"})) {
        copy = true;
      }
      
      // When the following script is used, the template sheet is removed at the last loop.
//      if (idx == a.length - 1) deck.getSlides().pop().remove();
    
    }
  });
}
  • In this modified script, when script is finished, the template slide is at the last slide. If you want to remove it, please use if (idx == a.length - 1) deck.getSlides().pop().remove(); in above script.

Note:

  • As the additional information, in above script, when updateSlide.move(presLength - 2) is modified to updateSlide.move(presLength), when the script is finished, the template slide is 2nd page of Google Slides.

Upvotes: 2

Related Questions