Append a list in Google Sheet from Python

Question

I have a list in Python which I simply want to write (append) in the first column row-by-row in a Google Sheet. I'm done with all the initial authentication part, and here's the code:

credentials = GoogleCredentials.get_application_default()
service = build('sheets', 'v4', credentials=credentials)

I do not have any clue as to how I could possibly do this in an easy way.

Answer 1

How about this sample script? This sample appends list to column A. The list as data is 2 dimensional array. Please be careful for this. In order to use this script, please enable Sheet API v4 at API console.

Sample script :

credentials = GoogleCredentials.get_application_default()
service = build('sheets', 'v4', credentials=credentials)

list = [["valuea1"], ["valuea2"], ["valuea3"]]
resource = {
  "majorDimension": "ROWS",
  "values": list
}
spreadsheetId = "### spreadsheet ID"
range = "Sheet1!A:A";
service.spreadsheets().values().append(
  spreadsheetId=spreadsheetId,
  range=range,
  body=resource,
  valueInputOption="USER_ENTERED"
).execute()

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

If this sample was not useful for you, I'm sorry.

Answer 2

Based on Google's official quickstart + @Tanaike's answer, I suggest the following example on how to append rows to a Sheet document:

---

Take the spreadsheet id from the URL:

Script:

import os
import pickle
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request

SHEETS_READ_WRITE_SCOPE = 'https://www.googleapis.com/auth/spreadsheets'
SCOPES = [SHEETS_READ_WRITE_SCOPE]


def main():
    spreadsheet_id = '1TfWKWaWypbq7wc4gbe2eavRBjzuOcpAD028CH4esgKw'  # this is part of the url of google
    rows = [
        ["Hello World", "שלום עולם ינעל העולם", ":)"],
        ["Hello"],
        ["World"]
    ]

    # -----------

    credentials = get_or_create_credentials(scopes=SCOPES)  # or use GoogleCredentials.get_application_default()
    service = build('sheets', 'v4', credentials=credentials)
    service.spreadsheets().values().append(
        spreadsheetId=spreadsheet_id,
        range="Sheet1!A:Z",
        body={
            "majorDimension": "ROWS",
            "values": rows
        },
        valueInputOption="USER_ENTERED"
    ).execute()


# Source: https://developers.google.com/sheets/api/quickstart/python
def get_or_create_credentials(scopes):
    credentials = None
    if os.path.exists('token.pickle'):
        with open('token.pickle', 'rb') as token:
            credentials = pickle.load(token)
    if not credentials or not credentials.valid:
        if credentials and credentials.expired and credentials.refresh_token:
            credentials.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file('credentials.json', scopes)
            credentials = flow.run_local_server(port=0)
        with open('token.pickle', 'wb') as token:
            pickle.dump(credentials, token)
    return credentials


if __name__ == '__main__':
    main()

• Remember to change - spreadsheet_id = "<your spreadsheet document id>"

---

Result:
This is how it looks like if you'll run the script multiple consecutive times

💡 Do follow Google's official quickstart and grant yourself API permissions + install these packages:

pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib 

Answer 3

You can convert your cell_values to pandas dataframe and then export it to Google Sheets using gspread_dataframe.set_with_dataframe. It shouldn't cause any quota issues as it sends a full dataframe at once.

import pandas as pd
import gspread
from gspread_dataframe import set_with_dataframe
from oauth2client.client import GoogleCredentials as GC
df = # YOUR DATAFRAME
document_id = # YOUR DOCUMENT ID
worksheet_name = # YOUR WORKSHEET NAME
gc = gspread.authorize(GC.get_application_default())
doc = gc.open_by_key(document_id)
# Update existing spreadsheet or create a new one
try: 
  sheet = doc.worksheet(worksheet_name)
except:
  sheet = doc.add_worksheet(worksheet_name, rows=1, cols=1)
set_with_dataframe(sheet, df, resize=True)

For more information about Python to Google Sheets, you can check out this article.