Add ability to add/remove comments and notes for cells

[archeg]

It would be really nice to be able to work with comments or notes via gspread. To be honest, I have doubts whether this is possible with current Google API, but I never worked with it before so I could be wrong.

If you have any idea, how this possibly could be done, but this feature doesn't fit your plans for the next release, please write it here - I will try to implement it myself. I need it for my own project, though it is not essential, but surely is nice to have.

And thanks for gspread, it is very cool :)

Best Regards,
Iurii

[burnash]

Hi Iurii! Yeah, it'd be great to add comments to gspread. But as for now I don't see any support for this from Google API. It is still very limited.

I've seen one trick to overcome API limitations in node-spreadsheets. Look at action9.js. It's a bit outdated and I'm not sure if it works. It looks like it uses browser API so it also may not meet Google's ToS.

[archeg]

Hi burnash,

Sorry I've been very busy and only now found enough time to go through all of this.

Here is what I've managed to do: https://gist.github.com/archeg/5652752
This code part should set a Note on the given cell to the given spreadsheet. It supports working only with first worksheet, but that is not a problem - should be easy to fix. This code example uses the same trick as in action9.js I believe. What am I doing is composing a proper POST request that pastes Note into spreadsheet.

Also I am not using your log in method as I need a bit more auth tokens than you are using, but it is still ClientLogin

I don't think it is doing anything illegal, but you better check that. The way it is done is the same as the official usage of some other Google services, I even took some parts of the code from that examples.

The coolest thing here is that in such way any spreadsheet functionality can be covered, even though it is not supported officially.

Are you interested in integrating this into gspread?

Btw, I am a bit new to working with such web services as Google spreadsheet, and even to Python itself, so let me know if you think something could have been done more easily or it is just done wrong.

Best Regards,
Iurii

[burnash]

Wow, it works! Thank you Iurii, it's really fabulous! I've been very busy too, but I look forward to integrating this into gspread. I've skimmed through the code and it seems ok. So it's just a matter of figuring out how to make it play with current gspread codebase.

[archeg]

I am glad you like it! Do you mind if I try to contribute to gspread a bit? This part of the code or some other issues. I was learning Python for some time, and I think it should be a great way for me to strengthen my skills in this language. For now I do not have enough free time, but I expect myself to have some free late evenings in a week or two that I could spend in coding.

[burnash]

Not only I won't mind, I believe gspread can be better with your contribution. Python is cool and mastering it was one of my points in starting gspread.

So go ahead and do not hesitate to ask any questions either through GitHub or email.

[msuozzo]

Comments are supported by the Drive API so that would be an option. I couldn't find any reference to notes in any of the API documentation but it seems like @archeg already presented a workable way of doing this.

[abrkn]

https://www.bountysource.com/issues/1141847-add-ability-to-add-remove-comments-and-notes-for-cells

[abdulwahid24]

I would love to have this feature in gspread itself.

[hexvolt]

Not sure how it was before, but SpreadSheet API v4 does support this.
This is adapted to gspread https://gist.github.com/hexvolt/4eee9435b9398a17ddd1588ad6d830fe :

from gspread.urls import SPREADSHEETS_API_V4_BASE_URL


def insert_note(worksheet: Worksheet, label, note):
    """
    Insert note into the google worksheet for a certain cell.
    
    Compatible with gspread.
    """
    spreadsheet_id = worksheet.spreadsheet.id
    worksheet_id = worksheet.id

    row, col = a1_to_coords(label)      # [0, 0] is A1

    url = f"{SPREADSHEETS_API_V4_BASE_URL}/{spreadsheet_id}:batchUpdate"
    payload = {
        "requests": [
            {
                "updateCells": {
                    "range": {
                        "sheetId": worksheet_id,
                        "startRowIndex": row,
                        "endRowIndex": row + 1,
                        "startColumnIndex": col,
                        "endColumnIndex": col + 1
                    },
                    "rows": [
                        {
                            "values": [
                                {
                                    "note": note
                                }
                            ]
                        }
                    ],
                    "fields": "note"
                }
            }
        ]
    }
    worksheet.spreadsheet.client.request("post", url, json=payload)

[cryptoteatime]

Replying in hopes it bumps this issue, since the API has been updated. Would be nice to have this as a simple add_note function to a cell object.

To anyone else who may be discovering this, you can accomplish this with the latest version of gspread and the batch_update method.

Your supplied dictionary parameter will be the same as the described parameter payload in hexvolts solution.

[burnash]

@cryptoteatime Thanks for bumping this.

If anyone is interested in implementing this I'm open for a PR.

[burnash]

@hexvolt would you be still interested?

[TomOnGit]

I won't be able to implement, but would very much like to be able to add/edit notes and comments via Gspread.

[lavigne958]

Hi guys I would be happy to have a look at it. It seems that what is missing is adapting the code snipet above to fit in gpsread then open a PR ?

[burnash]

@lavigne958 It's true. Please feel free to submit a PR