Repository: burnash/gspread

Issue:
call col_values via letters identifying the column
When I need to append values in columns where there may be blank values, for example:
```
row 1
row 2
row 3


row 6
```
It is necessary to collect the column values to calculate which first row will be empty to append the new values:
```
sheet = client.open_by_key('XXX').worksheet("GSPREAD")
rows = sheet.col_values(3)
new = [['b'],['c'],['b'],['c'],['b'],['c']]
sheet.append_rows(new,table_range='C'+ str(len(rows)+1))
```
To reduce the risk of errors in updates, for example, I used to add values it to column 3 ('C'), now I'm going to add it to column 1 ('A'), sometimes accidentally forget to change one or the other for the difference display between letters and numbers.

Because `col_values` uses numbers.
`append_rows table_range` uses letters.

If there was an option to use letters in `col_values` I could create a single object (`col`) for both calls:
```
sheet = client.open_by_key('XXX').worksheet("GSPREAD")
col = 'C'
rows = sheet.col_values(col)
new = [['a'],['b'],['c'],['d'],['e'],['f']]
sheet.append_rows(new, table_range = col + str(len(rows)+1))
```

Discussion:

Patch:
diff --git a/docs/api/exceptions.rst b/docs/api/exceptions.rst
index f2c3746..daedc46 100644
--- a/docs/api/exceptions.rst
+++ b/docs/api/exceptions.rst
@@ -6,6 +6,7 @@ Exceptions
 .. autoexception:: gspread.exceptions.CellNotFound
 .. autoexception:: gspread.exceptions.GSpreadException
 .. autoexception:: gspread.exceptions.IncorrectCellLabel
+.. autoexception:: gspread.exceptions.InvalidInputValue
 .. autoexception:: gspread.exceptions.NoValidUrlKeyFound
 .. autoexception:: gspread.exceptions.SpreadsheetNotFound
 .. autoexception:: gspread.exceptions.UnSupportedExportFormat
diff --git a/gspread/exceptions.py b/gspread/exceptions.py
index b641428..2b06da5 100644
--- a/gspread/exceptions.py
+++ b/gspread/exceptions.py
@@ -35,6 +35,10 @@ class IncorrectCellLabel(GSpreadException):
     """The cell label is incorrect."""
 
 
+class InvalidInputValue(GSpreadException):
+    """The provided values is incorrect."""
+
+
 class APIError(GSpreadException):
     def __init__(self, response):
 
diff --git a/gspread/utils.py b/gspread/utils.py
index 810cfd4..c53ac3f 100644
--- a/gspread/utils.py
+++ b/gspread/utils.py
@@ -8,22 +8,17 @@ This module contains utility functions.
 
 import re
 from collections import defaultdict, namedtuple
+from collections.abc import Sequence
 from functools import wraps
-from math import inf
-
-try:
-    from collections.abc import Sequence
-except ImportError:
-    from collections import Sequence
-
 from itertools import chain
+from math import inf
 from urllib.parse import quote as uquote
 
 from google.auth.credentials import Credentials as Credentials
 from google.oauth2.credentials import Credentials as UserCredentials
 from google.oauth2.service_account import Credentials as ServiceAccountCredentials
 
-from .exceptions import IncorrectCellLabel, NoValidUrlKeyFound
+from .exceptions import IncorrectCellLabel, InvalidInputValue, NoValidUrlKeyFound
 
 MAGIC_NUMBER = 64
 CELL_ADDR_RE = re.compile(r"([A-Za-z]+)([1-9]\d*)")
@@ -414,6 +409,48 @@ def a1_range_to_grid_range(name, sheet_id=None):
     return grid_range
 
 
+def column_letter_to_index(column):
+    """Converts a column letter to its numerical index.
+
+    This is useful when using the method :meth:`gspread.worksheet.Worksheet.col_values`.
+    Which requires a column index.
+
+    This function is case-insensitive.
+
+    Raises :exc:`gspread.exceptions.InvalidInputValue` in case of invalid input.
+
+    Examples::
+
+        >>> column_letter_to_index("a")
+        1
+
+    >>> column_letter_to_index("A")
+    1
+
+    >>> column_letter_to_index("AZ")
+    52
+
+    >>> column_letter_to_index("!@#$%^&")
+    ...
+    gspread.exceptions.InvalidInputValue: invalid value: !@#$%^&, must be a column letter
+    """
+    try:
+        (_, index) = _a1_to_rowcol_unbounded(column)
+    except IncorrectCellLabel:
+        # make it coherent and raise the same exception in case of any error
+        # from user input value
+        raise InvalidInputValue(
+            "invalid value: {}, must be a column letter".format(column)
+        )
+
+    if index is inf:
+        raise InvalidInputValue(
+            "invalid value: {}, must be a column letter".format(column)
+        )
+
+    return index
+
+
 def cast_to_a1_notation(method):
     """Decorator function casts wrapped arguments to A1 notation in range
     method calls.


Test patch:
diff --git a/tests/utils_test.py b/tests/utils_test.py
index eaf5a8d..90cf3c2 100644
--- a/tests/utils_test.py
+++ b/tests/utils_test.py
@@ -169,3 +169,35 @@ class UtilsTest(unittest.TestCase):
         self.assertEqual(from_top_left, from_bottom_right)
         self.assertEqual(from_top_left, from_bottom_left)
         self.assertEqual(from_top_left, from_top_right)
+
+    def test_column_letter_to_index(self):
+        # All the input values to test one after an other
+        # [0] input value
+        # [1] expected return value
+        # [2] expected exception to raise
+        inputs = [
+            ("", None, gspread.exceptions.InvalidInputValue),
+            ("A", 1, None),
+            ("Z", 26, None),
+            ("AA", 27, None),
+            ("AAA", 703, None),
+            ("ABCDEFGHIJKLMNOPQRSTUVWXYZ", 256094574536617744129141650397448476, None),
+            ("!@#$%^&*()", None, gspread.exceptions.InvalidInputValue),
+        ]
+
+        for label, expected, exception in inputs:
+            if exception is not None:
+
+                # assert the exception is raised
+                with self.assertRaises(exception):
+                    utils.column_letter_to_index(label)
+            else:
+                # assert the return values is correct
+                result = utils.column_letter_to_index(label)
+                self.assertEqual(
+                    result,
+                    expected,
+                    "could not convert column letter '{}' to the right value '{}".format(
+                        label, expected
+                    ),
+                )