Spreadsheet node -> how to convert output to base64?

hey guys,

I am trying to convert the output of a spreadsheet node to base64 since my graphql node (or the service called with it) does only accept base64 for file handling.

Can you give me a hint how to achieve this? Didn’t come further with studying the forum.

Thanks in Advance
Tim

@timbo Do not understand the file handling part. Does the spreadsheet output have a URL to an image?

@timbo if you want to encode the json output to base64 check the example below:

{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        250,
        300
      ]
    },
    {
      "parameters": {
        "functionCode": "const result = [];\n\nconst data = [\n  {\nheadline1: 'row 1',\nheadline2: 'row 1',\n  },\n  {\n\nheadline1: 'row 2',\nheadline2: 'row 2',\n  }\n];\n\nfor (const d of data) {\n  result.push({ json: d })\n}\n\nreturn result\n"
      },
      "name": "Function",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        500,
        300
      ],
      "notesInFlow": true,
      "notes": "spreadsheet output"
    },
    {
      "parameters": {
        "functionCode": "const data = []\n\nfor (const item of items) {\n  data.push(item.json)\n}\n\nconst dataBase64 = Buffer.from(JSON.stringify(data)).toString('base64');\n\nreturn [\n  {\n    json: {\n      dataBase64\n    }\n  }\n]\n\n"
      },
      "name": "Function1",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        770,
        300
      ],
      "notesInFlow": true,
      "notes": "Base64 encoding"
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Function",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Function": {
      "main": [
        [
          {
            "node": "Function1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Hey Ricardo,
thanks for your answer. I guess I didn’t phrase it correctly.

I have a spreadsheet-node (if you remember my other topic…). I need the “result” of that node to be encoded to base64 to be compatible to some of our internal services.

See screenshot below.
Screenshot 2020-06-25 at 20.08.00

I tried your encode code from above as function node before the upload, but it still isn’t encoded correctly.

How is it the upload a list is supposed to make the HTTP request. Is there any place where I can see the endpoint documentation? If you cannot share it here maybe you can send me a private message.

1 Like

Hi Ricardo. Of course:

The service expects a base64 encoded file.

Plan is:

Process Data --> create a spreadsheet file out of it (i.e. excel) --> upload it via our internal service (right now expects base 64)

I just do not really understand the output of the spreadsheet node.

Ah yes the issue here is that it actually sends “data” (so the text) and not the base64 string. It is not a binary-property name that has to get set in that parameter, it is the actual value. So you have to change it to an expression and then select the base64 string with the expression editor.

Ah yea, that’s acutally a follow up mistake. Corrected it again.

But still:
I do not get the output of the spreadsheet node converted to a file encoded as base64.
So the main problem still is, that I do not understand the out of the spreadsheet node.

Ah ok. Here a full example:

{
  "nodes": [
    {
      "parameters": {
        "functionCode": "items[0].json.dataArray = [\n  {\n    headline1: 'row 1',\n    headline2: 'row 1',\n  },\n  {\n\n    headline1: 'row 2',\n    headline2: 'row 2',\n  }\n];\nreturn items;"
      },
      "name": "Function",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        500,
        300
      ],
      "notesInFlow": true,
      "notes": "Init data"
    },
    {
      "parameters": {
        "functionCode": "return items[0].json.dataArray.map(item => { return {json: item} });"
      },
      "name": "Function1",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        650,
        300
      ],
      "notesInFlow": true,
      "notes": "Array to items"
    },
    {
      "parameters": {
        "operation": "toFile",
        "fileFormat": "csv",
        "options": {}
      },
      "name": "Spreadsheet File",
      "type": "n8n-nodes-base.spreadsheetFile",
      "typeVersion": 1,
      "position": [
        800,
        300
      ]
    },
    {
      "parameters": {
        "functionCode": "const data = items[0].binary.data.data;\n\nconst dataBase64 = Buffer.from(JSON.stringify(data)).toString('base64');\n\nreturn [\n  {\n    json: {\n      dataBase64\n    }\n  }\n]\n\n"
      },
      "name": "Function3",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        950,
        300
      ],
      "notesInFlow": true,
      "notes": "Base64 encoding"
    },
    {
      "parameters": {
        "requestMethod": "POST",
        "url": "http://example.com",
        "options": {},
        "bodyParametersUi": {
          "parameter": [
            {
              "name": "base64",
              "value": "={{$node[\"Function3\"].json[\"dataBase64\"]}}"
            },
            {
              "name": "name",
              "value": "????"
            }
          ]
        }
      },
      "name": "HTTP Request",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        1100,
        300
      ]
    }
  ],
  "connections": {
    "Function": {
      "main": [
        [
          {
            "node": "Function1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Function1": {
      "main": [
        [
          {
            "node": "Spreadsheet File",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Spreadsheet File": {
      "main": [
        [
          {
            "node": "Function3",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Function3": {
      "main": [
        [
          {
            "node": "HTTP Request",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

I guess, I am just not getting it but just to be on the same page:

I have a perfectly nice looking result in “spreadsheet node”. I am not able to pass that to Httprequest as base64.

This is the snippet of what I am trying, and it doesn’t work.

{
  "nodes": [
    {
      "parameters": {
        "operation": "toFile",
        "options": {}
      },
      "name": "Spreadsheet File",
      "type": "n8n-nodes-base.spreadsheetFile",
      "typeVersion": 1,
      "position": [
        1100,
        -290
      ]
    },
    {
      "parameters": {
        "functionCode": "const data = []\n\nfor (const item of items) {\n  data.push(item.json)\n}\n\nconst dataBase64 = Buffer.from(JSON.stringify(data)).toString('base64');\n\nreturn [\n  {\n    json: {\n      dataBase64\n    }\n  }\n]\n\n"
      },
      "name": "Function1",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        1270,
        -280
      ],
      "notesInFlow": true,
      "notes": "Base64 encoding"
    },
    {
      "parameters": {
        "requestMethod": "POST",
        "url": "SOME SERVICE",
        "options": {
          "bodyContentType": "json",
          "timeout": 300000
        },
        "bodyParametersUi": {
          "parameter": [
            {
              "name": "base64",
              "value": "={{$node[\"Function1\"].json[\"dataBase64\"]}}"
            },
            {
              "name": "name",
              "value": "somefile.xls"
            },
            {
              "name": "UUU",
              "value": "YYY"
            },
            {
              "name": "XXX",
              "value": "XYZ"
            }
          ]
        }
      },
      "name": "Upload List",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        1480,
        -300
      ]
    }
  ],
  "connections": {
    "Spreadsheet File": {
      "main": [
        [
          {
            "node": "Function1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Function1": {
      "main": [
        [
          {
            "node": "Upload List",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Please use the nodes from the example I did post above.

Ah acutally better use this node to “convert” the binary data to base64 data. This does it to 100% correct and does not need any special JavaScript code:

{
  "nodes": [
    {
      "parameters": {
        "setAllData": false,
        "destinationKey": "dataBase64",
        "options": {
          "keepAsBase64": true
        }
      },
      "name": "Move Binary Data",
      "type": "n8n-nodes-base.moveBinaryData",
      "typeVersion": 1,
      "position": [
        1323,
        493
      ]
    }
  ],
  "connections": {}
}

and here as alternative the correct one as Function. The other one I just realized converted it actually wrong:

{
  "nodes": [
    {
      "parameters": {
        "functionCode": "return [\n  {\n    json: {\n      dataBase64: items[0].binary.data.data\n    }\n  }\n]\n\n"
      },
      "name": "Function3",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        1055,
        734
      ],
      "notesInFlow": true,
      "notes": "Base64 encoding"
    }
  ],
  "connections": {}
}
1 Like

Perfect! the convert works perfectly. Thanks a lot guys, as usual!

Ah happy to hear! Have fun!