google-drive-ruby
google-drive-ruby copied to clipboard
Modify Worksheet#rows to enable returning numeric rows
concept for #378
Given the test spreadsheet: link ("Transactions" worksheet)
(unmodified) #rows will return:
[["", "", "", "", "", "", ""],
["", "Category Transfers", "", "", "Select a Category to view funding details", "Monthly Amount", ""],
["", "", "", "", "Dining Out", "€200.00", ""],
["", "Available to budget", "", "DOL", "To meet your monthly target for this category, budget another", "", ""],
["", "€11,505.00", "", "DOL", "€200.00", "", ""],
["", "", "", "", "", "", ""],
["", "DATE", "AMOUNT", "FROM CATEGORY", "TO CATEGORY", "MEMO", "✱"],
["", "29/5/20", "€375.00", "Available to budget", "Groceries", "Monthly target", ""],
["", "29/5/20", "€120.00", "Available to budget", "Electric Bill", "Monthly target", ""]]
#rows_with_numerics should return:
[["", "", "", "", "", "", ""],
["", "Category Transfers", "", "", "Select a Category to view funding details", "Monthly Amount", ""],
["", "", "", "", "Dining Out", 200.0, ""],
["", "Available to budget", "", "DOL", "To meet your monthly target for this category, budget another", "", ""],
["", 11505.0, "", "DOL", 200.0, "", ""],
["", "", "", "", "", "", ""],
["", "DATE", "AMOUNT", "FROM CATEGORY", "TO CATEGORY", "MEMO", "✱"],
["", 43980.0, 375.0, "Available to budget", "Groceries", "Monthly target", ""],
["", 43980.0, 120.0, "Available to budget", "Electric Bill", "Monthly target", ""]]
and #rows_with_inputs should return:
[["", "", "", "", "", "", ""],
["", "Category Transfers", "", "", "Select a Category to view funding details", "Monthly Amount", ""],
["", "", "", "", "Dining Out", "=IF(Calculations!B6 <= 0, \"N/A\", Calculations!B6)", ""],
["",
"Available to budget",
"",
"DOL",
"=\nIF(F3 = \"N/A\", \"This category does not have a Monthly Amount set\",\nIF(SUMIF(UserDefCategories, E3, UserDefGoals) > 0, \n\nIF(Calculations!C9 > 0, \"To complete your goal for this category, budget another\", \"You've completed your goal for this category\"), \nIF(Calculations!C8 > 0, \"To meet your monthly target for this category, budget another\", \"You've met your target this month\")))",
"",
"=IF(F3 = \"N/A\", BackendData!AO6, IF(SUMIF(UserDefCategories, E3, UserDefGoals) > 0, IF(Calculations!C9 <= 0, BackendData!AO3, BackendData!AO5), IF(Calculations!C8 <= 0, BackendData!AO3, BackendData!AO4)))"],
["",
"=ROUND(Calculations!Q2, 3)",
"",
"DOL",
"=\nIF(F3 = \"N/A\", \"N/A\",\nIF(SUMIF(UserDefCategories, E3, UserDefGoals) > 0, \n\nIF(Calculations!C9 > 0, Calculations!C9, \"No additional funds necessary\"), \nIF(Calculations!C8 > 0, Calculations!C8, \"No additional funds necessary\")))",
"",
""],
["", "", "", "", "", "", ""],
["", "DATE", "AMOUNT", "FROM CATEGORY", "TO CATEGORY", "MEMO", "✱"],
["", "43980", "375", "Available to budget", "Groceries", "Monthly target", ""],
["", "43980", "120", "Available to budget", "Electric Bill", "Monthly target", ""]]