Repository: robin900/gspread-dataframe

Issue:
get dataframe does not work with non-programatically created sheets
if get dataframe does not provide an option to remove empty rows and columns(which needless to say are added by default when you create a spreadsheet in google drive) at the end, it is easier to just do

```
rows = worksheet.get_all_values()
df = pd.DataFrame(rows[1:], columns=rows[0])
```

I kinda want to try and see what this library is about but its such a bad taste in the mouth that the first thing you'd want to do when working on a real spreadsheet you have lying around is to skip what it provides.


Discussion:

Patch:
diff --git a/README.rst b/README.rst
index f4e3412..3eab6b0 100644
--- a/README.rst
+++ b/README.rst
@@ -45,6 +45,12 @@ only options supported by that engine are acceptable:
 
     df = get_as_dataframe(worksheet, parse_dates=True, usecols=[0,2], skiprows=1, header=None)
 
+New in version 4.0.0: `drop_empty_rows` and `drop_empty_columns` parameters, both `True`
+by default, are now accepted by `get_as_dataframe`. If you created a Google sheet with the default
+number of columns and rows (20 columns, 1000 rows), but have meaningful values for the DataFrame
+only in the top left corner of the worksheet, these parameters will cause any empty rows
+or columns to be discarded automatically.
+
 Formatting Google worksheets for DataFrames
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
diff --git a/gspread_dataframe.py b/gspread_dataframe.py
index 32ded14..7505f18 100644
--- a/gspread_dataframe.py
+++ b/gspread_dataframe.py
@@ -12,6 +12,7 @@ Pandas 0.14.0 or greater installed.
 from gspread.utils import fill_gaps
 from gspread import Cell
 import pandas as pd
+import numpy as np
 from pandas.io.parsers import TextParser
 import logging
 import re
@@ -33,6 +34,8 @@ __all__ = ("set_with_dataframe", "get_as_dataframe")
 
 WORKSHEET_MAX_CELL_COUNT = 10000000
 
+UNNAMED_COLUMN_NAME_PATTERN = re.compile(r'^Unnamed:\s\d+(?:_level_\d+)?$')
+
 def _escaped_string(value, string_escaping):
     if value in (None, ""):
         return ""
@@ -176,7 +179,7 @@ def _get_all_values(worksheet, evaluate_formulas):
     return [[rows[i][j] for j in rect_cols] for i in rect_rows]
 
 
-def get_as_dataframe(worksheet, evaluate_formulas=False, **options):
+def get_as_dataframe(worksheet, evaluate_formulas=False, drop_empty_rows=True, drop_empty_columns=True, **options):
     r"""
     Returns the worksheet contents as a DataFrame.
 
@@ -184,6 +187,12 @@ def get_as_dataframe(worksheet, evaluate_formulas=False, **options):
     :param evaluate_formulas: if True, get the value of a cell after
             formula evaluation; otherwise get the formula itself if present.
             Defaults to False.
+    :param drop_empty_rows: if True, drop any rows from the DataFrame that have
+            only empty (NaN) values. Defaults to True.
+    :param drop_empty_columns: if True, drop any columns from the DataFrame
+            that have only empty (NaN) values and have no column name 
+            (that is, no header value). Named columns (those with a header value)
+            that are otherwise empty are retained. Defaults to True.
     :param \*\*options: all the options for pandas.io.parsers.TextParser,
             according to the version of pandas that is installed.
             (Note: TextParser supports only the default 'python' parser engine,
@@ -191,8 +200,62 @@ def get_as_dataframe(worksheet, evaluate_formulas=False, **options):
     :returns: pandas.DataFrame
     """
     all_values = _get_all_values(worksheet, evaluate_formulas)
