javascript - Update Chart Links of a new report copy and new workbook copy - Stack Overflow

admin管理员组

文章数量:1123038

I am using appscript (google for developers) to try and write a script for a monthly report I do. Basically, I take the Google slides that are the same each month and I make a copy, then I make a copy of the corresponding workbook.

function updateChartLinks() {
  // Replace with the ID of the presentation you want to update
  var presentationId = "abcdefg";
  // Replace with the ID of the new spreadsheet you want to link to
  var spreadsheetId = "abcdefg";

  var ss2 = SpreadsheetApp.openById(spreadsheetId);
  var sheet2 = ss2.getSheets()[0];
  var chart2 = sheet2.getCharts()[0];
  Logger.log('spreadsheet2 name is ' + ss2.getName());

  // get all charts from all sheets of the spreadsheet copy 
  var sheets2 = ss2.getSheets();
  var allCharts = []; // keep track of all charts on all sheets of the spreadsheet 
  var allChartsIds = []; // keep track of the ids for all the charts 
  var chartsnum = 0; 
  for (var i = 0; i < sheets2.length; i++) {
    var curSheet2 = sheets2[i];
    if (curSheet2) { // Check if curSheet2 is defined
      var charts2 = curSheet2.getCharts();
      for (var j = 0; j < charts2.length; j++) {
        var curChart2 = charts2[j]; 
        allCharts[chartsnum] = curChart2;
        allChartsIds[chartsnum] = curChart2.getChartId();
        chartsnum++;
        Logger.log('\n chart2 Id is ' + curChart2.getChartId());
      }
    }
  }

  //total number of charts in all sheets of the spreadsheet 
  var lengthAllCharts = allCharts.length;
  var pres2 = SlidesApp.openById(presentationId);
  Logger.log('pres2 name is ' + pres2.getName()); 

  var slides2 = pres2.getSlides(); 
  for (var i = 0; i < slides2.length; i++) {
    var slide = slides2[i];
    var shapes = slide.getShapes();

    shapes.forEach(shape => {
      if (shape.getShapeType() == SlidesApp.ShapeType.CHART) {
        var chart = shape.asChart();
        var chartData = chart.getChartData();

        // Assuming the charts are in the same order as in the new spreadsheet
        var newChart = allCharts.shift();

        chartData.setDataSourceId(newChart.getChartId());
        chart.setChartData(chartData);

        // Force a refresh of the chart data
        chart.refresh();
      }
    });
  }
}

The problem is, the copy of the google slides are still linked to the original workbook, not the copy. These presentations are usually super long with 30-50+ charts. Rather than copy and paste each individual chart, I'd like to write a script that takes the copy of the presentation and re-links the embedded charts with the copy of the workbook. I would like to run it manually. This is as far as I got, I have a feeling the bottom of the code is where things are going awry-- the code will run, but at the end when I refresh the copy of the presentation, it is still linked to the original workbook (not the copy).

本文标签： javascriptUpdate Chart Links of a new report copy and new workbook copyStack Overflow