Guide

Mix.install([
  {:kino_sheetex, "~> 0.4.0"}
])

Introduction

KinoSheetex is a Livebook smart cell for fetching rows from Google Sheets. Data is fetched using Google APIs and requires authorization – either using an API key (for public sheets) or a service account (for public and private sheets).

Usage

First, obtain authorization credentials as outlined in the Authorization section below. Then, find the smart cell (“Fetch rows from a Google Sheet”) in Livebook and open it.

Finally, paste the ID of your sheet into the “Spreadsheet ID” input field and select your authorization credentials. All other fields are optional (see notes on optional fields).

Authorization

For both authorization schemes (via an API key or service account), you need access to the Google Cloud console.

API key authorization (for accessing public sheets)

1. On the “APIs & Services” page, make sure the Google Sheets API is enabled.
2. On the Credentials page, click on “Create Credentials”, select “API key” and save the generated key.
3. In Livebook, store the key in a secret. You could name it GOOGLE_SHEETS_API_KEY.

Consider restricting your key’s access to “Google Sheets API” on its config page in Google Cloud console.

Service account authorization (for accessing public and private sheets)

1. On the “APIs & Services” page, make sure the Google Sheets API is enabled.
2. On the Credentials page, click on “Create Credentials”, and select “Service account”.
3. In the form, choose a name and an ID for your service account and submit the form. There is no need to grant it additional permissions.
4. You will later use the service account’s email address to grant it access to sheets, so save it somewhere handy.
5. Go to the service account’s config page. You should be able to find it in the “Service Accounts” section on the Credentials page.
6. Inside the “Keys” tab, click on “Add key”, select “Create new key” and then “Create”. The key type should be “JSON” (default).
7. Save the generated JSON file.
8. Finally, in Livebook, store the contents of the JSON file in a secret. You could name it GOOGLE_APPLICATION_CREDENTIALS.

Now, whenever you want to use the smart cell to fetch data from a private sheet, you need to share the sheet with the service account using its email.

Optional fields

You may assign the result to a variable or change the output format. By default the output will include a list of rows up to the last non-empty row in the sheet (or from within the specified range). If you choose to convert the output to key-value pairs, the first row will be used as the header row (additional information).