-    return TextParser(all_values, **options).read(options.get("nrows", None))
+    df = TextParser(all_values, **options).read(options.get("nrows", None))
+
+    # if squeeze=True option was used, df may be a Series.
+    # There is special Series logic for our two drop options.
+    if isinstance(df, pd.Series):
+        if drop_empty_rows:
+            df = df.dropna()
+        # if this Series is empty and unnamed, it's droppable,
+        # and we should return an empty DataFrame instead.
+        if drop_empty_columns and df.empty and (not df.name or UNNAMED_COLUMN_NAME_PATTERN.search(df.name)):
+           df = pd.DataFrame() 
+
+    # Else df is a DataFrame.
+    else:
+        if drop_empty_rows:
+            df = df.dropna(how='all', axis=0)
+            _reconstruct_if_multi_index(df, 'index')
+        if drop_empty_columns:
+            labels_to_drop = _find_labels_of_empty_unnamed_columns(df)
+            if labels_to_drop:
+                df = df.drop(labels=labels_to_drop, axis=1)
+                _reconstruct_if_multi_index(df, 'columns')
+
+    return df
+
+def _reconstruct_if_multi_index(df, attrname):
+    # pandas, even as of 2.2.2, has a bug where a MultiIndex
+    # will simply preserve the dropped labels in each level
+    # when asked by .levels and .levshape, although the dropped
+    # labels won't appear in to_numpy(). We must therefore reconstruct
+    # the MultiIndex via to_numpy() -> .from_tuples, and then
+    # assign it to the dataframe's appropriate attribute.
+    index = getattr(df, attrname)
+    if not isinstance(index, pd.MultiIndex):
+        return
+    reconstructed = pd.MultiIndex.from_tuples(index.to_numpy())
+    setattr(df, attrname, reconstructed)
+
+
+def _label_represents_unnamed_column(label):
+    if isinstance(label, str) and UNNAMED_COLUMN_NAME_PATTERN.search(label):
+        return True
+    # unnamed columns will have an int64 label if header=False was used.
+    elif isinstance(label, np.int64):
+        return True
+    elif isinstance(label, tuple):
+        return all([_label_represents_unnamed_column(item) for item in label])
+    else:
+        return False
 
+def _find_labels_of_empty_unnamed_columns(df):
+    return [ 
+        label for label 
+        in df.columns.to_numpy() 
+        if _label_represents_unnamed_column(label) and df[label].isna().all() 
+    ]
 
 def _determine_level_count(index):
     if hasattr(index, "levshape"):


Test patch:
diff --git a/tests/cell_list.json b/tests/cell_list.json
index 82bdfe1..803bac6 100644
--- a/tests/cell_list.json
+++ b/tests/cell_list.json
@@ -1,12 +1,13 @@
 [
-  ["Thingy", "Syntax", "Numeric Column", "Formula Column", "Date Column", "Values are...", "Selection", "Label(s) referencible in chart title", "Dialect-specific implementations", "Notes"], 
-  ["filter", "[expr=foo]", "[1, 2, 3]", "=R[0]C[-1]*2", "2017-03-04", "literals", "multiple", "no", "3e50", ""], 
-  ["'+", "[expr=daterange]", 2.01, "=R[0]C[-1]*2", "2017-03-05", "sometimes-parameterized SQL expressions brincolín", "single, REQUIRED", "yes as [daterange]", "yes probably", "static SQL but uses account's timezone setting to determine what SQL intervals to emit; Custom Date Range selection exposes two date pickers and provides the values. This is the only case I can see where more than one parameter is needed. Also, start date or end date can be empty, which should remove its clauses entirely. If not selected or reset, defaults to \"All Dates\" which appears as \"all time\" in chart titles. (UI bug fails to show All Dates as selected when filters are reset.)"], 
-  ["aggregation", "[expr:aggregation]", 2.907, "=R[0]C[-1]*2", "2017-03-06", "parameterized SQL expressions", "single, REQUIRED", "yes as [aggregation]", "yes probably", "one could argue this is a map of { label :  formatter } of which you can pick 1..1 entry to apply. If not selected or reset, defaults to Daily (though UI bug does not mark Daily in aggregation area)."], 
-  ["snippet", "[foo]", 3.804, "=R[0]C[-1]*2", "2017-03-07", "static SQL expressions", "n/a", "n/a", "no", ""], 
-  ["formatter", "[expr:foo]", 4.701, "=R[0]C[-1]*2", "2017-03-08", "parameterized SQL expressions", "n/a", "n/a", "yes", ""], 
-  ["automatic join", "[foo+bar]", 5.598, "=R[0]C[-1]*2", "2017-03-09", "SQL expression with two parameters!", "n/a", "n/a", "???", "uses key name conventions as rules to determine join columns"], 
-  ["Proposed Thingy", "Syntax", 6.495, "=R[0]C[-1]*2", "2017-03-10", "Values are...", "Selection", "Label(s) referencible in chart title", "Dialect-specific implementations", "Notes"], 
-  ["parameterized snippet", "[expr::foo]", 7.392, "=R[0]C[-1]*2", "2017-03-11", "parameterized SQL expressions", "n/a", "n/a", "", "Syntax not decided yet; unique among macro types in that it can evaluate all other macro types when rendering"], 
-  ["filter as SQL expression", "[expr=foo]", 8.289, "=R[0]C[-1]*2", "2017-03-12", "static SQL expression", "multiple", "no", "no", "map of { label : expression } of which you can pick 0..N-1. their expressions just get ORed together."]
+  ["Thingy", "Syntax", "Numeric Column", "Formula Column", "Date Column", "Values are...", "Selection", "Label(s) referencible in chart title", "Dialect-specific implementations", "Notes", "Unnamed: 10"], 
+  ["filter", "[expr=foo]", "[1, 2, 3]", "=R[0]C[-1]*2", "2017-03-04", "literals", "multiple", "no", "3e50", "", ""], 
+  ["'+", "[expr=daterange]", 2.01, "=R[0]C[-1]*2", "2017-03-05", "sometimes-parameterized SQL expressions brincolín", "single, REQUIRED", "yes as [daterange]", "yes probably", "static SQL but uses account's timezone setting to determine what SQL intervals to emit; Custom Date Range selection exposes two date pickers and provides the values. This is the only case I can see where more than one parameter is needed. Also, start date or end date can be empty, which should remove its clauses entirely. If not selected or reset, defaults to \"All Dates\" which appears as \"all time\" in chart titles. (UI bug fails to show All Dates as selected when filters are reset.)", ""], 
+  ["aggregation", "[expr:aggregation]", 2.907, "=R[0]C[-1]*2", "2017-03-06", "parameterized SQL expressions", "single, REQUIRED", "yes as [aggregation]", "yes probably", "one could argue this is a map of { label :  formatter } of which you can pick 1..1 entry to apply. If not selected or reset, defaults to Daily (though UI bug does not mark Daily in aggregation area).", ""], 
+  ["snippet", "[foo]", 3.804, "=R[0]C[-1]*2", "2017-03-07", "static SQL expressions", "n/a", "n/a", "no", "", ""], 
+  ["formatter", "[expr:foo]", 4.701, "=R[0]C[-1]*2", "2017-03-08", "parameterized SQL expressions", "n/a", "n/a", "yes", "", ""], 
+  ["automatic join", "[foo+bar]", 5.598, "=R[0]C[-1]*2", "2017-03-09", "SQL expression with two parameters!", "n/a", "n/a", "???", "uses key name conventions as rules to determine join columns", ""], 
+  ["Proposed Thingy", "Syntax", 6.495, "=R[0]C[-1]*2", "2017-03-10", "Values are...", "Selection", "Label(s) referencible in chart title", "Dialect-specific implementations", "Notes", ""], 
+  ["parameterized snippet", "[expr::foo]", 7.392, "=R[0]C[-1]*2", "2017-03-11", "parameterized SQL expressions", "n/a", "n/a", "", "Syntax not decided yet; unique among macro types in that it can evaluate all other macro types when rendering", ""], 
+  ["filter as SQL expression", "[expr=foo]", 8.289, "=R[0]C[-1]*2", "2017-03-12", "static SQL expression", "multiple", "no", "no", "map of { label : expression } of which you can pick 0..N-1. their expressions just get ORed together.", ""],
+  ["", "", "", "", "", "", "", "", "", "", ""]
 ]
