Definition of SpreadSheet Schema

[azu]

The user can edit their own spreadsheet directly. So, we should provide spreadsheet schema.

The user can know that what cell can be edit or not.

• https://twitter.com/windchime_yk/status/1370376317896552452

[azu]

Option 1: per year sheet

currently, spreadsheet has a sheet per year.

props

• can have a budget per year

cons

• complex logic
  • especially, create a new record by subscription

Option 2: all data in a single sheet

props

• simple
• easy to calculate the sum of all records

cons

• can not have a budget per year

[azu]

options 2: all data in a single sheet

=SUMIFS(C:C,A:A,"2021-*") can calculate date by year.

[azu]

If we can record the history of budge in option 2, we can select option 2.

[azu]

Budget Sheet is reasonable for me.

[azu]

Ok. I figure out!

Example

https://docs.google.com/spreadsheets/d/1We_196wyVdIu2Kttt4eHaqIfmS-86SWUMN7DsW8DfiI/edit?usp=sharing

Change

• Rename 2021 sheet to Record (bike sharing name)
• Create Budget sheet and move current budge to Budget sheet

Budget Schema

Year	Budget	Used	Balance
2020	¥200,000.00	¥0.00	¥200,000.00
2021	¥200,000.00	¥337,861.02	-¥137,861.02

Budget sheet formula

• Year is manual input(2020, 2021 ...)
• Budget is manual input
• Used refer Record sheet via =SUMIFS('Record'!C:C,'Record'!A:A,INDIRECT(ADDRESS(ROW(),COLUMN()-2))&"-*")
• Balance refer Record sheet via =INDIRECT(ADDRESS(ROW(),COLUMN()-2))-INDIRECT(ADDRESS(ROW(),COLUMN()-1))

Record sheet formula

• Budget to be =IFERROR(Index(QUERY(Budget!A:B, "select * where A = "&YEAR(TODAY()), 0),1,2), 0)
• Used to be =SUMIFS(C:C,A:A, YEAR(TODAY())&"-*")
• Balance to be =A2-B2

📝 If a budget of current year is missing, Budget show 0.

Note

• This design aim to reduce create sheet task suddenly
  • "Option 1" need to create new sheet when input new record. We want to avoid it
• This design get aim to human readable and machine readable
  • Separated definition help us to use
• A single Record sheet easy to introduce subscription sheet #8
  • Option 2 is simple by the reasone
• ⚠️ careful recursive reference

[azu]

Step

• [x] 1. Support new Record Schema https://github.com/azu/philan.net/issues/22#issuecomment-802884472 in #26
• [x] MAINTENANCE MODE #27
  • [ ] 2. append data with specific sheet name for avoiding race condition ~(fix batchUpdate) #28~
  • [ ] 3. Migrate existing records into the Records sheet and Budget sheet
• [x] 4. Support to input new budget by year-end

[azu]

Plan

2021-04-04 17:00 ~: SpreadSheetのマイグレーションメンテナンス · Discussion #36 · azu/philan.net

[azu]

After migration and we need to add docs

[azu]

Now, new schema. We will continue to write docs #37