Skip to content

Google Sheets

Google Sheets is a free online spreadsheet program that's part of the Google Workspace suite of tools. It's kind of like a digital version of those old-school paper spreadsheets, but way more powerful and flexible.

In order to use the Google Sheets syncer, you must first configure your local environment. The setup instructions below will help you create a Google Cloud Project and utilize the OAuth Client ID workflow to allow CS Tools to selectively interact with your Google Sheet.

Setup instructions
  1. Head to Google Developers Console and create a new project (or select the one you already have).
    • In the box labeled Search for APIs and Services, search for Google Drive API and enable it.
    • In the box labeled Search for APIs and Services, search for Google Sheets API and enable it.
  2. Go to APIs & Services > OAuth Consent Screen. Click the button for Configure Consent Screen
    • In the 1 OAuth consent screen tab, give your app a name and fill the User support email and Developer contact information. Click SAVE AND CONTINUE.
    • There is no need to fill in anything in the tab 2 Scopes, just click SAVE AND CONTINUE.
    • In the tab 3 Test users, add the Google account email of the end user, typically your own Google email. Click SAVE AND CONTINUE.
    • Double check the 4 Summary presented and click BACK TO DASHBOARD.
  3. Go to APIs & Services > Credentials
  4. Click + Create credentials at the top, then select OAuth client ID.
  5. Select Desktop app, name the credentials and click Create. Click Ok in the OAuth client created popup.
  6. Download the credentials by clicking the Download JSON button in OAuth 2.0 Client IDs section.

[optional]
, Move the downloaded file to ~/.config/cs_tools/gsheets/credentials.json.
Move the downloaded file to %APPDATA%\cs_tools\gsheets\credentials.json.

Google Sheets parameters

Required parameters are in red and Optional parameters are in blue.

  • spreadsheet, the name of the Google Sheet to write data to
  • credentials_file, the full path where your credentials.json is located
  • date_time_format, the string representation of date times
    default: %Y-%m-%dT%H:%M:%S.%f ( use the strftime.org cheatsheet as a guide )
  • save_strategy, how to save new data into an existing directory
    default: OVERWRITE ( allowed: APPEND, OVERWRITE )
How do I use the Google Sheets syncer in commands?

cs_tools tools searchable bi-server --syncer gsheets://spreadsheet=data&credentials_file=data-6538a3a8f574.json&save_strategy=APPEND

- or -

cs_tools tools searchable bi-server --syncer gsheets://definition.toml

Definition TOML Example

definition.toml 

[configuration]
spreadsheet = data
credentials_file = data-6538a3a8f574.json
save_strategy = APPEND