Get and Display Data from data source Online

Hey all…

I have a small problem. I have some data about some User Information such as, Name/Age/ID/Target/Achieved etc.
So this data is currently updated in a Spreadsheet and stored online. This spreadsheet is UPDATED with new data every day (REPLACED) by a not-so-tech recorder person. so only 1 day’s data is there in the spreadsheet. This is a spreadsheet with 2500 Rows.

i need to make an app for users to login and see the data daily. these data will be filtered so that each user will see only the data that is related to them.

How can i store this database in a cloud storage and use flutter to view that data?
You would say FIREBASE! but after hours of searching, i couldn’t find a easy way to convert a spreadsheet to firestore. There seems to be no easy way to convert a spreadsheet and json and import it to firestore.

Is there any way to use a easier method? all i need to do is Query and View data. No data will be Written to the database. just viewing.

thanx for helping…
-KAD

Hi and welcome! Let me suggest to always ask Google first…
Or, well, let others do it for you :wink: In case they don’t, fallback to the above suggestion.

https://pub.dev/documentation/googleapis/latest/googleapis.sheets.v4/googleapis.sheets.v4-library.html

hth

This is interesting… as u suggested, i asked google first obv…
But it never occurred to me to search for DART google sheets… i always looked for FLUTTER google sheets and never got a reliable solution… anyway i will check this out… thank you soo much!

You’re welcome, glad to know it could be helpful, I hope you can solve your requirement in the simplest way and with the least moving parts is possible (the best code is no code at all!)

hi…
I tried this and some examples from pub.dev…
But i have problems displaying items in flutter…

Is there any resource or example made for flutter?
can anyone help me pls?

Hello, I guess this is what you were looking for:

In the example the author is retrieving the data sheet converted to json, then he displays it using a PaginatedDataTable widget. I think you could still get the data with the googleapis.sheets api and then use a PaginatedDataTable to display them, in a similar way.

You really should improve your Google-fu! :wink:

google-fu :rofl: :rofl:

thank you!
i did find the first link that you’ve showed… but the url that he’s using (api.myjson.com) doesn’t seem to work anymore… :confused:

The goal was giving you an example of how to display a collection with a DataTable, not suggesting the json conversion step. If I understood your requirements, the data sheet will be updated every day, and you dont’t want to manually convert it in json each time.
As I wrote in the previous comment, IMO a straight approach to your problem would be using the google’s sheet api to access the data-sheet you need to display in your Flutter client app, using of a PaginatedDataTable to do that. The linked gist should provide you a reference.
I’d start by connecting to the G-Sheet spreadsheet and retrieving a collection of rows from it, without bothering about the UI side of the task. When I’m sure I can get it, I’d go down the path of building the UI using a table to show them. If I’d find myself in trouble understanding how PaginatedDataTable works, I’d experiment with it using a mock collection of hard-coded data, in isolation (meaning in a new self-contained project created just for that purpose).
It will take time, some head-scratching, lot of wtf-ing, but you’ll eventually put it all together, you’ll have learned a lot more and gained confidence in your problem solving skills. I encourage you to try, and try again. Then when you have a half-backed solution, there will be plenty of help on the practical problems you cannot solve by yourself. But nobody is going to provide you a complete solution out of the box, and you wont get much benefit out of it anyway. So, start coding and have fun with it!

Thanks @patrick_dm
ur awesome :wink:

i will try these and see what i can do… thank u for ur valuable feedback!!
its a good motivation!

1 Like

Well, thank you, but I didn’t do much at all! :smile:
I got interested in this case study though, so … I’m going to explore myself the solution I’ve proposed; it will be a nice opportunity to learn more Dart and Flutter with a pet project where I can do all the mistakes I want, no deadlines and no angry and hurried customers putting pressure on me.
Eventually I’ll let you know if I get something working out of it, but do not count on that :wink:

So the trickiest part was understanding what string I should assign to

String _spreadsheet_id = ''

in order to connect to the spreadsheet (an then the worksheet I’m interested in it)

final GSheets _gsheets = GSheets(_credentials);
_spreadsheet ??= await _gsheets.spreadsheet(_spreadsheet_id);
_workSheet ??= _spreadsheet.worksheetByTitle('myWorksheet');

After some head-scratching I finally realised that it was expecting the hash id that Google provide you when you want to share the document with a static url, it is the long hash in the generated URL, i.e.

https://docs.google.com/spreadsheets/d/1lCwGh7YmNbv7iAb…q2qCoKlX02kBgbhwC/

so:

String _spreadsheet_id = '1lCwGh7YmNbv7iAb..q2qCoKlX02kBgbhwC'

With that in place I could connect to the spreadsheet, and the worksheet inside it (with the name I gave it in its tab).

I will have to study more about PaginatedDataTable, to render the data in a proper way, not just with a dummy ListView.

ooooooooooooh yesessssssssssssss… It works now!!!
Lets try paginated tables now!

thanx aloooooooooooooooooooooooooooooooooooooot

Very good! I’m glad you could make it. I have crafted a working solution too, but have used a simpleTableView, not a PaginatedDataTable. My spreadsheet is quite short. Yours could be much bigger (you were talking about 2500 rows) though if I understood your goal, you will have to filter the rows belonging to the current user of the Flutter application, and maybe you will not have so many data rows to display either?

