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.

Answer 4

You could try something like this:

credentials = GoogleCredentials.get_application_default()
service = build('sheets', 'v4', credentials=credentials)
spreadsheet_id = "give the spreadsheet ID of the sheet you want to append to"
range_name = "specify the range you are looking at eg: A1:B1"

values = [
   [list of cell values per row]
   [additional rows of data]
]

body = {
'values': values
}

result = service.spreadsheets().values().update(
    spreadsheetId=spreadsheet_id, range=range_name,
    valueInputOption=value_input_option, body=body).execute()

from the docs:

Updates require a valid ValueInputOption parameter (for singular updates, this is a required query parameter; for batch updates, this parameter is required in the request body). The ValueInputOption controls whether input strings are parsed or not, as described in the following table:

RAW The input is not parsed and is simply inserted as a string, so the

input "=1+2" places the string "=1+2" in the cell, not a formula. (Non-string values like booleans or numbers are always handled as RAW.)

USER_ENTERED The input is parsed exactly as if it were entered

into the Google Sheets UI, so "Mar 1 2016" becomes a date, and "=1+2" becomes a formula. Formats may also be inferred, so "$100.15" becomes a number with currency formatting.

Answer 5

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.

Answer 6

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)