Is it possible to use the Google Spreadsheet API to add a comment in 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 could add comments (and ideally notes as well) to a cell, but don't see anything obvious like "addComment()".

Anyone have an idea?

Thanks

Answer 1

With google sheet API v4 you can set a note using spreadsheets.batchUpdate. Example javascript sdk:

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

Building on the answer from Lars Gunnar Vik, here's an example in Python.

The pertinent 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 lots of comments by repeating the objects inside the "requests": [] list.

Some important bits to note are:

• "sheetId" is the end number in the url
• If you want to add values, and comments, I think you need to do that in two passes (I'd like someone to prove me wrong on that!)

---

Below is a full 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's 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'll need to add in your own spreadsheet ID

Answer 3

According to Google, it's not in the API yet.

Source