[Flutter] Google Spreadsheet as a data source - mathru.net | App Development with Flutter, Unity/Music and Video Production/Material Distribution
2023-08-14
Development Flutter Masamune Dart pub

[Flutter] Google Spreadsheet as a data source

Hello. I'm Masaru Hirose.

I am constantly updating Masamune, an integration framework that runs on Flutter.

[Flutter] App development in just a few minutes!!
Hello. My name is Masaru Hirose.
https://medium.comhttps://medium.com
title

This time, there are even more new updates around the data model.

masamune

masamune | Flutter package
An application development framework centered on automatic code generation using build_runner.
https://pub.devhttps://pub.dev
title
masamune_builder | Dart package
Building system for masamune framework. Automatic creation of models, themes, pages, and translation data.
https://pub.devhttps://pub.dev
title

Introduction

Masamune Framework is a framework on Flutter that uses "CLI code generation" and "automatic code generation with build_runnner" to reduce the amount of code as much as possible and increase stability and speed of application development.

As for the database, I use a NoSQL database based on Firestore and provide a mechanism that allows switching between the runtime DB, the terminal local DB and Firestore with just one line of update.

I mentioned previously in the following article that ModelAdapter can now be set for each data model.

[Flutter] Enhancement of Masamune’s DB linkage function
Hello. I’m Masaru Hirose.
https://medium.comhttps://medium.com
title

This time, a ModelAdapter that can use data obtained from Google spreadsheets has been added.

App settings can be described in a Google Spreadsheet as well as in the app, and can be treated as similar data while connecting relationships with Firestore and local DB.

Advantages of describing data in a Google Spreadsheet

There are three main advantages to describing application settings in a Google Spreadsheet instead of in the source code.

  1. Reduce the number of source code descriptions
  2. Can be edited online.
  3. Can be edited by non-engineers

3 is especially important. External parties, such as app vendors and operators, and internal parties, such as PdMs and PjMs, are often non-engineers and cannot directly play with the source code.

In this regard, spreadsheet software such as Google Spreadsheet is easy to handle and easy to request work to be done because it is software that every businessman has touched.

For example, it makes sense for the necessary categories for an e-commerce application to be directly described by a member of the marketing team, who is highly dependent on the marketing team.

Having the data directly in a Google spreadsheet will reduce communication costs between other members and the engineer, and will also reduce the engineer's man-hours.

The Masamune framework already has a Google spreadsheet to manage translations.

This allows the translation work to be requested in a way that is easy for the translator, not the engineer, to perform.

Advance preparation

Google Spreadsheets will be made available in advance.

  1. Copy the spreadsheet from this template to your own Google Drive.
    • If you use CollectionModelPath, use the sheet for collections; if you use DocumentModelPath, use the sheet for documents.
    • You can copy from File -> Create Copy.
  2. In the copied spreadsheet, click File -> Share -> `Share with others
  3. In the Share (name of spreadsheet you created) window, change General Access to *Everyone who knows the link***.

Let's actually do it!

Now I would like to proceed with the actual implementation.

In this case, I will create a demo application that simply defines the products and categories of an e-commerce store in a spreadsheet, then purchases them and keeps them in the history.

Project Creation

First, create a project with katana create.

Copy 
katana create net.mathru.csvtest

Wait a few moments and the project file will be created.

Create spreadsheet data

First, let's create the data in a spreadsheet.

Change the sheet name to Product and describe the data as follows.

If you want to separate translations, please register data separately by key:language code like name:en.

image block

Model Creation

Let's return to the Flutter project and continue to create various data models.

First, generate the code with the CLI.

Copy 
# Product Data
katana code collection product

Edit the generated Dart file.

In the GoogleSpreadSheetDataSource, you should write the URL of the Product sheet of the Google Spreadsheet when it is open.

If you have registered translations in the spreadsheet, you can use ModelLocalizedValue to summarize the values.

Copy 
// models/product.dart

/// Value for model.
@freezed
@formValue
@immutable
// TODO: Set the path for the collection.
@CollectionModelPath("product")
@GoogleSpreadSheetDataSource(
  "https://docs.google.com/spreadsheets/d/1HojG5QzScb8b0EB1HvHHN11yaTEf2hwwtrrUwBI5U38/edit#gid=0",
  version: 1,
)
class ProductModel with _$ProductModel {
  const factory ProductModel({
    // TODO: Set the data schema.
    required int id,
    @Default(ModelLocalizedValue()) ModelLocalizedValue name,
    @Default(ModelLocalizedValue()) ModelLocalizedValue description,
    @Default(0.0) double price,
  }) = _ProductModel;
  const ProductModel._();

  ~~~~
}

The following command will perform automatic generation by build_runner.

Copy 
katana code generate

The data in the CSV will now be applied to the source code.

Create product listing page

The following command creates a product listing page.

Copy 
katana code page product

Rewrite to display the product list.

Copy 
// pages/product.dart

@immutable
// TODO: Set the path for the page.
@PagePath("product")
class ProductPage extends PageScopedWidget {
  const ProductPage({
    super.key,
    // TODO: Set parameters for the page.
  });

  // TODO: Set parameters for the page in the form [final String xxx].

  /// Used to transition to the ProductPage screen.
  ///
  /// ```dart
  /// router.push(ProductPage.query(parameters));    // Push page to ProductPage.
  /// router.replace(ProductPage.query(parameters)); // Push page to ProductPage.
  /// ```
  @pageRouteQuery
  static const query = _$ProductPageQuery();

  @override
  Widget build(BuildContext context, PageRef ref) {
    // Describes the process of loading
    // and defining variables required for the page.
    // TODO: Implement the variable loading process.
    final product = ref.model(ProductModel.collection())..load();

    // Describes the structure of the page.
    // TODO: Implement the view.
    return UniversalScaffold(
      appBar: const UniversalAppBar(
        title: Text("Product List"),
      ),
      body: UniversalListView(
        children: [
          ...product.mapListenable((item) {
            return ListTile(
              title: Text(item.value?.name.value.value(l.locale) ?? ""),
              subtitle:
                  Text(item.value?.description.value.value(l.locale) ?? ""),
              trailing:
                  Text("￥${item.value?.price.toStringAsFixed(0) ?? "0"}"),
            );
          }),
        ],
      ),
    );
  }
}