hi… any chance you can share your solution?

anyway i will be using 2500 rows at max…

Hello, I’ll edit my dart code to take out sensible data (credentials, ids…) and post it here if it can help you. Not now though, but stay tuned.
My solutions displays all the spreadsheets rows; the download is pretty small. How long does your request takes? Filtering 2500 rows should be quite fast, but it could be a nice use case for doing the computation in a Dart Isolate (more a learning opportunity than a real necessity maybe).

Here is my solution, please notice that I’m quite a Flutter beginner, so do not take my code as reference for idiomatic Dart/Flutter! :smile:

main.dart

import 'package:flutter/material.dart';
import 'spreadsheet_manager.dart';
import 'my_row.dart';

void main() {
  runApp(MyApp());
}

class MyApp extends StatelessWidget {
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      title: 'myWorksheet',
      debugShowCheckedModeBanner: false,
      theme: ThemeData(
        primarySwatch: Colors.blue,
        visualDensity: VisualDensity.adaptivePlatformDensity,
      ),
      home: MyHomePage(title: 'myWorksheet'),
    );
  }
}

class MyHomePage extends StatefulWidget {
  MyHomePage({Key key, this.title}) : super(key: key);

  final String title;

  @override
  _MyHomePageState createState() => _MyHomePageState();
}

class _MyHomePageState extends State<MyHomePage> {
  final _spreadsheetManager = SpreadsheetManager();

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(
        title: Text(widget.title),
      ),
      body: Padding(
        padding: const EdgeInsets.only(left: 24.0, right: 24.0),
        child: Center(
          child: SingleChildScrollView(
            scrollDirection: Axis.vertical,
            child: FutureBuilder(
                future: _spreadsheetManager.getAll(),
                builder: (context, snapshot) {
                  if (snapshot.hasData) {
                    return DataTable(
                      columns: [
                        DataColumn(
                          label: Text("Date"),
                          numeric: false,
                        ),
                        DataColumn(
                          label: Text("Quantity"),
                          numeric: true,
                        ),
                        DataColumn(
                          label: Text("Description"),
                          numeric: false,
                        ),
                      ],
                      rows: (snapshot.data as List<MyRow>)
                          .map(
                            (MyRow _row) => DataRow(cells: [
                              DataCell(Text(_row.date)),
                              DataCell(Text(_row.quantity.toString())),
                              DataCell(Text(_row.description)),
                            ]),
                          )
                          .toList(),
                    );
                  } else if (snapshot.hasError) {
                    return Text(snapshot.error);
                  } else {
                    return Padding(
                        padding: EdgeInsets.all(48),
                        child: LinearProgressIndicator());
                  }
                }),
          ),
        ),
      ),
    );
  }
}

spreadsheet_manager.dart

import 'package:gsheets/gsheets.dart';
import 'my_row.dart';

// Replace with your google auth credentials
const _credentials = r'''
{
"type": "",
"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": ""
}
''';

class SpreadsheetManager {
  final GSheets _gsheets = GSheets(_credentials);
  final String _spreadsheetID = '<your spreadsheet_id>';

  Spreadsheet _spreadsheet;
  Worksheet _worksheet;

  Future<void> init() async {
    _spreadsheet ??= await _gsheets.spreadsheet(_spreadsheetID);
    _worksheet ??= _spreadsheet.worksheetByTitle('<your worksheet title>');
  }

  Future<List<MyRow>> getAll() async {
    await init();
    final _rows = await _worksheet.values.map.allRows();

    return _rows.map((json) => MyRow.fromGsheets(json)).toList();
  }
}

my_row.dart

class MyRow {
  const MyRow({
    this.date,
    this.quantity,
    this.description,
  });

  final String date;
  final int quantity;
  final String description;

  factory MyRow.fromGsheets(Map<String, dynamic> json) {
    return MyRow(
      date: json['date'],
      quantity: int.tryParse(json['quantity'] ?? ''),
      description: json['description'],
    );
  }
}

works perfectly… :smiley:

any idea how you can filter the data before fetching?
i mean… lets say i want to display data about only 1 user. then how can i filter only the rows for that user and fetch to the app?
Im trying RowByKey, but it only fetches the 1st value… trying to figure out how to get all that matches… :confused:

No, at the moment I don’t know how to filter rows before fetching, like a database select… where… query would do. I’d have a look at the possibility to use a custom spreadsheet function, but not have no idea if it is viable at all. Is the json really heavy to download with all the rows and filter on the client side?

Edit: just found that Gsheet has a QUERY function:


Questions still remain about how to send the parameter to match the rows with, would it work with multiple concurrent queries or it would leak data?
I’m wondering if -maybe- each client could create a temporary worksheet -with an unique name like a random hash-, feed the user_id parameter to the query function, fetch the results, destroy the worksheet. Just and idea.

Update: DataFilter and batchGetByDataFilter look interesting, unfortunately I can’t find the implementation in ghseet dart library yet, but maybe you could make a raw http request to the endpoint

POST /v4/spreadsheets/{spreadsheetId}:getByDataFilter

Still couldn’t get anything that works…

anyway assuming that we load the whole spreadsheet, is it possible to filter the data from inside of flutter?