Spreadsheet Node Set Column Order

Questions
1

Is it possible to specify which key goes into which column?

Now the order is determined by the order of the keys in the json file, and as JSON objects are unordered sets, the order of the columns is unordered as well.

1 Like
2

I think what you are trying to do should be doable with the item lists node using the sort operation?

3

@djangelic as far as I can see now, this option sort the rows. What I want to do, it determine which key comes into which column in the spreadsheet … or am I overlooking something?

4

Ahh ok I see. Have you tried using the Rename node?

5

@djangelic just did and unfortunately, this node return an unordered set as well … unless you rename the keys, which I do not want to do …

6

Ahhh ok try this snippet: JavaScript Code Snippets | Docs

7

@djangelic thanks again, but uh … again sorting on key value and not keys. I would like to change

  {
      "id": 1,
      "name": "Jim"
  },

to

  {
      "name": "Jim"
      "id": 1,
  },

So that the first column (A) in the export spreadsheet is name and second column (B) is id …

8

Ah I see, that does sound more difficult to do. Are you trying to sort so when you pass to google sheets the columns are in a specific order? If the columns already exist in the sheet, the values should be passed to the correct column regardless of order. Trying to understand the use case.

9

@djangelic thanks for the tip! Before I try; does this means that if I create an empty spreadsheet with only column names before, that this might fix my key sort order problem?

10

That is correct!

11

@djangelic just read that you wrote ‘google sheets’. Unfortunately, I’m not working google sheet, but want to export to Excel .xlsx. Just tried that, and that doe not work …

12

Found a solution :sunglasses: … added a Function Item Node in between Item Lists and Spreadsheet File that regroups the JSON data.

Screenshot 2022-03-11 at 20.42.48
Screenshot 2022-03-11 at 20.42.48990×268 54.1 KB

The function is as follows:

var jsonata = require('jsonata');

var data = item

var expression = jsonata("$.{'TotalQTy': TotalQTy, 'ItemName': ItemName,'ItemCode': ItemCode}");
var result = expression.evaluate(data);

return result

Thanks to JSONata … again!

And this is the relevant part in their docs on Grouping

2 Likes
13

awesome! Glad you figured it out! Sorry I wasn’t more help!

2 Likes
14

@djangelic you helped me to keep going. Thanks!

2 Likes
15

@dickhoning hi!

Could you help with your solution. Would you please tell me how to connect jsonata ?
I have this error:

ERROR: Cannot find module ‘jsonata’ [Line 1]

Thank you!

16

Hi @mikhail you need to install the JSONata json module (via npm) and set the following environmental variable:

NODE_FUNCTION_ALLOW_EXTERNAL=jsonata
1 Like
17

@dickhoning Is there a guide how to install it for n8n/server ?
For those who don’t know much about the console. :slightly_frowning_face:

18

@Mikhail how are you creating your workflows now? Running in OS, Docker or with Desktop App?

19

@dickhoning VPS, Docker

20

@Mikhail you can find the relevant documentation here.

And here are my notes from the brief experience I have with Docker (on macOS):

To start n8n in Docker via the CLI/Terminal:

export NODE_FUNCTION_ALLOW_EXTERNAL=jsonata && n8n

Or via a docker-compose.yml

version: '3.9'

services:

  n8n:
    container_name: n8n
    image: n8nio/n8n
    ports:
      - 5678:5678
    volumes:
      - ~/.n8n:/home/node/.n8n
    environment:
      - NODE_FUNCTION_ALLOW_EXTERNAL=jsonata

Hope this helps.

2 Likes