Oflline sync gsheets and dart

[inkomomutane]

how to synchronize data from offline and online sheets on the flutter? please help me

[a-marenkov]

@inkomomutane Hi! Thanks for checking out gsheets library!

I'm not sure what you mean by "synchronize data from offline and online sheets". Could you please elaborate on problem you're trying to solve?

[y4m4h4]

Hi @a-marenkov ! Thanks for this awesome package!! I would like to comment on the issue with my own. What I wish is to save the sheet offline via path_provider and use it until I wish to refresh it to a later version via a function to fetch it. Is it possible? I tried to save the json data as string but am unable to use it as a future for listview builder.

Thanks again for the package!

[inkomomutane]

@a-marenkov I have a flutter project with Google Sheets as a database with internet access and it works normally But I need the app to work even without internet and when connected, synchronize the data.

[a-marenkov]

@inkomomutane @y4m4h4

Hi Caching data is not really in the scope of this package, since it's pure dart package. Thus caching and syncing data, should be done by yourself.

Here are some thoughts:

For caching you can use sqflite, hive, moor, floor and even shared_preferences. You can also cache data as @y4m4h4 suggested by creating a file via path_provider (this is how it's done https://flutter.dev/docs/cookbook/persistence/reading-writing-files).

Syncing can be tricky though, if you only need to update sheets with local changes, it can be easelly done with gsheets - just insert local values in the sheet (insertRows, insertColumns, appendRows, appendColumns can be used), but if you need to merge local and remote changes, there is no easy way to do so.

I'm not closing this issue just yet, so we can continue discussion that can lead to some improovements for gsheets or maybe creating a additional package for flutter with caching.

[y4m4h4]

@a-marenkov As I mentioned in the prior comment, I’m finding it hard to save the data retrieved from the getAll(). Request you to assist in saving the data locally via path_provider or nosql(using sembast). The link provided in your comment assists in saving files as strings which cannot be referenced as future for builders.

[piecubed]

@a-marenkov Can we make L1023 a public method?

[a-marenkov]

@a-marenkov Can we make L1023 a public method?

Why? I didn't understand

[a-marenkov]

@a-marenkov As I mentioned in the prior comment, I’m finding it hard to save the data retrieved from the getAll(). Request you to assist in saving the data locally via path_provider or nosql(using sembast). The link provided in your comment assists in saving files as strings which cannot be referenced as future for builders.

Why can't you use it for builders?

This snippet might help you

class MyHomePage extends StatefulWidget {
  @override
  _MyHomePageState createState() => _MyHomePageState();
}

class _MyHomePageState extends State<MyHomePage> {
  Future<List<List<String>>> data;

  @override
  void initState() {
    super.initState();
    data = getData();
  }

  Future<File> getFile() async {
    final directory = await getApplicationDocumentsDirectory();
    return File('${directory.path}/example.txt');
  }

  Future<List<List<String>>> getData() async {
    final file = await getFile();
    if (await file.exists()) {
      return fromJson(await file.readAsString());
    } else {
      await file.create();
      final rows = await getRemoteData();
      cache(file, rows);
      return rows;
    }
  }

  List<List<String>> fromJson(string) {
    var data = (jsonDecode(string) as List).cast<List>();
    return data.map((e) => e.cast<String>()).toList();
  }

  Future<List<List<String>>> getRemoteData() async {
    final ss = await gsheets.spreadsheet(_spreadsheetId);
    final sheet = ss.worksheetByTitle('example');
    return sheet.values.allRows(fromRow: 2);
  }

  void cache(File file, List<List<String>> rows) {
    file.writeAsString(jsonEncode(rows));
  }

  Future<List<List<String>>> refresh() async {
    final rows = await getRemoteData();
    cache(await getFile(), rows);
    return rows;
  }

  @override
  Widget build(BuildContext context) {
    return Container(
      color: Colors.white,
      padding: const EdgeInsets.all(24.0),
      child: Column(
        mainAxisAlignment: MainAxisAlignment.spaceEvenly,
        children: <Widget>[
          FutureBuilder(
            future: data,
            builder: (_, snapshot) {
              print(snapshot);
              if (snapshot.data == null) {
                return CircularProgressIndicator();
              }
              final List<List<String>> data = snapshot.data;
              return ListView.builder(
                  itemCount: data.length,
                  shrinkWrap: true,
                  itemBuilder: (ctx, index) {
                    return Row(
                      mainAxisAlignment: MainAxisAlignment.spaceEvenly,
                      children: data[index].map((e) => Text(e)).toList(),
                    );
                  });
            },
          ),
          RaisedButton(
            child: Text('refresh'),
            onPressed: () {
              setState(() {
                data = refresh();
              });
            },
          ),
        ],
      ),
    );
  }
}

[piecubed]

@a-marenkov I think that @y4m4h4 wants to just serialize the data, so if i understand _getAll, that would be what he wants. Is there a reason that its private?

[piecubed]

Oh, does sheet.values.allRows do the same thing? Must admit I haven't read all of the library yet.

[a-marenkov]

Oh, does sheet.values.allRows do the same thing? Must admit I haven't read all of the library yet.

Yeap, it does, but with nicer api for the dimension and the range

[piecubed]

@y4m4h4 Try using this.

final store = StoreRef.main();
final gsheets = GSheets(_credentials);
const spreadsheetID = 'spreadsheetID';
final spreadsheet = await gsheets.spreadsheet(spreadsheetID);

await store.record('googlesheet').put(db, spreadsheet.values.allRows());

Of course, put that in a function and save the googlesheets instance, but you can figure that out.

[y4m4h4]

@a-marenkov @piecubed Thanks for the quick response! Really appreciate your help! Although, I cannot understand the best way to implement this. I'll explain the problem at hand in detail.

Here is the album: https://imgur.com/a/D1OfgDY

The spreadsheet contains around 27000 entries similar to the ones depicted here: https://imgur.com/PUjWurR

The class to convert the data to map:

class PartsMaster {
  final String partNo;
  final String partName;
  final String oldPartName;
  final String largeCat;
  final String largeCatName;
  final String color;
  final int moq;
  final int mrp;
  final double ndp;
  final String hsn;
  final double sgstRate;
  final double cgstRate;
  final double igstRate;

  PartsMaster({
    this.partNo,
    this.partName,
    this.oldPartName,
    this.largeCat,
    this.largeCatName,
    this.color,
    this.moq,
    this.mrp,
    this.ndp,
    this.hsn,
    this.sgstRate,
    this.cgstRate,
    this.igstRate,
  });

  @override
  String toString() =>
      'PartsMaster{partNo: $partNo, partName: $partName,oldPartName: $oldPartName,largeCat: $largeCat,largeCatName: $largeCatName,color: $color,moq: $moq,mrp: $mrp,ndp: $ndp,hsn: $hsn,sgstRate: $sgstRate,cgstRate: $cgstRate,igstRate: $igstRate}';

  factory PartsMaster.fromGsheets(Map<String, dynamic> json) {
    return PartsMaster(
      partNo: json['Part No'],
      partName: json['Part Name'],
      oldPartName: json['Old Part No'],
      largeCat: json['Large Category'],
      largeCatName: json['Large Category Name'],
      color: json['Colour'],
      moq: int.tryParse(json['MOQ'] ?? ''),
      mrp: int.tryParse(json['MRP'] ?? ''),
      ndp: double.tryParse(json['NDP'] ?? ''),
      hsn: json['HSN'],
      sgstRate: double.tryParse(json['SGST-RATE'] ?? ''),
      cgstRate: double.tryParse(json['CGST-RATE'] ?? ''),
      igstRate: double.tryParse(json['IGST-RATE'] ?? ''),
    );
  }
}

This is how the spreadsheet is referred:

class PartsMasterManager {
  final GSheets _gsheets = GSheets(_credentials);
  Spreadsheet _spreadsheet;
  Worksheet _partsMasterSheet;

  Future<Worksheet> initSheet() async {
    _spreadsheet ??= await _gsheets.spreadsheet(_spreadsheetId);
    _partsMasterSheet ??= _spreadsheet.worksheetByTitle('partsmaster');
    return _partsMasterSheet;
  }

  Future<List<PartsMaster>> getAll() async {
    await initSheet();
    final parts = await _partsMasterSheet.values.map.allRows();
    List<PartsMaster> partsMap =
        parts.map((json) => PartsMaster.fromGsheets(json)).toList();
    return partsMap;
  }
}

And this is how it is referenced in the List: https://imgur.com/avtHYyL

class PartsMaster extends StatelessWidget {
  @override
  Widget build(BuildContext context) {
    return SafeArea(
      child: Scaffold(
        appBar: AppBar(
          title: Text('Parts Master'),
          centerTitle: true,
          actions: [
            GestureDetector(
              onLongPress: () => PartsMasterManager().getAll(),
              child: Padding(
                padding: EdgeInsets.only(right: 10),
                child: Icon(Icons.refresh),
              ),
            ),
          ],
        ),
        backgroundColor: Colors.black,
        body: Center(
          child: PartMasterData(),
        ),
      ),
    );
  }
}

class PartMasterData extends StatefulWidget {
  @override
  _PartMasterDataState createState() => _PartMasterDataState();
}

class _PartMasterDataState extends State<PartMasterData> {
  static final _data = PartsMasterManager().getAll();
  TextEditingController controller = new TextEditingController();
  String filter;

  @override
  void initState() {
    super.initState();
    controller.addListener(() {
      setState(() {
        filter = controller.text;
      });
    });
  }

  @override
  void dispose() {
    controller.dispose();
    super.dispose();
  }

  @override
  Widget build(BuildContext context) {
    return FutureBuilder(
      future: _data,
      builder: (BuildContext context, AsyncSnapshot snapshot) {
        if (snapshot.hasData) {
          return Stack(
            children: [
              Container(
                child: TextField(
                  decoration: InputDecoration(
                    border: OutlineInputBorder(
                      borderRadius: BorderRadius.circular(50),
                    ),
                    filled: true,
                    fillColor: Colors.white,
                    contentPadding: EdgeInsets.fromLTRB(20, 0, 0, 0),
                    hintText: 'Search Part',
                  ),
                  controller: controller,
                ),
              ),
              Container(
                margin: EdgeInsets.only(top: 60),
                child: Table(
                  columnWidths: {
                    0: FixedColumnWidth(
                        MediaQuery.of(context).size.width / 3.5),
                    2: FixedColumnWidth(MediaQuery.of(context).size.width / 7),
                  },
                  border: TableBorder(
                    bottom: BorderSide(color: Colors.white, width: 0.5),
                  ),
                  children: [
                    TableRow(
                      children: [
                        Text(
                          'Part No',
                          style: TextStyle(color: Colors.white),
                        ),
                        Text(
                          'Part Name',
                          style: TextStyle(color: Colors.white),
                        ),
                        Container(
                          alignment: Alignment.center,
                          child: Text(
                            'MRP',
                            style: TextStyle(color: Colors.white),
                          ),
                        ),
                      ],
                    ),
                  ],
                ),
              ),
              Container(
                margin: EdgeInsets.only(top: 85),
                child: ListView.builder(
                  itemCount: snapshot.data.length,
                  itemBuilder: (context, index) {
                    return filter == null || filter == ""
                        ? Table(
                            columnWidths: {
                              0: FixedColumnWidth(
                                  MediaQuery.of(context).size.width / 3.5),
                              2: FixedColumnWidth(
                                  MediaQuery.of(context).size.width / 7),
                            },
                            border: TableBorder(
                              bottom:
                                  BorderSide(color: Colors.white, width: 0.25),
                            ),
                            children: [
                              TableRow(
                                children: [
                                  Text(
                                    '${snapshot.data[index].partNo}',
                                    style: TextStyle(color: Colors.white),
                                  ),
                                  Text(
                                    '${snapshot.data[index].partName}',
                                    style: TextStyle(
                                        color: Colors.white, fontSize: 12),
                                  ),
                                  Container(
                                    padding: EdgeInsets.only(right: 10),
                                    alignment: Alignment.centerRight,
                                    child: Text(
                                      '${snapshot.data[index].mrp}',
                                      style: TextStyle(color: Colors.white),
                                    ),
                                  ),
                                ],
                              ),
                            ],
                          )
                        : '${snapshot.data[index].partNo}'
                                .toLowerCase()
                                .contains(filter.toLowerCase())
                            ? Table(
                                columnWidths: {
                                  0: FixedColumnWidth(
                                      MediaQuery.of(context).size.width / 3.5),
                                  2: FixedColumnWidth(
                                      MediaQuery.of(context).size.width / 7),
                                },
                                border: TableBorder(
                                  bottom: BorderSide(
                                      color: Colors.white, width: 0.25),
                                ),
                                children: [
                                  TableRow(
                                    children: [
                                      Text(
                                        '${snapshot.data[index].partNo}',
                                        style: TextStyle(color: Colors.white),
                                      ),
                                      Text(
                                        '${snapshot.data[index].partName}',
                                        style: TextStyle(
                                            color: Colors.white, fontSize: 12),
                                      ),
                                      Container(
                                    padding: EdgeInsets.only(right: 10),
                                    alignment: Alignment.centerRight,
                                    child: Text(
                                      '${snapshot.data[index].mrp}',
                                      style: TextStyle(color: Colors.white),
                                    ),
                                  ),
                                ],
                              )
                            : Container();
                  },
                ),
              ),
            ],
          );
        } else {
          return CircularProgressIndicator();
        }
      },
    );
  }
}

What is wish to achieve: Save the spreadsheet on device locally, reference it for the list with the above functionality to refer to it with partNo for search https://imgur.com/cyms0Tg .

Thanks!!

[y4m4h4]

@a-marenkov @piecubed Hi!

Since the attached image of the google sheet screenshot is not clear, attaching the response from the googlesheet via the getAll() function.

The response is:

[PartsMaster{partNo: 4LSF31450100, partName: OIL SEAL,oldPartName: ,largeCat: APP,largeCatName: Aurum Protex Parts,color: ,moq: 1,mrp: 315,ndp: 195.71,hsn: 40169300,sgstRate: 9.0,cgstRate: 9.0,igstRate: 18.0}, PartsMaster{partNo: B7JE44510000, partName: ELEMENT, AIR CLEANER,oldPartName: ,largeCat: APP,largeCatName: Aurum Protex Parts,color: ,moq: 1,mrp: 31,ndp: 19.26,hsn: 84219900,sgstRate: 9.0,cgstRate: 9.0,igstRate: 18.0}]

Hope it helps!

[a-marenkov]

@a-marenkov @piecubed Hi!

Since the attached image of the google sheet screenshot is not clear, attaching the response from the googlesheet via the getAll() function.

The response is:

[PartsMaster{partNo: 4LSF31450100, partName: OIL SEAL,oldPartName: ,largeCat: APP,largeCatName: Aurum Protex Parts,color: ,moq: 1,mrp: 315,ndp: 195.71,hsn: 40169300,sgstRate: 9.0,cgstRate: 9.0,igstRate: 18.0}, PartsMaster{partNo: B7JE44510000, partName: ELEMENT, AIR CLEANER,oldPartName: ,largeCat: APP,largeCatName: Aurum Protex Parts,color: ,moq: 1,mrp: 31,ndp: 19.26,hsn: 84219900,sgstRate: 9.0,cgstRate: 9.0,igstRate: 18.0}]

Hope it helps!

You're on a right track.

Just add fromJson/toJson methods to PartsMaster class.

Then add caching into PartsMasterManager (this should help you https://medium.com/@hrishikesh.deshmukh445/persist-data-with-sembast-nosql-database-in-flutter-2b6c5110170f)

[y4m4h4]

@a-marenkov Thanks a bunch!! The fromJson and toJson functions from the first lines in QuickType helped. If you could add those functions in your write up for gsheets it’ll be helpful for others looking at caching.

The only problem that remains fixing is the search function lags due to the huge list (27000+). But I’ll try to figure it out.

Thank you once again.