How to append data in a GoogleSheet using Python

How to append values to a Google Spreadsheet with Python.

1. Follow the quickstart to get set up. Make sure you follow all the steps exactly! You will need to do this for every project you start that uses the API so you might as well follow the instructions here. Make sure you get the expected output before moving on!

2. You can then modify the quickstart to make getting the service a separate function:

def getService():
    creds = None
    if os.path.exists('token.json'):
        creds = Credentials.from_authorized_user_file('token.json', SCOPES)
    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)
            creds = flow.run_local_server(port=0)
        with open('token.json', 'w') as token:
            token.write(creds.to_json())

    return build('sheets', 'v4', credentials=creds)

3. Once you have the service, you can make calls to the Sheets API. For example:

service = getService()
appendValues(service)
            
values = [
    [time, todayMaxProfit, todayMaxLoss, pl, len(orderList), int(orderList[0][4]+orderList[1][4])]
]
    
body = {'values': values}
result = service.spreadsheets().values().append(
    spreadsheetId="13rdolwpUD4h4RTuEgn1QbtgPMpJiZGMFubdh4loAfNQ", range="Sheet1!A1",
    valueInputOption="RAW", body=body).execute()

Note that the format the values has to be in is a two dimensional list.:

[
    [A1, B1, C1],
    [A2, B2, C2]
]

Using the append method, will simply add the row, as-is to the end of the sheet. The append takes a few arguments:

• spreadsheetId - the id of the spreadsheet that you want to append values to
• range - the rough range where the data is found. The Sheets API will try to evaluate the data in the Sheet and guess where the last row is. Usually, if you just have a table filled with data from A1 to the bottom, you can just leave this as A1, or maybe C5, if you have headers or spaces. The idea is to point the API to the collection of data that you want to append to.
• valueInputOption - this can usually be left as "RAW" which just inserts the data as it is passsed.
• body, where you have your two dimensional list of data.

References

• Guide to Reading and Writing Values
• Quickstart
• append