2

I am trying to learn Python and this Google API thing, and I am wondering if cell notes and colors are queryable in gspread or in another form? If so, could someone please point me in the right direction or documentation?

So far I have found two things that look like they can help, but i don't really understand how to adapt any of this to Python, or just what to use.

Google Apps Scripts - Accessing Cell Notes and Comments

https://github.com/burnash/gspread/issues/50

Community
  • 1
  • 1
user3696118
  • 343
  • 3
  • 17

3 Answers3

1

If you've followed the link to the GitHub issue, you see that setting a note is solved in GSpread 3.7, but getting was omitted (or I'm too dense to find it). Since I needed that I wrote my own using the Google service object. Presuming you've set up your service account credentials (same as for gspread), create your service:

from oauth2client.service_account import ServiceAccountCredentials
from googleapiclient import discovery

scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name(filename, scope)
service = discovery.build('sheets', 'v4', credentials=creds)

With service and the spreadsheet id get the note(s):

fields = 'sheets/data/rowData/values/note'
ranges = ["'Sheet1'!A1")]
request = service.spreadsheets().get(spreadsheetId='1IDDMQqAV5t4Rqblahblah', ranges=ranges, fields=fields)
note_json = request.execute()
try:
    note = note_json['sheets'][0]['data'][0]['rowData'][0]['values'][0]['note']  # <-- there's got to be a better way
except KeyError:
    note = None
print(note)

This is independent of the gspread model, so it's not great, but it works. I'd appreciate feedback about doing the note extraction from note_json in a better way.

Bill Gallagher
  • 121
  • 3
0

I think it would be helpful if you first learn how Python works with Google Spreadsheet. Here's the Python Quickstart for Spreadsheets. Make sure you also have Google Data Python Library installed.

There's a getBackground() that might be useful to you. It returns the background color of the top-left cell in the range (i.e., '#ffffff'). Though, try to find the equivalent in Python.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];

var cell = sheet.getRange("B5");
Logger.log(cell.getBackground());

Hope this helps.

ReyAnthonyRenacia
  • 17,219
  • 5
  • 37
  • 56
  • Hi, thank you for your response. My understanding is that the provided link is for Gdata which is Google Sheets v3, and that looking into Google Sheets v4 (Discover Service API)? Is this more or less correct? I have no idea what this means, does it mean you can still use this gdata library or is there something else that needs to be installed? – user3696118 Jun 21 '16 at 16:42
0

When using gspread, both setting and getting notes are supported, at least from 5.7.2 onward. Roughly:

import gspread 

url = "https://docs.google.com/spreadsheets/d/u7s6yehrblah....."
gc = gspread.service_account()
gs = gc.open_by_url(url)
sheet = gs.get_worksheet(0)

note = sheet.get_note("A1")
sheet.insert_note("A1", f"{note}? I don't think so!")

I don't see how to get/update several notes in one go (like sheet.batch_update()) so it is pretty easy to run into the default max of 60 API calls/minute. If you want to prevent that from happening, Bill Gallagher's method (constructing the API call by hand) is the way to go: specifying e.g. ranges=['Sheet1'!A1:Z100] will get you 2600 values for the cost of just one API call.

service.spreadsheets().batchUpdate() lets you update said 2600 values in one call as well. The packing and unpacking of the notes or cell contents can be a bit fiddly, but inspecting the results of request.execute() easily shows the way.

Hans Lub
  • 5,513
  • 1
  • 23
  • 43