How to merge duplicate values in array in InDesign?

[Vincent@GYUWorks]

Hi everyone

I am looking for some help, I am new to scripting but I have managed to create a basic script to collect the infomation from a series of tables into one large table with the help of this community, see snippit of code below:

var myDoc = app.activeDocument;
var myFileName = myDoc.name.substr(0,myDoc.name.lastIndexOf("."));
var myTablesArray = new Array;
myTablesArray = myDoc.stories.everyItem().tables.everyItem().getElements();
var numOfHeaderRows = 1;
var numOfBodyRows = myTablesArray.length;
var contentsArray = myTablesArray[0].columns[0].contents;
for (var n=0;n<myTablesArray.length; n++)
{
    contentsArray = contentsArray.concat(myTablesArray[n].columns[1].contents);
};

In some of the tables I have duplicate values:

Filename, File 1.                                     
Code, 1234
Dimensions, 798 (w) x 798 (h)
Material, Card
Notes, Gloss
Quantity,1
Elevation, Bay 1
Approved, Yes

Filename, File 1.                                     
Code, 1234
Dimensions, 798 (w) x 798 (h)
Material, Card
Notes, Gloss
Quantity, 3
Elevation, Bay 2
Approved, Yes

Is it possible to merge the duplicates in each column by sorting the array based on the filename and sum the quantity in row 6. Any help or advice would be greatly appriciated!

Thanks

Vincent

<Title renamed by moderator>

[brian_p_dts]

A few different way you can roll it. I'd roll it like this, assuming all the tables follow this six-row format: 

