Search code examples
google-apps-scriptgoogle-slides-api

getTables().getHeight does not work


I insert a table in an empty slide. Then I fill it with different texts. After this I want to get the height of the table. The result from .getHeight is null. The .getHeight method works good for shapes and images, but not for tables. How can I get the table height?

function getTableHeight(){

     var presentation = SlidesApp.openById('myPresentationId');    
     var slides = presentation.getSlides();
     var slideId = 'mySlideId';
     var slide = slides.filter(function(e){return e.getObjectId() == slideId})[0]; 

     slide.insertTable(5, 5);

     var tableHeight = slide.getTables()[0].getHeight();

     Logger.log(tableHeight);

}

Here is the full script. It works fine, but I need a function to split the imported table, when there is not enough space on the slide.

function getTableFromSpreadsheetoToSlides(){

  var presentation = SlidesApp.openById('myPresentationId');
  var slideId = "mySlideId";
  var slides = presentation.getSlides();
  var slide = slides.filter(function(e){return e.getObjectId() == slideId})[0]; 

  var spreadsheet = SpreadsheetApp.openById('mySpreadsheetId');
  var sheet = spreadsheet.getSheetByName('ActionItems');

  var numRows = sheet.getDataRange().getNumRows();
  var numColumns = sheet.getDataRange().getNumColumns();

  var values = sheet.getSheetValues(1, 1, numRows, numColumns).valueOf();

  var table = slide.getTables()[0];

  var numRowsToDelete = table.getNumRows() - 1;

  // remove old tableRows except the table head
  for (i = numRowsToDelete; i > 0; i--) {
    table.getRow(i).remove();
  }

  // new imported table rows append to the existing table head
  for ( var i = 1; i < numRows; i++ ) {

      table.appendRow();

      for ( var j = 0; j < numColumns; j++ ) {

        var text = table.getCell(i, j).getText().setText(values[i][j]);

      }

      // HERE I need a query how long the table in slide is. If it is longer/higher than the slide is, it should be split and the next row should go to a new slide

  }
}

Solution

  • How about this workaround? getHeight() cannot be used for Table as @Randy D says. So I propose a workaround using Slides API.

    • When the slide information is retrieved using Slides.Presentations.Pages.get() of Slides API, the information of tables is also included. In the information, the height and width of table are also included.
    • But the unit is EMU (English Metric Unit), and the height and width is separated by each cell. So it is required to sum each height and width while modifying the unit.

    The modified script reflected this is as follows.

    In order to use this script, please enable Slides API at Advanced Google Services and API console.

    Sample script :

    function getTableHeight(){
      var presentation = SlidesApp.openById('myPresentationId');
      var slides = presentation.getSlides();
      var slideId = 'mySlideId';
      var slide = slides.filter(function(e){return e.getObjectId() == slideId})[0]; 
      slide.insertTable(5, 5);
    
      // Added scripts from here
      var pageElements = Slides.Presentations.Pages.get(presentation.getId(), slideId).pageElements;
      var result = [];
      for (var i in pageElements) {
        var rowHeight = 0;
        var rowWidth = 0;
        if (pageElements[i].table) {
          for (var j in pageElements[i].table.tableRows) {
            rowHeight += pageElements[i].table.tableRows[j].rowHeight.magnitude / 12700;
          }
          for (var k in pageElements[i].table.tableColumns) {
            rowWidth += pageElements[i].table.tableColumns[k].columnWidth.magnitude / 12700;
          }
          result.push({Height: rowHeight, Width: rowWidth});
        }
      }
      Logger.log(result)
    }
    

    Note :

    • Unit of output values is point.
    • Because 12,700 EMUs per point, each values retrieved by pageElements[i].table.tableRows[j].rowHeight.magnitude and pageElements[i].table.tableColumns[k].columnWidth.magnitude are required to be divided by 12,700.
    • When there are several tables in the slide, this sample script retrieves the width and height for each table.

    Reference :

    Edit :

    Flow of additional script :

    1. Retrieve the page height of slides.
    2. Retrieve the height of one row for the exisiting table.
      • In your script, the table has only one row.
    3. Calculate the number of rows to append.
    4. Calculate the remaining rows.
    5. Add rows and values from spreadsheet.

    Modified script :

    function getTableFromSpreadsheetoToSlides(){
    
      var presentation = SlidesApp.openById('myPresentationId');
      var slideId = "mySlideId";
      var slides = presentation.getSlides();
      var slide = slides.filter(function(e){return e.getObjectId() == slideId})[0]; 
    
      var spreadsheet = SpreadsheetApp.openById('mySpreadsheetId');
      var sheet = spreadsheet.getSheetByName('ActionItems');
    
      var numRows = sheet.getDataRange().getNumRows();
      var numColumns = sheet.getDataRange().getNumColumns();
    
      var values = sheet.getSheetValues(1, 1, numRows, numColumns).valueOf();
    
      var table = slide.getTables()[0];
    
      var numRowsToDelete = table.getNumRows() - 1;
    
      // remove old tableRows except the table head
      for (i = numRowsToDelete; i > 0; i--) {
        table.getRow(i).remove();
      }
    
      // From here, I added and modified script.
      var pageHeight = Slides.Presentations.get(presentation.getId()).pageSize.height.magnitude / 12700;
      var rowHeight = getTableHeight(presentation.getId(), slideId);
      var additionalRows = Math.floor(pageHeight / rowHeight);
      var remainingRows = numRows - additionalRows; // You can add the remaining rows using this.
      for ( var i = 1; i < additionalRows; i++ ) { // Modified
        table.appendRow();
        for ( var j = 0; j < numColumns; j++ ) {
          var text = table.getCell(i, j).getText().setText(values[i][j]);
        }
      }
    }
    
    function getTableHeight(slidesId, slideId) {
      var pageElements = Slides.Presentations.Pages.get(slidesId, slideId).pageElements;
      var result = [];
      for (var i in pageElements) {
        var rowHeight = 0;
        var rowWidth = 0;
        if (pageElements[i].table) {
          for (var j in pageElements[i].table.tableRows) {
            rowHeight += pageElements[i].table.tableRows[j].rowHeight.magnitude / 12700;
          }
          for (var k in pageElements[i].table.tableColumns) {
            rowWidth += pageElements[i].table.tableColumns[k].columnWidth.magnitude / 12700;
          }
          result.push({Height: rowHeight, Width: rowWidth});
        }
      }
      return result[0].Height;
    }
    

    Note :

    • This sample script can be used for the case that the each cell height is the same, as you said "each row has the same height".
    • getTableHeight() can be used for the slide with several tables. But in this case, it supposes that there is one table in the slide.
    • This sample script can be used for the case that the table has already been existing. Because when in one running, when the table is created, the created table couldn't retrieve. It couldn't do even if it uses saveAndClose(). I don't know the reason.
      • So until this is solved, please use this sample script for the slide that the table was created.