Repository: burnash/gspread

Issue:
`insert_row`/`insert_rows` methods cannot add data to end of sheet (inheritFromBefore option)
**Describe the bug**

The [inheritFromBefore field](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#insertdimensionrequest) in the Sheets API is unavailable in `gspread`. This parameter allows formatting / properties to be inherited from the previous row rather than the subsequent row (which is the default).

Because of this, the `insert_row` and `insert_rows` `Worksheet` methods don't work when adding a row at the bottom of a spreadsheet, because there is no subsequent row to inherit formatting from.

**To Reproduce**
Steps to reproduce the behavior:
1. Make a spreadsheet with N rows
2. Open the sheet as usual and call `worksheet.insert_row([1, 2, 3, 4, 5], index=N+1)`; since `gspread` indices are is 1-indexed, `N+1` corresponds to a new row at the bottom of the sheet
3. Observe the following traceback:

```
  File "/Users/yongrenjie/progs/gspread/gspread/worksheet.py", line 1355, in insert_row
    return self.insert_rows([values], index, value_input_option=value_input_option)
  File "/Users/yongrenjie/progs/gspread/gspread/worksheet.py", line 1393, in insert_rows
    self.spreadsheet.batch_update(body)
  File "/Users/yongrenjie/progs/gspread/gspread/spreadsheet.py", line 131, in batch_update
    r = self.client.request(
  File "/Users/yongrenjie/progs/gspread/gspread/client.py", line 92, in request
    raise APIError(response)
gspread.exceptions.APIError: {'code': 400, 'message': 'Invalid requests[0].insertDimension: range.startIndex must be less than the grid size (N) if inheritFromBefore is false.', 'status': 'INVALID_ARGUMENT'}
```

Partially fixed by #1079 which adds an `inherit_from_before` parameter for `insert_rows`. (I'm working on a PR based on that!)

Discussion:

Patch:
diff --git a/gspread/worksheet.py b/gspread/worksheet.py
index a7fd82e..16c877d 100644
--- a/gspread/worksheet.py
+++ b/gspread/worksheet.py
@@ -1337,7 +1337,13 @@ class Worksheet:
 
         return self.spreadsheet.values_append(range_label, params, body)
 
-    def insert_row(self, values, index=1, value_input_option=ValueInputOption.raw):
+    def insert_row(
+        self,
+        values,
+        index=1,
+        value_input_option=ValueInputOption.raw,
+        inherit_from_before=False,
+    ):
         """Adds a row to the worksheet at the specified index and populates it
         with values.
 
@@ -1349,12 +1355,33 @@ class Worksheet:
             should be interpreted. Possible values are ``ValueInputOption.raw``
             or ``ValueInputOption.user_entered``.
             See `ValueInputOption`_ in the Sheets API.
+        :param bool inherit_from_before: (optional) If True, the new row will
+            inherit its properties from the previous row. Defaults to False,
+            meaning that the new row acquires the properties of the row
+            immediately after it.
+
+            .. warning::
+
+               `inherit_from_before` must be False when adding a row to the top
+               of a spreadsheet (`index=1`), and must be True when adding to
+               the bottom of the spreadsheet.
 
         .. _ValueInputOption: https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption
         """
-        return self.insert_rows([values], index, value_input_option=value_input_option)
+        return self.insert_rows(
+            [values],
+            index,
+            value_input_option=value_input_option,
+            inherit_from_before=inherit_from_before,
+        )
 
-    def insert_rows(self, values, row=1, value_input_option=ValueInputOption.raw):
+    def insert_rows(
+        self,
+        values,
+        row=1,
+        value_input_option=ValueInputOption.raw,
+        inherit_from_before=False,
+    ):
         """Adds multiple rows to the worksheet at the specified index and
         populates them with values.
 
@@ -1366,6 +1393,16 @@ class Worksheet:
             should be interpreted. Possible values are ``ValueInputOption.raw``
             or ``ValueInputOption.user_entered``.
             See `ValueInputOption`_ in the Sheets API.
+        :param bool inherit_from_before: (optional) If true, new rows will
+            inherit their properties from the previous row. Defaults to False,
+            meaning that new rows acquire the properties of the row immediately
+            after them.
+
+            .. warning::
+
+               `inherit_from_before` must be False when adding rows to the top
+               of a spreadsheet (`row=1`), and must be True when adding to
+               the bottom of the spreadsheet.
         """
 
         # can't insert row on sheet with colon ':'
@@ -1375,6 +1412,11 @@ class Worksheet:
                 "can't insert row in worksheet with colon ':' in its name. See issue: https://issuetracker.google.com/issues/36761154"
             )
 
