Substreams Google Sheets sink module

substreams-sink-sheets pushes Substreams DatabaseChanges map outputs to Google Sheets.

CLI

Use pre-built binaries

• [x] MacOS
• [x] Linux
• [x] Windows

Install globally via npm

$ npm install -g substreams-sink-sheets

Create new Google Sheets spreadsheet

$ substreams-sink-sheets create [options]

Stream substream output to Google Sheets

$ substreams-sink-sheets run [options] <spkg> <spreadsheet-id>

The spreadsheet-id can be found in the URL of the Google Sheets document: https://docs.google.com/spreadsheets/d/${ID}/edit

List compatible output modules for a given substream

$ substreams-sink-sheets list [options] <spkg>

Help

$ substreams-sink-sheets <run|create|list> -h

Features

• Consume *.spkg from:
  • [x] Load URL or IPFS
  • [ ] Read from *.spkg local filesystem
  • [ ] Read from substreams.yaml
• [x] List compatible modules from .spkg
• GoogleSheet API support
  • [x] Authenticate via JWT credentials
  • [x] Append row to sheet
  • [ ] Work with different credentials (service account, OAuth, etc.)
  • [ ] Permission checking for editing
• GoogleDrive API support
  • [ ] Update permissions
• [x] Select columns to output
  • [x] Time (date,year,month,day,timestamp,seconds)
  • [x] Block (block_num)
  • [x] DatabaseChanges
  • [x] Add missing columns headers to sheet if specified
• [x] Create a new sheet if no spreadsheetId specified
• [x] Set start-block & end-block
• [x] Select outputModule (default db_out)
• [x] Select Substream endpoint (default mainnet.eth.streamingfast.io:443)
• DatabaseChanges support
  • [ ] UNSET
  • [x] CREATE
  • [ ] UPDATE
  • [ ] DELETE
• Rate limiting
  • [x] 1 request per second (Google rate limit is 100 requests per 100 seconds)
• Winston logger
  • NODE_ENV='production' to silent logging

Google API authentication

Authenticate to Google Sheets using either OAuth2 tokens or a service account credential file.

First, create a new project from the Google Cloud developer console or select an existing one.

Then go to APIs & Services to enable the Google Sheets API.

OAuth2

OAuth2 allows the application to access the documents of a user on its behalf by requesting its consent. To set it up, go to the OAuth consent screen to setup the scope and permissions of the OAuth authentication.

Select the user type and add the auth/spreadsheets scope.

Then, you want to add an OAuth client ID for NextAuthJS to use to issue authentication requests to Google. For local deployement, you want to add localhost:3000 as an authorized origin. The callback url is provided by the NextAuthJS library (see the docs for more information).

After getting the Google Client ID and Google Client Secret, add these to your .env.local in the source directory of the application.

.env.local

GOOGLE_CLIENT_ID="<your-client-id>"
GOOGLE_CLIENT_SECRET="<your-client-secret>"

And you're done ! You should be able to use the Sign In with Google feature to authenticate with your Google Account.

If using the CLI, you will need your refresh token to generate access tokens from Google's endpoint.

REFRESH_TOKEN="<your-refresh-token>"
ACCESS_TOKEN="$(curl https://oauth2.googleapis.com/token -s --data-binary "client_id=<your-client-id>&client_secret=<your-client-secret>&refresh_token=$REFRESH_TOKEN&grant_type=refresh_token" | jq .access_token)"

Service account

Alternatively, if using the CLI, you can use a service account for pushing data to your Google spreadsheet.

Create a new service account from the IAM & Admin page and enter a name.

Then, edit the service account details to add a new JSON key. Download it, rename it to credentials.json and move it to the source folder (for convenience).

You can now use it by passing the --service-account-file credentials.json argument.

IMPORTANT: in order for the service account to be able to access your spreadsheet, you must enable sharing (either to everyone or by using the service account email found in the credentials file).

References

• https://developers.google.com/sheets/api/guides/concepts#cell
• https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption
• https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append#insertdataoption

Further resources

• Substreams documentation
• Substreams DatabaseChanges