Change initialQuery in main.dart.

Copy 
// main.dart

/// Initial page query.
// TODO: Define the initial page query of the application.
final initialQuery = ProductPage.query();

You will build and check it as is.

The list is now displayed!

image block

Add category

First, create a Category sheet in Google Spreadsheet and edit it as follows

image block

Also, add a category entry to the Product sheet to describe the relationship data.

Relation data can be written in the following format

Copy 
ref://[Collection name *Specified in the application]/[Target ID]

The data in this case is as follows.

Relation data English name Japanese name
ref://category/1 Katana 刀
ref://category/2 Gun 銃
image block

Return to the Flutter project and generate code in the CLI.

Copy 
katana code collection category

Edit the generated Dart file.

In the GoogleSpreadSheetDataSource, you should write the URL of the Category sheet of the Google Spreadsheet when it is open.

Copy 
// models/category.dart

/// Value for model.
@freezed
@formValue
@immutable
// TODO: Set the path for the collection.
@CollectionModelPath("category")
@GoogleSpreadSheetDataSource(
  "https://docs.google.com/spreadsheets/d/1HojG5QzScb8b0EB1HvHHN11yaTEf2hwwtrrUwBI5U38/edit#gid=653415878",
  version: 1,
)
class CategoryModel with _$CategoryModel {
  const factory CategoryModel({
    // TODO: Set the data schema.
    required int id,
    @Default(ModelLocalizedValue()) ModelLocalizedValue name,
  }) = _CategoryModel;
  const CategoryModel._();

  ~~~~
}

Also, add more version of GoogleSpreadSheetDataSource in models/product.dart to add relationships to CategoryModel and update data.

To create a relationship for other data, annotate it with @refParam and specify the type of the class (in this case CategoryModelRef) from which ModelRefBase is inherited.

Copy 
// models/product.dart

/// Value for model.
@freezed
@formValue
@immutable
// TODO: Set the path for the collection.
@CollectionModelPath("product")
@GoogleSpreadSheetDataSource(
  "https://docs.google.com/spreadsheets/d/1HojG5QzScb8b0EB1HvHHN11yaTEf2hwwtrrUwBI5U38/edit#gid=0",
  version: 2,
)
class ProductModel with _$ProductModel {
  const factory ProductModel({
    // TODO: Set the data schema.
    required int id,
    @Default(ModelLocalizedValue()) ModelLocalizedValue name,
    @Default(ModelLocalizedValue()) ModelLocalizedValue description,
    @Default(0.0) double price,
    @refParam CategoryModelRef category,
  }) = _ProductModel;
  const ProductModel._();

  ~~~~
}

The following command will perform automatic generation by build_runner.

Copy 
katana code generate

Use pages/product.dart to display the categories together.

Copy 
// pages/product.dart

