socialpoint-labs / sheetfu Goto Github PK

https://github.com/socialpoint-labs/sheetfu/blob/master/documentation/table.rst#get_field_range

Something is not quite right in the example. A line actually using get_field_range should be there but isn't.

A few methods simply stop working when passed a certain number of columns (26).
get_range method just does not work making it difficult to work with data larger than the 26 columns.

Is this something we have encountered before?

https://github.com/socialpoint-labs/sheetfu/blob/master/documentation/usage.rst#get_range_from_a1

get_max_column()
…
max_row = sheet.get_max_column()
…

should be

max_column = sheet.get_max_column()

Range no longer has a commit method, but the examples in the documentation use it and refer to it as "essential."

• https://github.com/socialpoint-labs/sheetfu/blob/master/documentation/usage.rst#get_max_row
  get_max_row get_max_rows

• https://github.com/socialpoint-labs/sheetfu/blob/master/documentation/usage.rst#get_max_column
  get_max_column get_max_columns

I can replicate this error by having data in columns A:AA, but the problem goes away when there is data in columns A:Z.

for column in range(0, self.coordinates.number_of_columns):
..
TypeError: 'float' object cannot be interpreted as an integer

This seems to be caused by

get_max_column()

which returns an int with columns A:Z and a float when there are any columns after Z

The methods are working but it would be handy to actually return the sheets.

For instance, given a spreadsheet object, this is how things are working:

spreadsheet.create_sheets(['new_sheet1', 'new_sheet2'])

new_sheets = [
    spreadsheet.get_sheet_by_name('new_sheet1'), 
    spreadsheet.get_sheet_by_name('new_sheet2')
]

It would be bad ass to simply have to do this.

new_sheets = spreadsheet.create_sheets(['new_sheet1', 'new_sheet2'])

Similar behavior when duplicating a sheet:

spreadsheet.duplicate_sheet(
    new_sheet_name='cloned sheet',
    sheet_name='original sheet'
)

duplicated_sheet = spreadsheet.get_sheet_by_name('cloned sheet') 

... should become

duplicated_sheet = spreadsheet.duplicate_sheet(
    new_sheet_name='cloned sheet',
    sheet_name='original sheet'
)

Currently there appears to be no way to set a #N/A value. This is a very important value that is commonly used. It would be useful to be able to set it for a Table field and also for a data range.

This is not a formula value.

Hi,
Just installed this today, and I'm unable to import sheetfu.

Wondering if I missed something.
I'm running this in a virtual environment with Python 3.6.5

Thanks!

I noticed that setting a cell value (or background colour, or note, etc.) always results in an entry in table.batches, even if you are setting the value to the initial value. This then results in the sheet changing

I can check it manually of course

   if item.get_field_value(score_name) != score_entry['value']:
                item.set_field_value(score_name, score_entry['value'])
    
    table.commit()

but it could be built into the library by caching the initial value, and checking it. You might want this as opt-in option on Table though.

Also it might not be easy to do for background colour. Setting a color like #80FF80 in sheetfu gets stored by sheets as backgroundColor": {"red": 0.5019607843137255, "green": 1.0, "blue": 0.5019607843137255}, so the setting #80FF80 will always look like a change.

For large table (over 10k/20k rows) that has a field with unique value (id, email, etc...), the library still needs to iterate on the list of items, one by one, giving us O(n) performance. In scenarios where we have to a lot of lookup based on fields we know are unique, this leads to disastrous performance.

We could add an optional index field when instantiating the table, which would create an index in a dictionary where key is the unique id, and the value is the table item.

We could then have a method get_item_by_id to retrieve the item using its ID.

table = Table(data_range, index_field="email")
item = table.get_item_by_id("<some unique field>")

This method was in the original Google Apps script version of Sheetfu, and was a killer feature for the library. I think it should be in the Python version as well.

@SP-alex-muelas thoughts?

Perhaps this line 204 should be fixed?
a1 = convert_coordinates_to_a1 (row, column, number_of_row, number_of_column)
a1 = convert_coordinates_to_a1 (row, column, number_of_row, number_of_column, self.name)

on a sample Google Sheet as such:

