Feature Request: set_record + set_records #677
Comments
thp44
changed the title
|
This would be simple to implement, but it would be very slow. We can add a mapping of the columns when the Most of the work would be validation and testing. |
|
The way I could see this feature working is similar to Python's CSV DictWriter class def append_records(
headers: List[str],
records: List[Dict[str, str]],
default_blank: Any = None,
ignore_extra_dict_items: bool = False,
# extra kwargs?
)
# use a provided "headers" array, probably obtained with worksheet.row_values(1)
# new_rows = []
# for each object in records
# for each header in headers
# if header not in headers, raise error! (unless kwarg)
# if header not in object, raise error! (unless default blank)
# new_rows[i].append(object[header])
# use worksheet.append_rows to add new_rows to worksheetand Questions I would have:
slow? not sure what you mean
also not sure what you mean if you desire to implement it, I think this could be a nice feature @muddi900 :) |
|
It would be slow in the sense that the order may not be correct each time. So we have to make sure the order is correct. Or use memoization: create an list of None the size of the record, loop through the whole record each time and fill the appropriate value according column mapping. |
|
not sure what you mean? dictionaries do not have an order as I understand it (or should not) So I would see the list being created with something like default_blank = None
ignore_extra_headers = False
data = {"fruit": "Apple", "tastiness": "very tasty"}
headers = ["fruit", "tastiness"]
new_row = []
if not set(data).issubset(set(headers)) and ignore_extra_headers == False:
raise ValueError("Extra headers found in data")
for key in headers:
if key not in data and default_blank is None:
raise ValueError(f"No key <{key}> found in data")
else:
new_row.append(default_blank)
continue
new_row.append(data[key])
# new_row is ["Apple", "very tasty"] |
|
I agree with your code @alifeee , small detail here, remember that so far we need to wrap the above code in a |
|
yes. my example, effectively, was an implementation of I think slowness will not be a problem. We will see if it is after making the code. |
|
alright then, let's put it in the next milestone. |
|
Is there an efficient way to getting the last row of a data range? Because we would have to call |
if you already know the range yes, if you don't know the range, like getting the last row in the worksheet without knowing the height of the worksheet -> then no you don't.
not necessarily, depends what we want the it to do 🙃
This can be improved by adding an extra argument:
or this can be done using the API this might be the easiest solution, though the extra input starting_row allow a user to choose where to put the data too. |
|
If a person is using set_records, they are probably appending the data range, rather than the creating the first row. I have implemented a basic solution locally. def set_records(self, rows: List[Dict[str, Any]]) -> Response:
cols = self.column_headers
insert_rows = []
for row in rows:
insert_row = []
for col in cols:
insert_row.append(row[col])
insert_rows.append(insert_row)
url = SPREADSHEET_VALUES_APPEND_URL % (
self.spreadsheet_id,
f"'{self.title}'!A1",
)
resp = self.append_rows(
insert_rows,
value_input_option=ValueInputOption.user_entered,
)
return resp
def set_record(self, row: Dict[str, Any]) -> None:
self.set_records([row])I also implemented a getter/setter for 'column_headers'. if client is None or not isinstance(client, HTTPClient):
raise RuntimeError(
"""Missing HTTP Client, it must be provided with a
valid instance of type gspread.http_client.HTTPClient .
Please allocate new Worksheet object using method like:
spreadsheet.get_worksheet(0)
"""
)
self.spreadsheet_id = spreadsheet_id
self.client = client
self._properties = properties
# kept for backward compatibility - publicly available
# do not use if possible.
self._spreadsheet = spreadsheet
+ self._column_headers = []
+
+ @property
+ def column_headers(self) -> List[str]:
+ if not self._column_headers:
+ self._column_headers = self.row_values(1)
+ return self._column_headers
+
+ @column_headers.setter
+ def column_headers(self, value: List[str]) -> None:
+ self._column_headers = value
# ...
def get_all_records(
self,
head: int = 1,
expected_headers: Optional[List[str]] = None,
value_render_option: Optional[ValueRenderOption] = None,
default_blank: str = "",
numericise_ignore: Iterable[Union[str, int]] = [],
allow_underscores_in_numeric_literals: bool = False,
empty2zero: bool = False,
) -> List[Dict[str, Union[int, float, str]]]:
"""Returns a list of dictionaries, all of them having the contents of
the spreadsheet with the head row as keys and each of these
dictionaries holding the contents of subsequent rows of cells as
values.
This method uses the function :func:`gspread.utils.to_records` to build the resulting
records. It mainly wraps around the function and handle the simplest use case
using a header row (default = 1) and the the reste of the entire sheet.
.. note::
for any particular use-case, please get your dataset, your headers
then use the function :func:`gspread.utils.to_records` to build the records.
Cell values are numericised (strings that can be read as ints or floats
are converted), unless specified in numericise_ignore
:param int head: (optional) Determines which row to use as keys,
starting from 1 following the numeration of the spreadsheet.
:param list expected_headers: (optional) List of expected headers, they must be unique.
.. note::
returned dictionaries will contain all headers even if not included in this list
:param value_render_option: (optional) Determines how values should
be rendered in the output. See `ValueRenderOption`_ in
the Sheets API.
:type value_render_option: :class:`~gspread.utils.ValueRenderOption`
:param str default_blank: (optional) Determines which value to use for
blank cells, defaults to empty string.
:param list numericise_ignore: (optional) List of ints of indices of
the columns (starting at 1) to ignore numericising, special use
of ['all'] to ignore numericising on all columns.
:param bool allow_underscores_in_numeric_literals: (optional) Allow
underscores in numeric literals, as introduced in PEP 515
:param bool empty2zero: (optional) Determines whether empty cells are
converted to zeros when numericised, defaults to False.
Examples::
# Sheet data:
# A B C
#
# 1 A1 B2 C3
# 2 A6 B7 C8
# 3 A11 B12 C13
# Read all rows from the sheet
>>> worksheet.get_all_records()
[
{"A1": "A6", "B2": "B7", "C3": "C8"},
{"A1": "A11", "B2": "B12", "C3": "C13"}
]
"""
entire_sheet = self.get(
value_render_option=value_render_option,
pad_values=True,
)
if entire_sheet == [[]]:
# see test_get_all_records_with_all_values_blank
# we don't know the length of the sheet so we return []
return []
keys = entire_sheet[head - 1]
+ self.column_headers = keys
values = entire_sheet[head:]
if expected_headers is None:
# all headers must be unique
header_row_is_unique = len(keys) == len(set(keys))
if not header_row_is_unique:
raise GSpreadException(
"the header row in the worksheet is not unique, "
"try passing 'expected_headers' to get_all_records"
)
else:
# all expected headers must be unique
expected_headers_are_unique = len(expected_headers) == len(
set(expected_headers)
)
if not expected_headers_are_unique:
raise GSpreadI will add error handling, header validation, docs and testing, but this works. |
|
I am sorry but I disagree with you, we never know how the users will use the feature, some could be interested in inserting values starting at some specific rows. We can enforce the feature to only be able to append values. that's a design choice we need to think first. |
|
I think it would better to have two sets of methods |
that's a very good idea. let's go for this option. |
get_all_records()
return nicely formatted dictionaries (thanks!)
set_record() (new feature)
inset one dictionary to google sheet (values under right heading)
set_records() (new feature)
inset list of dictionaries to google sheet (values under right heading)
The text was updated successfully, but these errors were encountered: