Script to Batch Process InDesign Files, Compiling the File Name, and Data from a Table, Into a Spreadsheet

Question

Our spec sheets are made with InDesign, and there is a certain set of data points in one of the tables that we'd like to extract and put into a spreadsheet. We have thousands of these files though, so I'd like to see if there's a way to script an automated process.

I've used scripts for other tasks before, and even hacked at a few to create little solutions; but this one is well beyond me, and I just want to see if it's even possible.

The spec sheets are two pages. On page 2, there's a table with specs on it. It's a 2-column table, with section titles merged across both columns, and data cells have the data title on the left, and the data itself on the right. The data we need is the last 4 rows of the table. The header cell is "SHIPPING DATA", and then the four rows are weight, height, width, and depth. This data is always the last four rows of the table in every spec sheet.

So, I'm wondering if it's possible that a script could:

• either process a batch of files in a folder, or any open files,;;
• where per file, it could grab the name of the file and put that into a spreadsheet, row 1 column A;;
• then Find "SHIPPING DATA", select the 4 cells of data beneath that, and paste them into the spreadsheet row 1 columns B-E / might have to grab the data from those four cells one by one;;
• and then move to the next file, putting that same info into row 2

As I said, I don't even know if this would be possible, so I've not tried anything yet. If anyone has any ideas or thoughts on this, I'm all ears. Thanks!

Edit 01: Adding a screenshot that shows the file name and the portion of the table I refer to in my question.

Answer 1

actually, InDesign provides data merging function

https://helpx.adobe.com/uk/indesign/using/data-merge.html

anyway, your idea is possible to do it in script

Answer 2

It could be something like this:

// dialog to select a folder if there are no open documents
if (app.documents.length == 0) {
    var from_folder = true;
    var folder = Folder.selectDialog('Select a folder with INDD files...');
    var files = folder.getFiles('*.indd');
    if (files.length == 0) {
        alert('No INDD files was found in the folder')
        exit();
    }
}

// if there are open documents process them
else {
    var from_folder = false;
    var files = app.documents;
}

// get the data from all tables each of the documents
var all_data = [];
for (var i=0; i<files.length; i++) {
    if (from_folder) var doc = app.open(files[i]);
    else var doc = files[i];
    var data = get_data_from_doc(doc);
    if (data) while (data.length) all_data.push(data.shift());
    if (from_folder) doc.close();
}

// if there is the data put it on the page of a new document
if (all_data.length > 0) {
    var doc = app.documents.add();
    var frame = doc.pages[0].textFrames.add();
    frame.geometricBounds = doc.pages[0].bounds;
    frame.contents = all_data.join('\r');
    frame.parentStory.texts[0].convertToTable();
} else {
    alert('No data was found');
}


// functions ------------------------------------------------------------------

function get_data_from_doc(doc) {
    var tables = get_all_tables_from_doc(doc);
    if (!tables || tables.length == 0) return;
    var data = get_data_from_all_tables(tables, doc);
    if (data && data.length > 0) return data;
}

function get_all_tables_from_doc(doc) {
    var stories = doc.stories;
    var all_tables = [];
    for (var i=0; i<stories.length; i++) {
        var tables = stories[i].tables;
        for (var j=0; j<tables.length; j++) all_tables.push(tables[j]);
    }
    if (all_tables.length > 0) return all_tables;
}

function get_data_from_all_tables(tables, doc) {
    var data = [];
    for (var i=0; i<tables.length; i++) {
        var table = tables[i];
        var data_row = get_data_from_table(table, doc);
        if (data_row) data.push(data_row);
    }
    if (data.length > 0) return data;
}

function get_data_from_table(table, doc) {
    var cell = get_cell_with_word(table, 'SHIPPING DATA');
    if (cell) {
        var index = cell.parentRow.index;
        var cell_1 = table.rows[index+1].cells[1].contents;
        var cell_2 = table.rows[index+2].cells[1].contents;
        var cell_3 = table.rows[index+3].cells[1].contents;
        var cell_4 = table.rows[index+4].cells[1].contents;
        return [doc.name, cell_1, cell_2, cell_3, cell_4].join('\t');
    }
}

function get_cell_with_word(table, word) {
    var cells = table.cells;
    for (var i=0; i<cells.length; i++) {
        if (cells[i].contents.toUpperCase() == word) return cells[i];
    }
}

It reads all the tables from all INDD documents (opened ones, or from selected folder if there are no open INDD documents). And makes a new INDD file with the table like this

file1.indd	448 lbs	58"	40"	43"
file2.indd	...	...	...	...
file3.indd	...	...	...	...