Update specific cell in Google Sheets based on column and row

[jlhsolutions]

I’ve only been using n8n for a short time but have managed to figure a few things out. Now I am working on updating data in a Google Sheet. I get an email every day that gives me the total number of each item sold. I have a code node that extracts the date into Month, Day, Year and the quantity sold. Now where my issue is is that I cannot figure out how to put that number into a specific cell in my google sheet file.

My file is set up in the first row with A1 as Month and then the next column is 1, 2, 3, etc up to 31. In A2 - A13 are the month names. Since today is May 25, I would want to enter in the quantity into cell Z6 as that is where 25 and May line up. Please see picture.

I am using the Get Row(s) node, but that doesn’t let me choose a row and column, at least not that I can figure out.

Then I tried Update Row, but I still can’t figure this out.

Maybe I need to change the way my Google Sheet is, but I can’t figure out how else I would set this up.

I tried searching, but apparently, no one else has had this issue. I am hoping there is someone here that can walk me through how to get this to work.

Thanks in advance!

Information on your n8n setup

• n8n version: 1.41.1
• Database (default: SQLite): default
• n8n EXECUTIONS_PROCESS setting (default: own, main): own
• Running n8n via (Docker, npm, n8n cloud, desktop app): Zimaboard Docker
• Operating system: CasaOS

[ihortom]

@jlhsolutions , here’s your improvised solution

Explanation

You cannot just update a specific cell, it has to be the whole row. As you do not want to override the cells in the required row that were filled out in the previous days, you need to know the current values and reenter them together with the new value for the current date.

Thus, the steps are

1. Get the current rows
2. Pick the one corresponding to the current month (with the help of Merge node)
3. Populate the cell corresponding to the current date (with the help of Code node)
4. Update the row using “Month” column to match on

NOTE: “Date and Quantity” node formats the current date to extract the name of the month and the date as separate entities to be used later on in the flow for matching purposes.

[jlhsolutions]

@ihortom Thank you for showing me how to do this. This works great. I was curious if there was a way I could modify this to pull in old data. I tried adding a loop function, but I got an error on my code node. I changed it to “Run once for Each Item” and I get this error.

When I leave it on “Run once for all Items” it loops thru but it doesn’t place the data with any of the cells.

Any ideas? Here is my workflow.

[ihortom]

@jlhsolutions , I’ve made small changes to your workflow

NOTE:

1. There is no need to keep my original “Date and Quantity” node as you create the needed data in your own Code node “Code: Extract Date & Data Usage”. Therefore “Date and Quantity” node becomes redundant.
2. The Google spreadsheet to get the rows has Filter option added to pull only the row for the specific month (as opposed to all 12 rows). Just for optimization purposes.
3. “Code: Extract Date & Data Usage” is taken out of the Loop. I also have made some changes to the code, which you need to validate as it is not something I could test without the samples of the data. Basically, you want to make sure that the item has a proper value for the Month (and not ‘No match found’) to ensure that this Code node does not have the Loop breaking items in its output.