Amazon.co.uk Widgets

Log in

X
Screenshot of VSCodium editing gsheets_example.dart

If you know me you'll probably know I spent a lot of time in my early career working at companies who made Spreadsheets. Naturally enough then, when I see a data set in a comma separated variable (.csv) format, I lean toward leaving it in its format and querying it. I need to use such a data set in a mobile app, so I wondered if directly getting the data from an online imported CSV was a workable strategy. This approach will likely not have some things I take for granted in Firebase but I could just host it in Google Sheets which might be worthwhile as maintenance of the .CSV would be easier and virtualy cost free if it was left alone as it just gets replaced with a new set of data from time to time.

Before you start!

The proof of concept work for this is in plain Dart. So you'll need a working local Dart environment. Flutter installs Dart, but you can also choose to install it on its own. If it works out it will need to be adjusted to work with FlutterFlow. Lets see if it fits my use case first.

TL:DR — Implementing a CSV data source for a mobile app, in Google Sheets, in Dart. how hard could it possibly be?

Gsheets with Dart

  • Go to https://console.cloud.google.com, from the google G-Suite user which is going to allow the access to the API and select the correct project for your app. You'll likely have the Cloud project set up already If you've already set up your app in FlutterFlow. If not you'll need to create it by working through the instructions for adding Google Firebase to your project - this sets up the Google Cloud project too.
    Screenshot of the project chooser in Google Cloud Console
    The project chooser in Google Cloud Console
  • Choose the Library menu item to go to the API Library and then search for sheets.
    Screenshot of the Google Console API Library search experience
    Google Console API Library search experience
  • Select the Google Sheets API.
    Screenshot of the Google Sheets API Search Result
    Google Sheets API Search Result
  • Enable it.
    Screenshot Enable Sheets API
    Enable Sheets API

Create credentials to access the Sheets API

  • Navigate to "Service accounts" and create a service account which will be able to use the Gsheets API.
  • Back in Google Sheets, grant this service account edit privileges to your spreadsheet using the "Share" button in Google sheets.
  • This service account will be able to read (and write) to this spreadsheet document. You can ignore the warning about sharing with a service account who is not in the Google Workspace organisation that this item belongs to.
    Screenshot of Sheets Share Dialog
    Sheets Share Dialog

Gsheets with Dart

A library for working with Google Sheets API v4.

    • Gsheets allows you to manage spreadsheet docments in Dart. This, I think, for my purposes, is exactly what is needed.
      Screenshot of the Gsheets page on pub.dev
      Gsheets page on pub.dev
    • Lets look at this package. The easiest way is to clone it and try it out so from a terminal you can git clone https://github.com/a-marenkov/gsheets.git and then cd gsheets/example then you can run dart pub get to get the dependencies.
% dart pub get
Resolving dependencies... (1.1s)
+ _discoveryapis_commons 1.0.3
+ async 2.9.0
+ collection 1.16.0
+ crypto 3.0.2
+ googleapis 8.1.0 (9.1.0 available)
+ googleapis_auth 1.3.1
+ gsheets 0.4.2 from path ..
+ http 0.13.4
+ http_parser 4.0.1
+ meta 1.8.0
+ path 1.8.2
+ source_span 1.9.1
+ string_scanner 1.1.1
+ term_glyph 1.2.1
+ typed_data 1.3.1
Downloading googleapis_auth 1.3.1...
Downloading googleapis 8.1.0...
Downloading collection 1.16.0...
Downloading http_parser 4.0.1...
Downloading typed_data 1.3.1...
Downloading string_scanner 1.1.1...
Downloading crypto 3.0.2...
Downloading _discoveryapis_commons 1.0.3...
Downloading path 1.8.2...
Downloading source_span 1.9.1...
Downloading term_glyph 1.2.1...
Downloading meta 1.8.0...
Downloading async 2.9.0...
Changed 15 dependencies!
  • I use VScodium, so then I can also type codium . to open all the files in the folder and manage the dart dependencies from there.
  • You will need to replace the credentials in % gsheets_example.dart with the content of the JSON file you created when you created the service account key
    {
      "type": "service_account",
      "project_id": "",
      "private_key_id": "",
      "private_key": "",
      "client_email": "",
      "client_id": "",
      "auth_uri": "https://accounts.google.com/o/oauth2/auth",
      "token_uri": "https://oauth2.googleapis.com/token",
      "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
      "client_x509_cert_url": ""
    }

  • Run your dart app dart gsheets_example.dart
  • You'll see the results in the console
    % dart gsheets_example.dart
    new
    new
    new2
    new2
    [index, letter, number, label]
    [0, 1, 2, 3, 4]
    [a, b, c, d, e]
    {index: number, 0: 1, 1: 2, 2: 3, 3: 4, 4: 5}
    {a: a1, b: b2, c: c3, d: d4, e: e5}
    {index: 5, letter: f, number: 6, label: f6}
    [_index, _letter, _number, _label]
      

    Screenshot of Google Sheet document generated by Gsheets Dart example app
    Google Sheet document generated by Gsheets Dart example app
  • Thats fantastic. I can see that I can create a sheet and write data to it. I can see that I can get the data in a usable for for my dart app.