@immutable
// TODO: Set the path for the page.
@PagePath("product")
class ProductPage extends PageScopedWidget {
  const ProductPage({
    super.key,
    // TODO: Set parameters for the page.
  });

  // TODO: Set parameters for the page in the form [final String xxx].

  /// Used to transition to the ProductPage screen.
  ///
  /// ```dart
  /// router.push(ProductPage.query(parameters));    // Push page to ProductPage.
  /// router.replace(ProductPage.query(parameters)); // Push page to ProductPage.
  /// ```
  @pageRouteQuery
  static const query = _$ProductPageQuery();

  @override
  Widget build(BuildContext context, PageRef ref) {
    // Describes the process of loading
    // and defining variables required for the page.
    // TODO: Implement the variable loading process.
    final product = ref.model(ProductModel.collection())..load();

    // Describes the structure of the page.
    // TODO: Implement the view.
    return UniversalScaffold(
      appBar: const UniversalAppBar(
        title: Text("Product List"),
      ),
      body: UniversalListView(
        children: [
          ...product.mapListenable((item) {
            return ListTile(
              title: Text(item.value?.name.value.value(l.locale) ?? ""),
              subtitle: Text(
                  "${item.value?.category?.value?.name.value.value(l.locale) ?? ""}\n${item.value?.description.value.value(l.locale) ?? ""}"),
              trailing: Text("￥${item.value?.price.toStringAsFixed(0) ?? "0"}"),
            );
          }),
        ],
      ),
    );
  }
}

If you build with this, you will see that the category is successfully displayed.

image block

Show pages by category

Modify the current page to display by category.

First, edit pages/product.dart.

Make ProductModel accept categoryId as an argument and filter the collection by category.

Copy 
@immutable
// TODO: Set the path for the page.
@PagePath("product/:category_id")
class ProductPage extends PageScopedWidget {
  const ProductPage({
    super.key,
    // TODO: Set parameters for the page.
    @pageParam required this.categoryId,
  });

  // TODO: Set parameters for the page in the form [final String xxx].
  final String categoryId;

  /// Used to transition to the ProductPage screen.
  ///
  /// ```dart
  /// router.push(ProductPage.query(parameters));    // Push page to ProductPage.
  /// router.replace(ProductPage.query(parameters)); // Push page to ProductPage.
  /// ```
  @pageRouteQuery
  static const query = _$ProductPageQuery();

  @override
  Widget build(BuildContext context, PageRef ref) {
    // Describes the process of loading
    // and defining variables required for the page.
    // TODO: Implement the variable loading process.
    final category = ref.model(CategoryModel.document(categoryId))..load();
    final product = ref.model(
      ProductModel.collection().category.equal(category),
    )..load();

    // Describes the structure of the page.
    // TODO: Implement the view.
    return UniversalScaffold(
      appBar: const UniversalAppBar(
        title: Text("Product List"),
      ),
      body: UniversalListView(
        children: [
          ...product.mapListenable((item) {
            return ListTile(
              title: Text(item.value?.name.value.value(l.locale) ?? ""),
              subtitle: Text(
                  "${item.value?.category?.value?.name.value.value(l.locale) ?? ""}\n${item.value?.description.value.value(l.locale) ?? ""}"),
              trailing: Text("￥${item.value?.price.toStringAsFixed(0) ?? "0"}"),
            );
          }),
        ],
      ),
    );
  }
}

Thus, it is possible to specify filter conditions for the type safe.

Copy 
ProductModel.collection().category.equal(category)

Once here, automatic generation by build_runner is performed.

Copy 
katana code generate

Then create a category listing page.

Copy 
katana code page category

Edit the created page.

Copy 
// pages/category.dart

@immutable
// TODO: Set the path for the page.
@PagePath("category")
class CategoryPage extends PageScopedWidget {
  const CategoryPage({
    super.key,
    // TODO: Set parameters for the page.
  });

  // TODO: Set parameters for the page in the form [final String xxx].

  /// Used to transition to the CategoryPage screen.
  ///
  /// ```dart
  /// router.push(CategoryPage.query(parameters));    // Push page to CategoryPage.
  /// router.replace(CategoryPage.query(parameters)); // Push page to CategoryPage.
  /// ```
  @pageRouteQuery
  static const query = _$CategoryPageQuery();

