I have an old spreadsheet node, where i write the timestamp of the last run to the spreadsheet cell. Resourse = Sheet, Operation = Update, Spreadsheet id, Range and RAW data flag - works like a charm.
Now i work on the new workflow and i’m totally confused. There’s no Sheet resource, only “Document” and “Sheet within Document”.
First off, what is Document? There’s no “Document” in the spreadsheets terminology ( Google Sheets API Overview | Google Developers ): there’s the Spreadsheet, the Sheet and the Cell.
Secondly, assuming Document is Spreadsheet. I pick the Update operation, i pick the Document by ID (get it from the Settings node) and i want to set the Sheet to 0, because my current sheet is zero ( sheet’s url ends with “***/edit#gid=0”). But the Sheet field is red, an error “Not valid sheet id”. What, why?
Third, Data mode. There are 2 mappings and “nothing” modes. Wut. There’s no “raw data” mode anymore, but there is (useless?) “nothing” mode. I have no time to play around with mappings, i just need to write raw data to the cell - thats it.
I’m sorry, but i’m TOTALLY confused.
As a bonus question, is there node version stored somehow? Can i copy the old node and just update the spreadsheet id, so it will work in the new flow?
It can take a bit of getting used to, We did some renaming to make things a bit clearer for new users so your Document is your Google Sheet document and the sheet within document is the sheet within the sheet.
I just managed to reproduce the same issue when using the By ID option for the sheet instead of selecting from the dropdown or inputting the URL, To work with this you would need to set the field to Expression which is not ideal and I will get an internal ticket open for this shortly.
I can’t remember what the Nothing option was there for now but I think the Raw data option might be automap now so we will work out what fields to send based on the incoming data. The define option is really handy as it removes the need for a set node before it to work out what to send.
If you wanted to use the old v1 version of the node if you have one in a workflow somewhere you can copy / paste the node to a new workflow and use it that way.
I’d still recommend to stick to official Google terminology and use Spreadsheet instead of Document. Its not that obvious for the beginners, but you can always refer to Google, so its their headache
As a side note, some usage samples (from the test cases?) would be great to speed up the development. Probably some popup with samples, or side-bar window (nah, probably popup) with mapping, raw input, etc - would be really great to have.
However, there’s no changes on the spreadsheet, not even in the history.
I’d vote this is creds issue, but it would be really nice to see the log to ensure, because playing with creds may take a while.
PS: old workflow that uses this node works fine
There isn’t really a great deal of debug output from the nodes unless something goes wrong, Are you using the same version of n8n that has the old workflow that is working?
It could be that when we copied the v1 node we messed something up which is why it isn’t working, In theory though you should be able to use the auto map option and it will work it out if your incoming key exists in the sheet.
Well, something is definitely not going well, because the status of both creds and the node is “ok”, but i dont see the expected result in the spreadsheet.
However, it would be hard to reproduce, i guess, because its a bit messy with old and new creds and nodes.
Where can i refer to see the automap options formatting? Should i send to the node something like { “B”: { “3”: 777 } } ? Or should it be { “B3”: 777 } ?
We search the sheet for any heading that matches the input item name to work out the columns so if you had the below data coming into the node…
[
{
"id": 1,
"name": "yavetal"
}
]
We would assume the sheet has 2 fields as well with columns for id and name, We would let you set the column to match on which may be id we would then search the column headings to work out which column contains name so that you don’t need to worry about it.
But how can i make the raw-est action possible: just overwrite (update) the value of cell B3?
Also, did i get your case right: with the sample data you provided, n8n will append new row, search for column “id”, put 1 to the corresponding empty cell, and then search for column “name” and put “vetal” to the corresponding empty cell?
And still, there’s something wrong with the spreadsheet node
The Documents list is always empty for me. So, i’ve switched to Create operation- on the right side you see the result of the Create operation, it was successful. Also, i can see the newly created spreadsheet (document) in the list of my spreadsheets, so it was ok for sure.
However, when i try to play with Update operation, it constantly returns me empty list.
I’d create the sample workflow, but its just single node + oauth creds.
I’ve tried to workaround the issue i mentioned before (about the “sheet by id”), so i used “sheet by url” now, and still i cant get the list of columns
There was a new scope that got added to the release so that it can access some data from Google Drive which is needed to list the Google Sheets files, It sounds like the issue could be that the scopes were not approved or maybe the account doesn’t have access to the sheet.
What version of n8n are you currently running? I know this working for me yesterday on 0.209.4 and 0.210.0
Cleaning everything up might be the quickest solution as there are a few different things that could be going wrong so it would be nice to have a clean starting point again and a cleaned Google App.
Okay, so i’ve spent a while and managed to wire everything up (regarding the creds).
Still, wonder how to override the cell value (as i mentioned, just to put a timestamp, so other script or human can ensure the latest execution date is valid).
I’ve played a bit, but it seems the current options are just appending new lines, instead of replacing (which kinda makes sense - users mostly need to append new data rather than replace the old one).
As a possible workaround, i’ve been thinking to utilize the Code (or httpRequest?) node and use the credentials there. @Jon would you recommend similar approach?
Well, after some struggling and cleanups, i managed to write to the cell using the old node.
Really looking forward for similar functionality on the new one
