Get and Display Data from data source Online

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?

Of course, you can use one of this two List instance methods

  • where
  • retainWhere

// mocking the list of rows
List<Map> rows = [
  {'id': 1, 'name': 'a', 'target': 'a1'},
  {'id': 2, 'name': 'b', 'target': 'b1'},
  {'id': 1, 'name': 'a', 'target': 'a2'},
  {'id': 3, 'name': 'c', 'target': 'c1'},
];

void main() {
  
  // where creates a new lazy Iterable, it doesn't mutate the rows list.
  List filteredRows1 = rows.where((row) => row['id'] == 1).toList();
  
  // retainWhere filters in place, it mutates the rows list.
  rows.retainWhere((row) => row['id'] == 1);
  
  print(filteredRows1);
  print(rows);

}

> [{id: 1, name: a, target: a1}, {id: 1, name: a, target: a2}]
> [{id: 1, name: a, target: a1}, {id: 1, name: a, target: a2}]

@KAD9_911, I just had the idea of checking if was possibile to implement a kind of sync between a Sheet file and a Firebase database, in order to take advantage of its querying capabilities, and save lots of bytes and energy downloading only specific user’s data.
I really wasn’t happy with the full data download and on-client late filtering for obvious efficiency reasons. The fact is that -yes why shouldn’t be!- it is quite seamless. I didn’t test the implementation described in the following post, but I think you really should have a look at this possibile solution:

Hello, I am trying to update the retrieved values from the sheets in a text widget. I am using Future Builder to display the text. But the widget doesn’t update and I have to hot reload the screen to get the text text widget. How can I fix this?

:thinking:How would you debug a code you cannot read? :wink:
Please share the relevant source code if you’d like someone to look at it and try to help you.