Google Sheets Trigger node fails if the worksheet name is too long

I've first spotted this behaviour on n8n self-hosted v1.6.1, but then proceeded to install v1.17.1 locally for testing purposes and still the error persists. I've investigated this issue quite deeply into the node code, but my methods might not be the "cleanest" from a dev perspective, for which I'm sorry. I'll try to provide all the information needed.

Describe the bug While using the Google Sheets Trigger node on Row Added or Updated mode I can fetch a test event and it works! However sometimes the flow would neither activate nor save. Only after I renamed the node could I proceed to save then activate. There seems to be another (perhaps related) bug around the save then activate vs. just activate action.

The flow would never run successfully tough. I would always get a bunch of executions with status "Could not complete" and the Sheets node would never have any error information. Captura de tela de 2023-11-28 14-45-22

After digging into the code I found the sheetBinaryToArrayOfArrays() function at /n8n-nodes-base/dist/nodes/Google/Sheet/GoogleSheetsTrigger.utils.js to be misbehaving. I've inserted a bunch of logging information until I could understand what was happening.

As I understand, the Sheets node queries Google Sheets and then processes it using the xlsx package. Turns out even if the Sheets node is configured to use a specific sheet By ID, it will still use the sheet name to query the worksheet object generated by xlsx.

Below are the arguments received by sheetBinaryToArrayOfArrays(). "Cópia de Planilha filtrada | Não ocultar | Não editar" is my sheet page name, and it's 54 characters long. Captura de tela de 2023-11-28 14-10-15

Below is part of the worksheet object generated by xlsx. Captura de tela de 2023-11-28 14-10-43

As pointed out by this post about Excel, worksheet names cannot contain more than 31 characters.

I believe the xlsx package doesn't expect a sheet name larger than that and upon trying to access it, it fails. Below is the surrounding code. This is from n8n v1.17.1. Captura de tela de 2023-11-28 15-09-58

When this happens, the sheet variable becomes undefined.

I've fixed my issue by renaming the sheet name with a shorter string, but the lack of debugging information made me waste a lot of time and I still don't know what's the best course of action.

I believe there's two alternatives for addressing this issue:

Testing if sheet if falsey on the back-end and giving a better error description upon execution failing;
Warning or even blocking the workflow from saving/activating if the user is using a lengthy sheet name on the front-end.

One other thing that bothers me is the fact that the Fetch Test Event button works but the background task doesn't. Seems they take two different paths through the code and I believe this to be a little bit concerning.

To Reproduce Steps to reproduce the behavior:

Create credentials to access Google Sheets API
Create a new workflow
Paste the node code from the box below into the newly created workflow
Verify the Fetch test event button works
Try activating the flow (should work)
Change something here (I've created this sheet for this single issue, it's fine)
Check the Executions tab. Once the flow executes, it should error out with status Could not complete and an empty error on the node.

{
  "meta": {
    "instanceId": "6d9487c2e9c7af1600b73693c8ccd7a73ad970c8059ea62c486f5df990df1f52"
  },
  "nodes": [
    {
      "parameters": {
        "pollTimes": {
          "item": [
            {
              "mode": "everyMinute"
            }
          ]
        },
        "documentId": {
          "__rl": true,
          "value": "https://docs.google.com/spreadsheets/d/1RbSO8HhUYOYNTyXAKJyX-sGe-KUYQh83ZzMQq4iL-eY/edit?usp=sharing",
          "mode": "url"
        },
        "sheetName": {
          "__rl": true,
          "value": 1105317451,
          "mode": "list",
          "cachedResultName": "Very very very very very very very long name",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1RbSO8HhUYOYNTyXAKJyX-sGe-KUYQh83ZzMQq4iL-eY/edit#gid=1105317451"
        },
        "options": {}
      },
      "id": "172f8a23-72c4-456f-8c48-9ae83db6402d",
      "name": "Google Sheets Trigger",
      "type": "n8n-nodes-base.googleSheetsTrigger",
      "typeVersion": 1,
      "position": [
        1040,
        140
      ],
      "credentials": {}
    }
  ],
  "connections": {}
}

Expected behavior Google Sheets Trigger should work on production the same way it works under testing. The mentioned code path does not provide a descriptive error message upon failing.

Environment (please complete the following information):

OS: elementary OS 6.1 Jólnir
n8n Version: 1.17.1
Node.js Version: 18.18.2
Database system: SQLite
Operation mode: not sure, haven't changed (default is main)

Thank you for your time!

n8n-io / n8n

Google Sheets Trigger node fails if the worksheet name is too long #7854