Python Google API writing to Google Sheet

Question

I'm having trouble writing to Google Sheets using python and the Google API. Here is the code:

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

# If modifying these scopes, delete your previously saved credentials
# at ~/.credentials/sheets.googleapis.com-python-quickstart.json
SCOPES = 'https://www.googleapis.com/auth/spreadsheets'
#this scope allows reading and writing to the sheet
CLIENT_SECRET_FILE = 'client_secret.json'
APPLICATION_NAME = 'Google Sheets API Python Quickstart'


def get_credentials():
"""Gets valid user credentials from storage.

If nothing has been stored, or if the stored credentials are invalid,
the OAuth2 flow is completed to obtain the new credentials.

Returns:
    Credentials, the obtained credential.
"""
    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():
"""Shows basic usage of the Sheets API.

Creates a Sheets API service object and prints the names and majors of
students in a sample spreadsheet:

https://docs.google.com/spreadsheets/d/\
    1ksrW3mUJvPJkmv1HZyWC9Ma1Fbe1cX0CDegSjW2yIAY
"""
    credentials = get_credentials()
    http = credentials.authorize(httplib2.Http())
    discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?'
                'version=v4')
    service = discovery.build('sheets', 'v4', http=http,
                          discoveryServiceUrl=discoveryUrl)

    spreadsheetId = '1ksrW3mUJvPJkmv1HZyWC9Ma1Fbe1cX0CDegSjW2yIAY'
    #see also https://developers.google.com/sheets/api/guides/values
    #https://developers.google.com/sheets/api/samples/writing
    PUT https://sheets.googleapis.com/v4/spreadsheets/\
    spreadsheets/d/spreadsheetId/values/\
    sheet1!A1:D5?valueInputOption=USER_ENTERED
        values = {
        "range": "'Main'!A1:D5",
        "majorDimension": "ROWS",
        "values": [
        ["Item", "Cost", "Stocked", "Ship Date"],
        ["Wheel", "$20.50", "4", "3/1/2016"],   #new row
        ["Door", "$15", "2", "3/15/2016"],
        ["Engine", "$100", "1", "30/20/2016"],
        ["Totals", "=SUM(B2:B4)", "=SUM(C2:C4)", "=MAX(D2:D4)"]
        ],
    }
    result = service.spreadsheets().values().update(
    spreadsheetId=spreadsheet_id, range=range_name,
    valueInputOption=value_input_option, body=body).execute()


if __name__ == '__main__':
    main()

I have quickstart.py working retrieving data from my sheet. https://developers.google.com/drive/v3/web/quickstart/python

The sheet is clear.

The error message is:

PUT https://sheets.googleapis.com/v4/spreadsheets/spreadsheets/\
d/spreadsheetId/values/sheet1!A1:D5?valueInputOption=USER_ENTERED
            ^

SyntaxError: invalid syntax

I linked the docs in your last question. I can't test right now but there is no `PUT` in Python. I don't understand the confusion here because the rest of your code suggests you have a decent grip of the language. — roganjosh, Oct 08 '17 at 19:09
Did my answer work? Would you please tell me about it? That is also useful for me to study. If this works, other people who have the same problem with you can also base your question as a question which can be solved. — Tanaike, Nov 14 '17 at 22:20

score 1 · Answer 1 · answered Oct 09 '17 at 00:21

How about modifying main() to the following script?

You can see the detail information of spreadsheets.values.update at here.

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

    spreadsheet_id = '1ksrW3mUJvPJkmv1HZyWC9Ma1Fbe1cX0CDegSjW2yIAY'
    range_name = 'Main!A1:D5'
    body = {
        "majorDimension": "ROWS",
        "values": [
            ["Item", "Cost", "Stocked", "Ship Date"],
            ["Wheel", "$20.50", "4", "3/1/2016"],  # new row
            ["Door", "$15", "2", "3/15/2016"],
            ["Engine", "$100", "1", "30/20/2016"],
            ["Totals", "=SUM(B2:B4)", "=SUM(C2:C4)", "=MAX(D2:D4)"]
        ],
    }
    result = service.spreadsheets().values().update(
        spreadsheetId=spreadsheet_id,
        range=range_name,
        body=body,
        valueInputOption='USER_ENTERED'
    ).execute()
    print(result)

Python Google API writing to Google Sheet

1 Answers1

Linked