N8N Google Sheets
Integrating in Google Sheets in an N8Nworkflow is pretty common. In fact whentaking a look at jobs on freelanceÂwebsites like Upwork I often seeÂrequests that have Google Sheets as partÂof a build a customer wants
There are quite a few usecases in which this can be used. Two common ones are:
- Extracting data from a PDFdocument and sending to Google Sheets
- Web Scraping from one or multiple sites and sending to Google Sheets
This video will cover how you can grab data from Google Sheets with N8N and update it. There will be sections on Reading, Filtering, Appending, and Deleting.
If you are a more visual person, we have a video on the YouTube channel. This is just one of many N8N and AI videos produced, so check it out!
Prepping the Google Sheets N8N Tutorial
The data we will be using in this article is this very simple spreadsheet. Feel free to replicate it, or use your own. This spreadsheet shows the Card, Player, Grade, and Price of different sports cards.
As a heads up, these values are fictitious. As these are rare cards in these grades, auction values can fluctuate quite a bit since they are not sold on a regular occurrence. Â
I will also be using a manual trigger to start the workflow. While there are options for Google Sheets triggers, that will be covered in another lesson.
I’veÂalready set up my API connection toÂGoogle Sheets and you can find a separate article covering this as well.
Get row(s) in Sheet - Read
Let’s start this tutorial by reading data from Google Sheets. After we read it in we can filter pretty easily.
Click the + Icon in the top right and search Sheets. You’ll be presented with a ton of options. To read we will select Get row(s) in sheet.
In the document section choose ID. The id can be found in the URL of the spreadsheet you are working on. I highlighted it down below.
Sometimes your spreadsheet will have multiple tabs. I know I often work with data that can have 10 or more. Since this only has 1 sheet, select Sheet 1. If you are working with a bigger document choose the sheet you want to grab the data from.Â
Now if you have used Python Pandas for data manipulation you’llnotice that the row numbers are kind ofÂfunky.
In N8N, we start with two where as in Pandas we usually startÂwith zero for the first row.N8N isconsidering the header as row one andthen the first row of data as two.
Filter
While it’s not the best approach we can filter directly within this node. Choose Add Filter under the filters secion.
For our first test, let’s filter for cards that are graded a 10. This should only leave us two results.Â
The better approach to use is to add a filter Node to your workflow after you read in your spreadsheet. Click the + Icon and search for filter.
With this filter node, we will quickly cover the basics of boolean logic. This included and or. At first this may sound confusing, but it’ll click after a while.Â
Let’s start though with replicating the grade logic we did above.Â
To filter on a specific column with a table, we need to drag in the column to a condition. You’ll see that JSON populates in the entry making our workflow more dynamic.Â
Next, we need to map the specific data type to the logic in the filter. Since the example above uses a number with grade, we have to choose logic with a number, otherwise the filter will not work. I set this to is equal to and filled in 10 as value2.
AND
When you use AND, both filters need to be True for the results to show.Â
OR
When you use OR only one of the filters has to be true to have results show.
String (Text) Example
The examples above only used numbers. Let’s quickly use an example that utilizes a string. We want to filter for only Mickey Mantle cards.
Choose string and then select is equal to
How this can be scaled up
While it wasn’t showcased in the YouTube video, you will find times in which you have to use AND and OR within conditional logic. Learn the basics first, and then continue to try and expanding out the logic on different use cases.Â
Append Row in Sheet
By using Append, we can quickly add in rows to a sheet. Think about adding in new scraped website results. We can append the daily results to a master sheet on a daily or weekly basis. The same could be said about a companies KPI updates each week.Â
Search for sheets again and look for append row in sheet
For Mapping, I chose Map Each Column Manually, and selected the values to send for each column. The ID and sheet are the same as the above example.
The Google Sheet will be updated down below.
Delete Rows or Columns From Sheet
Let’s take a look at the opposite above, deletion. What we will do is delete a card from the Spreadsheet: The 1975 George Brett.
Search Sheets and select delete rows or columns from sheet.
Start with the row number you want to delete from (In this case it’s 3) and then select the number of rows to delete (only 1 since we are deleting a single row).
Once you hit execute you’ll find the following sheet.
Update Row in Sheet
Lastly let’s look at how you can update a row in the spreadsheet. We will update the value of Honus Wagner from $4,000,000 to $6,000,000.
Search sheets and look for update row in sheet.
Select Map Each Column Manually, and look for the column that is a unique identifier. Now in this usecase I’m using 1909 t206, but in a real world scenario I would want to have a unique identifier for each card in the spreadsheet.
Fill out all the columns you want to update. Remember the column you are matching on should not change.
Once you hit execute, you will get the following spreadsheet.Â
And that’s it. You learned how to utilize the Google Sheets node in N8N. If you need help with N8N we offer both Data Freelancing and Mentorships. We also have a free discord server with over 500 members dedicated to learning Data and AI.Â
