Skip to main content
DavidW10
Known Participant
December 13, 2022
Question

Using a script to input prices from an Excel sheet into tables on InDesign

  • December 13, 2022
  • 4 replies
  • 6501 views

Hi there,

I'm trying to find a script that will match a product code in the first column of a table with a product code (column A) in an excel sheet and pull back the price (column b) and enter it into the third column of a table on InDesign. 

 

I'm worried it might be a bit too extensive for a simple script to help with. 

 

This might be a long shot, but I've tried for a couple of hours now to get a script I found to work on InDesign to input prices into tables from an Excel sheet. Script below:

// get the active InDesign document
var doc = app.activeDocument;

// open the Excel file and get the sheet
var excelFile = new File("/path/to/excel/file.xlsx");
var excel = new Excel(excelFile);
var sheet = excel.getSheet("Sheet1");

// loop through all rows in the sheet
for (var i = 1; i <= sheet.getLastRow(); i++) {
  // get the code and description from the Excel sheet
  var code = sheet.getValue(i, "A");
  var description = sheet.getValue(i, "B");

  // loop through all cells in the first column of the InDesign table
  for (var j = 0; j < doc.tables.length; j++) {
    var table = doc.tables[j];
    for (var k = 0; k < table.rows.length; k++) {
      var cell = table.rows[k].cells[0];

      // if the code in the InDesign table matches the code in the Excel sheet
      if (cell.contents == code) {
        // insert the description from the Excel sheet into the third column of the InDesign table
        var newCell = table.rows[k].cells.add();
        newCell.contents = description;
      }
    }
  }
}

 

Even after editing the file path to go my excel sheet (attached), it gives me an erorr on line 6 - Excel does not have a constructor:

var excel = new Excel(excelFile);

 

Any help or pointing me into the right direction would help a lot, thanks!

 

David

This topic has been closed for replies.

4 replies

Community Expert
December 15, 2022

Hi @DavidW10 ,

maybe the following algorithm would serve your needs:

InDesign's ExtendScript can place an Excel table to a new document. Check method place() with object document.

Further you could use ExtendScript to remove all not necessary columns in that placed table and build two arrays with the contents of each of the remaining table columns: myTable > columns[n].contents where n is the index of the column, 0 for the first column, 1 for the second one.

 

That gets you the raw data you are looking for.

 

Regards,
Uwe Laubender
( Adobe Community Expert )

Community Expert
December 15, 2022

A slightly different idea based on the one with placing that Excel file.

 

Note that ExtendScript is able to convert an InDesign table, the two columns of data left, to text.

With a simple GREP expression that is using a positive lookbehind with the article number plus a tab you'll be able to find the corresponding price.

 

Regards,
Uwe Laubender
( Adobe Community Expert )

Robert at ID-Tasker
Brainiac
December 14, 2022

I've done a lot of tools to build catalogs directly from Excel - but they are to complicated to post the source code - so here is an idea for you - instead of trying to read and process external files - just copy Excel as another table and work on tables in InDesign - source & destination - will be much easier for you.

 

DavidW10
DavidW10Author
Known Participant
December 15, 2022

I don't have any scripting experience at all and aren't really sure what to do with the scripts i've been given (other than put them in the right place and run them) but our Price List is 136 pages long with about 4 tables on each page, and our prices change often so to manually change each price would take a long, long time. (some aren't done by one multiplication, a range of products could be by 3% but others 20%). So i'm just trying to get a script to work which will make my life 20 times easier haha.

 

Appreciate you all trying to help me

Participating Frequently
December 15, 2022

I can help you, plese send me a csv file and one page indesign example.

Community Expert
December 13, 2022

Have a look at the following discussion, it uses VBScript to read Excel file you can use a similiar thing to use Applescript on a MAC.

https://community.adobe.com/t5/indesign-discussions/get-data-directly-from-excel/m-p/8943275#M45960

-Manan

DavidW10
DavidW10Author
Known Participant
December 13, 2022

Hi there, I am currently on windows and using Notepad++, I get this error when trying to use that script you have linked.

 

Appreciate your help!

 

Brainiac
December 13, 2022

Hello @DavidW10,

 

The error message shows that the script is failing when trying to find the .csv file.

Make sure the .csv file is in the same location and is named as shown in the top portion of the error message, or change the file path in the script to point to the location of the .csv file.

 

Regards,

Mike

Participating Frequently
December 13, 2022

There is no class that can read an excel file within indesign, to read the excel file you would have to unzip the xlsx file and read the xml files relating to the sheet you are interested in, alternatively you could use a csv file, i.e. a text file. If you need to use the excel file I can develop a class for reading the file for a fee.

DavidW10
DavidW10Author
Known Participant
December 13, 2022

A CSV would be fine, but I'm not familiar with scripts and it still gives me the same error: line 6 - Excel does not have a constructor: var excel = new Excel(excelFile);

 

How would I fix this?

Participating Frequently
December 13, 2022

// get the active InDesign document
var doc = app.activeDocument;

// open the Excel file and get the sheet
//var excelFile = new File("/path/to/excel/file.xlsx");
//var excel = new Excel(excelFile);
//var sheet = excel.getSheet("Sheet1");

var myCSVfile = File.openDialog("Select CSV file", "CSV files:*.csv", false);

if (myCSVfile != null)
{
myCSVfile.open("r");
var myCSVFileRead = myCSVfile.read().split("\n");

// loop through all rows in the sheet
for (var i = 1; i <= myCSVFileRead.length; i++) {
// get the code and description from the Excel sheet
var columns = myCSVFileRead[row].split(',"');
var code = columns[0];
var description = columns[1];

// loop through all cells in the first column of the InDesign table
for (var j = 0; j < doc.tables.length; j++) {
var table = doc.tables[j];
for (var k = 0; k < table.rows.length; k++) {
var cell = table.rows[k].cells[0];

// if the code in the InDesign table matches the code in the Excel sheet
if (cell.contents == code) {
// insert the description from the Excel sheet into the third column of the InDesign table
var newCell = table.rows[k].cells.add();
newCell.contents = description;
}
}
}
}
}