diff --git a/tests/gspread_dataframe_integration.py b/tests/gspread_dataframe_integration.py
index fc07c65..7685626 100644
--- a/tests/gspread_dataframe_integration.py
+++ b/tests/gspread_dataframe_integration.py
@@ -128,7 +128,7 @@ class WorksheetTest(GspreadDataframeTest):
         self.streamHandler = logger.addHandler(logging.StreamHandler(sys.stdout))
         if self.__class__.spreadsheet is None:
             self.__class__.setUpClass()
-        self.sheet = self.spreadsheet.add_worksheet(TEST_WORKSHEET_NAME, 20, 20)
+        self.sheet = self.spreadsheet.add_worksheet(TEST_WORKSHEET_NAME, 200, 20)
         self.__class__.spreadsheet.batch_update(
             {
                 "requests": [
@@ -151,8 +151,9 @@ class WorksheetTest(GspreadDataframeTest):
         rows = None
         with open(CELL_LIST_FILENAME) as f:
             rows = json.load(f)
+            # drop empty column, drop empty row
+            rows = [ r[:-1] for r in rows ][:-1]
 
-        self.sheet.resize(10, 10)
         cell_list = self.sheet.range("A1:J10")
         for cell, value in zip(cell_list, itertools.chain(*rows)):
             cell.value = value
@@ -183,8 +184,9 @@ class WorksheetTest(GspreadDataframeTest):
         rows = None
         with open(CELL_LIST_FILENAME) as f:
             rows = json.load(f)
+            # drop empty column and empty row
+            rows = [ r[:-1] for r in rows ][:-1]
 
-        self.sheet.resize(10, 10)
         cell_list = self.sheet.range("A1:J10")
         for cell, value in zip(cell_list, itertools.chain(*rows)):
             cell.value = value
@@ -204,8 +206,9 @@ class WorksheetTest(GspreadDataframeTest):
         rows = None
         with open(CELL_LIST_FILENAME) as f:
             rows = json.load(f)
+            # drop empty column and empty row
+            rows = [ r[:-1] for r in rows ][:-1]
 
-        self.sheet.resize(10, 10)
         cell_list = self.sheet.range("A1:J10")
         for cell, value in zip(cell_list, itertools.chain(*rows)):
             cell.value = value
@@ -215,7 +218,6 @@ class WorksheetTest(GspreadDataframeTest):
             df = get_as_dataframe(self.sheet, nrows=nrows)
             self.assertEqual(nrows, len(df))
 
-
     def test_resize_to_minimum_large(self):
         self.sheet.resize(100, 26)
         self.sheet = self.sheet.spreadsheet.worksheet(self.sheet.title)
@@ -276,6 +278,8 @@ class WorksheetTest(GspreadDataframeTest):
         rows = None
         with open(CELL_LIST_FILENAME) as f:
             rows = json.load(f)
+            # drop empty column and empty row
+            rows = [ r[:-1] for r in rows ][:-1]
         mi = list(
             pd.MultiIndex.from_product(
                 [["A", "B"], ["one", "two", "three", "four", "five"]]
@@ -289,7 +293,6 @@ class WorksheetTest(GspreadDataframeTest):
         for cell, value in zip(cell_list, itertools.chain(*rows)):
             cell.value = value
         self.sheet.update_cells(cell_list)
-        self.sheet.resize(10, 12)
         self.sheet = self.sheet.spreadsheet.worksheet(self.sheet.title)
         df = get_as_dataframe(self.sheet, index_col=[0, 1])
         set_with_dataframe(
@@ -309,6 +312,8 @@ class WorksheetTest(GspreadDataframeTest):
         rows = None
         with open(CELL_LIST_FILENAME) as f:
             rows = json.load(f)
+            # drop empty column, drop empty row
+            rows = [ r[:-1] for r in rows ][:-1]
         column_headers = [
             "SQL",
             "SQL",
@@ -326,14 +331,12 @@ class WorksheetTest(GspreadDataframeTest):
         for cell, value in zip(cell_list, itertools.chain(*rows)):
             cell.value = value
         self.sheet.update_cells(cell_list)
-        self.sheet.resize(11, 10)
         self.sheet = self.sheet.spreadsheet.worksheet(self.sheet.title)
         df = get_as_dataframe(self.sheet, header=[0, 1])
         self.assertEqual((2, 10), getattr(df.columns, "levshape", None)),
         set_with_dataframe(
             self.sheet,
             df,
-            resize=True,
             string_escaping=STRING_ESCAPING_PATTERN,
         )
         df2 = get_as_dataframe(self.sheet, header=[0, 1])
@@ -360,10 +363,6 @@ class WorksheetTest(GspreadDataframeTest):
     def test_header_writing_and_parsing(self):
         truth_table = itertools.product(*([[False, True]] * 4))
         for include_index, columns_multilevel, index_has_names, columns_has_names in truth_table:
-            logger.info(
-                "Testing include_index %s, index_has_names %s, columns_multilevel %s, columns_has_names %s",
-                include_index, index_has_names, columns_multilevel, columns_has_names
-            )
             data = [[uniform(0, 100000) for i in range(8)] for j in range(20)]
             index_names = ["Category", "Subcategory"] if index_has_names else None
             index = list(
@@ -388,14 +387,17 @@ class WorksheetTest(GspreadDataframeTest):
             # if include_index and columns_multilevel and index_has_names, there
             # will be an additional header row
             index_col_arg = list(range(len(getattr(index, "levshape", [1]))))
-            logger.info("header=%s, index_col=%s", header_arg, index_col_arg)
             df_readback = get_as_dataframe(
                 self.sheet, 
                 header=header_arg,
                 index_col=(index_col_arg if include_index else None)
             )
-            logger.info("DataFrames equal: %s", df.equals(df_readback))
             if not df.equals(df_readback):
+                logger.info(
+                    "Testing include_index %s, index_has_names %s, columns_multilevel %s, columns_has_names %s",
+                    include_index, index_has_names, columns_multilevel, columns_has_names
+                )
+                logger.info("header=%s, index_col=%s", header_arg, index_col_arg)
                 logger.info("%s", df)
                 logger.info("%s", df.dtypes)
                 logger.info("%s", df_readback)
diff --git a/tests/gspread_dataframe_test.py b/tests/gspread_dataframe_test.py
index 13f5897..dc6ad17 100644
--- a/tests/gspread_dataframe_test.py
+++ b/tests/gspread_dataframe_test.py
@@ -110,40 +110,54 @@ class TestWorksheetReads(unittest.TestCase):
 
     def test_noargs(self):
         df = get_as_dataframe(self.sheet)
-        self.assertEqual(list(df.columns.values), COLUMN_NAMES)
+        self.assertEqual(list(df.columns.array), COLUMN_NAMES)
         self.assertEqual(len(df.columns), 10)
         self.assertEqual(len(df), 9)
         self.assertEqual(df.index.name, None)
-        self.assertEqual(type(df.index).__name__, "RangeIndex")
-        self.assertEqual(list(df.index.values), list(range(9)))
+        self.assertEqual(list(df.index.array), list(range(9)))
+
+    def test_drop_empty_columns_false(self):
+        df = get_as_dataframe(self.sheet, drop_empty_columns=False)
+        self.assertEqual(list(df.columns.array), COLUMN_NAMES + ["Unnamed: 10"])
+        self.assertEqual(len(df.columns), 11)
+        self.assertEqual(len(df), 9)
+        self.assertEqual(df.index.name, None)
+        self.assertEqual(list(df.index.array), list(range(9)))
+
+    def test_drop_empty_rows_false(self):
+        df = get_as_dataframe(self.sheet, drop_empty_rows=False)
+        self.assertEqual(list(df.columns.array), COLUMN_NAMES)
+        self.assertEqual(len(df.columns), 10)
+        self.assertEqual(len(df), 10)
+        self.assertEqual(df.index.name, None)
+        self.assertEqual(list(df.index.array), list(range(10)))
 
     def test_evaluate_formulas_true(self):
         df = get_as_dataframe(self.sheet, evaluate_formulas=True)
-        self.assertEqual(list(df.columns.values), COLUMN_NAMES)
+        self.assertEqual(list(df.columns.array), COLUMN_NAMES)
         self.assertEqual(df["Formula Column"][0], 2.226)
 
     def test_evaluate_formulas_false(self):
         df = get_as_dataframe(self.sheet)
-        self.assertEqual(list(df.columns.values), COLUMN_NAMES)
+        self.assertEqual(list(df.columns.array), COLUMN_NAMES)
         self.assertEqual(df["Formula Column"][0], "=R[0]C[-1]*2")
 
     def test_usecols(self):
         df = get_as_dataframe(self.sheet, usecols=USECOLS_COLUMN_NAMES)
-        self.assertEqual(list(df.columns.values), USECOLS_COLUMN_NAMES)
+        self.assertEqual(list(df.columns.array), USECOLS_COLUMN_NAMES)
 
     def test_indexcol(self):
         df = get_as_dataframe(self.sheet, index_col=4)
         self.assertEqual(len(df.columns), 9)
         self.assertEqual(df.index.name, "Date Column")
         self.assertEqual(type(df.index).__name__, "Index")
-        self.assertEqual(df.index.values[0], "2017-03-04")
+        self.assertEqual(df.index.array[0], "2017-03-04")
 
     def test_indexcol_none(self):
         df = get_as_dataframe(self.sheet, index_col=False)
         self.assertEqual(len(df.columns), 10)
         self.assertEqual(df.index.name, None)
-        self.assertEqual(type(df.index).__name__, "RangeIndex")
-        self.assertEqual(list(df.index.values), list(range(9)))
+        self.assertEqual(list(df.index.array), list(range(9)))
 
     def test_header_false(self):
         df = get_as_dataframe(self.sheet, header=None)
@@ -163,7 +177,7 @@ class TestWorksheetReads(unittest.TestCase):
         )
         self.assertEqual(len(df), 9)
         self.assertEqual(
-            df.columns.tolist(), ["COL" + str(i) for i in range(10)]
+            df.columns.tolist(), ["COL" + str(i) for i in range(11)]
         )
 
     def test_squeeze(self):
@@ -213,7 +227,7 @@ class TestWorksheetReads(unittest.TestCase):
         df = get_as_dataframe(
             self.sheet,
             parse_dates=[4],
-            date_parser=lambda x: datetime.strptime(x, "%Y-%m-%d"),
+            date_parser=lambda x: datetime.strptime(x, "%Y-%m-%d") if x is not np.nan else None
         )
         self.assertEqual(df["Date Column"][0], datetime(2017, 3, 4))
 
@@ -279,7 +293,7 @@ class TestWorksheetWrites(unittest.TestCase):
             resize=True,
             string_escaping=re.compile(r"3e50").match,
         )
-        self.sheet.resize.assert_called_once_with(10, 10)
+        self.sheet.resize.assert_called_once_with(11, 11)
         self.sheet.update_cells.assert_called_once_with(
             CELL_LIST_STRINGIFIED, value_input_option="USER_ENTERED"
         )
@@ -294,7 +308,7 @@ class TestWorksheetWrites(unittest.TestCase):
             include_index=False,
             string_escaping=lambda x: x == "3e50",
         )
-        self.sheet.resize.assert_called_once_with(10, 9)
+        self.sheet.resize.assert_called_once_with(11, 10)
         self.sheet.update_cells.assert_called_once_with(
             CELL_LIST_STRINGIFIED_NO_THINGY, value_input_option="USER_ENTERED"
         )
@@ -309,7 +323,7 @@ class TestWorksheetWrites(unittest.TestCase):
             include_index=True,
             string_escaping=re.compile(r"3e50").match,
         )
-        self.sheet.resize.assert_called_once_with(10, 10)
+        self.sheet.resize.assert_called_once_with(11, 11)
         self.sheet.update_cells.assert_called_once_with(
             CELL_LIST_STRINGIFIED, value_input_option="USER_ENTERED"
         )
@@ -323,7 +337,7 @@ class TestWorksheetWrites(unittest.TestCase):
             resize=True,
             string_escaping=re.compile(r"3e50").match,
         )
-        self.sheet.resize.assert_called_once_with(10, 10)
+        self.sheet.resize.assert_called_once_with(11, 11)
         self.sheet.update_cells.assert_called_once_with(
             CELL_LIST_STRINGIFIED, value_input_option="USER_ENTERED"
         )
diff --git a/tests/mock_worksheet.py b/tests/mock_worksheet.py
index f8bb508..79898d6 100644
--- a/tests/mock_worksheet.py
+++ b/tests/mock_worksheet.py
@@ -54,7 +54,7 @@ CELL_LIST_STRINGIFIED_NO_THINGY = [
 class MockWorksheet(object):
     def __init__(self):
         self.row_count = 10
-        self.col_count = 10
+        self.col_count = 11
         self.id = "fooby"
         self.title = "gspread dataframe test"
         self.spreadsheet = MockSpreadsheet()
diff --git a/tests/sheet_contents_evaluated.json b/tests/sheet_contents_evaluated.json
index 5c29bd6..8a6b4d4 100644
--- a/tests/sheet_contents_evaluated.json
+++ b/tests/sheet_contents_evaluated.json
@@ -1,1 +1,1 @@
-{"range": "Sheet1!A1:J10", "majorDimension": "ROWS", "values": [["Thingy", "Syntax", "Numeric Column", "Formula Column", "Date Column", "Values are...", "Selection", "Label(s) referencible in chart title", "Dialect-specific implementations", "Notes"], ["filter", "[expr=foo]", "[1, 2, 3]", 2.226, "2017-03-04", "literals", "multiple", "no", "3e50"], ["'+", "[expr=daterange]", 2.01, 2.226, "2017-03-05", "sometimes-parameterized SQL expressions brincolín", "single, REQUIRED", "yes as [daterange]", "yes probably", "static SQL but uses account's timezone setting to determine what SQL intervals to emit; Custom Date Range selection exposes two date pickers and provides the values. This is the only case I can see where more than one parameter is needed. Also, start date or end date can be empty, which should remove its clauses entirely. If not selected or reset, defaults to \"All Dates\" which appears as \"all time\" in chart titles. (UI bug fails to show All Dates as selected when filters are reset.)"], ["aggregation", "[expr:aggregation]", 2.907, 2.226, "2017-03-06", "parameterized SQL expressions", "single, REQUIRED", "yes as [aggregation]", "yes probably", "one could argue this is a map of { label :  formatter } of which you can pick 1..1 entry to apply. If not selected or reset, defaults to Daily (though UI bug does not mark Daily in aggregation area)."], ["snippet", "[foo]", 3.804, 2.226, "2017-03-07", "static SQL expressions", "n/a", "n/a", "no"], ["formatter", "[expr:foo]", 4.701, 2.226, "2017-03-08", "parameterized SQL expressions", "n/a", "n/a", "yes"], ["automatic join", "[foo+bar]", 5.598, 2.226, "2017-03-09", "SQL expression with two parameters!", "n/a", "n/a", "???", "uses key name conventions as rules to determine join columns"], ["Proposed Thingy", "Syntax", 6.495, 2.226, "2017-03-10", "Values are...", "Selection", "Label(s) referencible in chart title", "Dialect-specific implementations", "Notes"], ["parameterized snippet", "[expr::foo]", 7.392, 2.226, "2017-03-11", "parameterized SQL expressions", "n/a", "n/a", "", "Syntax not decided yet; unique among macro types in that it can evaluate all other macro types when rendering"], ["filter as SQL expression", "[expr=foo]", 8.289, 2.226, "2017-03-12", "static SQL expression", "multiple", "no", "no", "map of { label : expression } of which you can pick 0..N-1. their expressions just get ORed together."]]}
+{"range": "Sheet1!A1:K10", "majorDimension": "ROWS", "values": [["Thingy", "Syntax", "Numeric Column", "Formula Column", "Date Column", "Values are...", "Selection", "Label(s) referencible in chart title", "Dialect-specific implementations", "Notes", ""], ["filter", "[expr=foo]", "[1, 2, 3]", 2.226, "2017-03-04", "literals", "multiple", "no", "3e50", ""], ["'+", "[expr=daterange]", 2.01, 2.226, "2017-03-05", "sometimes-parameterized SQL expressions brincolín", "single, REQUIRED", "yes as [daterange]", "yes probably", "static SQL but uses account's timezone setting to determine what SQL intervals to emit; Custom Date Range selection exposes two date pickers and provides the values. This is the only case I can see where more than one parameter is needed. Also, start date or end date can be empty, which should remove its clauses entirely. If not selected or reset, defaults to \"All Dates\" which appears as \"all time\" in chart titles. (UI bug fails to show All Dates as selected when filters are reset.)", ""], ["aggregation", "[expr:aggregation]", 2.907, 2.226, "2017-03-06", "parameterized SQL expressions", "single, REQUIRED", "yes as [aggregation]", "yes probably", "one could argue this is a map of { label :  formatter } of which you can pick 1..1 entry to apply. If not selected or reset, defaults to Daily (though UI bug does not mark Daily in aggregation area).", ""], ["snippet", "[foo]", 3.804, 2.226, "2017-03-07", "static SQL expressions", "n/a", "n/a", "no", ""], ["formatter", "[expr:foo]", 4.701, 2.226, "2017-03-08", "parameterized SQL expressions", "n/a", "n/a", "yes", ""], ["automatic join", "[foo+bar]", 5.598, 2.226, "2017-03-09", "SQL expression with two parameters!", "n/a", "n/a", "???", "uses key name conventions as rules to determine join columns", ""], ["Proposed Thingy", "Syntax", 6.495, 2.226, "2017-03-10", "Values are...", "Selection", "Label(s) referencible in chart title", "Dialect-specific implementations", "Notes", ""], ["parameterized snippet", "[expr::foo]", 7.392, 2.226, "2017-03-11", "parameterized SQL expressions", "n/a", "n/a", "", "Syntax not decided yet; unique among macro types in that it can evaluate all other macro types when rendering", ""], ["filter as SQL expression", "[expr=foo]", 8.289, 2.226, "2017-03-12", "static SQL expression", "multiple", "no", "no", "map of { label : expression } of which you can pick 0..N-1. their expressions just get ORed together.", ""], ["", "", "", "", "", "", "", "", "", "", ""]]}
diff --git a/tests/sheet_contents_formulas.json b/tests/sheet_contents_formulas.json
index dd4a04a..0355e99 100644
--- a/tests/sheet_contents_formulas.json
+++ b/tests/sheet_contents_formulas.json
@@ -1,1 +1,1 @@
-{"range": "Sheet1!A1:J10", "majorDimension": "ROWS", "values": [["Thingy", "Syntax", "Numeric Column", "Formula Column", "Date Column", "Values are...", "Selection", "Label(s) referencible in chart title", "Dialect-specific implementations", "Notes"], ["filter", "[expr=foo]", "[1, 2, 3]", "=R[0]C[-1]*2", "2017-03-04", "literals", "multiple", "no", "3e50"], ["'+", "[expr=daterange]", 2.01, "=R[0]C[-1]*2", "2017-03-05", "sometimes-parameterized SQL expressions brincolín", "single, REQUIRED", "yes as [daterange]", "yes probably", "static SQL but uses account's timezone setting to determine what SQL intervals to emit; Custom Date Range selection exposes two date pickers and provides the values. This is the only case I can see where more than one parameter is needed. Also, start date or end date can be empty, which should remove its clauses entirely. If not selected or reset, defaults to \"All Dates\" which appears as \"all time\" in chart titles. (UI bug fails to show All Dates as selected when filters are reset.)"], ["aggregation", "[expr:aggregation]", 2.907, "=R[0]C[-1]*2", "2017-03-06", "parameterized SQL expressions", "single, REQUIRED", "yes as [aggregation]", "yes probably", "one could argue this is a map of { label :  formatter } of which you can pick 1..1 entry to apply. If not selected or reset, defaults to Daily (though UI bug does not mark Daily in aggregation area)."], ["snippet", "[foo]", 3.804, "=R[0]C[-1]*2", "2017-03-07", "static SQL expressions", "n/a", "n/a", "no"], ["formatter", "[expr:foo]", 4.701, "=R[0]C[-1]*2", "2017-03-08", "parameterized SQL expressions", "n/a", "n/a", "yes"], ["automatic join", "[foo+bar]", 5.598, "=R[0]C[-1]*2", "2017-03-09", "SQL expression with two parameters!", "n/a", "n/a", "???", "uses key name conventions as rules to determine join columns"], ["Proposed Thingy", "Syntax", 6.495, "=R[0]C[-1]*2", "2017-03-10", "Values are...", "Selection", "Label(s) referencible in chart title", "Dialect-specific implementations", "Notes"], ["parameterized snippet", "[expr::foo]", 7.392, "=R[0]C[-1]*2", "2017-03-11", "parameterized SQL expressions", "n/a", "n/a", "", "Syntax not decided yet; unique among macro types in that it can evaluate all other macro types when rendering"], ["filter as SQL expression", "[expr=foo]", 8.289, "=R[0]C[-1]*2", "2017-03-12", "static SQL expression", "multiple", "no", "no", "map of { label : expression } of which you can pick 0..N-1. their expressions just get ORed together."]]}
+{"range": "Sheet1!A1:K10", "majorDimension": "ROWS", "values": [["Thingy", "Syntax", "Numeric Column", "Formula Column", "Date Column", "Values are...", "Selection", "Label(s) referencible in chart title", "Dialect-specific implementations", "Notes", ""], ["filter", "[expr=foo]", "[1, 2, 3]", "=R[0]C[-1]*2", "2017-03-04", "literals", "multiple", "no", "3e50", ""], ["'+", "[expr=daterange]", 2.01, "=R[0]C[-1]*2", "2017-03-05", "sometimes-parameterized SQL expressions brincolín", "single, REQUIRED", "yes as [daterange]", "yes probably", "static SQL but uses account's timezone setting to determine what SQL intervals to emit; Custom Date Range selection exposes two date pickers and provides the values. This is the only case I can see where more than one parameter is needed. Also, start date or end date can be empty, which should remove its clauses entirely. If not selected or reset, defaults to \"All Dates\" which appears as \"all time\" in chart titles. (UI bug fails to show All Dates as selected when filters are reset.)", ""], ["aggregation", "[expr:aggregation]", 2.907, "=R[0]C[-1]*2", "2017-03-06", "parameterized SQL expressions", "single, REQUIRED", "yes as [aggregation]", "yes probably", "one could argue this is a map of { label :  formatter } of which you can pick 1..1 entry to apply. If not selected or reset, defaults to Daily (though UI bug does not mark Daily in aggregation area).", ""], ["snippet", "[foo]", 3.804, "=R[0]C[-1]*2", "2017-03-07", "static SQL expressions", "n/a", "n/a", "no", ""], ["formatter", "[expr:foo]", 4.701, "=R[0]C[-1]*2", "2017-03-08", "parameterized SQL expressions", "n/a", "n/a", "yes", ""], ["automatic join", "[foo+bar]", 5.598, "=R[0]C[-1]*2", "2017-03-09", "SQL expression with two parameters!", "n/a", "n/a", "???", "uses key name conventions as rules to determine join columns", ""], ["Proposed Thingy", "Syntax", 6.495, "=R[0]C[-1]*2", "2017-03-10", "Values are...", "Selection", "Label(s) referencible in chart title", "Dialect-specific implementations", "Notes", ""], ["parameterized snippet", "[expr::foo]", 7.392, "=R[0]C[-1]*2", "2017-03-11", "parameterized SQL expressions", "n/a", "n/a", "", "Syntax not decided yet; unique among macro types in that it can evaluate all other macro types when rendering", ""], ["filter as SQL expression", "[expr=foo]", 8.289, "=R[0]C[-1]*2", "2017-03-12", "static SQL expression", "multiple", "no", "no", "map of { label : expression } of which you can pick 0..N-1. their expressions just get ORed together.", ""], ["", "", "", "", "", "", "", "", "", "", ""]]}