trying to add_one() row produces the following error message:
googleapiclient.errors.HttpError: <HttpError 400 when requesting https://sheets.googleapis.com/v4/spreadsheets/<redacted spreadsheet ID>:batchUpdate?alt=json returned "Invalid requests[0].updateCells: Range (Sheet1!A3:C3) exceeds grid limits. Max rows: 2, max columns: 3">

is there another method to add blank rows to a sheet?

Adding a "BREAKING CHANGE" text in a commit footer doesn't increase the major version when the semantic release creates a new version tag.

Example: 02a746d

I may be misunderstanding the expected output of get_max_rows() and get_max_columns(). I expected them to return the last row/column with data. The methods are not defined, so it's probably my misunderstanding of what they're intended to do, but I don't understand why it would be useful to report empty rows/columns. Apologies if I've misunderstood the intent.

If the intent was to return non-empty rows/columns, there is an issue. I have a simple spreadsheet, and get_max_rows() returns "990" and get_max_columns returns "26).

My sheet is 3 columns by 10 rows filled with some text and text formulas. My expectation would be the get_max_rows() would return "10", and get_max_columns() would return "3".

from sheetfu import SpreadsheetApp

spreadsheet = SpreadsheetApp("....access_file.json").open_by_id('long_string_id')
sheet = spreadsheet.get_sheet_by_name('test')
max_rows = sheet.get_max_rows()
max_cols = sheet.get_max_columns()
print(max_rows)
print(max_cols)

Output as mentioned above is:

990
26

Is it possible to run a function/macro on a google sheet from Sheetfu API?

Hi, thanks for the library - the API is exactly what I was looking for.

I'm having a problem where my call to set_field_note is actually updating the cell's value (which I am setting a line or two before).

    table = Table(data_range, notes=True)
    for item in table:
        row_name = item.get_field_value('Name')
        row_score_lookup = scores_lookup.get(sheet_name + '|' + row_name, {})
        for score_name, score_entry in row_score_lookup.items():
            item.set_field_value(score_name, score_entry['score'])
            item.set_field_background(score_name, score_to_colour_lookup[score_entry['score']])
            item.set_field_note(score_name, score_entry['note']) # this is setting the cell value
    
    table.commit()

It's a bit odd, but I can't figure out what's going wrong!
I'm using version 1.5.2
Thanks in advance!

Please add functionality to allow setting a formula, especially for a Table object, but also on a range. Currently there is no way to do this.

When range.get_values reads a DATE_TIME cell, you get a floating point number. For example, "4/26/2020 10:56:36" is returned as 43947.45597.

It would be helpful to me and probably a lot of people if range.get_values returned a datetime.datetime object. (I noticed that writing datetimes to gsheets is implemented.)

pygsheets has the same problem with their pygsheets.worksheet.Worksheet.get_values(), but at least I can retrieve cell.format and, if it is DATE_TIME, convert the floating point number to datetime (see https://stackoverflow.com/questions/47508174/convert-google-sheet-date-serial-value-into-human-readable-date)

Lastly, thank you for sheetfu! I haven't used it much yet, but the syntax and docs look great!

In this documentation It seems like I don't need anything to get the index of an item. But in code you actually need the field_name argument. Documentation is wrong? Or I don't understand why I need field_name to get the index of an Item

A known issue with Google API is Http() objects being not thread safe (https://github.com/googleapis/google-api-python-client/blob/main/docs/thread_safety.md).

For my usecase (a multithreaded chat bot interacting with Spreadsheets heavily) it appears as various SSL errors during sheet access and occasional python core dumps.

So far I've done a quick dirty fix which might eventually grow up to PR here, but just wanted to leave it as a note in case you folks know a better way to handle multithreading. Your package is way too convenient to just drop it because of concurrency issues :)

Datetime fields are converted with the datetime_to_serial_number function to integers on writing to the sheet. However, this can be a problem right now when sorting, as python will attempt to compare a datetime object (from a newly added item) with an int object (read from the existing table) and break.

Perhaps datetime objects should be converted to ints when adding them to the table, or they should be read as dates when loading the table (either one of those options would work).

Example of a code that breaks:
table = Table(data_range)
table.add_one({"Date": datetime.now()})
table.commit()

Later...
table = Table(data_range)
table.add_one({"Date": datetime.now()})
table.sort("Date")

the comparison of the previously added Date (an int now) with the newly added Date (datetime) will throw a TypeError