Publish to Google Sheets

Publishes selected metadata using Google Sheets API

Overview

This node is capable of publishing the pipeline metadata periodically in a Google Spreadsheet or when a trigger condition is met.

Inputs & Outputs

  • Inputs : 1, Media Format : Raw Video
  • Outputs : 1, Media Format: Raw Video
  • Output Metadata : None

Properties

PropertyValue
trigger_typeWhen to Collect Metadata
- custom_trigger : a custom trigger is fired
- auto_trigger :when significant node properties change (auto trigger),
- periodic :at an interval (periodic)
- scheduled :at fixed times past the hour (scheduled)
triggerIf set, the metadata is only collected when this trigger condition evaluates to True.

The trigger expression must be a valid Dot-notation expression that operates on Pipeline Metadata and evaluates to True or False. See details here.

ex. `nodes.annotate_line_counter1.lines.line1.total_objects_crossed_delta > 0
intervalHow frequently to publish metadata (in seconds). 0 means publish every time it is collected.

ex. 60
scheduleIf set, ignores the Interval property and schedules publishing at a synchronized time.

Options:
day :Every Day
hour :Every Hour
30min :Every 30 minutes past the hour
15mins :Every 15 minutes past the hour
meta_to_publish_typeType of metadata to publish to google sheets
- node_meta : Publish selected metadata from the nodes
- object_meta : Publish object attributes and metadata
- custom : Define the metadata via a path expression (see below)
meta_to_publish_regexComma separated path expressions to select the metadata that should be published when meta_to_publish_type is custom

ex. nodes.*.*_delta, nodes.*.*.*.*_delta
use_lumeo_gsheets_accountUse Lumeo-managed Google account to publish to Sheets. Easier to setup, but requires you to share your Google sheet with [email protected]

ex. True / False
gsheets_client_emailThe email address to use on Google Sheets API requests if use_lumeo_gsheets_account is False.

ex. [email protected]
gsheets_private_keyGoogle Sheets API Private Key if use_lumeo_gsheets_account is False.

ex. -----BEGIN PRIVATE KEY----- (...)
gsheets_spreadsheet_idGoogle Spreadsheet ID

ex. 1dTxg-PUPPg4hRJkULek-RgzmVd47RfdH-SGBGuC7iqS
gsheets_sheet_nameGoogle Sheet Name

ex. Sheet1

Google Sheets API registration

To use the Google Sheets API service with your own API account, you need to get OAuth 2.0 credentials from the Google API Console.

Using your Google account, visit the Google API Console website.

Click On Library and search for Google Sheets API
Press Enable button (Fig 1)

684

Fig 1

You should be redirected to the Google Sheets API Service page.
On the Credentials menu press Create Credentials -> Service account (Fig 2)

975

Fig 2

On the "Create service account page" you need to introduce the name you want to use for the Service account, the Service account email address, and a short description.
Press Done when finished (Fig 3)

835

Fig 3

On the next window you can grant the service account access to your project resources (Fig 4)

813

Fig 4

After the service account is created, you need to open the Keys separator.
Now click on Add Key -> Create new key (Fig 5)

856

Fig 5

Chose JSON Key type and press Create. (Fig 6)
This should start the download of a JSON file containing your service account credentials.

555

Fig 6

Open the JSON file with a text editor. (Fig 7)
Here you can find on each line a set of key/value pairs.
The client_email and the private_key are required for the node configuration.
You should use their values (without enclosing quotes) to fill respectively the following fields of the node properties: Google Sheets Client Email and Google Sheets Private Key

1210

Fig 7

On the Google Drive, create a new Spreadsheet. Right-click on the file and press Share
Add the client_email of your service account as Editor. (Fig 8)

1030

Fig 8

To find the Spreadsheet ID, open the spreadsheet file on Google Drive.

You will see an URL like this: https://docs.google.com/spreadsheets/d/10Rx6088O56gDDPpy0FYB9FDZ30Dzj13P-4b5wXwzXTD/edit#gid=0

The 10Rx6088O56gDDPpy0FYB9FDZ30Dzj13P-4b5wXwzXTD part is the spreadsheet ID that should be introduced on the node property Google Spreadsheet ID

To fill the Google Sheet Name node property, check the name on the bottom left corner, in this case Sheet1 (Fig 9)

245

Fig 9

Metadata to Publish

In this field you can insert multiple path expressions, separated by comma, to include or exclude the metadata that should be published by the node.

Example:
When parsing metadata augmented by the Line Counter Node we can select the total objects that crossed both directions of the lines specified on the Line Counter Node using the following regular expression:
nodes.annotate_line_counter1.lines.*.dir*.*crossed*

Fields appended on-the-fly to metadata published on the Google Sheets

KeyValue
system_timestampCurrent system timestamp

ex. 2021-04-14 09:22:24.617855
frame_timestampTimestamp computed based on frames difference, since the start of the pipeline execution

ex. '0:04:00

Metadata

This node does not add any new metadata to the frame.

Metadata PropertyDescription
NoneNone