I’ve just discoverd a strange behavior in spreadsheets.values.append API :
As described here, the API try to find “tables” in the sheet to decide where data should be appended.
This leads to unexpected behavior when some columns are empty : appended data will be shifted to the last “detected” table which may only be a part of the main table.
Is there any workaround to this ?
I found that if I provide a “A:A” range, it will detect the last line of the A column, but the node will only fill the first cell and ignore any other datas…
and it’s giving me “Entries exist but they do not contain any JSON data.”
But I am not sure what this is trying to tell me. Why would there be JSON data?
Sorry, I do sadly not understand the problem with append. What do you mean with the last “detectable table”? What it should do is to append the data after the last detectable row. Even if columns are empty it should still find the first row which does not have any data in any column and append it there.
About what the parameters do. If you hover over the name it should display a small question mark which gives hopefully some additional information what they do.
The values that get added to the sheet are the values that enter the node from a previous one. Like for example, this would add a new line to your above example sheet:
(you can simply copy and paste the data bellow in n8n, it will then create the nodes and connections)
About the JSON data. The data which gets passed from one node to another is an array of objects. These objects always contain a property “json” which is the data the most the nodes operate on. And what for example the Set-Node above sets. Apart from that, it is also possible to add binary data. Some information about the data structure can be found here:
So what the message you got is telling you that there is one item the Google Sheet node receives but that it does not contain any data. And for that reason can it not append anything to the Google Sheet.
@jan have a look to the link I provide, there is a good example to understand how google handle “table” detection and how rows are appended (I was really disappointed when I discovered that)
If I understand correctly my solution is that:
I use “set node” before “google sheets” in append feature.
In set node I use column title as my variables.
Then append in Google sheets node fill cells in a row in my range by my variables.
OK, the json error was confusing. I thought it was related to the sheet - not the input data. The “entries exists” part I still don’t understand. I guess a different message would help here.
With the “Set” node things come together now. Thanks for that pointer! What should go into the node documentation is that the mapping is done via the first row being the headers. And that the matching is case sensitive(!). It would be great if it could create an error if the append fails.
The question mark help isn’t super helpful TBH. IIUC by now the range defines the target “table” inside the sheet (as described in the docs from Google - which should be linked from the node IMO). The explanation of “Key Row” still leaves me puzzled.
Agree many of the error messages and descriptions could for sure be improved. Always happy about pull-requests which improve that and make it easier for other people.
The key row simply defines from which rows it should read the keys. So so using your term from above it is the row in which the headers get defined. As you have written it is by default the first row but in case a different one should be used it can be defined there which one. If I would write the node today, I would do many things differently. That being one of them. I would probably either add it as an option (so that it is only there if people add it specifically) or not add it at all as the same thing can be defined by setting the row number in the range.
About the “entries exists” part. You can have a look at the structure the data has which gets passed through the nodes here:
There you can see that it is an array of objects. And what the message is saying that the array contains an object (entry) but the “json” property in it does not contain any data and for that reason, nothing can be displayed in the table view. If you switch over to JSON-View you can see an empty object.
I feel that we have moved away from the original subject. @jan did you understand the problem that I raised?
I can send you an example in private if it can help you
@airmoi Sorry for hijacking the thread @jan I understand your explanation - but I think the message is too low level. Thanks for clarifying the key row part. I’ll look into making a PR.
@airmoi Yes very sorry we really got sidetracked here. I agree it works a bit differently than I would have expected. It does that however only if there are empty rows and not empty columns. You mentioned however empty columns. Was that a typo? Because if not I maybe do really not understand the problem and an example would be very helpful.
