How to pull a Google Sheets document into Node-RED

groov EPIC
1

Google Sheets files are a really common and useful way to store and organize data, but bringing that data into Node-RED requires some pretty specific steps, so I wrote this walkthrough to break it down so other users can make use of it.

Note that if you are using CSV files instead you can easily just read that in with the core, preinstalled “file-in” node – and Excel documents can either be saved in CSV format, or imported into Sheets. This post just focuses on the steps required to use the GSheet node.
The big advantage to using Sheets instead of these other options is that you don’t have to save or modify a file; it’s all done in the cloud!

Most of this only needs to be done once to configure your Google account to allow application access to your spreadsheet, and also set up the node. Once that’s set up you can more quickly and easily read and edit new sheets with only a few additional steps (I’ll go over that at the end of the post).

  1. Go to the Google Cloud Platform and log in with your Google account (this does not need to be the same account that owns the Google Sheet you want to access):
    https://console.cloud.google.com/

  2. Create a project if you don’t already have one. Click “Select a project” in the top left, then NEW PROJECT
    a. Name your project
    b. Click “CREATE”
    c. Wait for project creation to complete
    d. Click “SELECT PROJECT”, and choose the project you just created

  3. On the left-hand side, select “API’s & Services” → Credentials

  4. Create a “Service Account” if you don’t already have one
    a. Click “Manage Service Accounts”
    b. Blue “CREATE SERVICE ACCOUNT” at the top
    c. Fill in the service account details, then click “CREATE AND CONTINUE”, then “DONE”

  5. Click the service account email link, then click “KEYS” at the top

  6. Blue “ADD KEY” dropdown above the table of keys, then “Create new key” of type “JSON”, and “CREATE”
    This will save the key json file to your computer.
    NOTE: Make a secure backup of this file! You cannot download it again later!

  7. Enable the Sheets API for this Google account.
    a. Select “APIs & Services”
    b. At the top, click blue “+ ENABLE APIS AND SERVICES”
    c. Scroll down to “Google Workspace” and select “Google Sheets API” (or just search for it)
    d. Select the blue “ENABLE” button

  8. Go back to your service accounts list (under credentials)
    Copy the service account email to your clipboard (recommend highlighting and copying, rather than copying the link)

  9. Open the Google Sheets file that you want to bring into Node-RED, and share it with that email address as an editor

  10. In Node-RED use the palette manager to install node-red-contrib-google-sheets

  11. Place an instance of the node and double-click it to configure it
    a. Start by clicking the pencil next to “creds” to Add new gauth
    b. Paste in the full contents of the json file downloaded in step 6 (from first { to last } )
    c. Choose your method based on your application
    d. Get your spreadsheet ID from the URL of your spreadsheet. Example: for the document URL https://docs.google.com/spreadsheets/d/1c48qu0ZdU-Fu-waB-LJXO-m_DxoZ-MwUB6zr8-kXvKY/edit the spreadsheet ID would be:
    1c48qu0ZdU-Fu-waB-LJXO-m_DxoZ-MwUB6zr8-kXvKY
    e. Enter the cells you want in the format <sheet_name>!<start_cell>:<end_cell>,
    Example: Sheet1!A5:E72 will get 68 rows (5->72), 5 columns each (A->E) of “Sheet1” of that spreadsheet file, unless the cell is empty, where it will be a 0-lenth string. Also be aware that you will get a smaller array if the right-most cell or cells in that row are empty.
    f. Do not enable “Flatten Matrix” unless you are only getting one column
    Example: The easiest way to see it is to fetch a column like A1:A5; without flatten enabled you will get back [[A1],[A2],[A3],[A4],[A5]] instead of [A1,A2,A3,A4,A5]
    g . Give the node an appropriate name
    h. Hit “Done”

  12. Inject on the node to get your sheet data in two-dimensional array format, for example <sheet_name>!A1:B5 returns: [[A1,B1],[A2,B2],[A3,B3],[A4,B4],[A5,B5]]

Once you’ve gone through this whole process once, all you’ll need to do to access additional documents is step (9.) to share the Sheets document to this service account email, and step (11. c-h) to basically configure a new node: choose a method, point at the specific spreadsheet ID, and specify which cells you want to work with.

If you have any questions, or end up using this in your own application, drop a comment in the thread below – I’d be curious to hear how others end up using this.

And as always, happy flowing!

1 Like