Numbers + Node.js + JavaScript : How do I access a numbers document

Question

Level 1

8 points

Numbers + Node.js + JavaScript : How do I access a numbers document

Hello,

I have a Javascript that allows me to read a CSV file. It does so via functionality provided by Node.js. Now I would like to add functionally that allows me to access a Numbers file. In Apple's Script Editor the following works (less the ability to access a CSV file) along with regular JavaScript to read and write to cells:

let Numbers = Application("Numbers");

let allHistory = Numbers.documents[0].sheets[1].tables["All History"];

let principalData = Numbers.documents[0].sheets[2].tables["Principal"];

Now that I am using Node.js for CVS file access the above code does not work. The error is "Application (first line) is not recognized".

Is there a way to access a Numbers file thru Node.js? Or do I need to approach this problem in a different way?

Chris

iMac 27″, macOS 11.4

Posted on Aug 19, 2021 10:42 AM

Reply

Answer 1

SGIII

Level 8

37,532 points

Aug 19, 2021 7:40 PM in response to XChrisAH

You can consider trying something like this.

Paste the CSV data into a Numbers table, such as the one shown on the right.

Add a column that converts the string date in column A to a true Numbers date-time (right-aligned by default) with this formula:

=DATE(LEFT($A2,4),MID($A2,5,2),RIGHT($A2,2))

Use XLOOKUP to bring data for corresponding dates from the CSV Data table into your other table.

=XLOOKUP($A5,CSV Data::C,CSV Data::B,"") filled down Column C (in my example rows above row 5 were filtered out and not visible, but your table may be different.)

More on XLOOKUP here.

No need, I think, for JavaScript.

SG

Reply

Answer 2

XChrisAH Author

Level 1

8 points

Aug 20, 2021 2:45 PM in response to SGIII

Thanks for the input SG. I need to look into Xlookup.

I read up on AppleScript. Turns out it can read a CSV into a list and then interact with a Numbers document.

I have written an AppleScript that does both. In addition I setup my dates in Numbers to be based on the function "Workday" so all of the weekends and holidays are not present.

Now all I have to do is download the CSV file then run a script.

I certainly took the long way around the barn.

Thanks for your insight.

Chris

Reply

Answer 3

SGIII

Level 8

37,532 points

Aug 19, 2021 10:57 AM in response to XChrisAH

I know a little JXA (JavaScript for Automation) but know nothing about Node.js. Why you need to use Node.js. Numbers can open a csv file directly, without going through Node.js.

SG

Reply

Answer 4

XChrisAH Author

Level 1

8 points

Aug 19, 2021 2:20 PM in response to SGIII

I am currently reading the CSV file into Numbers. From there I run a JavaScript to pull data from the CSV and insert it into tables. I thought it would be easier to just run a script that reads the CSV then parses it out into the tables.

I have been using JavaScript but would be happy to explore another language if that might afford me the functionality of reading CSV files and accessing tables in Numbers.

Regards,

Chris

Reply

Answer 5

XChrisAH Author

Level 1

8 points

Aug 19, 2021 4:52 PM in response to SGIII

Hello,

Am using a Numbers template called My Stocks. It uses the stock and stockh functions in Numbers. I am able to use the stockh function for several companies stock values over the course of the previous year. Unfortunately one stock is not available this way. Instead I manually download a years worth of stock value and it comes as a CVS file. I copy and paste it into Numbers then run a JavaScript to pull the stock values over for the dates I have stock values from stockh i.e. I do not use all of the data in the CVS file. All works just fine this way but I thought it would be more convenient to run one script that creates an array from the CVS file then search the array for specific dates and then pull over the associated stock value.

My JavaScript that runs in Numbers provides the desired results but requires that I copy the CVS data into a blank sheet. My JavaScript that reads the CVS file into an array (via Node.js functionality) correctly does so. It is the marrying the two together that is got me scratching my head.

In the end I would be happy to try anything that might work including something other than JavaScript. Maybe there is a way to automate Numbers to automatically add the CVS file via the script then add to it my code that manipulates the data. Then I would not need Node.js or its ability to read CVS files. Food for thought.

Below is a snippet of the CVS file. I did not include the entire file since it is several hundred rows long.

Thank you for your assistance.

Chris

Reply

Answer 6

XChrisAH Author

Level 1

8 points

Aug 19, 2021 5:04 PM in response to XChrisAH

I probably should have uploaded the raw CSV file. See below.

Chris

Reply

Answer 7

SGIII

Level 8

37,532 points

Aug 19, 2021 4:16 PM in response to XChrisAH

XChrisAH wrote:

I am currently reading the CSV file into Numbers. From there I run a JavaScript to pull data from the CSV and insert it into tables.

I am not sure I entirely follow what you are doing. When you open a csv file with Numbers, the data will already be placed in a table automatically.

The question becomes, what do you need to do with that data once it is in the table?

Often it is most efficient to leave like data together in one tab le and use formulas (SUMIFS, COUNTIFS, AVERAGEIFS and cousins) to derive summaries. Or to use the built-in Smart Categories functionalities to slice and dice, without even having to use formulas.

And if the built-in functionalities are insufficient then both JavaScript (JXA) and AppleScript are also available to manipulate data.

Can you explain a little more what your data looks like and what you plan to do with it? A screenshot would help. (To take a screenshot, shift-command-4, select the area on the screen that you want to capture, release, then start a new post here and insert the downloaded image on the Desktop into the post using the icon that looks like "mountains" at the bottom of the compose window.)

SG

Reply

Numbers + Node.js + JavaScript : How do I access a numbers document

Similar questions