I am reading an entire Gsheet with ReadSheet and retrieving the data with no problem.
The problem arises when splitting the data into lists.
For example,: I have column A, with 7 items, column 2, with 42 items, and columns 3 and 4 with 23 items.
I extract columns using
but if B is shorter then A, C is shorter than B etc, it truncates the list_1.
Is there a way to extract lists of variable size?
do split them into separate lists (as col)
try using every row as a list.. Then in the needy place just use selet list item list block. so you will use less blocks than this
What I did was to add a column of 'sentinels', so that every column prior is the same length as the sentinel (101 items, numbers 1 to 101)
Then all I do is weed out the empty values.
transpose the sheet, that is make columns as rows.
I did think of that, but if I ever had to print out the sheet.... it would be a veeeeery looooong bit of paper....lol
Programatically though, it would have been a very simple solution. Thanks Kevin
What's in the sheet and in its rows and columns?
There might be a way to redesign the sheet to avoid holes and to associate rows with whatever you are tracking.
Essentially it is a combination of grocery list and meal planning, based on a 7 day cycle.
Column A will always hold 7 items, the main meal of the day.
The second column, B, will be the grocery list,
column C & D, respectively, will be menu items to choose from and the components or ingredients of the menu items. (which then transcribed to grocery list items)
Column A will be static, unless some governing body determines there should be more or less days in a week. (Have you met doug ford?!?! it could happen)
Column B, a grocery list, will obviously grow dynamically on Saturday (or any time) whilst we plan the week's meals.
The menu items and ingredients lists will eventually grow equally in size as I install a method from which one can add or subtract from the items one can choose from for the meal planner.
So, the only obvious design switch would be making horizontal rows as opposed to columns. I did consider it... but if I ever had to print it out... (for what reason I yet cannot fathom) it will be awkward.
Using the sentinel column, it forces values into every cell from A to D inclusively. Therefore I have only to select from from each item which is the list of four different values, weekly menu (1), grocery item(2), menu item(3) and ingredients(4), making sure that do in fact have a a non null value.
You could always use google sheets to do the work for you?
what about D... ?
Include it in the formula
This might get simpler if the recipes (unchanging) were kept in a second sheet, and that recipe sheet were used in conjunction with the meal planning sheet to come up with the grocery list sheet.
ha, utilizing two sheets, of course. Thanks ABG
I'll try that angle also, so simple...
