I have multiple InDesign files with linked .xls tables. About 150 tables per file, and these files update quite frequently (adding and removing rows etc).
And I was looking for a plugin that could do that, and was told that this should be scriptable.
And since I am not that deep into the scripting scene,does anyone know of script that can do the following?
What I would want to happen is a hight of a row of empty cells to be 7pt.
The hight of a cell with content to be a minimum of 14pt, unless this cell contains multiple lines it should increase by 7pt till it can fit all content (and decrease by 7pt if a line is removed in an update of the .xls file). The reason why we would need 7pt increments is that multiple lines often won't fit in a 14pt cell hight. And most designs call for a grid to work in.
A cell with a certain Cell-style to have a hight of 21pt.
Hope the attached example helps to clarify the question.
1 Correct answer
Also we have cells that should have a set height (subtotal and top-value)
In the above I am talking about cell-styles (subtotal, top-value)
Whoops. I suppose I should have checked, sorry!
Add ", styleName" to the var declaration on line 5, and then add this at line 13, before the "if (initial) {":
styleName = row.cells[0].appliedCellStyle.name;
if (styleName === "top-value" ||
styleName === "subtotal"
) {
continue;
}
31
Replies
Hi John,
Thanks again for your great post, and I stand corrected in one of my questions. I did not make myself clear.. but yes, a pony would be nice 
Mark:
Also we have cells that should have a set height (subtotal and top-value)
In the above I am talking about cell-styles (subtotal, top-value) 
I will try your script the first thing tomorrow when I am back in the office on the test document.
Thanks again for your great help!
Also we have cells that should have a set height (subtotal and top-value)
In the above I am talking about cell-styles (subtotal, top-value)
Whoops. I suppose I should have checked, sorry!
Add ", styleName" to the var declaration on line 5, and then add this at line 13, before the "if (initial) {":
styleName = row.cells[0].appliedCellStyle.name;
if (styleName === "top-value" ||
styleName === "subtotal"
) {
continue;
}
Hi John,
I now have made this script, and it works pretty good sofar. I only get a cell height of 14.173 pt at cell-style "top-value" and at 1 of the 2 subtotal fields in my test document I get the correct 21 pt, but at the other one I get 20.239 pt. To me this seems odd, since the height is set to 'exact' in InDesign.
Any idea where it gets these values from?
If I set all cells to an exact height of 50pt, the script resizes all the cells to the correct height, but leaves the cells with Cell-Style names 'top-value' and 'subtotal' at 50pt. So in a way I can do a workaround by setting all the cells to 21pt height, and then running the script (or is this the way it should run?)
---
After testing on some tables I get the following error message (sorry that it is in Dutch):
var debug = 0;
function fitTable(table, initial) {
var i, row, contentLength, styleName,
noOverflow = true;
for (i=0; i<table.rows.length; i++) {
row = table.rows;
contentLength = row.contents.join("").length;
if (debug > 1) {
$.writeln("Row "+i+" has height "+ row.height+" "+contentLength+" <"+table.rows.contents+">");
}
styleName = row.cells[0].appliedCellStyle.name;
if (styleName === "top-value" ||
styleName === "subtotal"
) {
continue;
}
if (initial) {
if (contentLength === 0) {
row.height = 7;
} else {
row.height = 14;
}
} else {
if (row.overflows) {
row.height += 7;
noOverflow = false;
if (debug > 0) {
$.writeln("row "+i+" overflows "+row.overflows);
}
}
}
}
return noOverflow;
}
function main() {
var d=app.activeDocument,
tables=d.stories.everyItem().tables.everyItem().getElements(),
overflowingTables, tries=0, safetyValve=10;
// All units in points. Not a persistent engine, so we need not reset this.
app.scriptPreferences.measurementUnit = MeasurementUnits.POINTS;
// Set the initial size.
for (i=0; i<tables.length; i++) {
fitTable(tables, true);
}
// Recompose the document, then check each row for overflow.
while (overflowingTables !== 0) {
if (tries++ >= safetyValve) {
alert("Exceeded safety valve\n"+
"Tried to recompose document "+tries+" times, something is wrong.\n"+
"Try setting debug=1.");
return false;
}
if (debug>0) {
$.writeln("Composition check with "+overflowingTables+" overflows.");
}
overflowingTables=0;
app.activeDocument.recompose();
for (i=0; i<tables.length; i++) {
if (!fitTable(tables, false)) {
overflowingTables++;
}
}
}
}
main();
Mark:
I now have made this script, and it works pretty good sofar. I only get a cell height of 14.173 pt at cell-style "top-value" and at 1 of the 2 subtotal fields in my test document I get the correct 21 pt, but at the other one I get 20.239 pt. To me this seems odd, since the height is set to 'exact' in InDesign.
Any idea where it gets these values from?
To be honest...no, I have no idea. Perhaps you could post a test document.
The script should be ignoring the height of any rows whose first cell is styled top-value or subtotal, so I wonder if they are pre-existing.
Also, it sets cell heights to 7 or 14 point, and then increments them by 7 points. It's hard to see how you would get 14.173pt or 20.239pt.
14.173pt is 5mm, which might be a clue. 20.239pt is 7.14mm, which is not so much a clue.
Have you run the script with debug=1 or debug=2? It should give you row-by-row insight. And of course you can add more $.writeln() statements in different parts of the script to see what is going on.
If I set all cells to an exact height of 50pt, the script resizes all the cells to the correct height, but leaves the cells with Cell-Style names 'top-value' and 'subtotal' at 50pt. So in a way I can do a workaround by setting all the cells to 21pt height, and then running the script (or is this the way it should run?)
Err, the idea was that top-value and subtotal should be unchanged from where they were at the start. Was that not your intent?
After testing on some tables I get the following error message (sorry that it is in Dutch): [row.contents.join is not a function]
OK, so this line of the script:
contentLength = row.contents.join("").length;attempts to determine if a row is empty, and it is failing for some reason. Perhaps involving something with merged cells?
It takes "row.contents," which it assumes to be an array of strings, and joins that array together as a single long string, and measures the length of that string.
According to the documentation, row.contents need not be an array of strings, it can also be a single string, or a special character enumerator or array thereof. So clearly what I wrote wasn't general enough. I guess it needs to be:
var rowContents;
...
rowContents = row.contents;
if (rowContents.hasOwnProperty("join")) {
contentLength = rowContents.join("").length;
} else {
contentLength = rowContents.length;
}
Which takes care of the Array or not-Array problem. I'm not sure what to do about the SpecialCharacters enumerators. I can't seem to find a case where they happen, even with stuff like Frame Break or Page Numbering inserted. It's probably fine not to worry about them.
Amazing
It works like a charm... You have helped me out heaps!!
I realise I am asking a lot here, but is it possible to give the
styleName = row.cells[0].appliedCellStyle.name;
if (styleName === top-value ||
styleName === subtotal
)
a set height of 21 points and then use the same 7pt increment when it's content overflow asks for it.
Learning a lot here... I think a proper Javascript course is looking more and more needed
Thanks again John,
From what I understand from your post, the
styleName = row.cells[0].appliedCellStyle.name;
// Below are the cell-style names that will be 21 points in height
if (styleName === "top-value" ||
styleName === "subtotal"
) {
row.height = 21;
}
} else {
if (row.overflows) {
// If 14 points is not enough to hold the content, it will expand by 7 point increments
row.height += 7;
noOverflow = false;
if (debug > 0) {
$.writeln("row "+i+" overflows "+row.overflows);
}
}
part is replaced as a whole by:
styles = row.cells.everyItem().appliedCellStyle;
while (style = styles.pop()) {
styleName = style.name;
if (styleName === "top-value" ||
styleName === "subtotal"
) {
row.height = 21;
break;
}
}
Forgive the indenting issues (somehow TextMate messes it up in copy/paste).
If I do this, the 7pt increment part of the script is deleted
If I leave that part of the script, like this:
styles = row.cells.everyItem().appliedCellStyle;
while (style = styles.pop()) {
styleName = style.name;
if (styleName === "top-value" ||
styleName === "subtotal"
) {
row.height = 21;
break;
}
}
if (row.overflows) {
// If 14 points is not enough to hold the content, it will expand by 7 point increments
row.height += 7;
noOverflow = false;
if (debug > 0) {
$.writeln("row "+i+" overflows "+row.overflows);
}
}
It gives me the following error message.
In the ExtenScript toolkit it stalls at:
Row 0 has height 14 78 <
Financial assets carried at fair value,Note,December 31, 2011,December 31, 2010>
Row 1 has height 14 60 <Carrying ,Effect of reasonably ,Carrying ,Effect of reasonably >
Here the table that it seems to have issues with.
Could it have something to do with combined and split rows?
It seems that it tries to extend the height of the cell that contains: 'Carrying •', but doing this also requires the first cell to grow as well. And that won't work?
AdChoices