Google Sheets API Python batchUpdate & Insert Note

Question

I am working on a Python script that updates the next row on a Google Sheet using spreadsheets.values.batchUpdate. All of the values in my request body are posting correctly except for the last value in my array, which I want to be a note in the cell. I found this article which did not seem to solve my problem. Link If anyone has any additional insight on how to add a note to a spreadsheets.values.batchUpdate that would be very helpful. I want the note to post to the '' in the first values array.

My function that posts the values is:

   sheet = service.spreadsheets()
   result = sheet.values().get(spreadsheetId=spreadsheetId, range='Sheet1!A1:A').execute()
   values = result.get('values', [])
   new_row = str(len(values)+1)
   data_range = 'A{0}:J{0}'.format(new_row)
   assignment_range = 'Q{0}'.format(new_row)

   range_body_values = {
       'value_input_option': 'USER_ENTERED',
       'data': [
       {
           'majorDimension': 'ROWS',
           'range': data_range,
           'values': [
               [form_values['date'], form_values['name'], form_values['email'], form_values['manufacturer'],
               form_values['generation'], form_values['bore_diameter'], form_values['thickness'], 
               form_values['power_added'], '']
           ]
       },
       {
           'majorDimension': 'ROWS',
           'range': assignment_range,
           'values': [
               ['Unassigned']
           ]
       }
   ]}

   request = service.spreadsheets().values().batchUpdate(spreadsheetId=spreadsheetId, body=range_body_values)
   response = request.ex```

score 1 · Accepted Answer · answered Mar 22 '21 at 12:03

The linked question doesn't have any accepted answer and there's a reason for that: the provided answer doesn't work.

In Google Drive v4 there are multiple batchUpdate on different levels. You are using spreadsheets.values.batchUpdate (see docs) but to set a comment you need to use spreadsheets.batchUpdate (see docs).

Basically you need to add another call after the one you already make setting the comment. It would look something like this:

def main():
    # [...]

    setNote = {
        "updateCells": {
            "range": {
                "sheetId": SHEET_ID,
                "startRowIndex": new_row-1,
                "endRowIndex": new_row,
                "startColumnIndex": 8,
                "endColumnIndex": 9
            },
            "rows": [
                {
                    "values": [
                        {
                            "note": "Here is your note to be added"
                        }
                    ]
                }
            ],
            "fields": "note"
        }
    }

    request = service.spreadsheets().batchUpdate(
        spreadsheetId=spreadsheetId, body={"requests": [setNote]})
    response = request.execute()

The sheet ID is the part after #gid= on the url when you have the sheet selected.

References

Have you tried this? Does this work for you? – Martí Mar 25 '21 at 12:34 — Martí, Mar 25 '21 at 12:34

Google Sheets API Python batchUpdate & Insert Note

1 Answers1

References