var myDoc = app.activeDocument;
var myFileName = myDoc.name.substr(0,myDoc.name.lastIndexOf("."));
var myTablesArray = new Array;
myTablesArray = myDoc.stories.everyItem().tables.everyItem().getElements();
var numOfHeaderRows = 1;
var numOfBodyRows = myTablesArray.length;
var contentsArray = myTablesArray[0].columns[0].contents;
//let's make an object!
var o = {};
var fn, qty;
for (var n=0;n<myTablesArray.length; n++) {
    //get just the filename from the 0 index of your contents array
    fn = myTablesArray[n].columns[1].contents[0];
    //parse the 5th index of contents into number by using Regular Expression to fine any number of digits in the line
    qty = Number(myTablesArray[n].columns[1].contents[5].match(/\d+/gi)[0]);
    //if the object doesn't have a filename property, create it, and set quant to 0
    if (!o.hasOwnProperty(fn)) {
        o[fn] = 0;
    }
    //then add your number
    o[fn] += qty;
};
//output your key/value pairs
for (var f in o) {
   $.writeln(f + ": Qty >> " + o[f];
}

Edit: Or are the dupe values in the same table? If so, my method above won't work, but maybe it steers you on the right path. You probably have to do some more parsing of your columns[1] contents values by row to get something right. 

[Vincent@GYUWorks]

Hi @brian_p_dts, thanks for taking the time to have a look at this for me and responding so quickly

I when I tried your code I got the following error

    qty = Number(myTablesArray[n].columns[1].contents[5].match(/\d+/gi)[0]);

null is not a object:

below is the full script and some sample inDesign files if that helps:

var myDoc = app.activeDocument;
if(myDoc.modified == true){
var userResponse = confirm ("Please save changes before continuing");
if (userResponse == true) {
myDoc.save();
} else {
exit();
}
};

app.scriptPreferences.userInteractionLevel = UserInteractionLevels.interactWithAll;
app.findGrepPreferences.findWhat="~N";
var FindGrep=app.activeDocument.findGrep();
for(i=0; i<FindGrep.length; i++)
{
var item = FindGrep[i];
var page = item.parentTextFrames[0].parentPage;
item.contents = page.name;
};

var myWidths = ["60mm", "150mm"];
for(var T=0; T < myDoc.textFrames.length; T++){
for(var i=0; i < myDoc.textFrames[T].tables.length; i++){
for(var j=0; j < myWidths.length; j++){
myDoc.textFrames[T].tables[i].columns[j].width = myWidths[j];
}
}
};

try{
for(var i = 0; i < app.documents.length; i++){
app.activeDocument.stories.everyItem().textVariableInstances.everyItem().convertToText();}
}catch(e){alert("Something gone wrong!r" + e);};

var myDoc = app.activeDocument;
var myFileName = myDoc.name.substr(0,myDoc.name.lastIndexOf("."));
var myTablesArray = new Array;
myTablesArray = myDoc.stories.everyItem().tables.everyItem().getElements();
var numOfHeaderRows = 1;
var numOfBodyRows = myTablesArray.length;
var contentsArray = myTablesArray[0].columns[0].contents;
for (var n=0;n<myTablesArray.length; n++)
{
contentsArray = contentsArray.concat(myTablesArray[n].columns[1].contents);
};

var w = new Window ("palette", "Progress", undefined, {closeButton: false});
w.add ("statictext", undefined, "Executing")
w.pbar = w.add ('progressbar', undefined, 0, contentsArray.length);
w.pbar.preferredSize.width = 300;
w.show();
w.update();
for (var i = 0; i < contentsArray.length; i++){
w.pbar.value = i+1;
$.sleep(20);
};

var myNewDoc = app.documents.add();
with(myNewDoc.documentPreferences){
pageHeight = "1000mm";
pageWidth = "600mm";
pagesPerDocument = 1;
pageOrientation = PageOrientation.portrait;
};

var dateStamp = getDateStamp();
myNewDoc.name = myFileName + "_Master Table";
myNewDoc.paragraphStyles.add({name:"Table Header", justification:Justification.CENTER_ALIGN, appliedFont:"Averta", fontStyle: "Bold", pointSize:10, leading:12, fillColor: "Paper"});
myNewDoc.paragraphStyles.add({name:"Page Title", justification:Justification.CENTER_ALIGN, appliedFont:"Averta", fontStyle: "Regular", pointSize:36, leading:32, fillColor: "Black"});
myNewDoc.paragraphStyles.add({name:"Page Sub Title", justification:Justification.CENTER_ALIGN, appliedFont:"Averta", fontStyle: "Light", pointSize:22, leading:32, fillColor: "Black"});
myNewDoc.paragraphStyles.add({name:"Table Text", justification:Justification.CENTER_ALIGN, appliedFont:"Averta", fontStyle: "Light", pointSize:9, leading:12, fillColor:"Black"});
myNewDoc.cellStyles.add({name:"Header", rightEdgeStrokeWeight: "0.25", leftEdgeStrokeWeight: "0.25", topEdgeStrokeWeight: "0.25", bottomEdgeStrokeWeight: "0.25", fillColor: "Black", verticalJustification:VerticalJustification.CENTER_ALIGN, appliedParagraphStyle: "Table Header"});

var myTextHeader = myNewDoc.textFrames.add();
myTextHeader.contents.appliedParagraphStyle = ("Page Title");
myTextHeader.geometricBounds = [10,10,35,590];
myTextHeader.contents = (myFileName + " - Scope of Works ");
myParagraphStyle = myNewDoc.paragraphStyles.item("Page Title");
myTextHeader.parentStory.texts.item(0).applyParagraphStyle(myParagraphStyle, true);

var myTextSubHeader = myNewDoc.textFrames.add();
myTextSubHeader.contents.appliedParagraphStyle = ("Page Sub Title");
myTextSubHeader.geometricBounds = [35,10,25,590];
myTextSubHeader.contents = ("Generated by " + app.userName + " " + dateStamp);
myParagraphStyle2 = myNewDoc.paragraphStyles.item("Page Sub Title");
myTextSubHeader.parentStory.texts.item(0).applyParagraphStyle(myParagraphStyle2, true);

var myTextFrame = myNewDoc.textFrames.add();
myTextFrame.geometricBounds = [40,10,990,590];
var myMasterTable = myTextFrame.tables.add({columnCount:8, numOfHeaderRows:1, bodyRowCount:contentsArray.length});

var myHeaderStyle = myNewDoc.textFrames[0].tables[0].rows[0].getElements();
for(i=0; i<myHeaderStyle.length; i++){
myHeaderStyle[i].cells.everyItem().appliedCellStyle = app.activeDocument.cellStyles.itemByName("Header");
myHeaderStyle[i].cells.everyItem().clearCellStyleOverrides();
};

myMasterTable.rows.everyItem().height = "8mm";
myMasterTable.columns[0].width = "120mm";
myMasterTable.columns[1].width = "40mm";
myMasterTable.columns[2].width = "40mm";
myMasterTable.columns[3].width = "70mm";
myMasterTable.columns[4].width = "200mm";
myMasterTable.columns[5].width = "20mm";
myMasterTable.columns[6].width = "60mm";
myMasterTable.columns[7].width = "30mm";

myMasterTable.contents = contentsArray;

app.findGrepPreferences=app.changeGrepPreferences=null;
app.findGrepPreferences.findWhat="\n";
app.changeGrepPreferences.changeTo="";
app.activeDocument.changeGrep();

app.findGrepPreferences=app.changeGrepPreferences=null;
app.findGrepPreferences.findWhat="\r";
app.changeGrepPreferences.changeTo="";
app.activeDocument.changeGrep();

app.findGrepPreferences=app.changeGrepPreferences=null;
app.findGrepPreferences.findWhat="_LR";
app.changeGrepPreferences.changeTo="";
app.activeDocument.changeGrep();

function getDateStamp(){
var d = new Date();
var mstr = (d.getMonth() + 1).toString();
var mm = ("00" + mstr).substr(-2);
var dstr = d.getDate().toString();
var dd = ("00" + dstr).substr(-2);
var fullYear = (d.getYear().toString());
var yy = fullYear.substr(-2);
var dateStamp = "("+dd+"/"+mm+"/"+yy+")";
return dateStamp;
};
try
{
myNewDoc.save();
myNewDoc.close();
}
catch (e)
{
};
//app.documents.everyItem().close(SaveOptions.NO);
app.activeDocument.close(SaveOptions.NO)

Also could anyone offer advise on the best resources or ways to help improve my knowledge base.

Thanks again

Vincent

[Vincent@GYUWorks]

Hi @brian_p_dts , thanks for the response,

When I run the code I get an error on the following line: Error 21. null is not an object.

qty = Number(myTablesArray[n].columns[1].contents[5].match(/\d+/gi)[0]);

I have included the script and some sample files if that helps

Thanks once again

Vincent

[rob day]

HI Vincent@GYUWorks , Just to clarify, are you trying to merge any rows with the same text in column 1 (the file name)?

So this:

File1.pdf  1234 210 (w) x 297 (h) Card Gloss 12 Bay 1 Yes
File2.pdf  1234 210 (w) x 297 (h) Card Gloss 25 Bay 2 Yes
File3.pdf  1234 210 (w) x 297 (h) Card Gloss 12 Bay 3 Yes
File1.pdf  1234 210 (w) x 297 (h) Card Gloss 30 Bay 4 Yes
File2.pdf 1234 210 (w) x 297 (h) Card Gloss 8 Bay 5 Yes

Merges to this:

File1.pdf  1234 210 (w) x 297 (h) Card Gloss 42 Bay 1 Yes
File2.pdf  1234 210 (w) x 297 (h) Card Gloss 32 Bay 2 Yes
File3.pdf  1234 210 (w) x 297 (h) Card Gloss 12 Bay 3 Yes

[Vincent@GYUWorks]

Hi Rob, thanks I really appriciate the response!
That is correct, I would like the finished table to look like the example above if that's possible

Vincent

[brian_p_dts]

The table getElements array is picking up the blank parent page table, too. To get around that, use isNaN to check the value of the cell before we try to match it to a digit: 

for (var n=0;n<myTablesArray.length; n++) {
        //get just the filename from the 0 index of your contents array
        fn = myTablesArray[n].columns[1].contents[0];
        //parse the 5th index of contents into number by using Regular Expression to fine any number of digits in the line
        qty = myTablesArray[n].columns[1].contents[5];
        if (isNaN(qty)) { continue; }
        qty = Number(qty.match(/\d+/gi)[0]);
        //if the object doesn't have a filename property, create it, and set quant to 0
        if (!o.hasOwnProperty(fn)) {
            o[fn] = 0;
        }
        //then add your number
        o[fn] += qty;
    };

[Vincent@GYUWorks]

Thanks @brian_p_dts , that worked great! but my outputted table doesn't have any contents

Vincent

[brian_p_dts]

I did not take, nor have, the time to go through your whole code to see what you're doing with the values you wold be getting from that object. But if you return that object from its own discrete function you should be able to get what you need.  

[Vincent@GYUWorks]

No problem, thanks for all your help!

[rob day]

Hi Vincent@GYUWorks , Does @brian_p_dts ’s code help you with removing the duplicates? You could add the new table using your existing code, and clean it with this function:

//call the cleanTable function after you add the contents to the table
myMasterTable.contents = contentsArray;
cleanTable(myMasterTable, 5)

/**
* Removes duplicate rows and updates column content
* @ param the table to clean 
* @ param the column number to update
* @ return void 
*/
function cleanTable(t, col){
    var ra = t.rows.everyItem().getElements();
    var fn, cnt, nn;
    var dupes = [];
    for (var i = 0; i < ra.length; i++){
        cnt = 0
        nn = 0
        fn = ra[i].cells[0].contents;
        for (var j = i; j < ra.length; j++){
            if (ra[j].cells[0].contents == fn) {
                cnt = cnt + Number(ra[j].cells[col].contents)
                nn ++
                if (nn > 1) {
                    ra[i].cells[col].contents = cnt.toString()
                    dupes.push(ra[j])
                }
            } 
        }; 
    };
    var n = dupes.length; 
    while (n--) dupes[n].remove();
}

This is what I get:

Also, I have my application view preferences set to Inches, which was breaking the script because you are assuming Millimeters. Maybe add this:

with(myNewDoc.viewPreferences){
    horizontalMeasurementUnits = MeasurementUnits.MILLIMETERS;
    verticalMeasurementUnits = MeasurementUnits.MILLIMETERS;
}

And I think you want the new table’s row count to be this:

var myMasterTable = myTextFrame.tables.add({columnCount:8, numOfHeaderRows:1, bodyRowCount:numOfBodyRows+1});

[Vincent@GYUWorks]

Thanks @rob day, for taking the time to look at this!

[Vincent@GYUWorks]

Hi, one last question please

I have 2 files that repeat 3 times, the code works perfectly but this line is removing all duplicates leaving the array empty

while (n--) dupes[n].remove();

MEN-HDR-2  3
MEN-HDR 3
MEN-HDR-2  2
MEN-HDR 2
MEN-HDR-2  1
MEN-HDR 1

I am looking for the final arry to just be

MEN-HDR-2  3
MEN-HDR 3

any idea's would be greatly appricated!

[nicosh · Accepted solution]

hi Vincent@GYUWorks 

My solution it’s totally different.

I process all the data with arrays, the final goal is to get a final array to populate the final table, so:

1. Populate an array with the columns[1] of every table.

2. Fill an array with all the filenames (file1.pdf, file2.pdf…)

3. Sort and delete duplicates of this array.

4. Group the colums[1] by filename in other array.

5. Do all the process with this last array.

6. Add header content to the final array.

7. Add the final table.

Considerations:

. I’m not an expert in scripting, I would say the opposite. So the script, surely, it can be improve to have less lines of code and more efficient. For example, there is a shorter way to delete duplicates in an array ( var newArray = […new Set(array)] ) but due to the next point I couldn’t test it.

. I have work with inDesign 2020 (15.1). In this version there are some array methods that doesn’t work. I have found an old thread in this community and a link that solve this problem. That’s why the first lines of my code some methods are implemented in array.prototype.

. I don’t know if you delete this lines of ‘prototype’ the script still works.

. I’m Spanish, my English is poor.

This is my code

//Array methods solution for older versions of indesign
//https://community.adobe.com/t5/indesign-discussions/indexof-is-not-a-function/m-p/9050746
//https://www.weepee.de/forum/indesign/viewtopic.php?f=10&t=56

Array.prototype.indexOf = function(/*any value*/ value, /*int*/startPosition) {
    var l = this.length;
    if (startPosition == null) startPosition=0;
    if (startPosition < 0) startPosition=l+startPosition;
    if (startPosition < 0) startPosition = 0;
    for (var i = startPosition; i<l; i++)
        if (this[i]==value)return i;
    return -1;
  }
  
Array.prototype.filter = function (functionName){
    var a = new Array (this.length);
    var l = this.length;
    var c = -1;
    for (var i = 0; i < l; i++) {
      if(functionName(this[i], i, this))
        a[++c] = this[i];
    }
    a.splice(c+1);
    return a;
}



var myDoc = app.activeDocument;
var myFileName = myDoc.name.substr(0,myDoc.name.lastIndexOf("."));
var myTablesArray = new Array;
myTablesArray = myDoc.stories.everyItem().tables.everyItem().getElements();
var header = myTablesArray[0].columns[0].contents;

//Array with all the table.columns[1]
contents_arr = [];

//Array with all the names of table.columns[#].cells[0]
//filename row
allFiles = [];

//allFiles array but sorted and deleted duplicates
files = [];

//Array with all the table.columns[1] grouped by file name
contentsGrouped_arr = [];

//Final array with the data to populate table
finalTbl_arr = [];

Main()

function Main() {
    //Populate array with every col 1
    populateArrayCol(myTablesArray);

    //Get every filename row content (File1.pdf, File2.pdf...)
    getFilesNames(contents_arr);

    //Delete duplicates and sort
    files = delDup(allFiles);

    //Group all file#.pdf contents
    colOrg(contents_arr);

    //Obtain final table
    finalTbl(contentsGrouped_arr);

    //Add header content to final table
    finalTbl_arr.splice(0, 0, header);

    //Add table to text frame
    addTable();    
}

function populateArrayCol(table_arr) {
    for (var i = 0; i < table_arr.length; i++) {
        var col = table_arr[i].columns[1].contents;
        contents_arr.push(col);
    }
}

function getFilesNames(temp_arr) {
    for (var i = 0; i < temp_arr.length; i++) {
        allFiles.push(temp_arr[i][0]);
    }
}

function delDup(temp_arr){
    tempSort_arr = temp_arr.sort();
    final_arr = tempSort_arr.filter(function(element, index) {
        return tempSort_arr.indexOf(element) === index;
    }); 
    return final_arr;  
}

 

function colOrg(table) {
    var temp_arr = [];
    if (table.length > 0) {
        for (var i = 0; i < files.length; i++) {
            var fileName = files[i];
            var fileNameRegEx = new RegExp(fileName);
            for (k = 0; k < table.length; k++) {
                var temp = table[k][0].match(fileNameRegEx);
                if (temp != null) {
                    temp_arr.push(table[k]);
                }              
            }
            contentsGrouped_arr.push(temp_arr);
            temp_arr = [];
        }
    }
}

function finalTbl(finalTbl_param) {
    var quantity = 0
    var elevationTemp_arr = [];
    var elevation_arr = [];
    for (var i = 0; i < finalTbl_param.length; i++) {
        if (finalTbl_param[i].length > 1){
            for (var k = 0; k < finalTbl_param[i].length; k++) {
                var qtyTemp = Number(finalTbl_param[i][k][5]);
                var file_temp = finalTbl_param[i][k][0];
                quantity = quantity + qtyTemp;
                elevationTemp_arr.push(finalTbl_param[i][k][6]);
            }
            elevation_arr = delDup(elevationTemp_arr);
            finalTbl_param[i][0][5] = quantity.toString();
            finalTbl_param[i][0][6] = elevation_arr.toString();
            var removeCols = finalTbl_param[i].length - 1;
            finalTbl_param[i].splice(1, removeCols);
            finalTbl_arr.push(finalTbl_param[i][0]);
            quantity = 0;
            elevationTemp_arr = [];
        } else {
            finalTbl_arr.push(finalTbl_param[i][0]);
        }
    }
}


function addTable() {
    var selection = app.selection[0];
    var props = {
        headerRowCount: 1,
        columnCount: finalTbl_arr[0].length,
        bodyRowCount: (finalTbl_arr.length - 1)
    }
    var finalTbl = selection.insertionPoints[0].tables.add(undefined, undefined, props);
    var headerRow = finalTbl.rows[0];
    headerRow.contents = finalTbl_arr[0];
    for (var i = 1; i < finalTbl_arr.length; i++) {
        finalTbl.rows[i].contents = finalTbl_arr[i];        
    }    
}