• October 26, 2024
• in NodeRED, Google Sheets, GSheet
• 3 min read

How to Access Google Sheet Data from NodeRED

Note

These instructions assume that you already have NodeRED installed somewhere, and are able to access it.

What's Involved

You will be:

1. Creating a Google Sheet with data that you want to be able to use in NodeRED
2. Authorizing remote access to your worksheet.
3. Configuring NodeRED to be able to grab your data.

Step 1: Authorizing Access of Google Sheets

If you don't already have a Google Cloud account (https://console.cloud.google.com) you'll need to establish an account. Once you are able to login to Google Cloud, you can proceed.

After logging into Google Cloud Console, if you don't already have a project created, you'll need to:

1. Create a new project. If you do already have a project, select that project. I already have one named "Home Assistant" so I'll use that one.
2. You should be looking at your Service Accounts page. Click CREATE SERVICE ACCOUNT.
3. Enter a descriptive name here such as "NodeRED Google Sheets". Service Account ID field will auto-populate.
4. If you want to describe what this is for, you can do so in the "Service account description" field.
5. Click CREATE AND CONTINUE
6. Ignore the next two steps and simply continue.
7. Click on your newly created service.
8. Click on the KEYS tab.
9. Click ADD KEY --> Create New Key --> Select JSON.
10. A file should automatically download with all of your credentials to your browser's default download location. Save this file.

Step 2: Installing GSheetAdvance in NodeRED.

1. While in NodeRED, next to the Deploy button in the upper right corner, click the 3 bars to open the side menu.
2. Select Manage Palette.
3. In the Nodes tab, search for gsheet. If there are no results, switch to the Install tab.
4. Install the GSheetAdvance node (node-red-contrib-google-sheets-advance).

Step 3: Create a new Google Sheet and Share It

1. Create a new Google Sheet (sheets.google.com)
2. In the upper right-hand corner, Share the sheet.
3. Under General Access select anyone with the link.
4. Copy the portion of the Google Sheets URL between the /d/ and /edit/
5. Add some data to the cell A1 and B1 in your Google Sheet so there's something to read.

Step 4: Configure GSheetAdvance

1. Drag an instance of GSheetAdvance to one of your flows, or a new flow.
2. Double-click the node to open it.
3. Where you see Creds and none, click the plus sign.
4. In the Creds field, paste the contents of the JSON file that you downloaded from Google Console.
5. Click Add.
6. Choose your Method. In this case, go with "Get Cells" so you can practice grabbing data from your Google Sheet.
7. In the SpreadsheetID field, paste the long string of characters you copied from your Google Sheet. Go back to Step 3 if you forgot.
8. In the cells field, assuming you didn't label your first tab in Google Sheets, enter exactly what you see here: Sheet1!A1:B1, otherwise, replace Sheet1 with the name of your google sheet tab. You can use multiple tabs in a single document for different sets of data.
9. Name your node instance if you'd like.
10. Click done.

Drop an inject node into your flow and connect it to the input of your GSheetAdvance node, and add a Debug node to the output. Set the Debug node to display the entire message object.

Deploy Your Flow

1. Open the debug window in NodeRED.
2. Click the inject button.

You will see a payload with an array containing your data. Each row will be an array, and each column will represent an item in that array. The first index position is the row, i.e. row 1 will be index 0, and the first column in the row will be the first index position under the row array, and so on.