  @override
  Widget build(BuildContext context, PageRef ref) {
    // Describes the process of loading
    // and defining variables required for the page.
    // TODO: Implement the variable loading process.
    final category = ref.model(CategoryModel.collection())..load();

    // Describes the structure of the page.
    // TODO: Implement the view.
    return UniversalScaffold(
      appBar: const UniversalAppBar(
        title: Text("Category List"),
      ),
      body: UniversalListView(
        children: [
          ...category.mapListenable((item) {
            return ListTile(
              title: Text(item.value?.name.value.value(l.locale) ?? ""),
              onTap: () {
                router.push(ProductPage.query(categoryId: item.uid));
              },
            );
          })
        ],
      ),
    );
  }
}

Replace the initial page with the category list in main.dart.

Copy 
// main.dart

/// Initial page query.
// TODO: Define the initial page query of the application.
final initialQuery = CategoryPage.query();

The following command will perform automatic generation by build_runner.

Copy 
katana code generate

Let's build it.

image block

image block

Tap an item from the category list to see that it is filtered by that item.

Implement purchase history

Let's make it so that when you tap an item, it is purchased and can only be purchased once.

The first step is to create a model of the purchase history.

Copy 
katana code collection history

Rewrite the generated code.

Copy 
// models/history.dart

/// Value for model.
@freezed
@formValue
@immutable
// TODO: Set the path for the collection.
@CollectionModelPath("history")
class HistoryModel with _$HistoryModel {
  const factory HistoryModel({
    // TODO: Set the data schema.
    @RefParam(ProductModelDocument) ProductModelRef product,
  }) = _HistoryModel;
  const HistoryModel._();

  ~~~~
}

The following command will perform automatic generation by build_runner.

Copy 
katana code generate

Edit pages/product.dart.

Verifies if the history collection contains the product's own uid.

If not included, the product is available for purchase, and tapping on it saves the document, including the product itself, in hisotry's collection.

Copy 
// pages/product.dart

@immutable
// TODO: Set the path for the page.
@PagePath("product/:category_id")
class ProductPage extends PageScopedWidget {
  const ProductPage({
    super.key,
    // TODO: Set parameters for the page.
    @pageParam required this.categoryId,
  });

  // TODO: Set parameters for the page in the form [final String xxx].
  final String categoryId;

  /// Used to transition to the ProductPage screen.
  ///
  /// ```dart
  /// router.push(ProductPage.query(parameters));    // Push page to ProductPage.
  /// router.replace(ProductPage.query(parameters)); // Push page to ProductPage.
  /// ```
  @pageRouteQuery
  static const query = _$ProductPageQuery();

  @override
  Widget build(BuildContext context, PageRef ref) {
    // Describes the process of loading
    // and defining variables required for the page.
    // TODO: Implement the variable loading process.
    final category = ref.model(CategoryModel.document(categoryId))..load();
    final product = ref.model(
      ProductModel.collection().category.equal(category),
    )..load();
    final history = ref.model(HistoryModel.collection())..load();

    // Describes the structure of the page.
    // TODO: Implement the view.
    return UniversalScaffold(
      appBar: const UniversalAppBar(
        title: Text("Product List"),
      ),
      body: UniversalListView(
        children: [
          ...product.mapListenable((item) {
            final purchased =
                history.any((e) => e.value?.product?.uid == item.uid);
            return ListTile(
              title: Text(item.value?.name.value.value(l.locale) ?? ""),
              subtitle: Text(
                  "${item.value?.category?.value?.name.value.value(l.locale) ?? ""}\n${item.value?.description.value.value(l.locale) ?? ""}"),
              trailing: purchased
                  ? Icon(Icons.check_circle, color: theme.color.success)
                  : Text("￥${item.value?.price.toStringAsFixed(0) ?? "0"}"),
              onTap: purchased
                  ? null
                  : () async {
                      final doc = history.create();
                      await doc
                          .save(
                            HistoryModel(
                              product: item,
                            ),
                          )
                          .showIndicator(context);
                    },
            );
          }),
        ],
      ),
    );
  }
}

Let's build it.

Tap on each product to see that it will be marked as purchased.

image block

In this case, I am using RuntimeModelAdapter, so the history is lost when the app is restarted, but it is possible to persist the purchase history by using LocalModelAdapter or FirestoreModelAdapter.

See below for details.

[Flutter] Ultra-fast app development with Masamune (1) “Create a Project”
Hello. I’m Masaru Hirose.
https://medium.comhttps://medium.com
title

Create purchase history list screen

The last step is to create the Purchase History List screen.

First, let's create the Purchase History List screen.

Copy 
katana code page history

Edit the generated code.

The product defined in HistoryModel stores the data of the related ProductModel and can be retrieved directly.

Copy 
// pages/history.dart

