Append a list in Google Sheet from Python

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 

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.

You can use pandas + gspread to export your list as single-column dataframe.

import pandas as pd
import gspread
from gspread_dataframe import set_with_dataframe
from oauth2client.client import GoogleCredentials as GC
my_list = ['apple', 'banana', 'orange']
df_my_list = pd.DataFrame(data=my_list[1:], columns=[my_list[0]])
gc = gspread.authorize(GC.get_application_default())
doc = gc.open_by_key(document_id)
document_id = # YOUR DOCUMENT ID
worksheet_name = 'my_list'
# Try to update current worksheet, 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_my_list, resize=True)

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

This code appends a list of lists to each column in google sheets. The my_list as data is a 2-dimensional array.

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


class spreadSheetLogger:
    def __init__(self, sheetName):
        SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
        credentials = config_file.get_google_credentials()
        # The ID and range of a sample spreadsheet.
        SAMPLE_SPREADSHEET_ID = '################'
        SAMPLE_RANGE_NAME = '{}!A:A'.format(sheetName, "DefaultName")
        service = build('sheets', 'v4', credentials=self.creds)
        sheet = self.service.spreadsheets()

    def spreadLogger(self, message, requestJson, timestamp)
        my_list = [[message], [str(timestamp)], [str(requestJson)]]
        body = {
            "majorDimension": "COLUMNS",
            "values": my_list
        }

        request = self.service.spreadsheets().values().append(spreadsheetId=self.SAMPLE_SPREADSHEET_ID,range=self.SAMPLE_RANGE_NAME,valueInputOption='RAW', body=body)
        response = request.execute()
        return "Record Inserted Successfully"

Output :(in GoogleSheets)