Can I use the Google Spreadsheet API to add a comment to a cell?

Question

I am looking at the CellEntry API (https://developers.google.com/gdata/javadoc/com/google/gdata/data/spreadsheet/CellEntry) to see how I can add comments (and, ideally, notes) to a cell but I don’t see anything obvious like "addComment ()".

Does anyone have an idea?

thanks

Answer 1

With google sheet v4 API, you can set a note using spreadsheets.batchUpdate. Javascript sdk example:

var requests = []; requests.push({ "repeatCell": { "range": { "sheetId": yourSheetId, "startRowIndex": 1, "endRowIndex": 2, "startColumnIndex": 0, "endColumnIndex": 1 }, "cell": { note: "Your note" }, "fields": "note" } }); gapi.client.sheets.spreadsheets.batchUpdate({ spreadsheetId: yourDocumentId, requests: requests }).then(function(response) { console.log(response); callback(); }); 

Answer 2

According to Google, this is not yet in the API.

A source

Answer 3

Based on the answer of Lars Gunnar Wick , I will give an example in Python.

The corresponding bit of code is here:

  body = { "requests": [ { "repeatCell": { "range": { "sheetId": 1704890600, # this is the end bit of the url "startRowIndex": 0, "endRowIndex": 1, "startColumnIndex": 0, "endColumnIndex": 1, }, "cell": {"note": "Hey, I'm a comment!"}, "fields": "note", } } ] } result = ( service.spreadsheets() .batchUpdate(spreadsheetId=SAMPLE_SPREADSHEET_ID, body=body) .execute() ) 

You can add a lot of comments by repeating objects within the "requests": [] list "requests": [] .

Some important points to pay attention to:

• "sheetId" is the final number in the URL
• If you want to add values ​​and comments, I think you need to do it in two passes (I would like someone to prove that I am wrong in this!)

---

The following is a complete program that writes a comment:

 import pickle import os.path from googleapiclient.discovery import build from google_auth_oauthlib.flow import InstalledAppFlow from google.auth.transport.requests import Request # If modifying these scopes, delete the file token.pickle. SCOPES = ["*", "https://www.googleapis.com/auth/spreadsheets"] # The ID and range of a sample spreadsheet. SAMPLE_SPREADSHEET_ID = "YOUR SPREADSHEET ID" def main(): """Shows basic usage of the Sheets API. Prints values from a sample spreadsheet. """ creds = None # The file token.pickle stores the user access and refresh tokens, and is # created automatically when the authorization flow completes for the first # time. if os.path.exists("token.pickle"): with open("token.pickle", "rb") as token: creds = pickle.load(token) # If there are no (valid) credentials available, let the user log in. if not creds or not creds.valid: if creds and creds.expired and creds.refresh_token: creds.refresh(Request()) else: flow = InstalledAppFlow.from_client_secrets_file( "credentials.json", SCOPES ) try: creds = flow.run_local_server() except OSError as e: print(e) creds = flow.run_console() # Save the credentials for the next run with open("token.pickle", "wb") as token: pickle.dump(creds, token) service = build("sheets", "v4", credentials=creds) # add a comment body = { "requests": [ { "repeatCell": { "range": { "sheetId": 1704890600, # this is the end bit of the url "startRowIndex": 0, "endRowIndex": 1, "startColumnIndex": 0, "endColumnIndex": 1, }, "cell": {"note": "Hey, I'm a comment!"}, "fields": "note", } } ] } result = ( service.spreadsheets() .batchUpdate(spreadsheetId=SAMPLE_SPREADSHEET_ID, body=body) .execute() ) print("{0} cells updated.".format(result.get("totalUpdatedCells"))) if __name__ == "__main__": main() 

You will need to add your own table id.