@immutable
// TODO: Set the path for the page.
@PagePath("history")
class HistoryPage extends PageScopedWidget {
  const HistoryPage({
    super.key,
    // TODO: Set parameters for the page.
  });

  // TODO: Set parameters for the page in the form [final String xxx].

  /// Used to transition to the HistoryPage screen.
  ///
  /// ```dart
  /// router.push(HistoryPage.query(parameters));    // Push page to HistoryPage.
  /// router.replace(HistoryPage.query(parameters)); // Push page to HistoryPage.
  /// ```
  @pageRouteQuery
  static const query = _$HistoryPageQuery();

  @override
  Widget build(BuildContext context, PageRef ref) {
    // Describes the process of loading
    // and defining variables required for the page.
    // TODO: Implement the variable loading process.
    final history = ref.model(HistoryModel.collection())..load();

    // Describes the structure of the page.
    // TODO: Implement the view.
    return UniversalScaffold(
      appBar: const UniversalAppBar(
        title: Text("History"),
      ),
      body: UniversalListView(
        children: [
          ...history.mapListenable((item) {
            final product = item.value?.product;
            return ListTile(
              title: Text(product?.value?.name.value.value(l.locale) ?? ""),
              subtitle: Text(
                  "${product?.value?.category?.value?.name.value.value(l.locale) ?? ""}\n${product?.value?.description.value.value(l.locale) ?? ""}"),
            );
          })
        ],
      ),
    );
  }
}

Create a menu that displays the category list and purchase history.

Create pages for menus from commands.

Copy 
katana code page menu

Edit the created page.

Copy 
// pages/menu.dart

@immutable
// TODO: Set the path for the page.
@PagePath("menu")
class MenuPage extends PageScopedWidget {
  const MenuPage({
    super.key,
    // TODO: Set parameters for the page.
  });

  // TODO: Set parameters for the page in the form [final String xxx].

  /// Used to transition to the MenuPage screen.
  ///
  /// ```dart
  /// router.push(MenuPage.query(parameters));    // Push page to MenuPage.
  /// router.replace(MenuPage.query(parameters)); // Push page to MenuPage.
  /// ```
  @pageRouteQuery
  static const query = _$MenuPageQuery();

  @override
  Widget build(BuildContext context, PageRef ref) {
    // Describes the process of loading
    // and defining variables required for the page.

    // Describes the structure of the page.
    // TODO: Implement the view.
    return UniversalScaffold(
      appBar: const UniversalAppBar(
        title: Text("Demo"),
      ),
      body: UniversalListView(
        children: [
          ListTile(
            title: const Text("Category"),
            onTap: () {
              router.push(CategoryPage.query());
            },
          ),
          ListTile(
            title: const Text("History"),
            onTap: () {
              router.push(HistoryPage.query());
            },
          )
        ],
      ),
    );
  }
}

Edit main.dart.

Copy 
// main.dart

/// Initial page query.
// TODO: Define the initial page query of the application.
final initialQuery = MenuPage.query();

The following command will perform automatic generation by build_runner.

Copy 
katana code generate

Let's build it.

After launching, tap the product and return to the History page to find that the purchased product is displayed.

image block

In this way, you can create a relationship from a database to a Google Spreadsheet data source and use it without being aware of its internals.

Conclusion

You can now implement applications while also collaborating with non-engineers because we can easily use Google Spreadsheets as a data source while also achieving relationships to other databases.

I made it for my own use, but if you think it fits your implementation philosophy, by all means, use it!

Also, I releasing the source here, so issues and PullRequests are welcome!

If you have any further work requests, please contact me directly through my Twitter or website!

Contact - mathru.net | App Development with Flutter, Unity/Music and Video Production/Material Distribution
Offers app development and apps using Flutter and Unity. Includes information on music and videos created by the company. Distribution of images and video materials. We also accept orders for work.
https://mathru.nethttps://mathru.net
title

GitHub Sponsors

Sponsors are always welcome. Thank you for your support!

Sponsor @mathrunet on GitHub Sponsors
Developed the katana/masamune framework, which has dramatically improved the overall efficiency of Flutter-based application development.
https://github.comhttps://github.com
title
Development Flutter Masamune Dart pub
[Flutter] Katana Value

Development Flutter Masamune Dart pub

◀︎ Next
[Flutter] Enhancement of Masamune's DB linkage function

Development Flutter Masamune Dart pub

▶︎ Previous
↑Top
Categories
Application Development Works Music Assets Blog
Menu
Requests for works Contact Privacy Policy Terms of service Regarding the use of music
Link
Github YouTube X (Twitter) Threads LinkedIn Booth note