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?
Contents
Gsheets with Dart
- Create a Sheets document https://docs.google.com/spreadsheets
- Once created, find its identifier - after the
/d/
and before the/edit/
.Sheets Identifier location in sheets link -
Enable the Sheets API. Use https://console.cloud.google.com to enable the sheets API
- 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.
The project chooser in Google Cloud Console - Choose the
Library
menu item to go to the API Library and then search forsheets
.Google Console API Library search experience - Select the Google Sheets API.
Google Sheets API Search Result - Enable it.
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.
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.
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": "" }
This is a security risk because anyone with your code can now access your sheet. Be careful where you check it in, or find a way to abstract it all out of the code which is out of scope for this article. - 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]
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.