Update value in Google Sheet APIv4 with Python

Question

I use the following python code to deal with Google sheet:

#!/usr/bin/python
from __future__ import print_function
import httplib2
import os

from apiclient import discovery
from oauth2client import client
from oauth2client import tools
from oauth2client.file import Storage

try:
    import argparse
    flags = argparse.ArgumentParser(parents=[tools.argparser]).parse_args()
except ImportError:
    flags = None

SCOPES = 'https://www.googleapis.com/auth/spreadsheets'
CLIENT_SECRET_FILE = 'client_secret.json'
APPLICATION_NAME = 'Google Sheets API Python Quickstart'


def get_credentials():

    home_dir = os.path.expanduser('~')
    credential_dir = os.path.join(home_dir, '.credentials')
    if not os.path.exists(credential_dir):
        os.makedirs(credential_dir)
    credential_path = os.path.join(credential_dir,
                                   'sheets.googleapis.com-python-quickstart.json')

    store = Storage(credential_path)
    credentials = store.get()
    if not credentials or credentials.invalid:
        flow = client.flow_from_clientsecrets(CLIENT_SECRET_FILE, SCOPES)
        flow.user_agent = APPLICATION_NAME
        if flags:
            credentials = tools.run_flow(flow, store, flags)
        else: # Needed only for compatibility with Python 2.6
            credentials = tools.run(flow, store)
        print('Storing credentials to ' + credential_path)
    return credentials

def main():
    credentials = get_credentials()
    print("get_credentials DONE")
    http = credentials.authorize(httplib2.Http())
    discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?'
                    'version=v4')
    service = discovery.build('sheets', 'v4', http=http,
                              discoveryServiceUrl=discoveryUrl)

    spreadsheetid = '1tMtwIJ1NKusQRMrF0FnV6WVaLJ1MUzun-p_rgO06zh0'
    rangeName = "QQ!A1:A5"

    values = [
        [
            500,400,300,200,100,
        ],
    ]

    Body = {
    'values' : values,
    }

    result = service.spreadsheets().values().update(
    spreadsheetId=spreadsheetid, range=rangeName,
    valueInputOption='RAW', body=Body).execute()

    print("Writing OK!!")

    result = service.spreadsheets().values().get(
        spreadsheetId=spreadsheetid, range=rangeName).execute()
    values = result.get('values', [])

    if not values:
        print('No data found.')
    else:
        print('Name :')
        for row in values:
            # Print columns A and E, which correspond to indices 0 and 4.
            print('%s' % (row[0]))


if __name__ == '__main__':
    main()

After I run the code:

Traceback (most recent call last):
  File "google-sheet.py", line 100, in <module>
    main()
  File "google-sheet.py", line 82, in main
    valueInputOption='RAW', body=Body).execute()
  File "/usr/local/lib/python2.7/dist-packages/oauth2client/_helpers.py", line 133, in positional_wrapper
    return wrapped(*args, **kwargs)
  File "/usr/local/lib/python2.7/dist-packages/googleapiclient/http.py", line 840, in execute
    raise HttpError(resp, content, uri=self.uri)
googleapiclient.errors.HttpError: <HttpError 400 when requesting https://sheets.googleapis.com/v4/spreadsheets/1tMtwIJ1NKusQRMrF0FnV6WVaLJ1MUzun-p_rgO06zh0/values/QQ%21A1%3AA5?alt=json&valueInputOption=RAW returned "Requested writing withinrange [QQ!A1:A5], but tried writing to column [B]">

But if I only read the value, it works perfectly. I have found a lot information in Google. No related helpful information about it.

After Sam Berlin's help,
just put the body content:

Body = {
'values' : values,
'majorDimension' : 'COLUMNS',
}

And it works perfect!!

Answer 1

The error message states the problem: Requested writing withinrange [QQ!A1:A5], but tried writing to column [B].

You are writing more data than your range says it wants to write, so the server is failing rather than letting you accidentally overwrite other data.

To fix, either increase the requested range or write only data within it.

Edit:. Based on rereading your code, it looks like you want to write a single column. By default, the major input dimension is "row". That is, [[1,2],[3,4]] puts 1 in A1, 2 in B1, 3 in A2 and 4 in B2. You can fix your input by either specifying the data that way, e.g [[1],[2],[3]] etc.. or by changing the majorDimension parameter to COLUMNS.