0
413
ViewsDeleting empty merged rows at bottom of Slides table with Google Apps Script
I have a Google Apps Script that takes data from a Google Sheet and copies it into a Google Slides Template with a Table. In some cases there are not as many rows of data in the sheet as there are in the template table, and some rows in the table are left partially blank as a result (see screenshot of table below). I wrote a function that removes the empty rows from a Google Slides table (see desired outcome screenshot below), and it works well on other tables that do not have any merged cells (with the modification of using rowIndex++ instead of rowIndex = rowIndex + 3 in the for loop).
function removeEmptyRowsTest(removeId) {
var slides = SlidesApp.openById(removeId).getSlides();
var metricExcepSlide = slides[0];
var metricExcepTables = metricExcepSlide.getTables();
var table = metricExcepTables[0];
var numberOfRows = table.getNumRows();
for (var rowIndex = 1; rowIndex < numberOfRows; rowIndex = rowIndex+3) {
var nextRow = table.getRow(rowIndex);
// A row is assumed empty until proven otherwise
var foundEmptyRow = false;
//Logger.log(numberOfColumns)
Logger.log(foundEmptyRow)
Logger.log(nextRow.getCell(0).getText().asString())
Logger.log(nextRow.getCell(0).getText().asString().length)
if (nextRow.getCell(0).getText().asString().length == 1) {
foundEmptyRow = true;
}
if (foundEmptyRow) {
table.getRow(rowIndex).remove()
numberOfRows--;
rowIndex--;
}
}
}
But when I run the function on the below pictured table, I get the error Exception: This operation is only allowed on the head (upper left) cell of the merged cells.. I'm confused, because I feel like initializing rowIndex to 1 and incrementing by 3 should only apply to the upper left cell of the set of three merged cells. I get the same error when I initialize rowIndex to 0 and/or increment it by 1. Does anybody know how I can avoid this error and delete the empty rows at the bottom of the table?
Link to sample google slides table
1 answers
Answer question0
I believe your goal is as follows.
- You want to delete the row when the columns of "A", "C", "D", and "E" on Google Slides are empty.
- Your goal is shown in your sample images on your question.
- You want to achieve this using Google Apps Script.
In this case, how about the following modification? In this modification, "HEAD" and "MERGED" are checked using the method of getMergeState. The reverse loop is used.
Modified script:
function removeEmptyRowsTest(removeId) {
var slides = SlidesApp.openById(removeId).getSlides();
var metricExcepSlide = slides[0];
var metricExcepTables = metricExcepSlide.getTables();
var table = metricExcepTables[0];
var numberOfRows = table.getNumRows();
// I modified below script.
var checkCols = [0, 2, 3, 4];
for (var r = numberOfRows - 1; r > 0; r--) {
if (table.getRow(r).getCell(0).getMergeState() == SlidesApp.CellMergeState.HEAD) {
var checkEmpty = checkCols.filter(e => table.getRow(r).getCell(e).getText().asString().trim() != "");
if (checkEmpty.length == 0) {
for (var dr = 3; dr > 0; dr--) {
table.getRow(r + dr - 1).remove();
}
}
}
}
}
- When the above script is run, the columns "A", "C", "D", and "E" of the table on Google Slides are checked, and when those columns are empty values, the row is removed.