+        if inherit_from_before and row == 1:
+            raise GSpreadException(
+                "inherit_from_before cannot be used when inserting row(s) at the top of a spreadsheet"
+            )
+
         body = {
             "requests": [
                 {
@@ -1384,7 +1426,8 @@ class Worksheet:
                             "dimension": Dimension.rows,
                             "startIndex": row - 1,
                             "endIndex": len(values) + row - 1,
-                        }
+                        },
+                        "inheritFromBefore": inherit_from_before,
                     }
                 }
             ]
@@ -1400,7 +1443,13 @@ class Worksheet:
 
         return self.spreadsheet.values_append(range_label, params, body)
 
-    def insert_cols(self, values, col=1, value_input_option=ValueInputOption.raw):
+    def insert_cols(
+        self,
+        values,
+        col=1,
+        value_input_option=ValueInputOption.raw,
+        inherit_from_before=False,
+    ):
         """Adds multiple new cols to the worksheet at specified index and
         populates them with values.
 
@@ -1412,7 +1461,23 @@ class Worksheet:
             should be interpreted. Possible values are ``ValueInputOption.raw``
             or ``ValueInputOption.user_entered``.
             See `ValueInputOption`_ in the Sheets API.
+        :param bool inherit_from_before: (optional) If True, new columns will
+            inherit their properties from the previous column. Defaults to
+            False, meaning that new columns acquire the properties of the
+            column immediately after them.
+
+            .. warning::
+
+               `inherit_from_before` must be False if adding at the left edge
+               of a spreadsheet (`col=1`), and must be True if adding at the
+               right edge of the spreadsheet.
         """
+
+        if inherit_from_before and col == 1:
+            raise GSpreadException(
+                "inherit_from_before cannot be used when inserting column(s) at the left edge of a spreadsheet"
+            )
+
         body = {
             "requests": [
                 {
@@ -1422,7 +1487,8 @@ class Worksheet:
                             "dimension": Dimension.cols,
                             "startIndex": col - 1,
                             "endIndex": len(values) + col - 1,
-                        }
+                        },
+                        "inheritFromBefore": inherit_from_before,
                     }
                 }
             ]


Test patch:
diff --git a/tests/cassettes/WorksheetTest.test_insert_row.json b/tests/cassettes/WorksheetTest.test_insert_row.json
index 67e4d96..e692e12 100644
--- a/tests/cassettes/WorksheetTest.test_insert_row.json
+++ b/tests/cassettes/WorksheetTest.test_insert_row.json
@@ -448,7 +448,7 @@
             "request": {
                 "method": "POST",
                 "uri": "https://sheets.googleapis.com/v4/spreadsheets/1ij8FSXamZqVkz9G8W2HtTsF9DrrMuXxxLX6sHTXipXs:batchUpdate",
-                "body": "{\"requests\": [{\"insertDimension\": {\"range\": {\"sheetId\": 0, \"dimension\": \"ROWS\", \"startIndex\": 1, \"endIndex\": 2}}}]}",
+                "body": "{\"requests\": [{\"insertDimension\": {\"range\": {\"sheetId\": 0, \"dimension\": \"ROWS\", \"startIndex\": 1, \"endIndex\": 2}, \"inheritFromBefore\": false}}]}",
                 "headers": {
                     "User-Agent": [
                         "python-requests/2.27.1"
@@ -746,7 +746,7 @@
             "request": {
                 "method": "POST",
                 "uri": "https://sheets.googleapis.com/v4/spreadsheets/1ij8FSXamZqVkz9G8W2HtTsF9DrrMuXxxLX6sHTXipXs:batchUpdate",
-                "body": "{\"requests\": [{\"insertDimension\": {\"range\": {\"sheetId\": 0, \"dimension\": \"ROWS\", \"startIndex\": 0, \"endIndex\": 1}}}]}",
+                "body": "{\"requests\": [{\"insertDimension\": {\"range\": {\"sheetId\": 0, \"dimension\": \"ROWS\", \"startIndex\": 0, \"endIndex\": 1}, \"inheritFromBefore\": false}}]}",
                 "headers": {
                     "User-Agent": [
                         "python-requests/2.27.1"
diff --git a/tests/worksheet_test.py b/tests/worksheet_test.py
index ce07d4a..2aadc1f 100644
--- a/tests/worksheet_test.py
+++ b/tests/worksheet_test.py
@@ -752,6 +752,10 @@ class WorksheetTest(GspreadTest):
         b3 = self.sheet.acell("B3", value_render_option=utils.ValueRenderOption.formula)
         self.assertEqual(b3.value, formula)
 
+        new_row_values = [next(sg) for i in range(num_cols + 4)]
+        with pytest.raises(GSpreadException):
+            self.sheet.insert_row(new_row_values, 1, inherit_from_before=True)
+
     @pytest.mark.vcr()
     def test_delete_row(self):
         sg = self._sequence_generator()